Harlan Grove's Custom Function

[Domenic]

Here's Harlan Grove's custom function, which concatenates values within a range or array...

Function AConcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002

    Dim y As Variant

    If TypeOf a Is Range Then
    
        For Each y In a.Cells
            AConcat = AConcat & y.Value & sep
        Next y
    
    ElseIf IsArray(a) Then
    
        For Each y In a
            AConcat = AConcat & y & sep
        Next y
        
    Else
    
        AConcat = AConcat & a & sep
    
    End If
    
    AConcat = Left(AConcat, Len(AConcat) - Len(sep))
    
End Function

As you can see, it first tests for a range and then for an array. Since the IsArray function returns True for both a range and array, is there any reason why the code cannot be re-written as follows?

Function AConcat(a As Variant, Optional sep As String = "") As String

    Dim y As Variant

    If IsArray(a) Then
    
        For Each y In a
            AConcat = AConcat & y & sep
        Next y
    
    Else
    
        AConcat = AConcat & a & sep
    
    End If
    
    AConcat = Left(AConcat, Len(AConcat) - Len(sep))
    
End Function

 

[tusharm]

Clearly IsArray is fundamentally flawed. I assume that's why I don't use it, having always written my own IsArr function. Here's one:

Function IsArr(X) As Boolean
    On Error Resume Next
    IsArr = UBound(X) = UBound(X)
    End Function

My guess is that Harlan wrote his function for use strictly as a UDF. In this scenario, one cannot pass a complex data item in an array. So, his function doesn't need any recursion.

Nonetheless, if one wanted to use it as a VB(A) function, the recursive version would look like the below -- it has been tested *very, very lightly.*

Option Explicit

Function TMJoin(ByVal X As Variant, Optional ByVal Sep As String = "")
    If TypeOf X Is Range Then
        Dim aCell As Range
        For Each aCell In X.Cells
            TMJoin = TMJoin & aCell.Value & Sep
            Next aCell
    ElseIf IsArr(X) Then
        Dim Y As Variant
        For Each Y In X
            TMJoin = TMJoin & TMJoin(Y, Sep) & Sep
            Next Y
    ElseIf TypeOf X Is Object  Then
        'Cannot process a generic object _
         though one could add code to process a collection _
         or a dictionary
    Else
        TMJoin = TMJoin & X & Sep
        End If
    TMJoin = Left(TMJoin, Len(TMJoin) - Len(Sep))
    End Function

It is still restricted in that one cannot pass it multiple arguments -- of course, that too fits in well with the premise that its primary function is to concatenate a range. But, to generalize it to accept an arbitrary number of arguments, each of an arbitrary kind, including ranges, arrays, dictionaries, and collections, one has to do something a bit counterintuitive. The separator is specified first and is mandatory.

Option Base 0
Function TMJoin(ByVal Sep As String, ParamArray Z())
    Dim X As Variant
    For Each X In Z
        If TypeOf X Is Range Then
            Dim aCell As Range
            For Each aCell In X.Cells
                TMJoin = TMJoin & aCell.Value & Sep
                Next aCell
        ElseIf IsArr(X) Then
            Dim Y As Variant
            For Each Y In X
                TMJoin = TMJoin & TMJoin(Sep, Y) & Sep
                Next Y
        ElseIf TypeName(X) = "Dictionary" Then
            Dim aDictItem As Variant
            For Each aDictItem In X.items
                TMJoin = TMJoin & TMJoin(Sep, aDictItem) & Sep
                Next aDictItem
        ElseIf TypeOf X Is Collection Then
            Dim anItem As Variant
            For Each anItem In X
                TMJoin = TMJoin & TMJoin(Sep, anItem) & Sep
                Next anItem
        ElseIf TypeOf X Is Object  Then
            'Cannot process a generic object
        Else
            TMJoin = TMJoin & X & Sep
            End If
        Next X
    TMJoin = Left(TMJoin, Len(TMJoin) - Len(Sep))
    End Function

To test:

Sub testTMJoin()
    Dim X(1) As Object
    Set X(0) = CreateObject("scripting.dictionary")
    X(0).Add 1, "a"
    Set X(1) = CreateObject("scripting.dictionary")
    X(1).Add 3, "c"
    Dim Y As Collection: Set Y = New Collection
    Y.Add 2, "b"
    MsgBox TMJoin(vbNewLine, X, Y, Array(Array(0, 1), Array(2, 3)), Range("b126:b129"))
    End Sub

It can also be used as a UDF as in
=TMJoin(CHAR(10),E125:E129,{1,2,3,4,5,6},B123:B129)

 

[Colin Legg]

There are a lot of questions and points on this thread, so I will restrict myself to a couple of key themes.

Firstly, use of the .Cells property:

Greg, Mike,

