Re: [Quantlib-users] Issue with QuantLibXL and Paste Special in Excel

SourceForge Headquarters 1320 Columbia Street Suite 310 San Diego, CA 92101 +1 (858) 422-6466

I also could not reproduce the error. This might be specific to Excel *365*.

thanks
Francois Botha

On Thu, 5 Nov 2020 at 17:49, <da...@el...> wrote:

> Hi Eric,
>
>
>
> To be honest, it is something of a cludge solution.
>
>
>
> Ideally, the constructor wouldn’t have code that could fail: you could end
> up with a constructed object that doesn’t work properly. Any initialization
> code that might not work could be put instead in a private Init() call
> which sets a Valid flag in the object. Then any method calls could lazily
> check the state via Init() before doing anything.
>
>
>
> Alternatively you could handle the exception within the constructor, but
> you still have the issue of what state to leave the object in.
>
>
>
> On the other hand, it is clear that this exception takes a quite specific
> set of circumstances, so it might not matter in practice.
>
>
>
> I am something of a GitHub novice, so, yes, I can send a pull request …
> once I learn how to do it!
>
> Best wishes,
>
>
>
> David
>
>
>
>
>
> *From:* Eric Ehlers <eri...@re...>
> *Sent:* Thursday, 5 November 2020 15:05
> *To:* da...@el...
> *Cc:* qua...@li...
> *Subject:* Re: [Quantlib-users] Issue with QuantLibXL and Paste Special
> in Excel
>
>
>
> 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...> <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... 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...
>
> https://lists.sourceforge.net/lists/listinfo/quantlib-users
>
> _______________________________________________
> QuantLib-users mailing list
> Qua...@li...
> https://lists.sourceforge.net/lists/listinfo/quantlib-users
>