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]

No, but have used Fortran, C, C++, and a little Pascal, all of which are not, like VB.Net, VBA.

 

[Domenic]

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

Yeah, agreed... So why is the Cells property added to 'a' when 'a' is a range?

For Each y In a.Cells

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

When a range is passed to the function, IsArray(a) returns True. IsArray(Range("A2:A10")) also returns True. I assumed this is the reason why the test for 'Range' occurs before one for an 'Array'.

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.

Can you please provide an example?

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.

Point taken... Better to be on the safe side.

 

[Greg Truby]

Hmmm, I'd never thought of Tushar's point #3... an excellent point.

Domenic, I don't know why Harlan did it that-a-way unless it was to specify the .Value property. Oh, and IsArray() will return false on a single-cell range. Although I didn't test it, I would think that the ELSE clause would then kick in.

 

[Greg Truby]

Yeah, agreed... So why is the Cells property added to 'a' when 'a' is a range?

For Each y In a.Cells

I've never been able to pinpoint why, but I have had cases where a

For Each rngItem in rngBigRange

failed because I didn't specify the .Cells bit.

 

[mikerickson]

Yeah, agreed...  So why is the Cells property added to 'a' when 'a' is a range?
[CODE]For Each y In a.Cells

[/CODE]

If a is a single cell range, a, without specifying .Cells, returns a value. One cannot loop through a value.

a.Value would return a value if a is a single cell, but an array if its not.

That's my guess as to why .Cells is specified.

 

[Greg Truby]

Mike,

The following does not error out for me

Sub foo()
    Dim r As Range, c As Range
 
    Set r = Range("A1:B3")
    For Each c In r
        Debug.Print c.Address
    Next c
 
    Set r = Range("A2")
    For Each c In r
        Debug.Print c.Address
    Next c
 
End Sub

Or did I misunderstand your point?

 

[mikerickson]

Greg, Yes, you got my point. I'm surprised. (That's what comes from posting without testing)

Like you, I've have situations where specifying .Cells was required, for no apparent reason. The Array/value dichotomy has bitten me in the past, so... it appears that Harlan's inclusion of .Cells was a result of his following the "good practice" (IMO) of never trusting defaults for production work, even though it may not be actually needed.

 

[Greg Truby]

Drat! I was hoping I'd misunderstood and that you'd found the cause. Like I said, I've had FOR EACH loops fail, but I've never pinpointed a common denominator.

 

[Domenic]

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.

 

[Richard Schollar]

Harlan may well have used the range test so that multi-region ranges passed in as arguments got concatenated instead of just the first area's cells, eg:

MsgBox Aconcat(Range("A1,B3,G5,Z10"))