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
| Parameter | Type | Required | Description |
|---|---|---|---|
| array | Array (Variant) | Yes | A one-dimensional array to join |
| delimiter | String | No | The delimiter (defaults to space) |
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
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
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
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
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
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
StringorVariant(for exampleLong()) → run-time error 13 (Type mismatch) - An element is
Null→ run-time error 94 (Invalid use of Null)
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.