Concatenation Operators in VBA

by Sola Bode
in VBA Coding Constructs
February 25, 2021

VBA concatenation operators - symbols that instruct the compiler to join two String operands together - are explored herein.

3 minutes read

VBA Operators

VBA operators are special tokens that instruct the compiler to “operate” on values or program identifiers. They are also referred to as program symbols or code elements. Also, the program identifiers they operate on (e.g., addition) must be capable of holding values.

You may have heard of the term operand. These are the values (i.e., literals) or identifiers (e.g., variables and constants) that operators act on. Based on their number of operands, operators are either unary or binary.

Unary VBA operators are operators that only act on a single operand at a time. Binary VBA operators are those that only act on two operands at a time.

VBA supports the following categories of operators:

This article takes an in-depth look at the VBA concatenation operators.

VBA Operator Precedence

Often, an operation involves more than one operator, sometimes of different categories. So, it is worthwhile paying special attention to the order of precedence of the VBA operators. Otherwise, VBA’s default operator precedence applies.

The default precedence is as follows: arithmetic first, then concatenation, followed by comparison, then logical, and finally assignment operators.

Moreover, there is a pre-set precedence for operators within each category. How do you override this default order and remove any ambiguity? Easy, set the preferred precedence with open and close parentheses, ( ).

Concatenation Operators

Concatenation operators join two String operands together. The result is a single expression, with the operand after the operator appended to the operand before it. The table below summarizes the different VBA concatenation operators.

Operator	Description	Return Data Type	Precedence
&	String Concatenation (binary) operator, i.e. ampersand (&), appends two *String* or *String Variant* operands into a *String* expression.	*String, if both operands are String; String Variant, if at least one operand is not a String; Null, if both operands are Null*.	1
+	String Concatenation (binary) operator, i.e. plus (+), appends two *Strings, two String Variants, or one String* and any *Variant* (except *Null* or numeric types) operands into a *String* expression.	*String, if both operands are Strings; String Variant, if both operands are String Variants; String Variant, if one operand is String* and the other is any *Variant* (except *Null* or numeric types); *Null, if either operand is Null*.	1

As shown in the table, both concatenation operators have the same precedence. So, when they both appear together in an expression, evaluation proceeds in the order of appearance from left to right.

Also, enclosing a part of an expression in parentheses causes its evaluation ahead of other parts. Moreover, note that the arithmetic plus (+) operator has precedence over concatenation plus (+).

Concatenation Operators: Ampersand (&)

The sample code below shows the ampersand (&) operator’s use in joining Strings.

Option Explicit

Sub ConcatenationOperators_Ampersand_Example01()
    
    Dim lStr_Result As String, lInt_i As Integer
    
    'two strings
    lStr_Result = "Hello" & " world!"
    Debug.Print lStr_Result
    
    'strings and Null expression
    lStr_Result = "Hello" & vbNullString & "world!"
    '   Null expression is treated as a zero-length string
    Debug.Print lStr_Result
    
    'strings and Empty expression
    lStr_Result = "Dilly" & Empty & "dally."
    '   Empty expression is treated as a zero-length string
    Debug.Print lStr_Result
    
    'strings and non-string
    lInt_i = 20
    lStr_Result = "Welcome to the new roaring " & lInt_i & "s!"
    '   non-string is converted to String Variant before the joining
    Debug.Print lStr_Result
    
End Sub

Option Explicit

Sub ConcatenationOperators_Ampersand_Example01()

Dim lStr_Result As String, lInt_i As Integer

'two strings

lStr_Result = "Hello" & " world!"

Debug.Print lStr_Result

'strings and Null expression

lStr_Result = "Hello" & vbNullString & "world!"

' Null expression is treated as a zero-length string

Debug.Print lStr_Result

'strings and Empty expression

lStr_Result = "Dilly" & Empty & "dally."

' Empty expression is treated as a zero-length string

Debug.Print lStr_Result

'strings and non-string

lInt_i = 20

lStr_Result = "Welcome to the new roaring " & lInt_i & "s!"

' non-string is converted to String Variant before the joining

Debug.Print lStr_Result

End Sub

Sample code illustrating the ampersand (&) concatenation operator’s usage. Non-String operands are converted to String Variants, while Null and Empty expressions are treated as zero-length strings (i.e., “”). — Sample code illustrating the ***Ampersand (&)*** operator’s usage – non-string operands are converted to ***String Variants***, while ***Null*** and ***Empty*** expressions are treated as zero-length strings (i.e., “”).

Concatenation Operator: Plus (+)

The sample code below illustrates the plus (+) operator’s use in joining Strings.

Option Explicit

Sub ConcatenationOperators_Plus_Example01()
    
    Dim lStr_Var1 As String, lVar_Var1 As Variant, lVar_Var2 As Variant
    
    'two strings
    lStr_Var1 = "Hello" + " world!"
    Debug.Print lStr_Var1
    
    'two string variants
    lVar_Var1 = "Howdy, "
    lVar_Var2 = " Rowdy!"
    Debug.Print lVar_Var1 + lVar_Var2
    
    'one string, one non-string variant (excluding: Null or numeric)
    lStr_Var1 = "Dilly"
    lVar_Var1 = Empty   'treated as zero-length string ("")
    Debug.Print lStr_Var1 + lVar_Var1 + "dally"
    
End Sub