Excel VBA: Unlimited Range Inputs With Versatility (?)

Question

Could someone help me create a function that will handle an unlimited number of diverse ranges? I have tried "Paramarray variables() as Variant" and "variable as Range" in my arguments list but neither of them provide the versatility that I am looking for.

The key is that I want my function to be able to simultaneously handle things like "MyFunc(A1:A10, B1)" or "MyFunc(A1, B1:10, C11)". The problem I'm finding is that "ParamArray" can only handle comma separated inputs while "variable as Range" can only handle non-comma separated inputs.

Basically, I want to have the same functionality that the SUM() function has. Where SUM can handle an infinite (sort of) number of inputs regardless if they are separated by commas or are in a range.

As requested, here is my code:

Function COMMA_DELIMITER(inputs as Range)
' this function basically concatenates a consecutive set of cells and places commas between values

For Each j in Inputs
    stringy = stringy & j.value & chr(44)
Next

stringy = Left(stringy, Len(stringy) - 1)

COMMA_DELIMITER = stringy

End Function

or

Function COMMA_DELIMITER_A(ParamArray others())
'this is the same function, only the cells don't have to be consecutive
For i = 1 to UBound(others) + 1
    stringy = stringy & others(i-1) & chr(44)
Next

COMMA_DELIMIERTER_A = Left(stringy, Len(stringy) - 1)
End Function

I pretty much want to create a function that has the flexibility to handle both consecutive cells and/or non-consecutive cells. The inputs would look like this, "=MyFunc(A1, B1:B10, C11, D12:D44)".

Could someone help me create a function that can handle something like this, "MyFunc(A1, B1:B10, C11, D12:D44)"?

Thanks,

Elias

Edit your question to show the code you tried: much easier to help that way. — Tim Williams, May 23 '13 at 17:44
"It wont work" is not really helpful, please post your current code as @TimWilliams suggests. This will help us here. — enderland, May 23 '13 at 19:03

score 1 · Answer 1 · edited May 23 '17 at 12:13

Actually it is possible to do that, and code from chris neilsen is almost there.

Function MyFunc1(ParamArray r()) As Variant
    Dim rng As Range
    Dim i As Long
    Dim j As Variant
    Dim s As String
      For i = LBound(r) To UBound(r)
        For each j in r(i)
          s = s & " " & j.Address
        Next
      Next
    MyFunc1 = s
End Function

See? You only have to put one more loop, so if you have a range like [A1:A4] it will loop for into that too. One loop will return another ParamArray, so you have to loop for twice. And if you have just [A1] that's not a problem either, the double looping will cause no problem.

score 0 · Answer 2 · answered May 24 '13 at 08:17

I think there are two issues with your approach

, and (comma and space) are the Union and Intersect operators for ranges
To pass a multi area range into a single parameter, you need to enclose it in ( )

To demonstrate

ParamArray version
Loop through the array variable to access to individual ranges

Function MyFunc1(ParamArray r()) As Variant
    Dim rng As Range
    Dim i As Long
    Dim s As String
    For i = LBound(r) To UBound(r)
        s = s & " " & r(i).Address
    Next

    MyFunc1 = s
End Function

Range version
Iterate the range Areas collection to access individual ranges

Function MyFunc2(r As Range) As Variant
    Dim rng As Range
    Dim i As Long
    Dim s As String
    For Each rng In r.Areas
        s = s & " " & rng.Address
    Next

    MyFunc2 = s
End Function

Both
=MyFunc1(B5:D5 C4:C6,B10:E10,D13:D16)
and
=MyFunc2((B5:D5 C4:C6,B10:E10,D13:D16))
will return
$C$5 $B$10:$E$10 $D$13:$D$16
(note that the intersection of B5:D5 and C4:C6 is C5)

Excel VBA: Unlimited Range Inputs With Versatility (?)

2 Answers2