4 
For Each cell In wb.Sheets("RP Analysis").Range("F5:F" & lastRow)
    structure = "Layer " & WorksheetFunction.RoundDown(cell.Value, 2) & ": " & WorksheetFunction.RoundDown(cell.Offset(0, 2).Value / 1000000, 2) & " xs " & WorksheetFunction.RoundDown(cell.Offset(0, 3).Value / 1000000, 2) & " attaches at "
    RMS = RMS & structure & WorksheetFunction.RoundDown(cell.Offset(0, 10).Value, 2) & "m and exhausts at " & WorksheetFunction.RoundDown(cell.Offset(0, 11).Value, 2) & "m" & vbLf
    AIR = AIR & structure & WorksheetFunction.RoundDown(cell.Offset(0, 6).Value, 2) & "m and exhausts at " & WorksheetFunction.RoundDown(cell.Offset(0, 7).Value, 2) & "m" & vbLf
Next cell

For Each cell In wb.Sheets("RP Analysis").Range("A9:A" & 19)
        gucurve = gucurve & cell.Value & ":-   " & Format(cell.Offset(0, 2).Value / cell.Offset(0, 1).Value, "Percent") & vbLf
Next cell

TextBox1.Value = "RP years    RMS/AIR difference" & vbLf & gucurve & vbLf & "AIR" & vbLf & AIR & vbLf & "RMS" & vbLf & RMS

This produces 

  Layer 1: 25 xs 50 attaches at 8.16m and exhausts at 10.4m
  Layer 2: 100 xs 75 attaches at 10.4m and exhausts at 20.15m
  Layer 3: 44 xs 175 attaches at 20.15m and exhausts at 24.96m
  Layer 4: 144 xs 175 attaches at 20.15m and exhausts at 34.86m

I want it to produce

  Layer 1: 25 xs  50 attaches at  8.16m and exhausts at  10.4m
  Layer 2:100 xs  75 attaches at  10.4m and exhausts at 20.15m
  Layer 3: 44 xs 175 attaches at 20.15m and exhausts at 24.96m
  Layer 4:144 xs 175 attaches at 20.15m and exhausts at 34.86m

So I think I need fixed columns with defined widths with everything right centered. The numbers won't be more than 4 digits ever

How do I go about this?

Improve this question

4 Answers 4

Reset to default
3

You could use Format with the @ symbol to pad and align each value on the right:

Format("123", "@@@@@@@@@@")     ' returns "       123"

Or on the left :

Format("123", "!@@@@@@@@@@")    ' returns "123       "

And by providing the number of characters:

Format("123", String(25, "@"))  ' returns "                      123"
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I love this. Much neater than my approach.
1

One method is to create your own function that returns fixed length strings. The below takes a string and prefixes with as many spaces as needed to hit the required length. Oversized strings are not trimmed, but this would be a simple change if required.

Public Function Pad(ByVal OriginalString As String, ByVal RequiredLength As Integer) As String
' Prefixes the passed string with spaces, to return a fixed width string.

    ' Check padding required.
    If RequiredLength > Len(OriginalString) Then

        ' Required, prefix with spaces.
        Pad = Space(RequiredLength - Len(OriginalString)) & OriginalString
    Else

        ' Padding not required, return original value.
        Pad = OriginalString
    End If
End Function

You would call this function like so:

..."Layer " & Pad(WorksheetFunction.RoundDown(cell.Value, 2), 10) &...

EDIT

@Michael posted a much neater approach. I wanted to rewrite my pad function using his code. At one line; the body of the function is now much easier to debug/follow. I'd forgotten just how flexible VBA's format function is.

Public Function Pad(ByVal OriginalString As String, ByVal RequiredLength As Integer) As String
' Prefixes the passed string with spaces, to return a fixed width string.

    Pad = Format(OriginalString, String(RequiredLength, "@"))
End Function
Improve this answer

2 Comments

I just tried this, although it does set the required amount of spaces it doesnt still line is up since 100 doesnt line up with " 25" as they take up different amount of space
I should have mentioned that the font you use will make a big difference. You must choose a monospaced font for the above technique to work. Alternatives include: using the tab character (ASCII 9) or a series of textboxes, each that displays part of the text.
1

I've found that the easiest code to maintain for column formatting is just building the output with fixed width strings. They will default to left aligned if you assign with = or use LSet. You can right align them by using RSet. Also keep in mind that a fixed length string will truncate if you try to assign a string that is longer than it can hold.

Example:

Private Function ToColumns(layer As Long, percent As Long, xs As Long, attach As Double, _
                           exhaust As Double) As String
    Dim col1 As String * 1      'Change the widths here to adjust your columns.
    Dim col2 As String * 3
    Dim col3 As String * 3
    Dim col4 As String * 5
    Dim col5 As String * 5

    RSet col1 = layer
    RSet col2 = percent
    RSet col3 = xs
    RSet col4 = Format$(attach, "#.##")
    RSet col5 = Format$(exhaust, "#.##")

    ToColumns = "Layer " & col1 & ":" & _
                col2 & " xs " & _
                col3 & " attaches at " & _
                col4 & "m and exhausts at " & _
                col5 & "m"
End Function

Usage:

Debug.Print ToColumns(1, 25, 50, 8.16, 10.4)
Debug.Print ToColumns(2, 100, 75, 10.4, 20.15)

Output:

Layer 1: 25 xs  50 attaches at  8.16m and exhausts at  10.4m
Layer 2:100 xs  75 attaches at  10.4m and exhausts at 20.15m

Note that as mentioned by other posters, if you are displaying this in UI you'll need a monospace font.

Improve this answer

5 Comments

Layer 1: 25 xs 50 attaches at 6.98m and exhausts at 8.35m Layer 2: 100 xs 75 attaches at 8.35m and exhausts at 13.5m Layer 3: 44 xs 175 attaches at 13.5m and exhausts at 15.85m Layer 4: 144 xs 175 attaches at 13.5m and exhausts at 21.43m Layer 5: 148 xs 319 attaches at 21.43m and exhausts at 30.55m Layer 6: 25 xs 50 attaches at 6.98m and exhausts at 8.35m Layer 7: 100 xs 75 attaches at 8.35m and exhausts at 13.5m Layer 8: 40 xs 35 attaches at 6.04m and exhausts at 8.35m
Sorry, but basically my output still isnt working like yours. Dont forget I am putting it into a textbox (not sure if makes a difference). But 25 and 100 stil dont line up, the 25 needs 1 more space in front of it. I guess " 25" isnt the same as "100" for example. (space and 1 digit take up different amount of room"
thanks comintern, I realised I need the monospaced font
@bossman1111 - Did you change your textbox to a monospace font?
@bossman1111 - If the widths aren't working, you can adjust them in the Dim lines (actually I typically declare them as module level constants) or remove spaces as needed from the string literals in the concat.
0

Reference: Monospaced font

You need to use a monospaced font, also called a fixed-pitch, fixed-width, or non-proportional font, is a font whose letters and characters each occupy the same amount of horizontal space. This contrasts with variable-width fonts, where the letters and spacings have different widths.

Reference: Microsoft-Supplied Monospaced TrueType Fonts

The only monospaced TrueType fonts shipped by Microsoft are Courier New, which shipped with Windows 3.1, and Lucida Sans Typewriter, which was included in the TrueType Font Pack. All other TrueType fonts included with Windows 3.1 and the TrueType Font Pack are proportional fonts.

Improve this answer

1 Comment

@bossman1111 - you need to do both -- use Monospaced font and padding string with spaces as explained in destination-data's answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.