ByRef Argument Type mismatch while using ParamArray

Question

I'm trying to build a function to use exclusively in VBA Editor that takes in a bidimensional array and filter it based on passed column number and criteria, like Excel's SUMIFS function. My approach is to write another function that only filters based on one column and one criteria and then build the bigger one which takes a ParamArray and then looping through each column/criteria pair and call the smaller function to do the filtering. The problem is when I pass the ParamArray into the bigger function it keeps showing "ByRef Argument Type Mismatch".

The bigger function takes in these arguments : (data array, columnnum1,criteria1,columnnum2,criteria2, etc)

This is the smaller function which filters only with one criteria.

Function HFILTER(arr As Variant, column As Long, name As Variant)
Dim res_arr() As Variant
Dim i As Long, j As Long
Dim coll As Collection

Set coll = New Collection

For i = 1 To UBound(arr, 1)
    If arr(i, column) = name Then
        coll.Add i
    End If
Next i

ReDim res_arr(1 To coll.count, 1 To UBound(arr, 2))

For i = 1 To coll.count
    For j = 1 To UBound(arr, 2)
        res_arr(i, j) = arr(coll(i), j)
    Next j
Next i

HFILTER = res_arr

End Function

The bigger function takes in these arguments : (data array, columnnum1,criteria1,columnnum2,criteria2, etc)

Function HFILTERS(arr As Variant, ParamArray args()) As Variant
Dim i As Long, res_arr() As Variant

res_arr = arr

For i = 0 To UBound(args, 1) - 1 Step 2
  res_arr = HFILTER(res_arr, args(i), args(i + 1))
Next i

HFILTERS = res_arr
End Function

However when I try to pass arguments into the bigger function I got the error ByRef Argument Type Mismatch.

Data :

Here's a picture of the data

Sub Test()
Dim data() As Variant

data = Range("A2", "B16").Value

data = HFILTERS(data, 1, "A", 2, False)
End Sub

Any input is greatly appreciated. I think I'm missing something quite simple but cannot make out what it is.

Please provide a minimal reproducible example - exactly what you have isn't clear. You can use the edit link below the question to provide sample data (we don't have your workbook!) as well as all the (minimal) code required to duplicate the problem. It doesn't have to be the entire function - it can be a "stub", just enough to trigger the error. — Cindy Meister
@CindyMeister Hi. I've added a sample data. All the codes required to reproduce the error is already included. — Bach Pham

Bach Pham Bach Pham · Accepted Answer · 2019-07-22T09:52:00

Never mind I figured it out. Since ParamArray needs to be in Variant anything that uses the passed parameters have to be declared as Variant as well. In my HFILTER function (the smaller one above) colunm is defined as Long, changed that to Variant and everything works fine now.

ByRef Argument Type mismatch while using ParamArray

1 Answers