change vba lookup function into event with vba array and dictionary-CodePudding

the data record in the "MASTERVALIDATION" sheet there are approximately one hundred thousand records please the solution so that it can run fast and if I use the vba function it makes the processor calculations so slow.

Thanks SHEETS-MASTERVALIDATION : Source data SHEETS-MASTERVALIDATION

in this below sheet I input through the code column then it appears in the item column that is looking for or lookup from the "MASTERVALIDATION" sheet SHEETS-SEARCH SHEETS-SEARCH

'Previously I used the excel formula array below
={IF(A2="","",INDEX(MASTERVALIDATION[[ITEM]:[CODE]],MATCH(TRUE,EXACT(A2,MASTERVALIDATION[CODE]),0),1))}

Function toItem(c As Range) As String
Dim f As Range

Set f = Sheets("MASTERVALIDATION").ListObjects("MASTERVALIDATION").ListColumns(2).DataBodyRange.Find(What:=c.Text, LookIn:=xlValues, lookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If Not f Is Nothing Then
        toItem = f.Offset(, -1).Value
    End If
End Function

CodePudding user response：

I'd guess you mean something like this (untested):

Function toItem(c As Range) As String
    Static dict As Object
    Dim arr, arr2, r As Long, v
    
    If dict Is Nothing Then  'need to create the lookup dictionary?
        Set dict = CreateObject("scripting.dictionary")
        dict.comparemode = 1 'vbTextCompare
        With Sheets("MASTERVALIDATION").ListObjects("MASTERVALIDATION").ListColumns(2).DataBodyRange
            arr = .Value
            arr2 = .Offset(, -1).Value
        End With
        For r = 1 To UBound(arr, 1) 'fill the dictionary
            dict(CStr(arr(r, 1))) = arr2(r, 1)
        Next r
    End If
    v = CStr(c.Value)
    If dict.exists(v) Then toItem = dict(v)
End Function

You would then have to manage the static dictionary in the event the source data gets changed though...