VBA Concatenate

Updated on January 1, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Concatenation means joining two values or two strings together, similar to Excel. We use & or also known as an ampersand operator, to concatenate. For example, for two concatenate strings, we use & operators like String 1 and String 2. Now, there is an important thing to remember: while using the & operator, we need to provide spaces, or VBA will consider it long.

VBA Concatenate Strings

VBA concatenates one of those things we used to combine two or more value cell values. So if we say it in simple language, it is combined. 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 helps the user concatenate or join two or more cell values which may be in the form of characters, strings or numbers.read more, which will combine two or more values or two or more cell values.

But in VBA, we do not have any built-in function to concatenate two or more values together. We do not even get to access the worksheet function class to access the VBA CONCATENATE function as a worksheet function.

VBA Concatenate

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA Concatenate (wallstreetmojo.com)

How to Concatenate Strings in VBA?

Suppose we do not have any built-in function to concatenate values and even do not integrate the worksheet function with VBA. Now the challenge is, how do we combine values?

Even though there are no built-in functions, we can still combine them in VBA using the “ampersand” (&) symbol.

If you follow our posts regularly, we often use the ampersand (&) symbol in our coding.

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

For example, if you have the first and last names separately, we can combine these two and make them a full name. Then, 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.read more.

Step 2: Define three variables as String.

Code:

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 variable’s first and last names.

Code:

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.

Code:

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.

Code:

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.

VBA Concatenate Example 1

The problem with this full name is we have not added a first name and last name separator character space. Therefore, combine space characters while combining the first and last names.

Code:

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

It will give a proper full name now.

VBA Concatenate Example 1-1

Like this, using the ampersand symbol, we can concatenate values. Now, we will solve the worksheet problem of solving first name and last name together to make it a full name.

Example 2

Since we need to combine many names, we need to use loops to combine the first and last names. The below code will do the job for you.

Code:

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

It will combine the first and last names, just like our VBA concatenate function.

Example 2-1

Common Mistake in Ampersand VBA Concatenation

If you notice my codes, we have added a space character between values and an ampersand symbol. It is essential because of the nature of VBA programming.

We cannot combine values and ampersand symbols. Otherwise, we will get a “Compile error” like the one below.

Example 2-2

VBA Concatenate Using JOIN Function

In VBA, we can use the JOIN function to combine values. First, look at the VBA JOINVBA JOINIn VBA, we use the Join command to join two or more strings together, similar to the concatenate function and the "&" command. read more function syntax.

VBA Join Formula
  • 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.

Code:

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

Recommended Articles

This article has been a guide to VBA Concatenate. Here, we learned how to use the Join function’s VBA concatenate function, some practical examples, and a downloadable Excel template. Below are some useful Excel articles related to VBA: –