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 excelCONCATENATE In ExcelThe concatenate function in Excel is used to connect or concatenate two or more characters, strings, or numbers. The concatenate function is a better alternative to using the & operator to connect two variables., 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 the 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 the 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 the “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 VBA sub procedureCreate A VBA Sub ProcedureSUB in VBA is a procedure which contains all the code which automatically gives the statement of end sub and the middle portion is used for coding. Sub statement can be both public and private and the name of the subprocedure is mandatory in VBA..
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 the 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 the full name in the message box.
The problem with this full name is we have not added a 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 a 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. The 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 an ampersand symbol. This is essential because of the nature of VBA programming.
We cannot combine values and ampersand symbols together; otherwise, we will get Compile Error like the below.
VBA Concatenate Using JOIN Function
In VBA, we can use the JOIN function to combine values. First, look at VBA JOINVBA JOINIn VBA, we use the Join command to join two or more strings together, similar to the concatenate function and the "&" command. function syntax.
- The array is nothing but an array that holds our values—for example, both first name & last name.
- The delimiter is nothing but what is the separator between each array value, in this case, space character.
The 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 a downloadable excel template. Below are some useful excel articles related to VBA –