How to pass multirange in VBA functions as parameter?

Question

Public Function iQuery(pName As Varient, pTime As Varient)
For Each item in pName
   ...
Next item
End Function

I have a function named iQuery and I want to pass range $A$3,$A$4,$A$5,$A$6 as pName and range $A$19 as pTime but it picks $A$3 as pName and $A$4 as pTime when iQuery is called as iQuery($A$3,$A$4,$A$5,$A$6,$A$19)

Answer 1

If you are doing this from VBA then you need to pass the range as range objects. See this example

Sub Sample()
    Dim rngA As Range
    Dim rngB As Range
    Dim Ret As ??????

    Set rngA = Range("$A$3,$A$4,$A$5,$A$6")
    Set rngB = Range("$A$4")

    Ret = iQuery(rngA, rngB)
End Sub

Public Function iQuery(pName As Range, pTime As Range) As ??????
    Dim itm As Range

    For Each itm In pName
       ...
    Next itm
End Sub

Where ?????? is the relevant datatype you want to return.

If you are doing this from the worksheet then pass it as one range. For example

=iQuery($A$3:$A$6,$A$19)

But What if sFormula is "=iQuery($A$3,$B$14,$C$11,$A$6,$A$19)" and I want first parameter to be $A$3,$B$14,$C$11,$A$6 and second one $A$19 – rs4 18 mins ago

For non contigous ranges, use a bracket. For example

=iQuery(($A$3,$B$14,$C$11,$A$6),$A$19)