excel-vba VBA Best Practices WorksheetFunction object executes faster than a UDF equivalent
Example
VBA is compiled in run-time, which has a huge negative impact on it's performance, everything built-in will be faster, try to use them.
As an example I'm comparing SUM and COUNTIF functions, but you can use if for anything you can solve with WorkSheetFunctions.
A first attempt for those would be to loop through the range and process it cell by cell (using a range):
Sub UseRange()
Dim rng as Range
Dim Total As Double
Dim CountLessThan01 As Long
Total = 0
CountLessThan01 = 0
For Each rng in Sheets(1).Range("A1:A100")
Total = Total + rng.Value2
If rng.Value < 0.1 Then
CountLessThan01 = CountLessThan01 + 1
End If
Next rng
Debug.Print Total & ", " & CountLessThan01
End Sub
One improvement can be to store the range values in an array and process that:
Sub UseArray()
Dim DataToSummarize As Variant
Dim i As Long
Dim Total As Double
Dim CountLessThan01 As Long
DataToSummarize = Sheets(1).Range("A1:A100").Value2 'faster than .Value
Total = 0
CountLessThan01 = 0
For i = 1 To 100
Total = Total + DataToSummarize(i, 1)
If DataToSummarize(i, 1) < 0.1 Then
CountLessThan01 = CountLessThan01 + 1
End If
Next i
Debug.Print Total & ", " & CountLessThan01
End Sub
But instead of writing any loop you can use Application.Worksheetfunction which is very handy for executing simple formulas:
Sub UseWorksheetFunction()
Dim Total As Double
Dim CountLessThan01 As Long
With Application.WorksheetFunction
Total = .Sum(Sheets(1).Range("A1:A100"))
CountLessThan01 = .CountIf(Sheets(1).Range("A1:A100"), "<0.1")
End With
Debug.Print Total & ", " & CountLessThan01
End Sub
Or, for more complex calculations you can even use Application.Evaluate:
Sub UseEvaluate()
Dim Total As Double
Dim CountLessThan01 As Long
With Application
Total = .Evaluate("SUM(" & Sheet1.Range("A1:A100").Address( _
external:=True) & ")")
CountLessThan01 = .Evaluate("COUNTIF('Sheet1'!A1:A100,""<0.1"")")
End With
Debug.Print Total & ", " & CountLessThan01
End Sub
And finally, running above Subs 25,000 times each, here is the average (5 tests) time in milliseconds (of course it'll be different on each pc, but compared to each other they'll behave similarly):
- UseWorksheetFunction: 2156 ms
- UseArray: 2219 ms (+ 3 %)
- UseEvaluate: 4693 ms (+ 118 %)
- UseRange: 6530 ms (+ 203 %)
