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.

Basic syntax

Join(sourceArray, [delimiter])

Parameter

• 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

In this example, the Join function concatenates the elements of the words array into a single string, separating each element with a space.

Custom Delimiter Example

You can also use a custom delimiter. For instance, if you want to join elements with a comma and a space:

Sub JoinExampleWithComma()
    Dim items() As String
    items = Array("Apple", "Banana", "Cherry")

    Dim itemList As String
    itemList = Join(items, ", ")  ' Using comma and space as delimiters

    MsgBox itemList  ' Output: "Apple, Banana, Cherry"
End Sub

Joining Numbers

If your array consists of numeric values, they will be automatically converted to strings:

Sub JoinNumbers()
    Dim numbers() As Integer
    numbers = Array(1, 2, 3, 4, 5)

    Dim numberString As String
    numberString = Join(numbers, "-")  ' Using hyphen as a delimiter

    MsgBox numberString  ' Output: "1-2-3-4-5"
End Sub

Important Notes

• If any element in the array is Null, Join will return Null.
• Join is particularly useful when you need to quickly convert an array into a single, formatted string.

By using the Join function in these ways, you can efficiently concatenate array elements into a string with the desired format in VBA.

Follow us on Social Media

facebooklinkedin

Unlock Your Potential

Excel

Access

Power BI

Help us grow the project