Add delimiter to concatenated list

I found this custom Excel Function:

Function Join(source As Range, Optional delimiter As String) As String
'
' Join Macro
' Joins (concatenates) the values from an arbitrary range of cells,
' with an optional delimiter.
'

'optimized for strings
'   check len is faster than checking for ""
'   string Mid$ is faster than variant Mid
'   nested ifs allows for short-circuit + is faster than &

    Dim sResult As String
    Dim oCell As R开发者_StackOverflow中文版ange

    For Each oCell In source.Cells
        If Len(oCell.Value) > 0 Then
            sResult = sResult + CStr(oCell.Value) + delimiter
        End If
     Next

    If Len(sResult) > 0 Then
        If Len(delimiter) > 0 Then
            sResult = Mid$(sResult, 1, Len(sResult) - Len(delimiter))
        End If
    End If

    Join = sResult
End Function

I would like to tweak it to show a comma between each cell it combines to create a list.

There are a couple things wrong with that UDF you found:

• Concatenation should be done with "&" not "+".
• Working with cells in a range is slower than a variant array and working purely from inside VBA. Each call to Excel produces a small hit in performance that could add up.
• The casting to string is unnessesary if the concatenation was done properly.
• Concatenation should be optimized so that the smaller parts are joined first, then added to the result, otherwise the result is copied twice to do each concatenation.
• Name should not be Join since VBA has a function of that name.
• There should be no need to check for LEN of delimiter since it's a string. By default it will be LEN(0) if not existing and you can subtract 0 from the len(result) without any worry.
• Not a big deal but checking for inequality <> is slightly faster than >.

Here's my version. By default it will seperate each cell by ", " if you leave the second argument empty (ex. =ConcatenateRange(A1:A100)

Function ConcatenateRange(ByVal cell_range As range, _
                    Optional ByVal seperator As String = ", ") As String

Dim cell As range
Dim newString As String
Dim vArray As Variant
Dim i As Long, j As Long

vArray = cell_range.Value

For i = 1 To UBound(vArray, 1)
    For j = 1 To UBound(vArray, 2)
        If Len(vArray(i, j)) <> 0 Then
            newString = newString & (seperator & vArray(i, j))
        End If
    Next
Next

If Len(newString) <> 0 Then
    newString = Right$(newString, (Len(newString) - Len(seperator)))
End If

ConcatenateRange = newString

End Function

It looks like it already does this with the optional delimiter parameter.

Just call it like this:

=JOIN(A1:A100,",")