VBA Join Function
EXCEL VBA Join function joins (concatenates) two or more elements separated by a user specified delimiter (separator).
Syntax:
Join(Array, Sep)
The VBA Join function accepts 2 arguments, as described below.
Arguments:
| Array | - a single dimension array of elements |
| Sep | - delimiter to insert between joined elements |
Notes about usage:
- Array may be string or variant. Join will not accept any other data types for Array.
- Array elements may be of any data type, but the joined result will be string.
- Array elements can be of different data types.
- Any of several data types may be the delimiter.
- If the delimiter is omitted, then a blank space (" ") is inserted between array elements.
- If the delimiter is a zero-length string (""), then the elements are concatenated with no separator.
- If the elements of Array are zero-length strings (""), then zero-length strings ("") are joined with the specified delimiter.
- If the elements of Array are zero-length strings ("") and the delimiter is a zero-length string (""), then a zero-length string is returned.
How to use VBA Join function to concatenate two or more strings in VBA code with no delimiter
Sub joinEX1()
Dim arr(2) As String
arr(0) = "Annabelle"
arr(1) = "is"
arr(2) = "asleep."
MsgBox Join(arr, Sep)
Returns "Annabelleisasleep."
End Sub
In this example, the delimiter Sep was not set, so its value is by default a zero-length string (""). The three input strings are concatenated with no separation.
How to use VBA Join function to concatenate two or more strings in VBA code with a single space delimiter
Sub joinEX2()
Dim arr(2), Sep As String
arr(0) = "Annabelle"
arr(1) = "is"
arr(2) = "asleep."
Sep = " "
MsgBox Join(arr)
MsgBox Join(arr, Sep)
Both return "Annabelle is asleep."
End Sub
If we call Join when omitting the delimiter, it will have the same effect as if we had set the delimiter to a single blank space. The three strings are concatenated with a single space between.
Use the VBA Space function to control the number of spaces between joined strings
arr(0) = "Annabelle"
arr(1) = "is"
arr(2) = "asleep."
Sep = Space(0)
MsgBox Join(arr, Sep)
Returns "Annabelleisasleep."
Sep = Space(1)
MsgBox Join(arr, Sep)
Returns "Annabelle is asleep."
Sep = Space(5)
MsgBox Join(arr, Sep)
Returns "Annabelle is asleep."
The Excel VBA Space function allows you to more easily control the spacing between strings. An argument of zero (0) returns the three strings with no blank spaces between them. Space(1) joins the three with a single space between, while Space(5) inserts five (5) blank spaces.
Add new lines when using Join to concatenate strings in VBA code
Sep = vbNewLine
MsgBox Join(arr, Sep)
' Returns "Annabelle
Is
asleep."
Sep = vbNewLine & vbNewLine
MsgBox Join(arr, Sep)
' Returns "Annabelle
Is
asleep."
One or more VBA new lines can separate strings.
Join delimiter taking several data types, some of which may be retrieved from a spreadsheet
Sub joinEX5()
Dim X1 As String
Dim X2 As Integer
Dim X3 As Double
Dim X4 As Boolean
Dim X5 As Date
Dim X6 As Currency
Dim arr(2)
arr(0) = "?"
arr(1) = "/"
arr(2) = "@"
X1 = "Hello"
X2 = 5
X3 = 5.7
X4 = False
X5 = Cells(1, 1)
X6 = Cells(2, 1)
MsgBox Join(arr, X1)
MsgBox Join(arr, X2)
MsgBox Join(arr, X3)
MsgBox Join(arr, X4)
MsgBox Join(arr, X5)
MsgBox Join(arr, X6)
End Sub
Here, we have two data types stored in a spreadsheet. One is date format and the other currency format.
While we can only use an array that is string or variant to store the expressions to be joined, VBA Join will accept delimiters of many data types. In this example, strings of special (string) characters stored in a single-dimension array, arr(2), are joined using delimiters that are of six different data types: string, integer, double, Boolean, date and currency.
Note that arr(2) is declared, but the data type was not set. VBA allows this syntax and will set the data type as variant, so arr(2) was initialized as variant.
Join elements of several data types, including non-string data types
Sub joinEX6()
Dim X1 As String
Dim X2 As Double
Dim X3 As Boolean
Dim arr(2)
X1 = "Hello"
X2 = 5.7
X3 = False
arr(0) = X1
arr(1) = X2
arr(2) = X3
MsgBox Join(arr)
End Sub
The array, arr(2), is again a variant. Its first element arr(0) is string data type, the second arr(1) is double, and the third arr(2) is Boolean. These retain their data types within the array, but the array itself is variant. The returned value is string.
Note: If arr() had been declared as string, then each of the elements stored in arr would be converted to string when stored in the array, and the returned value is string.
