VBA listobject lookup function fast

Question

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. but:

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

Answer 1

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!