hello, i'm trying to make a user-customizable workbook. the user will be able to customize the "Settings" sheet and type in fields and criteria that they want to compare.

the program does a WorksheetFunction.CountIfs method with multiple criteria. i was just wondering if it's possible to pass a variant array of arguments to this method, so that i don't have to change the code when the number of criteria changes.

right now it looks like this:
Sub MyCountIfsTest()
    
    'Columns where conditions will be checked
    Dim rngCol1 As Range
    Dim rngCol2 As Range
    Dim rngCol3 As Range
    
    'Cells which contain data that is being compared with the columns
    Dim rngCell1 As Range
    Dim rngCell2 As Range
    Dim rngCell3 As Range
    
    Dim rngResult As Range
    Dim avResult As Variant
   
    With WorksheetFunction
        'Type in ranges and cells manually for each condition (1, 2, 3)
        avResult = .CountIfs(rngCol1, rngCell1, rngCol2, rngCell2, rngCol3, rngCell3)
    End With
    
    'Instead I would like to do something like this:
    
    Dim avArguments As Variant          'Array for storing arguments to be fed to CountIfs
    Dim lTotalConditions As Long        'Total number of conditions (from Settings sheet)
    
    ReDim avArguments(1 To lTotalConditions)
    
    vaArray(1) = rngCol2
    vaArray(2) = rngCell2
    vaArray(3) = rngCol3
    vaArray(4) = rngCell3
    'etc..
    
    With WorksheetFunction
        avResult = .CountIfs(rngCol1, rngCell1, avArguments)
    End With
    
End Sub
so the criteria will be read from the "Settings" sheet into the array and then it will resize automatically to fit the number of arguments.

or is there another way that i can do this?

thanks!