Custom Formula too slow

Question

I've used various guides, documentations and tutorials to create a custom formula. Basically the formula takes two parameters ItemID and DateV.

=DP(ItemID,DateV)

Sub TurnOffStuff()
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
    Application.ScreenUpdating = False
End Sub

Sub TurnOnStuff()
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Public Function DP(ItemID As Variant, Optional DateV As Variant)

    Dim SheetName As Variant, RangeSl As Range, RangeTP As Range, RangeTP2 As Range, RangeTP1 As Range
    
    Call TurnOffStuff
    
    If ItemID = "" Then
        DP = ""
    Else
        Set SheetName = ActiveWorkbook.Sheets("Prod")
        Set RangeSl = SheetName.Range("A:A")
        If DateValue(DateV) < DateValue("Sep/01/2021") Then
            Set RangeTP1 = SheetName.Range("G:G") 'TP_210901
            DP = WorksheetFunction.index(RangeTP1, WorksheetFunction.Match(ItemID, RangeSl, 0))
        ElseIf DateValue(DateV) < DateValue("Dec/07/2021") Then
            Set RangeTP2 = SheetName.Range("F:F") 'TP_211207
            DP = WorksheetFunction.index(RangeTP2, WorksheetFunction.Match(ItemID, RangeSl, 0))
        Else
            Set RangeTP = SheetName.Range("E:E")
            DP = WorksheetFunction.index(RangeTP, WorksheetFunction.Match(ItemID, RangeSl, 0))
        End If
    End If
    
   Call TurnOnStuff

End Function

The code works, but since I've added it to a table, every cell edits to the table is now taking about 5 seconds. The table I'm testing on have 3000 rows, but the real file has much higher number.

Is it possible to speed up this function? I'm a beginner.

My original title was "VBA Custom Formula with Index-Match too slow" but I kept getting the "This looks like spam" on the title. So had to remove those words. — Q_H_T, Apr 22 '22 at 09:02
Turn off calculation, do edits, then turn on calculation and save. — Solar Mike, Apr 22 '22 at 09:04
@SolarMike, that isn't really a suitable option for me as I need to do calculations on that returned value and check with the hard copy. — Q_H_T, Apr 22 '22 at 09:06
@SolarMike yeah, but that doesn't affect my other calculations on that table though. Plus the workbook is pretty large and using manual refresh will take longer as it has External Data and Pivot Tables as well in other sheets. — Q_H_T, Apr 22 '22 at 09:12
Well, you have your approach set up so I only made a suggestion. — Solar Mike, Apr 22 '22 at 09:13
If you use the function as UDF, I guess it's *not* a good idea to use `TurnOffStuff` and `TurnOnStuff`. For every call of the function (and if you use it in every row, that's 3000 calls) the calculation is switched off and on again (and the screenUpdating also). Not sure if this can be handled properly by Excel or if a new recalculation is triggered. — FunThomas, Apr 22 '22 at 09:51
Still learning Lambda, but maybe: `=LAMBDA(ItemID,DateV,MatchRange,IFERROR(INDEX(LET(DateValue,DateV,IdxRange,IF(DateV<44440,$G:$G,IF(DateV<44537,$F:$F,$E:$E)),IdxRange),MATCH(ItemID,MatchRange,0)),""))`. Haven't put as an answer as tagged `VBA`. — Darren Bartrup-Cook, Apr 22 '22 at 14:29
Using `ActiveWorkbook` in a UDF could lead to errors or wrong results being returned if you have multiple workbooks open. Assuming your source data is in the same workbook then `ThisWorkbook` is safer. — Tim Williams, Apr 22 '22 at 18:43
Also from a UDF you can set `EnableEvents`, but *not* `ScreenUpdating` or `Calculation`... — Tim Williams, Apr 22 '22 at 18:49
If your source data table is not changing often you can "memoize" your function by adding a static Dictionary object to your UDF - you use keys which are a combination of the two arguments to `ItemId` and `DateV`: if there's no matching key then do the lookup and cache the result in the dictionary - next time it's called with the same arguments you can just return the value from the dictionary. — Tim Williams, Apr 22 '22 at 18:54
@DarrenBartrup-Cook Lambda is not yet available on Excel Desktop. — Q_H_T, Apr 23 '22 at 18:26
@TimWilliams yes, I've already taken care of the `ActiveWorkbook` conflict. Also, my source changes almost every week from an external source. So I guess memorizing isn't viable. — Q_H_T, Apr 23 '22 at 18:27
The dictionary cache would only be active during the Excel session - anything which cleared global/local variables would cause it to be refreshed. So unless you have Excel pen all week it wouldn't be an issue. Here's an example of caching in a function which makes database calls - https://stackoverflow.com/a/34059374/478884 — Tim Williams, Apr 23 '22 at 21:26
@TimWilliams Hmm, thanks. I'll have to try it out over the weekends. — Q_H_T, Apr 24 '22 at 22:08

