# Harlan Grove's Custom Function

#### Domenic

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

Code:
``````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?

Code:
``````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``````

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

#### shg

##### MrExcel MVP
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:

Code:
``````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

##### Well-known Member
...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

##### MrExcel MVP
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

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

#### shg

##### MrExcel MVP
Domenic said:
... 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
Code:
``myRng(1,2)``
in preference to
Code:
``myRng.Item(1,2)``
... for example.

#### Domenic

##### MrExcel MVP
shg4421, Ruddles,

Thank you very much! I really appreciate your help!

#### tusharm

##### MrExcel MVP
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...

Code:
``````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?

Code:
``````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

##### MrExcel MVP
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

##### MrExcel MVP
It would certainly be a disaster for Microsoft; 10 million users would have their heads on a pike.
Tried .Net?