excel - VBA listobject lookup function fast

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.
Learn more about Collectives
Teams
Q&A for work
Connect and share knowledge within a single location that is structured and easy to search.
Learn more about Teams
I have several sheets containing ListObjects
When I have to look for corresponding values in a listbject what I do is the following:
dim mytable as Listobject
set mytable = thisworkbook.sheets(x).listobject(1)
ValuetoSearch="whatever"
valueResult=""
' looking for the corresponding value of column A in column B
for i=1 to mytable.listrows.count
    if mytable.listcolumns("A").databodyrange.item(i).value=ValuetoSearch then
       valueResult=mytable.listcolumns("B").databodyrange.item(i).value
       exit for
    end if
next i
That works. fine.
Its that the FASTEST way to do the search?
I am using several of those lookup operations "on the fly" when the user select certain cells in the sheet (with workbook change select) and it comes a point when "you feel it" there is this almost a second delay that starts to be annoying for the user.
cheers
thanks
One of the major slow-downs in VBA is reading/writing cell values. You want to minimize the number of times you read/write to a worksheet as much as possible. As it turns out, in most cases it's much, much faster to read a range of values into an array, then do calculations on that array, than it is to do the same calculations on the range of values itself.
In your case, you could read the range of the table into an array (only one read operation), instead of doing a read operation for each row.
Dim mytable As ListObject
Dim myArr() As Variant
Set mytable = ThisWorkbook.Sheets(x).ListObject(1)
valuetosearch = "whatever"
valueResult = ""
myArr = mytable.Range.Value 'Read entire range of values into array
' looking for the corresponding value of column A in column B
For i = 1 To mytable.ListRows.Count
    If myArr(i, 1) = valuetosearch Then 'Check the value of the ith row, 1st column
       valueResult = myArr(i,2) 'Get the value of the ith row, 2nd column
       Exit For
    End If
Next i
I ran a quick benchmark on a table with 1,000,000 rows, and with the searched value only appearing in the very last row (worst possible case). Your original code takes 4.201 seconds, and this one takes 0.484 seconds. That's nearly 9 times faster!
                That will make it @Josh I am taking about 50 to 100 rows max. As soon as it is under 0,5 seconds the user dont feel the that "hanging thinking". thanks. cheers. I have to pass to the range only two columns. Or probably time-wise its not important if I pass the whole list object
– JFerro
                Dec 14, 2018 at 8:31
                as a foot note and for possible other readers; Josh solution is really clever, but be aware of the following. When working with listObjects normally columns can and are accessed by name column. In this way at any time if neccesary a new column can be inserted and will not affect the VBA code. This approach would not work if columns are reffered with indexes. when passing the data into an array like this: myArr = mytable.Range.Value indexes are hard coded, and inserting a new column would make the code to fail. ideally myArr is passed just the two lookup-columns reffered by name.
– JFerro
                Dec 14, 2018 at 9:25
    Dim m, rng, t
    Set rng = ThisWorkbook.Sheets(1).ListObjects(1).ListColumns(1).DataBodyRange
    t = Timer()
    m = Application.Match("Val_1", rng, 0) 'on the first row...
    Debug.Print m, Timer - t 'approx 0 sec
    t = Timer()
    m = Application.Match("Val_1000000", rng, 0) 'on the last row...
    Debug.Print m, Timer - t 'approx 0.03 to 0.05 sec
End Sub
m will either be the index of the matched row, or an error if there's no match - you can test for that using IsError(m)
                @mike01010 It only returns the first match, but you can change the lookup range each time you call it.  Whether or not that's the "best" approach for your particular case would depend on what you want to do...
– Tim Williams
                Oct 27, 2023 at 17:43
        Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.