Pass multi-area range to user-defined function

Question

I have the following code:

Function MyFunc(DataRng As Range) As String

    Debug.Print "Exitting Function."

    MyFunc = "Dog"

End Function

The following cell formula is fine: =MyFunc(A1:B1)

The following cell formula generates a #VALUE! error: =MyFunc(A1:B1,D1:E1)

I read somewhere on the web that passing multi-area ranges to user-defined functions is not recommended.  My two question are:

1. Why is it not recommended i.e. is it an issue with Excel stability/correctness?
2. Are UDFs designed in such a way that passing multi-area ranges should be possible?

Thanks.

Answer 1

As written:  =MyFunc(A1:B1,D1:E1)  you are passing two arguments to your function which is only set up to accept a single argument.

One solution is to use ParamArray.

Another, if you want to pass a multiple range argument to your function, as written, you need to enter the formula:

=MyFunc((A1:B1,D1:E1))

Note that the multirange itself is enclosed within parentheses.

And take a look at this for some further insight:

Option Explicit

Function MyFunc(DataRng As Range) As String

    Debug.Print DataRng.Areas.Count, _

        DataRng.Areas(1).Address, DataRng.Areas(2).Address

    MyFunc = "Dog"

End Function

Answer 2

I have the following code:

Function MyFunc(DataRng As Range) As String

    Debug.Print "Exitting Function."

    MyFunc = "Dog"

End Function

The following cell formula is fine: =MyFunc(A1:B1)

The following cell formula generates a #VALUE! error: =MyFunc(A1:B1,D1:E1)

I read somewhere on the web that passing multi-area ranges to user-defined functions is not recommended.  My two question are:

1. Why is it not recommended i.e. is it an issue with Excel stability/correctness?
2. Are UDFs designed in such a way that passing multi-area ranges should be possible?

Thanks.

Ron has already shared ways to do what you want.  As far as what you read somewhere goes, it's just flat out wrong.  There's nothing wrong with passing multi-area ranges to a UDF.  It's just that the UDF must be coded for that possibility -- and, of course, in some cases the functionality might not allow it but that's a different issue.

Answer 3

Thanks, Ron, Tushar, Jim.

I can't seem to mark more than two replies as the answer, even though they all answer various aspects of the question...

Answer 4

You can use a param array something like this...

Public Function MySum(ParamArray rng() As Variant) As Double

    Dim lng As Long

    For lng = LBound(rng) To UBound(rng)

        MySum = MySum + Application.Sum(rng(lng))

    Next lng

End Function

Generally you use these arrays when you have a variable number of inputs such as ranges in your case. There is nothing wrong with doing this. It is basically what the Sum function is doing in standard XL.