Worksheetfunction vs UDF – 2

Previous Post

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)
End Function

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
a range.

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. “

This entry was posted in Excel, UDFs and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.