score 2 · Answer 1 · edited Apr 22 '22 at 12:13

2

Use LOOKUP instead of INDEX and MATCH. Note: I have changed the date strings to my local format. You need to change them back.

Public Function DP(ItemID As Variant, Optional DateV As Variant) As Variant
    Dim i As Integer
       
    If ItemID = "" Then
        DP = ""
    Else
        If DateValue(DateV) < DateValue("2021-09-01") Then
            i = 7
        ElseIf DateValue(DateV) < DateValue("2021-12-07") Then
            i = 6
        Else
            i = 5
        End If
        DP = WorksheetFunction.VLookup(ItemID, Range("Prod!A:G"), i, False)
    End If
End Function

You can test execution time with this Sub

Sub Test()
    
    repetitions = 1000
    
    startTime = VBA.DateTime.Timer
    For i = 1 To repetitions
        x = DP("Value3", "2021-12-24")
        endTime = VBA.DateTime.Timer
    Next i
    Debug.Print "This code ran in " & (endTime - startTime) & " seconds"
    
End Sub

I have used an example data with 4300 rows. The implementation with VLOOKUP took 0.02s and your implementation took 25s (for 1000 repititions).

edited Apr 22 '22 at 12:13

Pᴇʜ

56,719
10
49
73

answered Apr 22 '22 at 10:44

slarag

153
8

Note that for most purposes, `vbNullString` is equivalent to `""` but a practical difference is that `vbNullString` is faster to assign and process and it takes less memory. Also defining `i As Long` could make a [tiny difference](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long/26409520#26409520). – Pᴇʜ Apr 22 '22 at 12:17
Wouldn't VLOOKUP be more problematic on larger data? Like when I am matching column X with column A? – Q_H_T Apr 22 '22 at 13:00
Vlookup can become slow on large data in exact match mode, but I think it will still be significantly faster than the index/match method. If you need to speed thins up you could have a look at [link](https://exceljet.net/formula/faster-vlookup-with-2-vlookups) – slarag Apr 22 '22 at 13:21
But VLOOKUP only works if the value you lookup is right of the search value. – Pᴇʜ Apr 22 '22 at 14:26

score 1 · Accepted Answer · answered Apr 22 '22 at 10:25

Remove the TurnOnStuff and TurnOffStuff in UDFs, that's making it slower and it doesn't help at all as the code in the function does not do anything that affects what you turn off.
I made your function a bit slimmer, but that's more or less cosmetics to not repeat the code. I use some variables less that may have a small effect too.
Variant is the worst type you can use. If you can declare more precise, eg for text use ̀ String`. That can give a benefit too.

Option Explicit

Public Function DP(ByVal ItemID As Variant, Optional ByVal DateV As Variant) As Variant
    If ItemID = vbNullString Then
        DP = vbNullString
    Else
        Dim ws As Worksheet
        Set ws = ActiveWorkbook.Sheets("Prod")
        
        Dim MatchRange As Range
        Set MatchRange = ws.Range("A:A")
        
        Dim IdxRange As Range
        If DateValue(DateV) < DateValue("Sep/01/2021") Then
            Set IdxRange = ws.Range("G:G") 'TP_210901
        ElseIf DateValue(DateV) < DateValue("Dec/07/2021") Then
            Set IdxRange = ws.Range("F:F") 'TP_211207
        Else
            Set IdxRange = ws.Range("E:E")
        End If
        DP = WorksheetFunction.Index(IdxRange, WorksheetFunction.Match(ItemID, MatchRange, 0))
    End If
End Function

Note that using VBA is in most cases slower than using formulas. VBA can only use single threadding while formulas are not limited to that. So if you use your function a lot that might just take some time. You can't do much against it. Use formulas if you can and avoid using UDFs and VBA.

Custom Formula too slow

2 Answers2