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

[<da...@el...>]

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... <mailto: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  <mailto:eri...@re...> <eri...@re...> 
Sent: Wednesday, 4 November 2020 19:57
To: qua...@li...
<mailto: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-sh
ortcut-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