VBA String Spaces: Preferred Method?

Question

When concatenating strings, there are multiple valid methods of inserting spaces.

Space Function:

Print "A" & Space(1) & "B"
A B

VS

Quotes:

Print "A" & " " & "B"
A B

VS

Chr:

Print "A" & Chr(32) & "B"
A B

---

All three methods yield the same result. The only place where I found Space beneficial was when using a variable for the number of spaces.

Space( <expression of number - len(var)> )

Can anyone provide any insight on why one method is better than another?

Solution 1

As suggested, they all yield the same result when compiled. There might be situational benefit such as:

1) Space() - Good when multiple space is needed.
Space(100) is better than chr(32) & chr(32) & chr(32) & ...

2) " " - Visual expression that is easy to understand, esp for new vba learner
EDIT: Also faster than a method call, thanks to Blackhawk to point out

3) chr() - character expression that could be used to print particular character to say a text file. E.g. printing Enter/Return with chr(10). I don't see any obvious benefit for print space however

Solution 2

Using a string literal should be the fastest of the bunch because the other two are functions. As you pointed out, it's easy to understand, but as @Alex pointed out Space() is definitely better when you need more than a single space. I would always prefer the literal or space() over chr(32). chr(32) is pretty obscure for the maintainer to grok.

There is another option you didn't consider though. Creating a constant.

Const space As String = " "

This has all of the readability of space(1) with the performance of the literal " ". As an added benefit, there is only truly one instance of the constant in memory, where as the literal will show up once for each instance.

---

If you do decide to use chr(32), it would be beneficial to use to the string version of it: chr$(32). The regular version returns a variant, whereas the string version returns a string. Variants have some extra overhead that can be avoided here.

Solution 3

Maybe there is a performance difference? You can test it yourself! See the the code at this answer related to the performance counters to get a useful VBA timer class.

Here's an example of this type of timing in (Excel-ish) VBA:

Option Explicit

Sub DoTimings()
    Dim oTimer As New CTimer
    Dim fTimer(3) As Double

    Dim iiter As Long
    Const ITERATIONS As Long = 10000

    oTimer.StartCounter
    For iiter = 1 To ITERATIONS
        Debug.Print "A" & Space(1) & "B"
    Next
    fTimer(1) = oTimer.TimeElapsed

    oTimer.StartCounter
    For iiter = 1 To ITERATIONS
        Debug.Print "A" & " " & "B"
    Next
    fTimer(2) = oTimer.TimeElapsed

    oTimer.StartCounter
    For iiter = 1 To ITERATIONS
        Debug.Print "A" & Chr(32) & "B"
    Next
    fTimer(3) = oTimer.TimeElapsed

    Range("A1").Value = "Spaces: "
    Range("B1").Value = fTimer(1)
    Range("A2").Value = "Quotes: "
    Range("B2").Value = fTimer(2)
    Range("A3").Value = "Chr: "
    Range("B3").Value = fTimer(3)

End Sub

When I run that on my machine, the differences are negligible or dominated by other factors (most notably the I/O to whatever you are printing to). So it is pretty safe to say that there isn't any obvious performance difference, and you can use whatever makes the most sense from a maintainability perspective.