Need for Speed

What is the fastest way to search Excel's ranges in VBA?

While working on MLOOKUP() and XLOOKUP() I tested several different search methods. They include:

• • Application.Match(Key(s), Table Column(s), Match Type)

  • Application.WorksheetFunction.Match(Key(s), Table Column(s), Match Type)

  • Application.WorksheetFunction.VLookup(Key, Table, Column#, Match Type)

  • Application.Evaluate("=Match(Key(s), Table Column(s), Match Type)")

  • (Worksheet).Evaluate("=Match(Key(s), Table Column(s), Match Type)")

I also tested key types including:

• • Single Numeric Key

  • Single Text Key

  • Compound Number and Date Key

  • Compound Text Key

I timed each method and key type combination in a loop of 10,000 iterations on multiple systems. The test spreadsheet is included in the MLOOKUP() or XLOOKUP() demos. So if you would like to critique my test methods or test them on your PC, download the demos and have at it.

How to Time Routines

Timing routines is easy. The first thing we need is a timer with enough resolution to time really short events. For this we use Windows function GetTickCount. GetTickCount retrieves the number of milliseconds elapsed since system start. We can access GetTickCount in our VBA by adding these declarations near the top of a module (After options and before other functions or subroutines).

#If VBA7 And Win64 Then

' 1 Millisecond counter with a 10 to 16 ms resolution

Public Declare PtrSafe Function GetTickCount Lib "kernel32" () As Long

#Else

' 1 Millisecond counter with a 10 to 16 ms resolution

Public Declare Function GetTickCount Lib "kernel32" () As Long

#End If

Need more information?

• • • #If, #Else and #End If: Click here.

    • The pound sign/hash mark designates to VBA's compiler to conditionally include lines of code. In this instance we want to declare GetTickCount one way if we are running in 64 bit Microsoft Office and another way for 32 bit (see next).

    • VBA7 and Win64: Click here.

    • VBA got a code base update in 2010. That update added two VBA compiler constants: VBA7 and Win64. VBA7 is TRUE when running in the new VBA code base. Win64 is TRUE when running in 64 bit Microsoft Office.

    • Public Declare: Click here

    • This module level statement declares references to external procedures in a dynamic-link library (DLL).

• • PtrSafe: Click here.

  • This tells VBA that our Declare statement is safe to run in 64-bit versions of Microsoft Office.

  • GetTickCount: Click here.

  • This is a small program we want to include in our VBA project. It resides in kernel32.dll (see next). When we call GetTickCount() it returns a long variable containing how many milliseconds have elapsed since our system started. We can compare two GetTickCount() results to determine how many milliseconds elapsed between the two calls.

  • kernel32: Click here

  • Kernel32 is a 'dynamic link library' (DLL). DLL's contain bundles of resources we can access in our projects.

GetTickCount is the shortest timer I know. It returns the number of milliseconds elapsed in 10 to 16 ms increments (depends on system speed). But even that is too slow to measure how fast most Excel functions are. So we need to create a loop that executes the function we want to measure and time how long the loop takes. Here is an example routine:

Function TimeThis(byVal lLoops as Long) as Long

Dim lLoop as Long

Dim lTick as Long

lTick = GetTickCount

For lLoop = 1 to lLoops

vResult = Application.Match(FindThis, InThis, 0)

Next

TimeThis = GetTickCount - lTick

End Function

The result is a comparative measure, Now change what is in the loop, call the function with the same number of loops and the result will tell us which is faster and by how much.

Results

The test results indicated some basics that probably won't surprise anyone. They are:

• • Smaller tables can be searched faster

  • Single Numeric keys are faster than Text Keys

  • Single keys of any type are faster than multiple keys

  • A souped up i7 improved speed by 30% over an i5 laptop.

    • Application.WorksheetFunction.VLookup(Key, Table, Column#, TRUE) is fastest over sorted lists.

While VLOOKUP() is fastest, it only works with single keys and it can only return values. For my VBA routines I needed the possibility of searching multiple keys and I needed the cell range returned because I can perform updates and deletes on cell ranges and cell ranges provide far more information than just values such as: position, number formats, validation, conditional formatting, etc. If I find myself needing a value at the fastest speed possible I'll use WorksheetFunction.VLookup. But for generic routines like MLOOKUP() and XLOOKUP() that return ranges I needed to know which method was fastest.

These charts show results of various methods, keys, and sorts over a 500 record table looped 10,000 times.

Dim vResult as Variant