How to Concatenate Strings, Integers, Variables, Cells in VBA
In VBA, you may concatenate values, strings, variables, and Excel data by using the ‘&’ operator.
Concatenation in VBA
In VBA, you may concatenate values, strings, variables, and Excel data by using the ‘&’ operator.
An example of concatenating two string variables
In the first example, we have two string variables.
The task is to combine these two strings and display them in the message box.
VBA Code:
|
1 2 3 4 5 6 7 8 9 10 11 |
Sub concat() Dim str1 As String, str2 As String str1 = "Hello and Welcome " str2 = "to VBA World!" MsgBox str1 & str2 End Sub |
Output:
Combining variable with text example
We have one string variable and some text in the message box that is concatenated by using the ‘&’ operator:
The code:
|
1 2 3 4 5 6 7 8 9 |
Sub concat() Dim str1 As String str1 = "VBA!" MsgBox "Excel and " & str1 End Sub |
Result:
Adding a new line as a concatenation between variables
You may also add a new line by using its constant to make the text clearer after concatenation.
The following example uses a new line constant
For adding a new line we used vbCrLf constant.
VBA code:
|
1 2 3 4 5 6 7 8 9 10 11 |
Sub concat() Dim str1 As String, str2 As String str1 = "VBA is Cool..!" str2 = "C# is also not less :)" MsgBox str1 & vbCrLf & vbCrLf & str2 End Sub |
Output:
Note: You may also use vbNewLine constant instead of vbCrLf.
Concatenating string and integer type variable
Let us see what we get as we concatenate some text, a string variable, and an integer type variable – combined all in a message box:
VBA code:
|
1 2 3 4 5 6 7 8 9 10 11 |
Sub concat() Dim str1 As String, num1 As Integer str1 = "A String and Integer" num1 = 10 MsgBox str1 & vbCrLf & num1 End Sub |
Output:
An example of concatenating Excel cells data
In the following example, we will concatenate the data of Excel cells. For that we have the following sample sheet:
We will combine the text as follows:
Code:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Sub concat() Dim Product_name As String, Product_Status As String, Product_Price As Currency Product_name = Range("B8").Value Product_Status = Range("D8").Value Product_Price = Range("C8").Value MsgBox "Product Name = " & Product_name & vbCrLf & "Product Price = $" & Product_Price & vbCrLf & "Product Status = " & Product_Status End Sub |
Result:
Concatenating range of cells
You may also specify a range of cells and combine by using different techniques. One of the techniques is using the For Each loop.
See an example below where we will concatenate “Product Names” from B2 to B7 cells and display all in a MsgBox:
The code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
Sub concat() Dim SheetRng As Range Dim cell Dim concated As String 'Setting the range of cells Set SheetRng = Range("B2:B7") For Each cell In SheetRng concated = concated & cell & ", " Next cell 'Display concatenated cells MsgBox concated End Sub |
Result:
