Visual Basic for Applications (VBA) is a powerful tool for automating tasks in Microsoft Office applications. One common requirement in programming is to concatenate strings, especially when dealing with names. However, handling null values can lead to unexpected results. In this article, we will explore two methods of concatenation in VBA: using the ampersand (&) and the plus sign (+). We will also discuss how to manage null values effectively.
 

The Basics of Concatenation

Concatenation is the process of joining two or more strings together. In VBA, this can be achieved using either the `&` operator or the `+` operator. While both operators can be used for string concatenation, they behave differently when encountering null values.
 

Using the Ampersand (&)

The ampersand operator is explicitly designed for string concatenation. It treats all operands as strings, converting them as necessary. If any operand is Null, it is treated as an empty string (“”). This behavior makes the & operator a reliable choice for concatenating strings without introducing unexpected results. Let’s look at some code samples that demonstrate this.

Sub TestConcatanateWithAmp()
    Dim vFirstName            As Variant
    Dim vMiddleName           As Variant
    Dim vLastName             As Variant

    vFirstName = "Emily"
    vMiddleName = "Grace"
    vLastName = "Thompson"
    Debug.Print "'" & vFirstName & " " & vMiddleName & " " & vLastName & "'"
    ' Outputs => 'Emily Grace Thompson'


    vFirstName = "Emily"
    vMiddleName = Null
    vLastName = "Thompson"
    Debug.Print "'" & vFirstName & " " & vMiddleName & " " & vLastName & "'"
    ' Outputs => 'Emily  Thompson'   - with 2 spaces in the middle


    vFirstName = "Emily"
    vMiddleName = Null
    vLastName = Null
    Debug.Print "'" & vFirstName & " " & vMiddleName & " " & vLastName & "'"
    ' Outputs => 'Emily  '   - with 3 ending spaces


    vFirstName = Null
    vMiddleName = "Grace"
    vLastName = "Thompson"
    Debug.Print "'" & vFirstName & " " & vMiddleName & " " & vLastName & "'"
    ' Outputs => ' Grace Thompson'   - with a leading spaces


    vFirstName = Null
    vMiddleName = "Grace"
    vLastName = Null
    Debug.Print "'" & vFirstName & " " & vMiddleName & " " & vLastName & "'"
    ' Outputs => ' Grace '   - with leading and trailing spaces
End Sub

The Solution to handling potential Null values.

Sub TestConcatanateWithAmp()
    Dim vFirstName            As Variant
    Dim vMiddleName           As Variant
    Dim vLastName             As Variant

'    If the issue is Leading, or Trailing - use Trim
'    ***********************************************
    vFirstName = Null
    vMiddleName = "Grace"
    vLastName = Null  
    Debug.Print "'" & Trim(vFirstName & " " & vMiddleName & " " & vLastName) & "'"
    ' Outputs => 'Grace'
    
'    The more versatile IIF & IsNull approach
'    ****************************************
    vFirstName = "Emily"
    vMiddleName = Null
    vLastName = "Thompson"
    Debug.Print "'" & vFirstName & IIf(IsNull(vMiddleName), "", " " & vMiddleName) & IIf(IsNull(vLastName), "", " " & vLastName) & "'"
    ' Outputs => 'Emily Thompson'
    
    
    vFirstName = "Emily"
    vMiddleName = Null
    vLastName = Null
    Debug.Print "'" & vFirstName & IIf(IsNull(vMiddleName), "", " " & vMiddleName) & IIf(IsNull(vLastName), "", " " & vLastName) & "'"
    ' Outputs => 'Emily'
End Sub

Key Takeaways from Ampersand Concatenation

• Handling Spaces: When any part of the concatenated string is null, it results in additional spaces. This can be problematic if not managed properly.
• Using Trim: The Trim function can be employed to eliminate leading and trailing spaces from the final output.
• Conditional Concatenation: The IIf() function combined with IsNull() allows for more controlled concatenation by checking each variable before adding it to the final string.

 

Using the Plus Sign (+)

