Introduction | Example | Tutorial | Applications
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 spreadsheets with our free VBA Developer's Guide 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 Guide and a bunch of pre-built macros so you can master file I/O, arrays, strings and more.
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 Guide and a bunch of pre-built macros so you can master file I/O, arrays, strings and more.
Tutorial - VBA Concatenate Strings
How to concatenate strings with VBA
In this example, the strings
&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
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
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
&. 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.
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
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.”