Join Function

The Join function combines array elements into a single string using a specified delimiter. It’s the reverse operation of Split and is very useful for data output and formatting.

Basic Syntax

Join(array, [delimiter])

Parameters

Return Value

A combined string (String type)

Basic Usage

Simple Join

Sub JoinBasicExample()
    Dim fruits() As Variant
    Dim result As String

    ' Define array
    fruits = Array("apple", "banana", "orange")

    ' Join with comma
    result = Join(fruits, ",")
    Debug.Print result  ' apple,banana,orange

    ' Join with space
    result = Join(fruits, " ")
    Debug.Print result  ' apple banana orange

    ' Join with line breaks
    result = Join(fruits, vbCrLf)
    Debug.Print result
    ' Output:
    ' apple
    ' banana
    ' orange
End Sub

Omitting the Delimiter

Sub JoinDefaultDelimiter()
    Dim words() As Variant
    Dim sentence As String

    words = Array("Hello", "World", "from", "VBA")

    ' Omit delimiter (joins with space)
    sentence = Join(words)
    Debug.Print sentence  ' Hello World from VBA
End Sub

Combining Split and Join

Processing and Rejoining Data

Sub SplitJoinCombo()
    Dim text As String
    Dim parts() As String
    Dim result As String

    ' Read CSV data and process
    text = "apple,banana,orange"

    ' Split
    parts = Split(text, ",")

    ' Process each element (e.g., add numbering)
    Dim i As Long
    For i = LBound(parts) To UBound(parts)
        parts(i) = (i + 1) & ". " & parts(i)
    Next i

    ' Rejoin
    result = Join(parts, vbCrLf)
    Debug.Print result
    ' Output:
    ' 1. apple
    ' 2. banana
    ' 3. orange
End Sub

Creating CSV Data

Sub CreateCSVLine()
    Dim data() As Variant
    Dim csvLine As String

    ' Create data array
    data = Array("John Smith", "Sales", "New York", "jsmith@example.com")

    ' Convert to CSV format
    csvLine = Join(data, ",")
    Debug.Print csvLine
    ' Output: John Smith,Sales,New York,jsmith@example.com

    ' Write to file
    Dim fileNum As Integer
    fileNum = FreeFile
    Open "C:\output.csv" For Append As #fileNum
    Print #fileNum, csvLine
    Close #fileNum
End Sub

Practical Examples

Building SQL IN Clause

Function BuildSQLInClause(values As Variant) As String
    Dim quotedValues() As String
    Dim i As Long

    ReDim quotedValues(LBound(values) To UBound(values))

    For i = LBound(values) To UBound(values)
        quotedValues(i) = "'" & values(i) & "'"
    Next i

    BuildSQLInClause = "(" & Join(quotedValues, ", ") & ")"
End Function

Sub TestBuildSQL()
    Dim cities As Variant
    cities = Array("NYC", "LA", "Chicago")

    Debug.Print "SELECT * FROM users WHERE city IN " & BuildSQLInClause(cities)
    ' Output: SELECT * FROM users WHERE city IN ('NYC', 'LA', 'Chicago')
End Sub

Creating HTML Lists

Function CreateHTMLList(items As Variant) As String
    Dim listItems() As String
    Dim i As Long

    ReDim listItems(LBound(items) To UBound(items))

    For i = LBound(items) To UBound(items)
        listItems(i) = "  <li>" & items(i) & "</li>"
    Next i

    CreateHTMLList = "<ul>" & vbCrLf & Join(listItems, vbCrLf) & vbCrLf & "</ul>"
End Function

Sub TestHTMLList()
    Dim fruits As Variant
    fruits = Array("Apple", "Banana", "Orange")

    Debug.Print CreateHTMLList(fruits)
    ' Output:
    ' <ul>
    '   <li>Apple</li>
    '   <li>Banana</li>
    '   <li>Orange</li>
    ' </ul>
End Sub

Joining Range Values

Sub JoinRangeValues()
    Dim rng As Range
    Dim cell As Range
    Dim values() As String
    Dim i As Long

    Set rng = Range("A1:A5")

    ReDim values(0 To rng.Cells.Count - 1)

    i = 0
    For Each cell In rng
        values(i) = CStr(cell.Value)
        i = i + 1
    Next cell

    Debug.Print Join(values, ", ")
End Sub

Building File Paths

Function BuildPath(ParamArray pathParts() As Variant) As String
    BuildPath = Join(pathParts, "\")
End Function

Sub TestBuildPath()
    Dim fullPath As String

    fullPath = BuildPath("C:", "Users", "John", "Documents", "report.xlsx")
    Debug.Print fullPath  ' C:\Users\John\Documents\report.xlsx
End Sub

Creating Formatted Reports

Sub CreateReport()
    Dim reportLines() As String
    Dim i As Long

    ReDim reportLines(0 To 5)

    reportLines(0) = "=== Sales Report ==="
    reportLines(1) = "Date: " & Format(Date, "yyyy-mm-dd")
    reportLines(2) = ""
    reportLines(3) = "Total Sales: $" & Format(12345.67, "#,##0.00")
    reportLines(4) = "Items Sold: 150"
    reportLines(5) = "==================="

    ' Join with line breaks for display
    MsgBox Join(reportLines, vbCrLf), vbInformation, "Report"
End Sub

Converting Array to Delimited String

Function ArrayToString(arr As Variant, Optional delim As String = ", ") As String
    If Not IsArray(arr) Then
        ArrayToString = CStr(arr)
        Exit Function
    End If

    ' Handle empty array
    On Error Resume Next
    If UBound(arr) < LBound(arr) Then
        ArrayToString = ""
        Exit Function
    End If
    On Error GoTo 0

    ' Convert each element to string
    Dim strArr() As String
    Dim i As Long

    ReDim strArr(LBound(arr) To UBound(arr))

    For i = LBound(arr) To UBound(arr)
        strArr(i) = CStr(arr(i))
    Next i

    ArrayToString = Join(strArr, delim)
End Function

Sub TestArrayToString()
    Dim numbers As Variant
    numbers = Array(1, 2, 3, 4, 5)

    Debug.Print ArrayToString(numbers)          ' 1, 2, 3, 4, 5
    Debug.Print ArrayToString(numbers, " - ")   ' 1 - 2 - 3 - 4 - 5
End Sub

Summary

The Join function is the complement to Split for string manipulation in VBA:

• Combines array elements with a specified delimiter
• Defaults to space when delimiter is omitted
• Perfect for creating CSV data, SQL clauses, HTML, and reports
• Works well with Split for data transformation workflows
• Essential for building formatted output from arrays

Master Join along with Split for complete string manipulation capabilities in your VBA projects.