Concatenate Array Using VBA

Concatenation is the process of joining two or more text strings to form one text string. Excel has the CONCATENATE function that can be used to accomplish this.

Excel VBA however does not have an in-built CONCATENATE function and we cannot call it using Excel’s WorksheetFunction property because in VBA this function is not permitted.

In this tutorial, we will explore 4 methods that we can use to work around this limitation and concatenate arrays or several text strings using VBA.

Method 1: Use the ampersand (&) operator to concatenate text strings

We can use the ampersand (&) operator to combine several strings into one. We will use the following dataset that has three columns containing the First Name, Middle Name, and Last Name to show how this method works.

We use the following steps:

1. Press Alt + F11 to open the Visual Basic Editor (VBE). Alternatively, click Developer >> Code >> Visual Basic:

2. Insert a new module by clicking Insert >> Module:

Alternatively, right-click the ThisWorkbook object and click Insert >> Module on the shortcut menu:

3. In the module type in the code below:

Sub concatArrays()
Dim String1 As String, String2 As String
Dim String3 As String, String4 As String
Dim String5 As String, String6 As String
Dim String7 As String, String8 As String
Dim String9 As String

End Sub

4. Save the code and save the workbook as a macro-enabled workbook.
5. Place the cursor anywhere in the code and press F5 to run the code. Alternatively, click the Run button on the standard toolbar:

6. Press Alt + F11 to switch to the active worksheet to see the results. Alternatively, click the View Microsoft Excel button on the standard toolbar:

We can see that the code has concatenated the First Name, Middle Name, and Last Name into one Full Name:

Explanation of the code

• Nine string variables are declared.
• The Cells function is used to return the values of the cells in the table array. These values are then assigned to the nine variables.
• The values in three variables at a time are concatenated using the ampersand operator with spaces in between them and the result is assigned to a relevant cell.
• For example, in the statement Cells(4, 5).Value = String7 & ” ” & String8 & ” ” & String9, the values in the three string variables are joined with spaces in between them and the result is assigned to cell E4.

Method 2: Use the addition (+) operator to concatenate text strings

We can use the addition (+) operator in place of the ampersand (&) operator to achieve the same results as in Method 1.

We shall use the same dataset we used before to explain how this method works.

We use the following steps:

1. Open the Visual Basic Editor and insert a new module as explained previously.
2. In the module, type in the following code:

Sub concatArrays2()
Dim String1 As String, String2 As String
Dim String3 As String, String4 As String
Dim String5 As String, String6 As String
Dim String7 As String, String8 As String
Dim String9 As String

3. Save the procedure and save the workbook as a macro-enabled workbook.
4. Place the cursor anywhere in the procedure and run the code by pressing F5 or the click the Run button on the standard toolbar.
5. Press Alt + F11 or click the View Microsoft Excel button to switch back to the active worksheet to see the results of the code.

The code has worked perfectly, and the various names have been combined into full names:

Method 3: Use VBA to combine columns of data

The previous two methods work well on a small dataset. If you are working on a large dataset, you will have to write very many lines of code just to combine a few cells at a time. This is time-consuming, slow, and inefficient.

For a large dataset, we need a faster approach. We need a procedure that concatenates many columns at once. We will learn how to do this by using the following steps:

1. Press Alt + F11 or click Developer >> Code >> Visual Basic to open the Visual Basic Editor.
2. Insert a new module and type in the following procedure:

3. Save the procedure and save the workbook as a macro-enabled workbook.
4. Press Alt + F11 to switch back to the active worksheet. Select the top cell in the right column of the data that we want to concatenate to make it the active cell, in this case, select cell C2.
5. Press Alt + F11 to switch back to the Visual Basic Editor.
6. Place the cursor anywhere in the procedure and press F5 or the Run button.
7. Press Alt + F11 to switch back to the active worksheet to see the results of the procedure.

We see that the procedure did what we wanted it to do:

Explanation of the procedure

• The procedure uses the Do While Loop constructs to loop through the data in column C until the active cell is blank.
• The procedure utilizes the offset property which returns a range object that represents a range that is offset from the specified range. For example, ActiveCell.Offset(0, -2) returns a cell reference that is two cells away from the active cell on the left. If the active cell is C2 it returns cell A1.

ActiveCell.Offset(0, 2).Formula = _
ActiveCell.Offset(0, -2) & ” ” & ActiveCell.Offset(0, -1) & ” ” & ActiveCell.Offset(0, 0).

• In this statement, the value of the active cell and the values of the two cells on its left are concatenated with spaces between them, and the result is assigned to the cell that is two cells to the right of the active cell.

• ActiveCell.Offset(1, 0).Select. The cell that is just below the active cell is selected and it becomes the active cell.
• The ampersand (&) operator must have a space on both sides otherwise it will be treated as a variable type of Long integer.

Method 4: Use VBA to combine rows of data

In this method, we concatenate values in several rows into one cell.

We use the following steps:

1. Press Alt + F11 or click Developer >> Code >> Visual Basic to open the Visual Basic Editor.
2. Insert a new module and type in the following procedure:

3. Save the procedure and save the workbook as a macro-enabled workbook.
4. Place the cursor anywhere in the procedure and press F5 or the Run button.
5. Press Alt + F11 to switch back to the active worksheet to see the results of the procedure.

Explanation of the formula

• Two range variables and one string variable are declared.
• Range A1:A3 is assigned to the SourceRange variable using the Set keyword.
• The For Each Next construct is utilized to concatenate the values in Range A1:A3 and assign the concatenated string to the variable i.
• The value in the variable i is passed to the TRIM function which removes all the spaces from the text string except for single spaces between words.
• The cleaned text string returned by the TRIM function is then assigned to cell C1.

Conclusion

Concatenation is the process of combining two or more text strings to form one text string. Excel has the in-built CONCATENATE function that can be used to accomplish this easily.

Excel VBA however does not have an in-built CONCATENATE function and it does not allow us to call it using Excel’s WorksheetFunction property because in VBA this function is not permitted.

In this tutorial, we have explored four different workarounds that we can use to concatenate arrays using Excel VBA: use the ampersand (&) and addition (+) operator and utilize sub procedures to concatenate data in columns and rows.