How to arrange a string to display

Question

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.

Answer 1

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