So you've both experienced problems with .Cells not being specified. Good to know. All the more reason to be safe. I guess it's not only good practice, but it also makes the intent clear. Thanks guys, much appreciated.

Cells, columns and rows are all Range class objects. Each time a (for example) range property is called, a new range object is created. When a range object is created, an internal flag is used to indicate whether it is a cell, column or row 'type'.

The function cannot control the 'type' of range is passed into it so the .Cells property is specified to ensure that the For Each loop enumerates through the range on a cell by cell basis, rather than by columns or by rows.

For example, if you remove the .Cells from the original function and then call it as follows, the result is disasterous:

Sub test()
    Dim v
 
    v = AConcat(Range("A1").EntireColumn)
 
End Sub
 
Function AConcat(a As Variant, Optional sep As String = "") As String
    Dim y As Variant
    If TypeOf a Is Range Then
 
        For Each y In a
            AConcat = AConcat & y.Value & sep
        Next y
 
    ElseIf IsArray(a) Then
 
        For Each y In a
            AConcat = AConcat & y & sep
        Next y
 
    Else
 
        AConcat = AConcat & a & sep
 
    End If
 
    AConcat = Left(AConcat, Len(AConcat) - Len(sep))
 
End Function

The second point I will make is one that has already been agreed on the thread: the .Value property absolutely should be used. I think that Tushar's mention of .Net was very prudent and, as a .Net learner, it put a little smile on my face.

The third point I will make is that no allowance has been made in the function for error values in cells/implicit string conversion within the function. I'm not keen on that. If a cell in the range contains an error value then a RTE will occur.

 

[mikerickson]

I've use CStr to protect against error values from cells causing code to crash.

AConcat = AConcat & CStr(y.Value) & sep

If I'm expecting a numeric result, I have to apply Val to the CStr.

When gorilla proofing, "garbage in, garbage out" is a goal, rather than "garbage in, dump truck breaks".

 

[Domenic]

Richard,

Am I missing something? When I try passing the multi-region ranges to the custom function, each area is concatentated, not just the first one. This holds true whether or not I include the range test with the .Cells and .Value properties.

Tushar,

Yes, I had wondered why the IsArray function returns True for a range. Somehow it doesn't make sense. However, your IsArr function takes care of this nicely. Also, I now see your concern with an array or collection when used as a VBA function. Your recursive version also deals with this nicely. Very interesting. Your last example is a comprehensive one. It too is very interesting. I'll need to take a closer look at it.

Colin,

That's great. Your example clearly illustrates why the .Cells property is needed when used as a VBA function. Although, it doesn't seem to be an issue when used as a worksheet function.

I think the reason no allowance was made for error values/implicit string conversion is probably because, as Tushar suspects, the function was written for use strictly as a worksheet function. As such, it would behave like native functions. So, for example, as you well know, a worksheet formula could deal with error values as follows...

{=SUBSTITUTE(AConcat(IF(1-ISERROR(Range),", " &Range,"")),", ","",1)}

 

[shg]

Here's an example where the results are different:

    Dim c As Range
 
    For Each c In Rows(1)
        Debug.Print c.Address
    Next c
 
    For Each c In Rows(1).Cells
        Debug.Print c.Address
    Next c

EDIT: Nevermind, sorry, I see that Colin covered this in his last post.

 

[Greg Truby]

Colin and shg - THANKS!

For any readers that are using XL2007 or better, you might not want to loop through 16K columns...

    Dim c As Range, i%
 
    For Each c In Rows(1)
        Debug.Print c.Address
    Next c
 
    For Each c In Rows(1).Cells
        Let i = i + 1: If i > 26 Then Exit For
        Debug.Print c.Address
    Next c

 

[shg]

Sorry if I'm pointing out the obvious, but it doesn't just apply to whole rows:

    Dim c As Range
 
    For Each c In Range("A1:E1").Rows(1)
        Debug.Print c.Address
    Next c
 
    For Each c In Range("A1:E1").Rows(1).Cells
        Debug.Print c.Address
    Next c

 

[Domenic]

Mike,

I like it... "gorilla proffing"

Greg,

Yeah, I don't think anyone wants to loop through 16k columns. Of course, one could always step through the code using F8, as I did. Thanks Greg!

shg,

I'm glad you pointed that out. I hadn't thought about that aspect of it and this certainly emphasizes the point. Thanks shg!

 

[Greg Truby]

...one could always step through the code using F8, ...

Aye, as did I. The tuppence weren't fer someone like you, but rather any VBA newbie that might just let 'er rip. :wink:

 

[Domenic]

Aye, as did I. The tuppence weren't fer someone like you, but rather any VBA newbie that might just let 'er rip. :wink:

Greg, I have to say that you really do have a way with words. I like it...