UDF Several SUMIF optimination

[fredlo2010]

Hello everyone.

I have been working on a UDF for a while to perform several SUMIFS and I was wondering if there is a ways to improve performance on it. So far the fastest one I have is by using purely Excel formulas but I want to simplify the way data is used for the comparisons.

I have come up with three different ways to do this with U_UDF_GetData3 been the fastest. In general anything that uses "Evaluate" would run slower that the rest.

Any ideas on how I could achieve this ?

Here are all my tries:

Summary

Function	Time in Milliseconds
U_UDF_GetData1	0.8965
U_UDF_GetData2	0.5693
U_UDF_GetData3	0.5064
Using Default Functions	0.1478

Number One
Time: ~ 0.8965 ms

Public Function U_UDF_GetData1(ByVal strSheetName As String, _
                              ByVal sColumnToMatch As String, _
                              ByVal sColumnWithData As String, _
                              ByRef varLabels As Variant) As Double

    Const strDIVIDER As String = "|"

    Dim arrLabels As Variant
    Dim i As Long
    Dim ret As Double

    On Error GoTo Error_Handler
    Application.Volatile True

    ' Set the sheet, fix the columns and populate the array.
    sColumnToMatch = sColumnToMatch & ":" & sColumnToMatch
    sColumnWithData = sColumnWithData & ":" & sColumnWithData


    ' Determine if the values are a string or a range.
    If TypeName(varLabels) = "Range" Then
        arrLabels = Split(varLabels.Value, strDIVIDER)
    ElseIf TypeName(varLabels) = "String" Then
        arrLabels = Split(varLabels, strDIVIDER)
    End If
    
    Dim strFormula As String
    Dim strSection As String
       

    ' Aggregate the items.
    For i = LBound(arrLabels) To UBound(arrLabels)        
        strSection = "SUMIF('" & strSheetName & "'!" & sColumnToMatch & ",""" & arrLabels(i) & """,'" & strSheetName & "'!" & sColumnWithData & ")"
        
        If strFormula = vbNullString Then
            strFormula = strSection
        Else
            strFormula = strFormula & "+" & strSection
        End If        
    Next i

    ret = Evaluate(strFormula)
    
Exit_Handler:

    On Error GoTo 0
    ' Return the value
    U_UDF_GetData1 = ret
    Exit Function

Error_Handler:
    ret = 0
    Resume Exit_Handler
End Function

Number Two
Time: ~ 0.5693 ms

Public Function U_UDF_GetData2(ByVal strSheetName As String, _
                              ByVal sColumnToMatch As String, _
                              ByVal sColumnWithData As String, _
                              ByRef varLabels As Variant) As Double


    Const strDIVIDER As String = "|"


    Dim sh As Worksheet
    Dim shFunction As WorksheetFunction
    Dim strArray As Variant
    Dim i As Long
    Dim ret As Double


    On Error GoTo Error_Handler
    Application.Volatile True


    ' Set the sheet, fix the columns and populate the array.
    Set shFunction = Application.WorksheetFunction
    Set sh = ThisWorkbook.Sheets(strSheetName)
    sColumnToMatch = sColumnToMatch & ":" & sColumnToMatch
    sColumnWithData = sColumnWithData & ":" & sColumnWithData


    ' Determine if the values are a string or a range.
    If TypeName(varLabels) = "Range" Then
        strArray = Join(Split(varLabels.Value, strDIVIDER), """,""")
    ElseIf TypeName(varLabels) = "String" Then
        strArray = Join(Split(varLabels, strDIVIDER), """,""")
    End If
    
    Dim strSection As String
    
    strSection = "SUM(SUMIF('" & strSheetName & "'!" & sColumnToMatch & ",{""" & strArray & """},'" & strSheetName & "'!" & sColumnWithData & "))"
    
    ret = Evaluate(strSection)


Exit_Handler:


    On Error GoTo 0
    ' Return the value
    U_UDF_GetData2 = ret
    Exit Function


Error_Handler:


    ret = 0
    Resume Exit_Handler
End Function

Number Three
Time: ~ 0.5064 ms

Public Function U_UDF_GetData3(ByVal strSheetName As String, _
                              ByVal sColumnToMatch As String, _
                              ByVal sColumnWithData As String, _
                              ByRef varLabels As Variant) As Double


    Const strDIVIDER As String = "|"


    Dim sh As Worksheet
    Dim shFunction As WorksheetFunction
    Dim arrLabels As Variant
    Dim i As Long
    Dim ret As Double


    On Error GoTo Error_Handler
    Application.Volatile True


    ' Set the sheet, fix the columns and populate the array.
    Set shFunction = Application.WorksheetFunction
    Set sh = ThisWorkbook.Sheets(strSheetName)
    sColumnToMatch = sColumnToMatch & ":" & sColumnToMatch
    sColumnWithData = sColumnWithData & ":" & sColumnWithData


    ' Determine if the values are a string or a range.
    If TypeName(varLabels) = "Range" Then
        arrLabels = Split(varLabels.Value, strDIVIDER)
    ElseIf TypeName(varLabels) = "String" Then
        arrLabels = Split(varLabels, strDIVIDER)
    End If
    
  
    ' Aggregate the items.
    For i = LBound(arrLabels) To UBound(arrLabels)
        ret = ret + shFunction.SumIf(sh.Range(sColumnToMatch), arrLabels(i), sh.Range(sColumnWithData))
    Next i


Exit_Handler:


    On Error GoTo 0
    ' Return the value
    U_UDF_GetData3 = ret
    Exit Function


Error_Handler:


    ret = 0
    Resume Exit_Handler
End Function

[Bob Phillips]

Surely it would be better, and quicker, to pass the actual ranges, with sheet names, rather than letters that you then test the whole column of?

[mikerickson]

It is very common that native Excel formulas are faster than UDF's.

[fredlo2010]

Thanks for the replies.

@xld I changed the code as per your suggestion and the performance increase was not noticeable. As average speed remained more or less the same.

@mike I know Mike. The problem here is that I am trying to simplify some formulas that are supposed to get data automatically. It's a matter of simplicity vs speed.