Concatenation means joining two values or two strings together, similar to excel we use & or also known as ampersand operator to concatenate, two concatenate two strings we use & operator like String 1 & String 2, now there is an important thing to remember and that is while using the & operator we need to provide spaces or VBA will consider it as long.
VBA Concatenate Strings
VBA Concatenate is one of those things which we used to combine two or more value cell values together if I say in simple language it is combining, it is joining two or more values together to have full value.
We have a function called CONCATENATE in excel which will do the combining together two or more values or two or more cell values together.
But in VBA we don’t have any built-in function to concatenate two or more values together. In fact, we don’t even get to access the worksheet function class to access VBA CONCATENATE function as a worksheet function.
How to Concatenate Strings in VBA?
If we don’t have any sort of built-in function to concatenate values and even worksheet function is not integrated with VBA. Now the challenge is how do we combine values?
Even though there are no built-in functions still we can combine in VBA using “ampersand” (&) symbol.
If you are following our posts regularly we often use the ampersand (&) symbol in our coding.
For example, if you have the first name and last name separately we can combine these two and make it as a full name. Follow the below steps to write the vba macro code on our own.
Step 1: Go to Visual Basic Editor and create a sub procedure.
Step 2: Define three variables as String.
Sub Concatenate_Example() Dim First_Name As String Dim Last_Name As String Dim Full_Name As String End Sub
Step 3: Now assign the first name and last name to the variable.
Sub Concatenate_Example() Dim First_Name As String Dim Last_Name As String Dim Full_Name As String First_Name = "Sachin" Last_Name = "Tendulkar" End Sub
Step 4: Now combine these two names to the variable Full_Name using ampersand variable.
Sub Concatenate_Example() Dim First_Name As String Dim Last_Name As String Dim Full_Name As String First_Name = "Sachin" Last_Name = "Tendulkar" Full_Name = First_Name & Last_Name End Sub
Step 5: Now show the value of the variable Full_Name in the message box.
Sub Concatenate_Example() Dim First_Name As String Dim Last_Name As String Dim Full_Name As String First_Name = "Sachin" Last_Name = "Tendulkar" Full_Name = First_Name & Last_Name MsgBox Full_Name End Sub
Now run the code we will get full name in the message box.
The problem with this full name is we have not added first name and last name separator character space, while combining the first name and last name, combine space character as well.
Sub Concatenate_Example() Dim First_Name As String Dim Last_Name As String Dim Full_Name As String First_Name = "Sachin" Last_Name = "Tendulkar" Full_Name = First_Name & " " & Last_Name MsgBox Full_Name End Sub
This will give proper full name now.
Like this using the ampersand symbol, we can concatenate values. Now we will solve the worksheet problem of solving First Name & Last Name together to make it a full name.
Since we need to combine many names we need to use loops to combine the first name & last name. Below code will do the job for you.
Sub Concatenate_Example1() Dim i As Integer For i = 2 To 9 Cells(i, 3).Value = Cells(i, 1) & " " & Cells(i, 2) Next i End Sub
This will combine the first name and last name just like our VBA concatenate function.
Common Mistake in Ampersand VBA Concatenation
If you notice my codes I have added space character between values and ampersand symbol. This is essential because of the nature of VBA programming.
We cannot combine values and ampersand symbol together otherwise we will get Compile Error like the below.
VBA Concatenate Using JOIN Function
In VBA we can use JOIN function to combine values. First, look at JOIN function syntax.
- Array is nothing but an array which holds our values. For example both first name & last name.
- Delimiter is nothing but what is the separator between each array value, in this case, space character.
Below code will show the example of the same.
Sub Concatenate_Example2() Dim MyValues As Variant Dim Full_Name As String MyValues = Array("Sachin", "Tendulkar") Full_Name = Join(MyValues, " ") MsgBox Full_Name End Sub
This has been a guide to VBA Concatenate. Here we learned how to use VBA Concatenate Function using the Join function along with some practical examples and downloadable excel template. Below are some useful excel articles related to VBA –