The plus sign operator can also concatenate strings, but it has a more complex behavior. If any operand in a concatenation operation using `+` is Null, the entire expression evaluates to Null. This means that if you concatenate a string with a Null value using `+`, you will not get any output; instead, you will receive a Null result.

Using the following setup, let’s look at some code samples that demonstrate this.

Sub TestConcatanateWithPlus()
    Dim vFirstName            As Variant
    Dim vMiddleName           As Variant
    Dim vLastName             As Variant
    
    '? ("Something" & null)
    '  Returns => Something
    '? ("Something" + null)
    '  Returns => Null

    vFirstName = "Emily"
    vMiddleName = "Grace"
    vLastName = "Thompson"
    Debug.Print "'" & vFirstName + " " + vMiddleName + " " + vLastName & "'"
    ' Outputs => 'Emily Grace Thompson'


    vFirstName = "Emily"
    vMiddleName = Null
    vLastName = "Thompson"
    Debug.Print "'" & vFirstName + " " + vMiddleName + " " + vLastName & "'"
    ' Outputs => ''     '!!! Watch out, if any part of the concatenation is null, then the entire thing is omitted!


    vFirstName = "Emily"
    vMiddleName = Null
    vLastName = "Thompson"
    Debug.Print "'" & vFirstName + (" " + vMiddleName) + (" " + vLastName) & "'"
    ' Outputs => ''
End Sub

And here are examples of how to properly handle thing to manage the possibility of Null values:

Sub TestConcatanateWithPlus()
    Dim vFirstName            As Variant
    Dim vMiddleName           As Variant
    Dim vLastName             As Variant
    
    vFirstName = "Emily"
    vMiddleName = Null
    vLastName = "Thompson"
    Debug.Print "'" & vFirstName & " " + vMiddleName & " " + vLastName & "'"
    ' Outputs => 'Emily Thompson'


    vFirstName = "Emily"
    vMiddleName = Null
    vLastName = "Thompson"
    Debug.Print "'" & vFirstName & (" " + vMiddleName) & (" " + vLastName) & "'"
    ' Outputs => 'Emily Thompson'
End Sub

Key Takeaways from Plus Sign Concatenation

• Null Handling: If any part of the expression is null, the entire result becomes null. This behavior can lead to unexpected results if not carefully managed.
• Combining Expressions: Even if you try to concatenate parts separately, if any part evaluates to null, it will still result in an empty output.

 

Summary of Key Differences

Operator	Behavior with Null Values
&	Treats Null as an empty string; returns other strings as expected.
+	Returns Null if any operand is Null; may lead to loss of data.

 

Exploring `&` and `+` In Query Concatenations

I thought we should also explore the implications of `&` and `+` in the context of SQL Queries.

Let’s assume an ‘Employee’ table containing 3 Short Text fields: FirstName, MiddleName and LastName.

[FirstName] => Emma
[MiddleName] => Grace
[LastName] => Thompson

SELECT [FirstName] & " " & [MiddleName] & " " & [LastName] AS Fullname
FROM Employee;

Outputs: 'Emma Grace Thompson'

[FirstName] => Emma
[MiddleName] => Null
[LastName] => Thompson

SELECT [FirstName] & " " & [MiddleName] & " " & [LastName] AS Fullname
FROM Employee;

Outputs: 'Emma  Thompson'

To fix the above we can take 1 of 2 approaches

SELECT [FirstName] & IIf(IsNull([MiddleName]), "", " " & [MiddleName]) & " " & [LastName] AS Fullname
FROM Employee;

Outputs: 'Emma Thompson'

Or

SELECT [FirstName] & (" " + [MiddleName]) & " " & [LastName] AS Fullname
FROM Employee;

Outputs: 'Emma Thompson'

Based on the above, I hope you are starting to see the value of the `+` operator in the context of handling Null values! With that in mind, we could further improve the above to:

SELECT [FirstName] & (" " + [MiddleName]) & (" " + [LastName]) AS Fullname
FROM Employee;

Outputs: 'Emma Thompson'

The above expression can now handle any scenario we throw at it.
 

Conclusion

Understanding how to concatenate strings effectively in both VBA and SQL queries is essential for developing robust applications that handle user input and database records seamlessly.