How to Concatenate Strings and Variables in Excel VBA (3 Examples)

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

Here’s a dataset with book names, authors, and prices from 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

⧭ Output:

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

Example 2: Creating a user-defined function to concatenate string (s) and variable (s)

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.

Enter the following formula:

=ConcatenateValues(B4:B14,30,", ")

Press CTRL + SHIFT + ENTER.

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

Enter the following formula:

=ConcatenateValues(B4:B14,C4:C14,", ")

Press CTRL + SHIFT + ENTER

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

Example 3: Developing a UserForm to concatenate string (s) and variable (s)

⧪ Step 1: Inserting the UserForm

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

⧪ Step 2: Dragging Tools to the UserForm

A UserForm called UserForm1 and a Toolbox called Control will open.
Move your mouse over the Toolbox and drag 2 ListBoxes, 5 TextBoxes, 7 Labels, and 1 CommanButtons in the UserForm.
Change the displays of the Labels as shown in the figure.
Change the display of the CommandButton to OK.

⧪ Step 3: Writing Code for TextBox1

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

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

Double-click on TextBox3.
A Private Subprocedure called TextBox3_Change will open.
Enter the following code:

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

⧪ Step 5: Writing Code for TextBox4

Double-click on TextBox3.
A Private Subprocedure called TextBox3_Change will open.
Enter the following code:

Private Sub TextBox4_Change()

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

End Sub

⧪ Step 6: Writing Code for ListBox2

Double click on ListBox2.
When the Private Subprocedure called ListBox2_Click opens, enter the following code:

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

Double-click on CommandButton1.
The Private Subprocedure called CommandButton1_Change opens,
Insert the following code:

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

⧪ Step 7: Writing Code for Running the UserForm

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

⧪ Step 8: Running the UserForm

Your UserForm is now ready to use.

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 you want to concatenate 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 list box. Here, I’ve entered Sheet 3.

(When you select the sheet, it’ll be activated, even if inactive.)