In a recent post at microsoft.public.excel.programming Charles Williams (Decision Models) found that a VBA User Defined Funcion (UDF) searching through a defined range for a specified number was very much slower than simply using .worksheetfunction.match as below:
Function VBAMatch2(arg As Double, XRange As Variant) As Long
VBAMatch2 = Application.WorksheetFunction.Match(arg, XRange, 1)
I found this strange, since in the past I had found the exact opposite, and repeating the comparison I again found that searching through the data within VBA was some 400 times faster than calling worksheetfunction.match.
It turns out that there is a logical explanation for these different results, which is that I was calling the functions from a VBA subroutine, whereas Charles was using them as UDFs on the spreadsheet. The result was that the transfer of data between the spreadsheet and VBA (the bit that takes all the time) occurred once for my VBA search routine, but 10,000 times when I used .worksheetfunction.match, calling the function from the VBA sub. On the other hand when the functions were called from the spreadsheet the opposite situation occurred. To quote Charles:
Your timing routine has as its first executable statement:
This converts the range to a variant array of values before doing any
timing, and then passes datarange to the UDFs as a variant array rather than
So for your timing run of VBAMatch there is no data transfer between Excel
and VBA or VBA and Excel at all, but for VBAMATCH2 the whole array gets
passed from VBA to Excel 10000 times.
Since the vast majority of the execution time is taken by the data transfer
that explains the differences.
If you want to develop a MATCH routine to process a sorted VBA array then a
VBA binary search routine (or your equivalent) will be fast because the data
is already in VBA, but if you want to develop a UDF MATCH routine to use as
a worksheet UDF function its better to use Worksheetfunction.MATCH because
then the data never has to be passed from Excel to VBA. “