Excel VBA String Operators
Concatenate Strings
Excel VBA uses & (ampersand) as the operator to join (concatenate) two strings. Spaces may be inserted between strings by using either the Space(N) function, where N is the number of spaces to insert, or place double quotes around spaces created by typing in the space bar on the keyboard. To insert a double quote in the string, use """" (four double quotes).
Concatenate - join two strings
Sub joinEx()
X = "John" & "Hall"
Msgbox X
End Sub
VBA concatenation will not insert spaces between joined strings unless instructed to do so.
Use double quotes to insert a space between two strings
Sub joinEx()
X = "John" & " " & "Hall"
Msgbox X
End Sub
In this example, double quotes around one space are used to insert a single space between two strings. Additional spaces (using the keyboard space bar) will create more space between the strings.
Use Excel VBA Space(N) to insert space between two strings
Sub joinEx()
X = "John" & space(10) & "Hall"
Msgbox X
End Sub
Another way to insert spaces between strings is by using the Excel VBA function Space(N), where N is any non-negative number. In this example, ten (10) spaces are inserted between the two strings. Space(0) will insert zero (0) spaces between the two, but a call using a negative number triggers an error message and program execution stops. Use of positive, fractional numbers is allowed, but the fraction is rounded to a whole number: 0.5 and less, rounding is down; greater than 0.5, rounding is up.
Join a string and a double quote
Sub joinEx()
X = "John Hall " & """"
Msgbox X
End Sub
A special method is required to include double quotes in the returned string. Use four (4) double quotes to add one (1) double quote to the string operation.
In this example, a space was created between the string and the double quote by including it as part of the initial string. Had the space after Hall been omitted, the result would be John Hall".
Insert a New Line between Strings
Excel VBA provides the operator vbNewLine for inserting a new line character between strings, thus creating vertical strings.
Use Excel VBA vbNewLine to create a vertical string
Sub joinEx()
X = "John" & vbNewLine & "Hall"
Msgbox X
End Sub
In this example, a new line is inserted between the two strings to produce a vertical string as output.
Excel VBA provides four methods of inserting a new line between strings: vbNewLine, vbCrLf, vbLf, and vbCr. Although there are subtle differences internal to the computer, all produce the same effect for the vast majority of cases, so use whichever you are most comfortable with.
Use Excel VBA vbNewLine to show use of fractional number in Space(N)
Sub joinEx()
X = "John" & Space(2) & "Hall"
Y = "John" & Space(2.5) & "Hall"
Z = "John" & Space(3) & "Hall"
X = X & vbNewLine & Y & vbNewLine & Z
Msgbox X
End Sub
Excel VBA Space(N) function will accept positive, fraction numbers. The fractional number will be rounded and a whole number of spaces will then be inserted.
