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.
Read More: How to Concatenate String and Integer with VBA
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.
Read More: Excel VBA to Concatenate Cell Values
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.)
- Insert the Output Location. It’s the cell reference of the first cell of the concatenated range. Here, I’ve put B3.
(When you enter the Output Location, the output range will be selected).
- Enter the name of the Output Header (Header of the Output Range). Here, I’ve put Concatenated Range.
(When you put the Output Header, the output column header will be set.)
- Click OK. You’ll get the desired output in the desired location.
Download the Practice Workbook
Download this workbook to practice.
