How to Concatenate String in VBA

Iqra Hasnain Feb 15, 2024
  1. Concatenate String in VBA
  2. Concatenate Space Characters in VBA
  3. Concatenate Quotation Marks in VBA
How to Concatenate String in VBA

We will introduce how to concatenate strings in VBA and explain how we can use Excel & Operators.

Concatenate String in VBA

We can link multiple strings into a singular string in VBA and utilize the & operator to separate the value of the string. We can use the & operator in the worksheet and Excel as a VBA function.

We can enter the & operator as a part of the formula in a cell of the worksheet as a function in the worksheet. We can utilize this & operator in large code, which will enter through the Microsoft Visual Basic Editor as a function in VBA.

Syntax:

# VBA
strOne & strTwo [& strThree & str_p]

These string values concatenate together. This & operator returns a string /text value.

Concatenate Space Characters in VBA

We will be concatenating values together, adding space characters to separate the concatenated values. If not, we can get a long string with the concatenated value running together.

It will make it more valuable to read the result.

Code:

# Vba
Sub concatenate()
Set B3 = Range("B3")
Set C3 = Range("C3")
MsgBox (B3 & " " & C3)
End Sub

Output:

Concatenate Space Characters in VBA

We used the & operator to add a space character between B1 and B2 cell values. This will contain our values from being squished together.

Concatenate Quotation Marks in VBA

The & operator will concatenate string values held in quotation marks. This & operator adds a quotation mark to the resulting concatenated in this example below.

Code:

# Vba
Sub concatenate()
MsgBox ("Orange " & """" & " Mango")
End Sub

Output:

Concatenate Quotation Marks in VBA

This & operator adds a quotation mark in the middle of the resulting string. Our strings concatenate enclosed in quotation marks.

Related Article - VBA String