How to Concatenate String and Variable in Excel VBA (A Detailed Analysis)

While working with VBA in Excel, we often have to concatenate string (s) and variable (s) in a worksheet. Concatenating string (s) and variable (s) is widely used in almost all the sectors in our works, starting from summarizing the results of students to analyzing a complex business. In this article, I’ll show you how you can concatenate string (s) and variable (s) in a worksheet with VBA in Excel. I’ll explain the things with proper examples and illustrations.


Concatenate String (s) and Variable (s) in Excel VBA (Quick View)

Concatenate String and Variable in Excel VBA


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Concatenate String (s) and Variable (s) in Excel VBA (Step by Step Analysis)

First o all, let’s see how we can concatenate string (s) and variable (s) in VBA step by step.

⧪ Concatenating String (s):

To concatenate two or more strings in VBA, you can use both the arithmetic addition (+) symbol and the ampersand (&) symbol.

For example, to concatenate the strings “Great Expectations” and “A Tale of Two Cities” with a comma, you can use:

New_String = "Great Expectations" + ", " + "A Tale of Two Cities"

Concatenate String and Variable in Excel VBA

Or,

New_String = "Great Expectations" & ", " & "A Tale of Two Cities"

Concatenate String and Variable in Excel VBA

Run any of the above codes. It’ll display the concatenated output, Great Expectations, A Tale of Two Cities.

⧪ Concatenating Variable (s)

If all the variables contain string values, then you can use both the arithmetic addition (+) symbol and the ampersand (&) symbol.

But if they don’t, then you can use only the ampersand (&) symbol to concatenate.

For example, Let’s have two variables, A and B.

A contains a string, “A Tale of Two Cities”, and B contains another string, “The Forty Rules of Love”.

You can use both the addition (+) symbol and ampersand (&) symbol to concatenate them.

A = "A Tale of Two Cities"
B = "The Forty Rules of Love"
New_String = A + ", " + B

Concatenate String and Variable in Excel VBA

Or,

A = "A Tale of Two Cities"
B = "The Forty Rules of Love"
New_String = A & ", " & B

Concatenate String and Variable in Excel VBA

In both cases, they’ll return the concatenated string.

But if A is a string (“ A Tale of Two Cities”) and B is an integer (27), you must use the ampersand (&) symbol to concatenate.

A = "A Tale of Two Cities"
B = 27
New_String = A & ", " & B

Concatenate String and Variable in Excel VBA

It’ll return the concatenated output.

Output to Concatenate String and Variable in Excel VBA


Examples to Concatenate Strings and Variables in Excel VBA (Involving Macro, UDF, and UserForm)

We’ve learned to concatenate string (s) and variable (s) with VBA in Excel. This time we’ll explore some examples involving concatenating string (s) and variable (s) with VBA.


Example 1: Developing a Macro to Concatenate string (s) and variable (s) in Excel VBA

We’ve learned to concatenate strings and variables with VBA. This time we’ll develop a Macro to concatenate strings and variables of multiple columns in a worksheet.

Here we’ve got a data set with book names, authors, and prices of some books of a bookshop called Martin Bookstore.

Let’s develop a Macro to concatenate columns 1, 2, and 3 of the data set B4:D14 in cell F4.

The VBA code will be:

⧭ VBA Code:

Sub Concatenate_String_and_Variable()

Dim Rng As Range
Set Rng = Range("B4:D14")

Dim Column_Numbers() As Variant
Column_Numbers = Array(1, 2, 3)

Separator = ", "

Output_Cell = "F4"

For i = 1 To Rng.Rows.Count
    Output = ""
    For j = LBound(Column_Numbers) To UBound(Column_Number
        If j <> UBound(Column_Numbers) Then
            Output = Output & Rng.Cells(i, Int(Column_Numbers(j))) & Separator
        Else
            Output = Output & Rng.Cells(i, Int(Column_Numbers(j)))
        End If
    Next j
    Range(Output_Cell).Cells(i, 1) = Output
Next i

End Sub

Developing Macro to Concatenate String and Variable in Excel VBA

⧭ Output:

Run this code. You’ll get the 3 columns concatenated in the range F4:F14.

Output to Concatenate String and Variable in Excel VBA

Read More: Macro to Concatenate Multiple Columns in Excel (with UDF and UserForm)


Example 2: Creating a User-Defined Function to Concatenate string (s) and variable (s) in Excel VBA

We’ve learned to develop a Macro to concatenate multiple columns of a data set. This time we’ll create a User-Defined function to concatenate strings or variables in Excel.

The complete VBA code will be:

⧭ VBA Code:

Function ConcatenateValues(Value1, Value2, Separator)

If VarType(Value1) <> 8204 And VarType(Value2) <> 8204 Then
    ConcatenateValues = Value1 & Separator & Value2

ElseIf VarType(Value1) = 8204 And VarType(Value2) <> 8204 Then
    Dim Output1() As Variant
    ReDim Output1(Value1.Rows.Count - 1, 0)
    For i = 1 To Value1.Rows.Count
        Output1(i - 1, 0) = Value1.Cells(i, 1) & Separator & Value2
    Next i
    ConcatenateValues = Output1

ElseIf VarType(Value1) = 8204 And VarType(Value2) = 8204 Then
    Dim Output2() As Variant
    ReDim Output2(Value1.Rows.Count - 1, 0)
    For i = 1 To Value1.Rows.Count
        Output2(i - 1, 0) = Value1.Cells(i, 1) & Separator & Value2.Cells(i, 1)
    Next i
    ConcatenateValues = Output2
End If

End Function

⧭ Output:

Select the column where you want to concatenate the range and enter this formula:

=ConcatenateValues("She","H. Rider Haggard",", ")

It’ll return She, H. Rider Haggard as the output.

Entering Function to Concatenate String and Variable in Excel VBA

Again, enter the formula:

=ConcatenateValues(B4:B14,30,", ")
[Array Formula. So don’t forget to press CTRL + SHIFT + ENTER unless you are in Office 365.]

It’ll concatenate 30 with all the values of the range B4:B14.

Inserting Function to Concatenate String and Variable in Excel VBA

Finally, enter:

=ConcatenateValues(B4:B14,C4:C14,", ")
[Again Array Formula. So don’t forget to press CTRL + SHIFT + ENTER unless you are in Office 365.]

It’ll concatenate all the values of the range B4:B14 with those of C4:C14.

Read More: How to Concatenate in Excel (3 Suitable Ways)


Similar Readings:


Example 3: Developing a UserForm to Concatenate string (s) and variable (s) in a Different Worksheet in Excel VBA

We’ve learned to develop a Macro and a User-Defined function to concatenate strings and values. Finally, we’ll develop a UserForm to concatenate strings and values to the desired location of the desired worksheet.

⧪ Step 1: Inserting the UserForm

Go to the Insert > UserForm option in the VBA toolbar to insert a new UserForm.

Inserting UserForm to Concatenate String and Variable in Excel VBA

⧪ Step 2: Dragging Tools to the UserForm

A UserForm called UserForm1 will be opened, along with a Toolbox called Control.

Move your mouse over the Toolbox and drag 2 ListBoxes, 5 TextBoxes, 7 Labels and1 CommanButtons in the UserForm.

Change the displays of the Labels as shown in the figure.

Similarly, change the display of the CommandButton to OK.

Dragging Tools to Concatenate String and Variable in Excel VBA

⧪ Step 3: Writing Code for TextBox1

Double click on TextBox1. A Private Subprocedure called TextBox1_Change will open. Enter the following code there.

Private Sub TextBox1_Change()

On Error GoTo Task

Range(UserForm1.TextBox1.Text).Select

UserForm1.ListBox1.Clear

For i = 1 To Range(UserForm1.TextBox1.Text).Columns.Count
    UserForm1.ListBox1.AddItem Range(UserForm1.TextBox1.Text).Cells(1, i)
Next i

Exit Sub

Task:
    x = 5

End Sub

⧪ Step 4: Writing Code for TextBox3

Similarly, double click on TextBox3. Another Private Subprocedure called TextBox3_Change will open. Enter the following code there.

Private Sub TextBox3_Change()

On Error GoTo Task

Starting_Cell = UserForm1.TextBox3.Text

For i = 1 To Len(Starting_Cell)
    If Asc(Mid(Starting_Cell, i, 1)) >= 48 And Asc(Mid(Starting_Cell, i, 1)) <= 57 Then
        Col = Left(Starting_Cell, i - 1)
        Row = Right(Starting_Cell, Len(Starting_Cell) - i + 1)
        End_Range = Col + Right(Str(Int(Row) + Range(UserForm1.TextBox1.Text).Rows.Count - 1), Len(Str(Int(Row) + 10)) - 1)
        Set Rng = Range(Starting_Cell + ":" + End_Range)
        Rng.Select
        Exit For
    End If
Next i
Rng.Cells(1, 1) = UserForm1.TextBox4.Text

Exit Sub

Task:
    x = 5

End Sub

TextBox3 Code to Concatenate String and Variable in Excel VBA

⧪ Step 5: Writing Code for TextBox4

Also, double click on TextBox3. Another Private Subprocedure called TextBox3_Change will open. Enter the following code there.

Private Sub TextBox4_Change()

If UserForm1.TextBox3.Text <> "" Then
    Selection.Cells(1, 1) = UserForm1.TextBox4.Text
End If

End Sub

TextBox4 Code to Concatenate String and Variable in Excel VBA

⧪ Step 6: Writing Code for ListBox2

Then double click on ListBox2. When the Private Subprocedure called ListBox2_Click opens, enter this code there.

Private Sub ListBox2_Click()

Reserved_Address = Selection.Address

For i = 0 To UserForm1.ListBox2.ListCount - 1
    If UserForm1.ListBox2.Selected(i) = True Then
        Worksheets(UserForm1.ListBox2.List(i)).Activate
        Range(Reserved_Address).Select
        Exit For
    End If
Next i

If UserForm1.TextBox3.Text <> "" Then
    Selection.Cells(1, 1) = UserForm1.TextBox4.Text
End If

End Sub

⧪ Step 7: Writing Code for CommanButton1

Also, double-click on CommandButton1. After the Private Subprocedure called CommandButton1_Change opens, insert the following code there.

Private Sub CommandButton1_Click()

On Error GoTo Message

Dim Rng As Range

Set Rng = Worksheets(UserForm1.TextBox5.Text).Range(UserForm1.TextBox1.Text)

Dim Column_Numbers() As Variant

Count = 0

For i = 0 To UserForm1.ListBox1.ListCount - 1
    If UserForm1.ListBox1.Selected(i) = True Then
        ReDim Preserve Column_Numbers(Count)
        Column_Numbers(Count) = i + 1
        Count = Count + 1
    End If
Next i

Separator = UserForm1.TextBox2.Text

Output_Cell = UserForm1.TextBox3.Text

For i = 0 To UserForm1.ListBox2.ListCount-1
    If UserForm1.ListBox2.Selected(i) = True Then
        Sheet_Name = UserForm1.ListBox2.List(i)
        Exit For
    End If
Next i

Worksheets(Sheet_Name).Range(Output_Cell).Cells(1, 1) = UserForm1.TextBox4.Text

For i = 2 To Rng.Rows.Count
    Output = ""
    For j = LBound(Column_Numbers) To UBound(Column_Numbers)
        If j <> UBound(Column_Numbers) Then
            Output = Output & Rng.Cells(i, Int(Column_Numbers(j))) & Separator
        Else
            Output = Output & Rng.Cells(i, Int(Column_Numbers(j)))
        End If
    Next j
    Worksheets(Sheet_Name).Range(Output_Cell).Cells(i, 1) = Output
Next i

Unload UserForm1

Exit Sub

Message:
    MsgBox "Choose All the Options Correctly.", vbExclamation

End Sub

CommandButton Code to Concatenate String and Variable in Excel VBA

⧪ Step 7: Writing Code for Running the UserForm

Now is the final step. Insert a new Module from the VBA toolbar and insert the following code.

Sub Run_UserForm()

UserForm1.Caption = "Concatenate Values"

UserForm1.TextBox1.Text = Selection.Address

UserForm1.TextBox5.Text = ActiveSheet.Name

UserForm1.ListBox1.ListStyle = fmListStyleOption
UserForm1.ListBox1.BorderStyle = fmBorderStyleSingle
UserForm1.ListBox1.MultiSelect = fmMultiSelectMulti
UserForm1.ListBox1.Clear

For i = 1 To Selection.Columns.Count
    UserForm1.ListBox1.AddItem Selection.Cells(1, i)
Next i

UserForm1.ListBox2.ListStyle = fmListStyleOption
UserForm1.ListBox2.BorderStyle = fmBorderStyleSingle

For i = 1 To Sheets.Count
    UserForm1.ListBox2.AddItem Sheets(i).Name
Next i

Load UserForm1
UserForm1.Show

End Sub

UserForm Code to Concatenate String and Variable in Excel VBA

⧪ Step 8: Running the UserForm

Your UserForm is now ready to use. To run it, select the data set from the worksheet (Including the Headers) and run the Macro called Run_UserForm.

The UserForm will load with all the options. The selected range address will be shown on TextBox1 (B3:D4 here). If you wish, you can change it. The selected range in the worksheet will change.

Select the columns that you want to concat from the Columns to Concat ListBox. Here I’ve selected Book Name and Price.

Enter the Separator. Here I’ve entered a comma (,).

Select the worksheet name where you want to put the concatenated range from the Concatenated In listbox. Here I’ve entered Sheet3.

(The moment you select the sheet, it’ll be activated, even if it’s not the active one.)

Then insert the Output Location. It’s the cell reference of the first cell of the concatenated range. Here I’ve put B3.

(The moment you enter the Output Location, the output range will be selected).

And finally, enter the name of the Output Header (Header of the Output Range). Here I’ve put Concatenated Range.

(The moment you put the Output Header, the header of the output column will be set.)

UserForm to Concatenate String and Variable in Excel VBA

Click OK. You’ll get the desired output in the desired location.

Read More: How to Concatenate String and Integer using VBA


Conclusion

So these are a few examples in which you can use Excel VBA to concatenate strings and variables. Hope the examples made everything pretty clear for you. Do you have any questions? Feel free to ask us. And don’t forget to visit our site ExcelDemy for more posts and updates.


Related Articles

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo