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
VBA
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.
VBA
In this example, the
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
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:VBA
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:VBA
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.
Join function in these ways, you can efficiently concatenate array elements into a string with the desired format in VBA.
Unlock Your Potential
Excel
Access
Power BI
