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

 

[shg]

The only difference to me if that you're using a default property (Value) of the range in the second case, which, IMO, is only a venial sin.

I take it one step further and use this:

Function Cat(vInp As Variant, _
             Optional sSep As String = ",", _
             Optional bCatEmpty As Boolean = False) As String
    ' Catenates the elements of vInp separated by sSep
    ' Empty values and null strings are ignored unless bCatEmpty is True
 
    Dim vItem       As Variant
    Dim sItem       As String
 
    If bCatEmpty Then
        For Each vItem In vInp
            Cat = Cat & CStr(vItem) & sSep
        Next vItem
 
    Else
        For Each vItem In vInp
            sItem = CStr(vItem)
            If Len(sItem) Then Cat = Cat & sItem & sSep
        Next vItem
    End If
 
    If Len(Cat) Then Cat = Left(Cat, Len(Cat) - Len(sSep))
End Function

 

[Ruddles]

...is there any reason why the code cannot be re-written...

IME sometimes the only way to confirm this sort of thing is to test it.

 

[Domenic]

shg4421,

Thanks shg4421. That's great. So I guess if one wants to "go by the book" one should state the Value property for the range object explicitly, right? Also, I like your version, which deals with empty/null strings.

Ruddles,

For sure... I did test it, and it seemed to work fine. I just wanted to make sure that I wasn't missing something. Thanks Ruddles.

 

[Ruddles]

Yes indeed - I tend to mistrust most of the things I read on the Internet until I've tested them!

 

[shg]

... one should state the Value property for the range object explicitly, right?

I mostly avoid using default properties as a matter of practice, but sometimes it's just too convenient not to. I always use

myRng(1,2)

in preference to

myRng.Item(1,2)

... for example.

 

[Domenic]

shg4421, Ruddles,

Thank you very much! I really appreciate your help!

 

[tusharm]

1) If Harlan wrote something there is probably a very good reason why.

2) If IsArray returns true for a Range, then either it's improperly named or incorrectly written.

3) If a is an array or a collection, I would recursively call AConcat since there is no way of knowing the content of each element.

4) I would definitely not rely on Value being the default property. It's a prescription for a disaster, if not now then in the future.

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

 

[shg]

4. I would definitely not rely on Value being the default property. It's a prescription for a disaster, if not now then in the future.

It would certainly be a disaster for Microsoft; 10 million users would have their heads on a pike.

 

[tusharm]

It would certainly be a disaster for Microsoft; 10 million users would have their heads on a pike.

Tried .Net?