VBA Concatenate Strings with Ampersand Operator

Introduction - VBA Concatenate Strings

In VBA, you concatenate strings into a single string using the &, or ampersand, operator. “Concatenate” is just a fancy way of saying “combine” or “join.” This isn’t just a VBA thing. The word “concatenate” is used in almost all programming languages. When you concatenate strings, you are joining them together into one string.

How you concatenate strings varies by programming language, but in VBA you simply add an ampersand between your strings to combine them into one string.

Just like most other VBA tasks, there are many different ways to combine VBA strings, but the & operator is the best way. If you’re familiar with my VBA tutorials, you no doubt have seen me use “&” to concatenate strings. This article will be the first time I’ve actually explained what the ampersand operator is and why concatenation is so valuable to understand.

For more tips and tricks for handling strings, check out my Working with Strings series.

Example - VBA Concatenate Strings

Sub Concatenate_Strings()
Dim str1 As String
Dim str2 As String
Dim strCombined As String

str1 = "Four score and "
str2 = "seven years ago."

strCombined = str1 & str2 'concatenate strings
MsgBox strCombined
End Sub

Make powerful macros with our free VBA Developer Kit

It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.

Tutorial - VBA Concatenate Strings

How to concatenate strings with VBA

In this example, the strings str1 and str2 are concatenated (or combined) into one string, strCombined. There’s no practical limit to how many strings you can combine together using &s. I mean, there is probably a limit but it’s not a practical one. Chances are you’ll be limited by the maximum number of characters in a string before you’re limited by how many ampersands you can have.

Take a look at this example to see what I mean:

Sub VBA_Concatenate()
Dim MyString As String
Dim str1 As String
Dim str2 As String
Dim str3 As String

str1 = "18"
str2 = "06"
str3 = "29"

MyString = str1 & str2 & str3 'creates "180629"
End Sub

In this example, multiple &s are used to join the strings into the combined string "180629".

You’re not limited to using ampersands to concatenate string variables, either. You can actually use them to combine literal strings or strings and numbers. Check out this example, which combines a string and a number:

Sub VBA_Concatenate_2()
Dim MyString As String
Dim MyNumber As Double

MyNumber = 18.53
MyString = "Your total is $" & MyNumber
MsgBox MyString
End Sub

When you run this macro, you’ll be greeted with msgbox containing your combined string:

How to concatenate quotation marks with VBA

Concatenating quotation marks in VBA can be tricky since your strings are often surrounded by quotation marks. To combine strings that include quotation marks, you need to surround one or two quotation marks with a pair of quotation marks.

I know, it’s really confusing! Instead of trying to make sense of it with words, let’s look at an example.

Sub VBA_quotation_marks()
MyString = "My cow goes " & """mooo"""
MsgBox MyString
End Sub

The phrase mooo is now surrounded by quotation marks.

Okay, so it’s three consecutive quotation marks to display a quotation mark around typed strings. But what if you want a stand alone quotation mark around a variable? In that case, it’s four consecutive quotation marks to concatenate a quotation mark around a variable.

Sub VBA_quotation_marks()
strSound = "oink"

MyString = "My pig goes " & """" & strSound & """"
MsgBox MyString
End Sub

Makes perfect sense, right? I’m kidding. I know it’s tough, but it’s just one of those little things you have to memorize if you want to show quotation marks in your concatenated strings.

Common VBA Concatenate Errors

Notice in all my examples there’s a space between the variable and the & operator. This is extremely important because the ampersand has more than one meaning in VBA.

When a variable is immediately followed by an ampersand (with no space), it tells VBA you are trying to declare the variable as a Long data type. This is a very old feature of VBA that still exists for backward compatibility.

Let’s go up to my first example and try to remove the space between str1 and &. Your line of code will turn red and you’ll get an error, like this:

This is by far the biggest mistake I see when people are trying to concatenate strings. You must have a space before and after your ampersand to concatenate strings!

Another mistake I see people make is trying to combine strings with the + symbol. While it technically works when you have all strings, it won’t work the way you want it to when you trying combining strings and numbers. Case in point:

Sub VBA_add_strings()
val1 = "25"
val2 = 5

strCombined = val1 + val2 'yields 30, not 255!
MsgBox strCombined 
End Sub

Notice how val2 isn’t a string because it’s not surrounded by quotes. When you try to combine the values using the plus symbol instead of the ampersand, VBA immediately interprets both values as numbers and adds them together. You’ll get a result of 30. If you used the ampersand to properly concatenate, you’d get the string 255.

The bottom line is you should ALWAYS use the & operator to concatenate VBA strings.

Application Ideas

One of the most practical uses for string concatenation is when you’re looping through all the rows in a range in Excel. You’ll often combine a column letter with an iterating row number to loop through different rows in a column. You’ve probably been concatenating without even realizing it!

Sub VBA_Loop_Through_Rows()
dim i as Integer
For i = 1 To 100
    If Range("A" & i) = "Waldo" Then 'you're concatenating
        MsgBox "You found Waldo!"
    End If
Next i
End Sub

Notice how I’m concatenating the string "A" with the integer i. This combines the variables in such a way that it checks all the values in cells A1, A2, …, A100. This is where the value of concatenation becomes evident. Concatenating strings and numbers to represent cells is the key component of automating Excel.

Almost every VBA article I write has some element of string concatenation. Take a look and see if you can spot them!

That’s all for this tutorial. When you’re ready to take your VBA to the next level, subscribe using the form below. After that, share this article on Facebook and Twitter, and you may inspire others to “do more with Excel.”