How to arrange a string to display

0 votes

How can I create a string in Visual Basic that allows specific characters to line up? Let's use the following strings as an example, created over two rows:

"96 x $219.00 = $21,024.00"
"-8 x 45.00 = -360.00"

The "=" indications must line up with the spaces placed to the left of the rightmost integer. In order for the "x" character to line up, I also need spaces to be placed to the left of the center number. A VBA function produced these strings.

Times-Roman Size 8 is the only font I may use (this is a company requirement; there is no haggling allowed). If anyone is worried, the string must also be right justified.

The characters still don't line up after attempting to use Columns.ColumnWidth to extract the character width and compare the widths of repeating a character, say, 10 and then 11 times (I tried other sets of numbers than these), building a function to split a string into characters and sum them, and padding as described. There should be a way to do this in VBA. There are too many tables to fix by hand. The previous posting discussing just padding characters fails to address how they are displayed.

Feb 3, 2023 in Others by Kithuzzz
• 38,010 points

1 answer to this question.

0 votes

It's a bad idea to use spaces to align text (word processing 101). Additionally, it is changing the data, and if I entered a=b into a cell and all of a sudden it became a=b, I would be upset.

It is impossible to align text for a non-proportional typeface by adding spaces. Only a "best attempt" that roughly aligns them will do.

This cannot be done using an Excel (or VBA) built-in function.

I, therefore, did the following for fun. Accept it or reject it.

The text width of a string has no built-in function in VBA. You can discover various attempts if you search online, however the following advice is the most straightforward: Place a label on a user form and format it with the desired font (for example, Times New Roman, 8pt, Non-Bold, Non-Italic) and AutoSize = True and. WordWrap is not true.

This label changes in size when any text is assigned to it, allowing you to see how wide it is. Since the label utilizes a few additional pixels to the left and right, this doesn't exactly return the text's width, but for our purposes, we can ignore that.

Now put the following code into a regular module:

Function GetTextWidth(s As String) As Double
    Static f As UserForm1
    If f Is Nothing Then Set f = New UserForm1
    With f.Label1
        .Font.Name = "Times New Roman"
        .Font.Size = 8
        .Font.Italic = False
        .Font.Bold = False
        .AutoSize = True
        .WordWrap = False
        .Caption = s
        GetTextWidth = .Width
    End With
End Function
answered Feb 3, 2023 by narikkadan
• 63,720 points

Related Questions In Others

0 votes
1 answer
0 votes
0 answers

How to convert a string to an integer in JavaScript?

How do I convert a string to ...READ MORE

May 2, 2022 in Others by Kichu
• 19,050 points
0 votes
1 answer

How to make an error flagging array in VBA and translate all array elements as a string message?

In my opinion, using an array in ...READ MORE

answered Mar 17, 2023 in Others by Kithuzzz
• 38,010 points
0 votes
1 answer

Reversing Text to Columns in Excel/Calc

There are built-in functions that will allow ...READ MORE

answered Nov 15, 2022 in Others by narikkadan
• 63,720 points
0 votes
1 answer

How do I use the Indirect Function in Excel VBA to incorporate the equations in a VBA Macro Function

Try this: Sub Test() Dim str As String: str ...READ MORE

answered Jan 19, 2023 in Others by narikkadan
• 63,720 points
0 votes
1 answer

Concatenate or TextJoin Same Cell Multiple Times based on Value in another Cell?

Use the REPT function: =REPT("ABC",4) => ABCABCABCABC ...READ MORE

answered Feb 18, 2023 in Others by narikkadan
• 63,720 points
0 votes
1 answer

Retrieve Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
0 votes
1 answer

How to turn a string formula into a "real" formula?

Evaluate might suit: Function Eval(Ref As String) ...READ MORE

answered Jan 5, 2023 in Others by narikkadan
• 63,720 points
0 votes
1 answer

How can a column be set to display multiple spaces and tabs (white space) in a cell?

By entering your "code" in Word and ...READ MORE

answered Jan 19, 2023 in Others by narikkadan
• 63,720 points
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP