How an Unhandled Exception Can Leave QuantLibXL in a Failed State
Summary
This troubleshooting account describes a QuantLibXL failure triggered intermittently by Excel’s Paste Values shortcut. A worksheet formula passed into a QuantLib calendar function could return #NUM! after the shortcut was used, and subsequent calls continued failing until Excel was restarted. Hard-coded input values avoided the issue, while the intermittent behavior suggested a recalculation timing component.
The explanation traces the failure to a constructor in the Excel interface wrapper. It stored a global pointer indicating that a function call object existed before performing caller-resolution work that could throw an exception. When conversion of Excel’s caller reference failed, construction stopped before the destructor could clear the pointer, so later calls encountered the stale state and failed an assertion. Moving the pointer assignment until after the potentially throwing operations resolved the issue in the author’s rebuilt add-in. This is a specific historical software defect and workaround, not general guidance for current QuantLibXL releases or other Excel versions.
Key ideas
- The Excel Paste Values shortcut could provoke an intermittent failure in a QuantLibXL worksheet.
- A caller-reference conversion in the wrapper could throw while resolving how a function was invoked.
- The constructor set its singleton pointer before the operation that could fail, leaving stale global state after an exception.
- Moving the pointer assignment after the risky operations fixed the reported issue when the add-in was rebuilt.
Tags
Full text
# Excel PasteSpecial Values shortcut button causing QuantLib to crash
# 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:
- Select cell B1
- Right-click on cell B2, and choose the Paste Options / Values shortcut button (as in the picture above).
- Cell B3 turns to #NUM!
Notes:
- If B2 is already hard-coded, then the error doesn't occur.
- The error does not occur 100% of the time, though much more often than not, suggesting some sort of timing issue on re-calculation.
- 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
## Answer by DS_London (score 5, accepted)
https://quant.stackexchange.com/a/59153
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.Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)
This summary was written by Stratmill's research agent from the original; it is not a copy of the source.