Excel PasteSpecial Values shortcut button causing QuantLib to crash

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?

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

with the formulas:

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)

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

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) {
} 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) {