How to Pass a Range to Function and have it recognized as Range?

Question

I am trying to develop a generic function to find matches of multiple criteria in a range. Based on a routine I found in a post by FastExcel, I have the following code working when I hardcode the range in the function. But if I try to pass in the range (in the ParamArray), I either get:

Error: "Object Variable Not Set" if I Dim aRange as Range and then aRange = args(0), or
Error: "Object Required" with Dim aRange As Variant and vArr Dim as Variant then vArr = aRange.Value2

This is my code:

Sub Test_FindCriteria()
    Dim lrow As Long
    lrow = FindCriteria(ActiveSheet.Range("a1:f100"), 1, "Ncompass", 3, "7.2", 4, "ncomphorizontrc", 5, "V85")
End Sub


Function FindCriteria(ParamArray args() As Variant) As Long
' Find row in range where multiple conditions are true
'   Arg(0) = Range to search
'   Remaing args are pairs where 1st is the column number (relative to Range) and 2nd is the item to be matched
'   Arg(1) = 1st Column number to search for Match Item 1
'   Arg(2) = match item 1 (in Range(,Arg(1))
'   Arg(3) = 2nd Column number to search for Match Item 2
'   Arg(4) = match item 2 (in Range(,Arg(3))
'   Arg(n-1) = (n/2)th Column number to search for Match Item
'   Arg(n) = match item n/2 (in Range(,Arg(n-1))

  Dim vArr As Variant
  Dim i As Integer
  Dim j As Long
  Dim k As Long
'  Dim aRange As Range                'Gives Error Object Variable not set
  Dim aRange As Variant
  
  aRange = args(0)                    'Gives Error: "Object Variable Not Set" if Dim aRange as Range

'    bfound = False
'    dTime = MicroTimer
    vArr = aRange.Value2              'Gives Error: "Object Required" with Dim aRange As Variant
'    vArr = ActiveSheet.Range("a1:f100").Value2      'Works when Range is declared in Function
    j = LBound(vArr)
    k = 0
    Do
        Do

        k = k + 1
        For i = 1 To UBound(args) Step 2
            If vArr(j, args(i)) = args(i + 1) Then
                bfound = True
            Else
                bfound = False
                Exit Do
            End If
        Next i
        Loop While False
        j = j + 1
    Loop While j < UBound(vArr) And Not bfound
    
    Debug.Print "Found at " & k
    
FindCriteria = k                                'could just use (j - LBound(vArr)) instead of k
    
End Function

How do I make it work passing in the Range to the function? Thanks!

you should do Function FindCriteria(rng as range, ParamArray args() As Variant) As Long — Scott Craner
In addition to Scott Craner's advice objects, like a range, need the set keyword. So Set aRange = ... — Warcupine
Thank you both Scott and Warcupine. I appreciate your quick assistance. I found my error (omitting Set) at about the same time as Warcupine, and have posted the corrected version in my answer to myself. — MonroeGA

MonroeGA MonroeGA · Accepted Answer · 2021-03-12T18:24:37

My mistake was forgetting objects require to be Set, so with Dim aRange As Variant, instead of:

aRange = args(0)

Should have been

Set aRange = args(0)

So, this now works:

Function FindCriteria(ParamArray args() As Variant) As Long
' Find row in range where multiple conditions are true
'   Arg(0) = Range to search
'   Remaing args are pairs where 1st is the column number (relative to Range) and 2nd is the item to be matched
'   Arg(1) = Column number 1 to search for Match Item 1
'   Arg(2) = match item 1 (in Range(,Arg(1))
'   Arg(3) = Column number 2 to search for Match Item 2
'   Arg(4) = match item 2 (in Range(,Arg(3))
'   Arg(n-1) = Column number to search for Match Item
'   Arg(4) = match item (in Range(,Arg(n-1))

  Dim vArr As Variant
  Dim i As Integer
  Dim j As Long
  Dim k As Long
  Dim aRange As Variant
  
  Set aRange = args(0)                                  'Gives Error: "Object Variable Not Set" if Dim aRange as Range

    vArr = aRange.Value2                            'Gives Error: "Object Required" with Dim aRange As Variant
    j = LBound(vArr)
    k = 0
    Do
        Do

        k = k + 1                                   'Not Really Required k = (j - LBound(vArr))
        For i = 1 To UBound(args) Step 2
            If vArr(j, args(i)) = args(i + 1) Then
                bfound = True
            Else
                bfound = False
                Exit Do
            End If
        Next i
        Loop While False
        j = j + 1
    Loop While j < UBound(vArr) And Not bfound
    
    Debug.Print "Found at " & k & j - LBound(vArr)
    
    FindCriteria = k                                'could just use (j - LBound(vArr)) instead of k
    
End Function

How to Pass a Range to Function and have it recognized as Range?

1 Answers