vba · DOCS

Join Function

Maintained on

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

ParameterTypeRequiredDescription
arrayArray (Variant)YesA one-dimensional array to join
delimiterStringNoThe delimiter (defaults to space)

Return Value

A combined string (String type)

Basic Usage

Simple Join

join_basic.bas
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

join_default_delimiter.bas
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

Tip

When you omit the delimiter, elements are joined with a space (” ”). Passing an empty string ("") explicitly joins the elements with no separator.

Combining Split and Join

Processing and Rejoining Data

split_join_combo.bas
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

join_create_csv.bas
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

join_sql_in.bas
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

Caution

When using string IDs in an IN clause, wrap each element in single quotes. Concatenating user input directly into SQL also invites SQL injection, so use parameterized queries in production.

Creating HTML Lists

join_html_list.bas
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

join_range_values.bas
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

join_file_path.bas
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

join_report.bas
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

join_array_to_string.bas
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

Arrays That Join Cannot Handle

Join accepts only one-dimensional arrays. The following cases raise errors:

  • Passing a two-dimensional array (such as Range("A1:C3").Value) → run-time error 5 (Invalid procedure call or argument)
  • Passing an array of a type other than String or Variant (for example Long()) → run-time error 13 (Type mismatch)
  • An element is Null → run-time error 94 (Invalid use of Null)
join_range_row.bas
Sub JoinRangeRow()
    Dim v As Variant
    Dim arr() As Variant
    Dim c As Long

    v = Range("A1:C1").Value
    ReDim arr(0 To UBound(v, 2) - 1)
    For c = 1 To UBound(v, 2)
        arr(c - 1) = v(1, c)
    Next c

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

チェック

In Excel 2019 and later or Microsoft 365, the TEXTJOIN worksheet function can join a cell range directly. From VBA, call Application.WorksheetFunction.TextJoin(",", True, Range("A1:C1")).

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.

  • #VBA
  • #Join
  • #String Operations
  • #Array