Excel Solver: Can't interrupt if UDF is marked IsExceptionSafe=True

[Daniel T]

I have an ExcelDNA (VB) UDF that I am optimising using the Solver addin that comes bundled with Excel.

 

If I compile with IsExceptionSafe=False then behaviour is as expected; pressing <Escape> during the Solver iterations brings up the Solver dialog box offering an option to stop Solver, save scenario or resume.

 

If I compile with IsExceptionSafe=True (to improve speed) then Solver still runs but I can't interrupt it.

 

The best solution I have so far is for my UDF to detect <Escape> via:

If(XlCall.Excel(XlCall.xlAbort, true)) then Exit Function

 

... but that is a bit ungraceful and feeds Solver an incorrect value for the interrupted iteration.

 

I'm not sure exacty what IsExceptionSafe=False wraps around the UDF and how it helps Solver see the xlAbort call, but whatever it is, it would be nice to be able to emulate that via something like:

If(XlCall.Excel(XlCall.xlAbort, true)) then [do something that lets Solver see the xlAbort]

 

Any ideas?

[Govert van Drimmelen]

Hi Daniel,

What is the signature of your function?

The `IsExceptionSafe=true` would not be visible to Excel or the solver, it just affects how Excel-DNA rewrites and registers the function. One difference is that a function marked `IsExceptionSafe=true` that returns a `double` would be re-written and registered as a function that returns `object`. Then an unhandled exception inside the function would be returned as #VALUE (ExcelError.ExcelErrorValue).

So maybe the answer to your question is to make your `IsExceptionSafe=true` function return `object`, and then in the xlAbort case you return `ExcelError.ExcelErrorValue`.

-Govert

[Daniel T]

Thanks Govert for the quick response.

 

Current definition (I've abbreviated the arguments) is:

 

<ExcelFunction(IsExceptionSafe:=true)>
Public Shared Function Fuzzy_Model(x as Double,y as Double, etc...) as Object

...

It returns a 11x16 array; Solver is looking to maximise just one of the cells of that array.

 

 

[Daniel T]

As you suggested, I can return ExcelError.ExcelErrorValue in the event of xlAbort

 

However, if I want to resume Solver after the interrupt then there is a risk that the ExcelErrorValue it received will distort the optimisation process.

 

Is it possible that the `IsExceptionSafe=true` wrapper is trapping all xlAbort events and that they are not being seen by Solver?

 

Daniel

 

[Daniel T]

Looks like this is solved (pardon the pun) by the following:

 

<ExcelFunction(IsExceptionSafe:=true)>
Public Shared Function Fuzzy_Model(x as Double,y as Double, etc...) as Object

    XlCall.Excel(XlCall.xlAbort, True, 1, True)

    '                                     ^pxRetain=True means don't clear the abort

    [do

    [lots

    [of

    [calculations

    '

    Return Result

End Function

 

Putting the xlAbort at the start seems to work better then putting it at the end of the function.  Check inside the main iteration loop also works but risks slowing down the calculation.

 

Thanks,

Daniel