Hello all,

I know there are various versions of this same thing out there (AConcat by Harlan Grove, MCONCAT from the MoreFunc addin, etc), but I wanted to throw my version in the ring (also so I can find it/point people to it if it ever comes up). I developed it to work with ranges, arrays, and collections so that it can be used in worksheet formulas (both regular and array formulas) as well as with other VBA code that might be using collections. It ignores anything passed to it that has a Len(0) (which I have found very handy).

The function call:
ConcatAll(varData, [sDelimiter])

The function takes two arguments:
varData: This is a Required variant that can be a Range object, Array, Collection, or single item (like a String or Double value).
sDelimiter: This is an Optional string used to determine how the data is concatenated. The default is vbNullString, so there will be no separation if this argument is omitted.

Examples:
=ConcatAll({"a","b","c"}) -> will result in "abc"
=ConcatAll({"a","b","c"},"/") -> will result in "a/b/c"

If "a" "b" "c" are in cells A1:A3, then you could similarly use:
=ConcatAll(A1:A3) -> will result in "abc"
=ConcatAll(A1:A3,", ") -> will result in "a, b, c"

Here is the code:
Public Function ConcatAll(ByVal varData As Variant, Optional ByVal sDelimiter As String = vbNullString) As String
'Created by TigerAvatar at www.excelforum.com, September 2012
'Purpose is to concatenate many strings into a single string
'Can be used with arrays, range objects, and collections
    
    Dim DataIndex As Variant    'Used to loop through arrays, range objects, and collections
    Dim strResult As String     'Used to build the result string
    
    'Test if varData is an Array, Range, or Collection
    If IsArray(varData) _
    Or TypeName(varData) = "Range" _
    Or TypeName(varData) = "Collection" Then
        
        'Found to be an, array, range object, or collection
        'Loop through each item in varData
        For Each DataIndex In varData
            'Check if the item isn't empty and if so add it to the result with the delimiter
            If Len(DataIndex) > 0 Then strResult = strResult & sDelimiter & DataIndex
        Next DataIndex
        
        'Correct strResult to remove beginning delimiter
        strResult = Mid(strResult, Len(sDelimiter) + 1)
        
    Else
        'Found not to be an array, range object, or collection
        'Simply set the result = varData
        strResult = varData
    End If
    
    'Output result
    ConcatAll = strResult
    
End Function


As this is in Tips and Tutorials, here are some quick steps to implenting the code in a workbook:
How to use a User Defined Function (UDF):
  1. Make a copy of the workbook the macro will be run on
    • Always run new code on a workbook copy, just in case the code doesn't run smoothly
    • This is especially true of any code that deletes anything
  2. In the copied workbook, press ALT+F11 to open the Visual Basic Editor
  3. Insert | Module
  4. Copy the provided code and paste into the module
  5. Close the Visual Basic Editor


Now you will have the UDF available and can use it as a worksheet formula as shown in the examples above. It can also be called from within VBA for joining collections, multiple dimensional arrays, etc. It is my sincere hope that others will find this useful.

Regards,
~TigerAvatar