2
$\begingroup$

I am experiencing a strange issue with QuantLibXL when I use the quick-access PasteOptions/Values button on the Excel right-click toolbar (apologies for the picture: it is surprisingly hard to capture screen-grabs of right-click menus!).

Has anyone else experienced this?

enter image description here

If I use this button to Paste Special/Values, rather than the 'Paste Special...' sub-menu, then QuantLib seems to crash internally and all calls to the library return #NUM!. The only solution is to close and re-start Excel.

These is a simple sheet that reproduces the issue (NB Excel calculation is Automatic).

enter image description here

with the formulas:

enter image description here

So the qlCalendarAdvance() function is taking a parameter (B2) which is itself the result of a function (=B1).

Steps to reproduce the error:

  1. Select cell B1
  2. Right-click on cell B2, and choose the Paste Options / Values shortcut button (as in the picture above).
  3. Cell B3 turns to #NUM!

Notes:

  1. If B2 is already hard-coded, then the error doesn't occur.
  2. The error does not occur 100% of the time, though much more often than not, suggesting some sort of timing issue on re-calculation.
  3. Clearly the work-around is to avoid that button (!).

Excel: Microsoft 365 64-bit. Version 2009 (Build 13231.20390)

QL Addin: QuantLibXL-vc141-x64-mt-s-1_16_0.xll

$\endgroup$
3
  • $\begingroup$ Hi. Did you already raise this issue at github.com/quantlib/QuantLibXL ? $\endgroup$ – Kermittfrog Oct 30 '20 at 17:47
  • $\begingroup$ @Kermittfrog Yes, I have done now. Thanks for the suggestion. $\endgroup$ – DS_London Nov 5 '20 at 13:34
  • $\begingroup$ I think I had noticed something similar at one point, though had trouble reproducing. Thanks for the hard work! $\endgroup$ – StackG Nov 5 '20 at 23:28
5
$\begingroup$

After digging away, I discovered an issue deep within the base QuantLibXL code: a constructor was not handling an exception.

Within the base code that QL uses to wrap the Excel4 C-style interface (functioncall.cpp) is this code, where the class sets a static pointer to ensure only one instance exists:

FunctionCall *FunctionCall::instance_ = 0;    

FunctionCall::FunctionCall(const std::string functionName) :
        functionName_(functionName), 
        callerDimensions_(CallerDimensions::Uninitialized),
        error_(false) {
    OH_REQUIRE(!instance_, "Multiple attempts to initialize global FunctionCall object");
    instance_ = this; //The issue is here

    Excel(xlfCaller, &xCaller_, 0);
    if (xCaller_->xltype == xltypeRef || xCaller_->xltype == xltypeSRef) {
        Excel(xlfReftext, &xReftext_, 1, &xCaller_);
        refStr_ = ConvertOper(xReftext_()); //THIS CAN FAIL 
        callerType_ = CallerType::Cell;
    } else if (xCaller_->xltype & xltypeErr) {
        callerType_ = CallerType::VBA;
    } else if (xCaller_->xltype == xltypeMulti) {
        callerType_ = CallerType::Menu;
    } else {
        callerType_ = CallerType::Unknown;
    }
}

The issue seems to be that if you are using the right-click button, the Excel interface has trouble resolving the "Caller" (which tells a UDF how it is being called). The call to ConvertOper(xRefText...) throws an exception (as it cannot coerce an Error OPER). This isn't handled in the constructor, but before dying the constructor code has set

instance_ = this;

Since the destructor (which sets instance_ back to 0) never gets called, later calls to the constructor always think there is already an instance, and fail the OH_REQUIRE assertion.

The solution was after all that, pretty simple: just move the line that sets the instance to AFTER the code that might throw an exception:

   //instance_ = this; MOVE FROM HERE 

    Excel(xlfCaller, &xCaller_, 0);
    if (xCaller_->xltype == xltypeRef || xCaller_->xltype == xltypeSRef) {
        Excel(xlfReftext, &xReftext_, 1, &xCaller_);
        refStr_ = ConvertOper(xReftext_()); //THIS CAN FAIL 
        callerType_ = CallerType::Cell;
    } else if (xCaller_->xltype & xltypeErr) {
        callerType_ = CallerType::VBA;
    } else if (xCaller_->xltype == xltypeMulti) {
        callerType_ = CallerType::Menu;
    } else {
        callerType_ = CallerType::Unknown;
    }

    instance_ = this; //TO HERE
}

Recompiled and it all worked.

$\endgroup$
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.