Pass worksheet function argument in an array

Question

The list of argument passed to the SUMIFS worksheet function call below consists of an array. Instead of spelling out each element I would like to create it using a loop. Does anyone know the syntax for that?

Function SUMIFS(SumRng As Range, _
                ParamArray Ifs() As Variant) As Double
    ' each element of Ifs is an array of 3 elements:
    '   0 = Criteria range, 1 = Operator, 2 = Criterium

    Const Symbols   As String = "=,<>,>,<,<=,>="
    
    Dim Symb()      As String
    Dim Fun()       As Variant          ' Converted Ifs()
    Dim Tmp         As Variant
    Dim i           As Long             ' Ifs index
    
    ReDim Fun(7, 1)                     ' extend to a maximum of 13 if required
    Symb = Split(Symbols, ",")
    For i = 0 To UBound(Fun)
        If i > UBound(Ifs) Then
            Fun(i, 0) = SetMissing()
            Fun(i, 1) = SetMissing()
        Else
            Set Fun(i, 0) = Ifs(i)(0)
            Fun(i, 1) = Ifs(i)(2)
            Tmp = Ifs(i)(1)
            If VarType(Ifs(i)(2)) = vbDate Then
                Fun(i)(1) = CLng(Ifs(i)(2))
            Else
                Fun(i, 1) = Ifs(i)(2)
            End If
            If Val(Tmp) Then Fun(i)(1) = Symb(Tmp) & Fun(i)(1)
        End If
    Next i
    
    SUMIFS = WorksheetFunction.SUMIFS(SumRng, Fun(0, 0), Fun(0, 1), _
                                              Fun(1, 0), Fun(1, 1), _
                                              Fun(2, 0), Fun(2, 1), _
                                              Fun(3, 0), Fun(3, 1), _
                                              Fun(4, 0), Fun(4, 1), _
                                              Fun(5, 0), Fun(5, 1), _
                                              Fun(6, 0), Fun(6, 1), _
                                              Fun(7, 0), Fun(7, 1), _
                                              Fun(8, 0), Fun(8, 1), _
                                              Fun(9, 0), Fun(9, 1), _
                                              Fun(10, 0), Fun(10, 1), _
                                              Fun(11, 0), Fun(11, 1), _
                                              Fun(12, 0), Fun(12, 1), _
                                              Fun(13, 0), Fun(13, 1))
End Function

The above function has reference to Pass arguments to a worksheet function as an array in VBA where it is fully explained.

It's not clear exactly what you mean by "create it using a loop" - you're filling the array `Fun` so yes you can do that in a loop... — Tim Williams, Jan 21 '21 at 02:05
There is an array starting from `SUMIFS(SumRng, ..` and ending with `.. Fun(13,1))` which would be easy to produce in a loop. The difficulty lies in giving it a format that can be passed to the SUMIFS worksheet function. It's the required syntax that I'm asking about. — Variatus, Jan 21 '21 at 13:32

Pass worksheet function argument in an array

0 Answers0