Multi-threading XLL functions – Evaluate fails

I have just about finished converting the first part of the FILTER.IFS function from VB6 to C++.
This first part uses modified binary search routines to handle multiple kinds of criteria (EQ, GT, LT, GE, LE, NE with AND and OR etc) on sorted columns.

The second part handles unsorted columns and additional criteria types such as Regex and Like. Some of this is done in the VB6 version of the function using EVALUATE to get the results of array formulas on subsets of the data. So the C++ XLL version uses the same technique but using the XLL equivalent xlfEvaluate.

The explanation here seems to say that using xlfEvaluate is threadsafe as long as the expression being evaluated does not contain any non-threadsafe components.

But in practice using xlfEvaluate on even a simple formula string like =2+3 fails with a return code of 128 if the UDF function is flagged as multi-threaded, but works OK if the function is flagged as single-threaded.

At the moment this leaves me with a choice of either making FILTER.IFS single-threaded or using some alternative to xlfEvaluate. Both of these choices look bad.

So has anyone found a way of using xlfEvaluate inside a multi-threaded XLL function?

This entry was posted in Calculation, XLL. Bookmark the permalink.

5 Responses to Multi-threading XLL functions – Evaluate fails

  1. The following code returns 5 using the library.

    static AddIn12 xai_threadsafe(
    Function12(XLL_DOUBLE12, L”?xll_threadsafe”, L”THREADSAFE”)
    double WINAPI
    #pragma XLLEXPORT
    OPER12 o = Excel(xlfEvaluate, OPER12(L”=2+3″));

    return o.val.num;

    Do you have any sample code that reproduces what you are seeing?

  2. fastexcel says:

    @Keith, Many thanks for testing. I have (finally) downloaded your XLL stuff. Your test example does work, but its not using multi-threading. I think you forgot to uncomment the line #define EXCEL12 in your header file Header.h. Unless you do that it does not run multi-threaded, but when you do try to run it multi-threaded it crashes. Looks to me like you really can’t use xlfEvaluate in multi-threaded mode.

    • kalx says:

      You are correct Charles. The call to xlfEvaluate returns xlretNotThreadSafe when EXCEL12 is defined.
      One of the design goals of the xll library is to make it possible to write one set of source code that can be compiled for either classic Excel or the newest versions. Use, e.g., AddInX and get either AddIn or AddIn12 based on the define.
      I also try to allow for creating new style add-ins even when EXCEL12 is not defined but the ThreadSafe call was using compile time instead of runtime checking. Fixed now in the repository.

  3. ltleelim says:

    I was trying to make my XLL multithreaded and have this same problem with xlfEvaluate. Did you ever find a solution?

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s