Menu
▾
▴
Re: [Quantlib-users] Issue with QuantLibXL and Paste Special in Excel
|
From: Eric E. <eri...@re...> - 2020-11-05 15:06:13
|
Hi David,
Thank you, that analysis looks great to me. I say we stick this in to
1.20, if anybody reports a problem we can always reverse the change and
release a patch.
Would it be possible for you to send me a pull request?
Kind Regards,
Eric
On 11/5/20 2:06 PM, da...@el... wrote:
>
> Thanks for coming back on this Eric.
>
> I’m using 64-bit Excel fwiw.
>
>
> Yes, the way to start a “blank” Excel is to use
> > Excel.exe /automation
>
> So add “/automation” to the command line arguments in the Debugger.
>
> I think I found the solution: it’s an issue of an exception not being
> handled within a constructor. As an aside, it might also be why
> evaluating functions in the Function Wizard sometimes has problems.
>
> The code for the constructor and destructor of the
> ObjectHandler::FunctionCall class is below. There is a static member
> _instance which I assume is there to ensure that there is only one
> FunctionCall object at a time?
>
> The problem comes from the Excel()s call in the constructor. For some
> reason when you are using the Paste Values button, xCaller_ is type
> xltypeSRef, but Excel(xlfReftext, …) fails (perhaps because Excel
> doesn’t know what to do when the function in this right-click ‘mode’)
> and xReftext_ is of type Error. Inside the subsequent call to
> ConvertOper::operator std::string() is an Excel(xlCoerce,…) call.
>
> It is this call which throws the exception, as the underlying
> Excel4v() call for the Coerce does not return xlretSuccess. The
> Excel() code then generates an exception with the text:
> "Error in call to Excel: (xlSpecial | 2) callback failed: Command failed "
>
> This exception is not caught until the top-level spreadsheet function
> code (eg qlCalendarAdvance() in my example):
>
> XLL_DEC OPER *qlCalendarAdvance(…)
> {
>
> boost::shared_ptr<ObjectHandler::FunctionCall> functionCall;
>
> try {
>
> // instantiate the Function Call object
>
> functionCall = boost::shared_ptr<ObjectHandler::FunctionCall>(
>
> new ObjectHandler::FunctionCall("qlCalendarAdvance")); <<<
> ---- GENERATES EXCEPTION
>
> BUT the FunctionCall constructor is now bailing out with an exception,
> and TBH I don’t know what state of the shared pointer is in at this
> stage. However, before dying, the constructor has made this call:
>
> instance_ = this;
>
>
> And this instance_ is not 0 anymore. If the instance had been
> constructed successfully, then the later destructor code would have
> reset instance_ =0. (see below). But I don’t think the destructor ever
> gets called. This is why all subsequent QuantLibXL calls fail at:
> OH_REQUIRE(!instance_, "Multiple attempts to initialize global
> FunctionCall object");
>
> And the underlying function call never gets made.
>
> How to fix this? Here’s what I did. I just moved the
>
> Instance_ = this;
>
> Line in the constructor to AFTER the Excel call (in fact the last line
> of the constructor). I recompiled and the Paste Values issue seems to
> have gone away.
>
> Original code
>
> namespace ObjectHandler {
>
> 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; <<<<< MOVE THIS FROM HERE
>
> Excel(xlfCaller, &xCaller_, 0);
>
> if (xCaller_->xltype == xltypeRef || xCaller_->xltype ==
> xltypeSRef) {
>
> Excel(xlfReftext, &xReftext_, 1, &xCaller_);
>
> refStr_ = ConvertOper(xReftext_());
>
> callerType_ = CallerType::Cell;
>
> } else if (xCaller_->xltype & xltypeErr) {
>
> callerType_ = CallerType::VBA;
>
> } else if (xCaller_->xltype == xltypeMulti) {
>
> callerType_ = CallerType::Menu;
>
> } else {
>
> callerType_ = CallerType::Unknown;
>
> }
>
> <<<<< TO HERE
>
> }
>
> FunctionCall::~FunctionCall() {
>
> if (!error_) {
>
> if (callerType_ == CallerType::Cell) {
>
> RepositoryXL::instance().clearError();
>
> } else if (callerType_ == CallerType::VBA || callerType_
> == CallerType::Menu) {
>
> RepositoryXL::instance().clearVbaError();
>
> }
>
> }
>
> instance_ = 0;
>
> }
>
> Maybe this is something you could change in the next release?
>
>
> Best wishes
>
> David Sansom
>
> *From:*Eric Ehlers <eri...@re...>
> *Sent:* Wednesday, 4 November 2020 19:57
> *To:* qua...@li...
> *Subject:* Re: [Quantlib-users] Issue with QuantLibXL and Paste
> Special in Excel
>
> Hi David,
>
> I just tried unsuccessfully to recreate the problem. I am using
> 32-bit Excel on 64-bit Windows 10. Would be interesting to know if
> anybody else can recreate the error.
>
> I have not done Excel support in many years but I seem to remember
> that there was a way to start Excel in safe mode so that it would not
> load any addins. Then load only QLXL and try again. If the error
> does not occur in that case then it would suggest that the problem is
> with a different addin.
>
> Regards,
> Eric
>
> On 11/2/20 10:33 AM, da...@el...
> <mailto:da...@el...> wrote:
>
> Hi everyone.
>
> I’m having a curious issue with QuantLibXL and the Paste Special /
> Values button in Excel.
>
> I’ve put a full description in the link, but the executive summary
> is that if I used the Paste Special/Values quick access button
> (not the Paste Special … sub-menu) from the right-click menu in
> Excel, QuantLibXL falls over and all subsequent calls fail.
>
> https://quant.stackexchange.com/questions/59026/excel-pastespecial-values-shortcut-button-causing-quantlib-to-crash
>
> I wonder if anyone else can reproduce this?
>
> Best wishes
>
> David
>
>
>
>
> _______________________________________________
>
> QuantLib-users mailing list
>
> Qua...@li... <mailto:Qua...@li...>
>
> https://lists.sourceforge.net/lists/listinfo/quantlib-users
>
|
