PDA

View Full Version : UDF Several SUMIF optimination



fredlo2010
12-21-2015, 01:20 PM
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
12-21-2015, 04:47 PM
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
12-21-2015, 04:55 PM
It is very common that native Excel formulas are faster than UDF's.

fredlo2010
12-22-2015, 09:01 AM
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.