Excel VBA - String padding not working

Question

I have a VBA function that returns a lot of information about the computer it's running on. I'd like to format the output neatly, with the titles left-justified and the results all tabbed on a neat line to the right.

Here's what I thought would work, and this is the solution on many sites I've consulted:

AllInfo = _
    "Date/Time Opened:" & Space(33 - Len("Date/Time Opened:")) & Now() & vbCrLf & _
    "Filename:" & Space(33 - Len("Filename:")) & Application.ActiveWorkbook.Name & vbCrLf & _
    "Name in Cell B2:" & Space(33 - Len("Name in Cell B2:")) & Cells(2, 2).Value & vbCrLf & _

But the output, in both a message box and the body of an email, is jagged. The titles are all lined up on the left, of course, but the results aren't lined up nicely.

Can anyone see what I'm doing wrong?

Answer 1

This works:

Sub djfsdf()
    allinfo = _
        "Date/Time Opened:" & Space(33 - Len("Date/Time Opened:")) & Now() & vbCrLf & _
        "Filename:" & Space(33 - Len("Filename:")) & Application.ActiveWorkbook.Name & vbCrLf & _
        "Name in Cell B2:" & Space(33 - Len("Name in Cell B2:")) & Cells(2, 2).Value
    Cells(3, 3) = allinfo


End Sub

with the proper font:

Answer 2

You are not including the string vars in the calculation for the number of spaces to add.

AllInfo = _
    "Date/Time Opened:" & Space(33 - Len("Date/Time Opened:" & Now())) & Now() & vbLf & _
    "Filename:" & Space(33 - Len("Filename:" & Application.ActiveWorkbook.Name)) & Application.ActiveWorkbook.Name & vbLf & _
    "Name in Cell B2:" & Space(33 - Len("Name in Cell B2:" & Cells(2, 2).Value)) & Cells(2, 2).Value & vbLf & _
    (etc)

I've also change your vbCRLF to just vbLF; that may or may not be correct.