How to use Join function in VBA?

In VBA (Visual Basic for Applications), the `Join` function is used to concatenate elements of an array into a single string, with each element separated by a specified delimiter.

Here is the syntax for the `Join` function:

Join(sourceArray, [delimiter])

`sourceArray` is the array containing the elements you want to concatenate.
`delimiter` is an optional string character that will be placed between each array element in the resulting string. If omitted, the default delimiter is a space.

Below is an example of how to use the `Join` function in VBA:

Sub ExampleJoin()
    Dim arr() As String
    Dim result As String
    
    ' Define an array with some elements
    arr = Array("Hello", "World", "This", "Is", "VBA")
    
    ' Use Join function with a space as a delimiter
    result = Join(arr, " ")
    
    ' Output: "Hello World This Is VBA"
    Debug.Print result
    
    ' Use Join function with a comma and space as a delimiter
    result = Join(arr, ", ")
    
    ' Output: "Hello, World, This, Is, VBA"
    Debug.Print result
End Sub

To run this code in Excel:

Press `ALT` + `F11` to open the VBA editor.
Insert a new module by right-clicking on any existing module (or on the VBAProject), then choose `Insert` > `Module`.
Paste the above code into the module.
Press `F5` while the cursor is inside the `ExampleJoin()` procedure to execute the code.

The outputs of the `Join` function will be printed to the Immediate Window (`Ctrl` + `G` to display if it’s not already visible).

You can use the `Join` function with different types of arrays, including numeric arrays, but prior to joining, the numeric values will be implicitly converted to strings, so be mindful of any potential formatting issues.

facebooklinkedin