How to use the VBA JOIN Function (Syntax + Example)

HomeVBA Functions LIST (Category Wise)How to use the VBA JOIN Function (Syntax + Example)

The VBA JOIN function is listed under the array category of VBA functions. When you use it in a VBA code, it can join two or more strings and returns a single string in the result. In simple words, it can combine multiple strings into one string, just like we can use concatenate in the worksheet to combine values.

Syntax

Join(SourceArray, [Delimiter])

Arguments

  • SourceArray: An array of values that you want to join as a new string.
  • [Delimiter]: A delimiter that you want to use to separate each of the substrings when making up the new string.

Example

To practically understand how to use VBA JOIN function, you need to go through the below example where we have written a vba code by using it:

Sub example_JOIN()
Dim myAry(0 To 4) As String
myAry(0) = Range("A1")
myAry(1) = Range("A2")
myAry(2) = Range("A3")
myAry(3) = Range("A4")
myAry(4) = Range("A5")
Range("A7").Value = Join(myAry)
End Sub

In the above code, we have used JOIN value from an array (myAry) which takes all the values from the cell ranges which we have defined and it has returned the new string in the cell A1.

Notes

  • Join function doesn’t work if the array is declared as date data type or variant data type.

Related Functions

ARRAY | FILTER | LBOUND | SPLIT | UBOUND