Using + as concatenation operator in VBA

Lately I've seen some VBA code that uses the plus sign ('+') as a concatenation operator. I think it's from other programming languages that allow this.

Some examples:

So what is really going on here? Does this really work? Can I really use '+' instead of '&'?

Let's start out by checking some strings and numbers.

Debug.Print TypeName("2")
Debug.Print TypeName(2)

If I run this code I get "String" followed by "Integer". So that tells me that when you enclose anything in quotes, it becomes a string. I tested on a few more items and sure enough they all return String types.

Debug.Print TypeName("abc")
Debug.Print TypeName("12/1/2011")

Now let's test out some typecasting. I declared a few variables as follows:

Dim firstNum As Integer
Dim secondNum As Integer

Dim firstString As String
Dim secondString As String

Now I assign some initial values:

firstNum = "2"
secondNum = "3"

firstString = 2
secondString = 3

I deliberately assign a string value to an Integer and an integer value to a String to test out how VBA treats each value. My conclusion is that as long as the value can be treated as an Integer, it will be accepted by an Integer type. The only errors I was able to trigger was "Type mismatch" if you try to assign a string value like "abc", or "Overflow" if you use a sufficiently large value like 10000000000000000000. Otherwise, the Integer variable dutifully accepts the string and casts it into an Integer. String variables will take any variable in double quotes, even numbers and dates.

So now let's cross the streams and see what happens.

Sub testConcat()

  Dim firstNum As Integer
  Dim secondNum As Integer

  Dim firstString As String
  Dim secondString As String

  firstNum = "2"
  secondNum = "3"

  firstString = 2
  secondString = 3

  Debug.Print TypeName(firstNum) & " + " & TypeName(secondNum) & " = " & (firstNum + secondNum)
  Debug.Print TypeName(firstNum) & " & " & TypeName(secondNum) & " = " & (firstNum & secondNum)
  Debug.Print TypeName(firstString) & " + " & TypeName(secondString) & " = " & (firstString + secondString)
  Debug.Print TypeName(firstString) & " & " & TypeName(secondString) & " = " & (firstString + secondString)
  Debug.Print TypeName(firstNum) & " + " & TypeName(secondString) & " = " & (firstNum + secondString)
  Debug.Print TypeName(firstNum) & " & " & TypeName(secondString) & " = " & (firstNum & secondString)

End Sub

This procedure displays every possible combination of Integer and String concatenation using both '+' and '&'. The results are:

  • Integer + Integer = 5
  • Integer & Integer = 23
  • String + String = 23
  • String & String = 23
  • Integer + String = 5
  • Integer & String = 23

So I conclude the following:

  1. The concatenation operator '&' always results in a concatenation, regardless of the type of input variables or values. All of the relevant values are cast into String types and concatenated.
  2. Even this produces a concatenation:

    Debug.Print #12/1/2011# & "3"

    This prints "12/1/20113" to the Immediate Window. But we knew that would happen, right?

  3. The operator '+', normally used for addition operations, acts differently depending on the type of variable/value.
  4. If at least one value is an Integer type, it adds them, but if both are strings (even if they can be interpreted as numbers) then they are concatenated.

    ?2 + 3 = 5
    ?2 + "3" = 5
    ?"2" + "3" = 23

The two operators are actually interchangeable for the cases where they are most often confused, because "String + String" returns the same thing as "String & String". It's when you accidently try to concatenate strings with integers that you run into trouble. I always use '&'.

Have you ever run into a problem with concatenation? What operator do you use?

About JP

I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space to learn more about VBA. Keep Reading »



Share This Article:

Share and bookmark this articledelicious buttonfacebook buttonlinkedin buttonstumbleupon buttontwitter button

This article is closed to any future comments.
Peltier Tech Charting Utilities for Excel