How to Concatenate Strings, Integers, Variables, Cells in VBA

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:

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:

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

VBA code:

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:

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:

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 cell 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:

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

See an example below where we will concatenate “Product Names” from B2 to B7 cells and display all in a MsgBox:

The code:

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: