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.

CodePudding user response：

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).

CodePudding user response：

1. 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.

2. 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.

3. 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.