VBA JOIN Function (Syntax + Example)

Last Updated: March 24, 2024
puneet-gogia-excel-champs

- Written by Puneet

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 the VBA JOIN function, you need to go through the below example where we have written a vba code by using it:

example-vba-join-function
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 the JOIN value from an array (myAry) which takes all the values from the cell ranges that we have defined and it has returned the new string in cell A1.

Notes

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