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?

I'm pretty sure they all come down to the same thing when compiled; they only are different representations. For example, " " is a string (list of caracters containing only one space); whereas Chr(32) is the Char representation of a space. — Bernard Saucier, Oct 27 '14 at 19:33

Alex · Accepted Answer · 2014-10-27T21:43:48.630

8

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

edited Oct 27 '14 at 21:43

answered Oct 27 '14 at 19:44

Alex

1,632
1
12
28

1

@RHDxSPAWNx, If all you're doing is writing an immediate string value with a few spaces, "A B" makes the most sense - VBA is going to parse the code anyway, so a few extra spaces in code is probably faster than a method call such as `space()` or `chr()`. `Space()` is often used to pre-allocate memory in a string for use as a buffer, for example in Windows API calls. `Chr()` is generally used, as Alex implies, for getting nonprintable characters into a string, such as CR or LF. I've used it to match non-breaking spaces in webpage text, chr(160). – Blackhawk Oct 27 '14 at 21:01

score 2 · Answer 2 · edited May 23 '17 at 10:26

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.

score 1 · Answer 3 · edited May 23 '17 at 10:26

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.

This won't work without the `CTimer` class, whatever that is. — RubberDuck, Oct 27 '14 at 21:07
Yes, @RubberDuck... That code is available at the link in the post: http://stackoverflow.com/questions/198409/how-do-you-test-running-time-of-vba-code — jeffrey_t_b, Oct 27 '14 at 21:14

VBA String Spaces: Preferred Method?

3 Answers3