The Excel VBA Join function returns a single string from an array of strings separated by a delimiter.
This is the opposite of the VBA Split function which splits a string into a VBA Array across a delimiter.
VBA Join Function Syntax
Join( sourcearray [, delimiter ] )
Parameters
sourcearray
The one dimensional string array which elements you want to join into a single string.
delimiter
Optional. A delimiter that is to separate the joined string items in the array. By default this is a Space character.
Examples using the VBA Join functions
Simple concatenated string from Array
Dim arr As Variant, joinString as String joinString = Join(Array("Dragon", "Dog", "DRAGONfly", "Cat", "fly")) Debug.Print joinString 'Result: "Dragon Dog DRAGONfly Cat fly"
Notice that all items are separated in the final string. This is because the default delimiter is a Space character.
In above example I also used the VBA Array function to quickly define a VBA Array of strings.
Below a simple example showing that only String arrays can be Joined:
Dim arr As Variant, joinString as String joinString = Join(Array(1, 2, 3 )) 'ERROR: array is not a String array joinString = Join(Array("1", "2", "3" )) 'OK. Result: "1 2 3"
Concatenated string using delimiter
We will modify code example above using a delimiter of our choosing:
Dim arr As Variant, joinString as String joinString = Join(Array("Dragon", "Dog", "DRAGONfly", "Cat", "fly"), ";") Debug.Print joinString 'Result: "Dragon;Dog;DRAGONfly;Cat;fly"