Getting Used Range in an XLL UDF: Multi-threading and COM

In two previous blog posts I discussed why handling whole-column references efficiently in VBA UDFs meant that you had to find the used range for the worksheet containing the whole-column reference. The posts also discussed how using a cache for the used ranges could give a significant performance improvement.

Full Column References in UDFs
Getting used Range efficiently with a cache

But how do you do this for multi-threaded C++ XLLs?

The XLL API cannot directly get the Used Range

The first problem is that the XLL API is based on the old XLM language, and that does not have a method for finding the used range from a worksheet function.

So you have to make a callback from the XLL into Excel to the COM interface, and that can very easily go wrong. Excel does not generally expect call-backs of this type, so the call-back may be ignored, or access the wrong part of memory or even crash Excel.

And with multi-threaded UDFs you certainly only want one UDF calling back at a time!

When you combine this with the need to access and maintain a cache for efficiency reasons the logic looks something like this:

• For each reference referring to a large number of rows (I am currently using 100000 rows as large)
  • nRows=number of rows referenced
  • Try to get a shared read lock on the cache: if fails exit
  • If there is anything in the cache for this worksheet then nRows=MIN(cached rows, nRows)
  • Else try for an exclusive lock on the cache (write lock): if fails exit
    • Try to callback to Excel COM to get the used range rows.
    • If succeeded store in the cache for this worksheet & nRows=MIN(cached rows, nRows)
    • Unlock the cache
  • If failed to get a lock or COM failed then exit
• Next large reference

Note that the logic is fail-safe: if the UDF cannot get the lock it needs on the cache or the COM call does not succeed it just uses the number of rows in the reference.
This worked well most of the time but in some situations it always failed.

Excel COM callbacks must be on the main thread.

Trying to figure out exactly what the problem was was tricky, but eventually Govert van Drimmelen, the author of the wonderful Excel DNA, pointed out that calls to COM have to be executed on the main thread.

So I grab the thread ID of the main thread in the XLL On Open event, and then only call the exclusive lock and COM if the UDF is being executed on the main thread.

And it works: thanks Govert!