[ExcelDna] UDF crashes on ExcelReference.GetValue() / strange volatile behavior

[SFun28]

Hi Govert,

I have a UDF that take 3 object parameters.
a. It verifies that the first is an ExcelReference (call this
MyReferenceParam), and the last two are doubles.
b. Then it gets the values of MyReferenceParams using
MyReferenceParams.GetValue() (after casting to ExcelReference).
c. Finally it performs calculation and returns values.

Anytime I delete a cell (or change a cell's value) that's in the
dependency chain of my UDF, my UDF crashes when calling
ExcelReference.GetValue() (stacktrace below). Right now I have
IsVolatile=false. So technically speaking I don't think the UDF
should be called at all. But even if it is called, there's no reason
why my UDF should crash. I check that MyReferenceParam is a perfectly
fine ExcelReference. Also, it doesn't seem to matter where in the
dependency chain I make the change. In fact, I can make changes
OUTSIDE of the dependency chain and the crash occurs. So if I happen
to type some random numbers in a fresh column (never populated), and
then I delete that column, the UDF is called and crashes!

I have very-well documented source code along with an example
spreadsheet if needed.

Exception of type 'ExcelDna.Integration.XlCallException' was thrown.
at ExcelDna.Integration.XlCall.Excel(Int32 xlFunction, Object[]
parameters)
at ExcelDna.Integration.ExcelReference.GetValue()

thoughts?

Best,
Suraj

--
You received this message because you are subscribed to the Google Groups "ExcelDna" group.
To post to this group, send email to exce...@googlegroups.com.
To unsubscribe from this group, send email to exceldna+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/exceldna?hl=en.

[Govert van Drimmelen]

Hi Suraj,

By the UDF crashing, do you mean that it returns a #VALUE to Excel?
(I certainly hope Excel doesn't crash!)

I'm guessing that ExcelReference.GetValue() (which calls
XlCall.xlCoerce) fails if there are #REF cells in the range.
I think some cell delete operations trigger a recalc - you'd need to
investigate a bit.

Is your function marked as IsMacroType=true? I suspect that makes a
difference to how XlCoerce works.

Could you post a minimal .dna file with some instructions on how to
reproduce what you see?

Regards,
Govert

[SFun28]

Hi Govert,

Before I work-up a minimal solution (would be a .net solution - this
is a compiled UDF), I wanted to answer your questions and pose my
own. If the minimum-solution is the best way to solve this then just
let me know and I'll create it:

- By crashing I mean that ExcelReference.GetValue() throws an
exception that I don't catch because I believe my UDF is exception
safe (I've marked IsExceptionSafe=true). As a result, Excel
crashes.
- my UDF has IsMacroType=false
- How can we explain the case where deleting a column that isn't in
the dependency chain of the UDF causes GetValue() to throw? In this
case, no references that affect the UDF have changed. bizarre huh?

-Suraj

[Govert van Drimmelen]

Hi Suraj,

- I think you should not be using IsExceptionSafe=true - your function
is clearly not exception safe :-) You can call
XlCall.TryExcel(XlCall.xlCoerce, myReference) to simulate the
GetValue() call but without the exception. I doubt whether there will
be a measureable performance benefit for your function when marked
with IsExceptionSafe=true and there is no reason to let Excel crash.
In any event, I am planning to remove this option in future, or to
rename it to something like AllowExcelCrashes=true.
(In version 0.23 I added the option to deal with unhandled exceptions
yourself, by calling
ExcelDna.Integration.Integration.RegisterUnhandledExceptionHandler .
This might allow you to give a nice result to the user.)

- The recalculation is not a mystery. I presume your paramter is
marked by an ExcelArgument(AllowReference=true)]. This automatically
causes the function to be registered as Volatile by Excel, even if you
mark the function by IsVolatile=falsem and have IsMacroType=false.
From this article: http://msdn.microsoft.com/en-us/library/bb687891.aspx
"Excel treats XLL UDFs that take range arguments and that are declared
as macro-sheet equivalents as volatile by default. You can turn this
default state off using the xlfVolatile function when the UDF is first
called."

I'm just not sure why exactly the xlCoerce call fails sometimes, but
is OK at other times. I'm still guessing a #Ref or some strange value
in one of the cells in that Range. If you set the 'Describe' function
in the MoreSamples.dna file to AllowReference=true, does it also fail
under the same conditions? Maybe you could check....

Hope this makes sense,
Govert

> For more options, visit this group athttp://groups.google.com/group/exceldna?hl=en.- Hide quoted text -
>
> - Show quoted text -

[SFun28]

Hi Govert,

Thanks for the suggestions and the detailed reply. Here's what I
found (but I cannot explain any of it =)

1. I set IsExceptionSafe=false. Then I performed the same test (I
deleted a row that is NOT in the dependency chain of an input
ExcelReference). And everything works fine! But here's the strange
part:
a. the first time I delete a column my UDF throws an exception on
GetValue(). But then the UDF is called again! and the second time it
works fine.
b. any subsequent column deletions work just fine. no exceptions
are thrown.

Again, there is no #REF! issues here because I'm deleting a column
that is NOT in the dependency chain. In fact, this test proves this
out - my UDF returns the exact same values as expected.

2. If I set all parameters AllowReference=false, and I set IsVolatile
= false, and then I delete a column that is NOT in the dependency
chain, my UDF is still called! (although now the parameter isn't an
ExcelReference as expected)

So I guess I'm technically unblocked, but these behaviors give me a
bad feeling. I believe there's a bug here (especially given behavior
of #1). thoughts?

-Suraj

> > For more options, visit this group athttp://groups.google.com/group/exceldna?hl=en.-Hide quoted text -

>
> > - Show quoted text -
>
> --
> You received this message because you are subscribed to the Google Groups "ExcelDna" group.
> To post to this group, send email to exce...@googlegroups.com.
> To unsubscribe from this group, send email to exceldna+u...@googlegroups.com.

[Govert van Drimmelen]

Hi Suraj,

I have made a small test to see when recalculation happens.
I tested on Excel 2007, and don't see the problem you describe. Are
you perhaps dereferencing other parts of the sheet in your function?

I append a small .dna file that will allow us to test and discuss the
same function. The TestRecalc function will show a messagebox for
every recalc, and internally calls GetValue on references. I observe
this:
* Deleting any row or column in my sheet causes a recalc of the
function for me - presumably the whole sheet is recalced. This is true
for all functions, also for UDFs made in VBA. So your point 2. is just
a fact of how Excel recalculates.
* I _never_ see the #Value and then recalc issue you describe in point
1.
* Even if I set IsExceptionSafe=true, I never get Excel to crash
(unless I set IsMacroType=false and call the disallowed xlfRefText).
* You can reset the volatility of IsMacroType functions with a call to
xlfVolatile.
* If seems AllowReference=true arguments in IsMacroType=false
functions do not make the function volatile by default.

Perhaps are calling other functions with unexpected side effects you
describe in 1. Could you try again with the TestRecalc you see below,
and some detailed steps to get behaviour you don't expect?

Regards,
Govert

<DnaLibrary Language="C#">
<Reference AssemblyPath="System.Windows.Forms.dll"/>
<![CDATA[
using System;
using System.Windows.Forms;
using ExcelDna.Integration;

public class MyFunctions
{
[ExcelFunction(Description="Testing Recalculation.",
IsMacroType=true)]
public static string
TestRecalc([ExcelArgument(AllowReference=true)]object arg)
{
MessageBox.Show("Now Calculating ...");

// This function is volatile by default because of the
IsMacroType=true.
// This can be switched off by uncommenting the next line.
// XlCall.Excel(XlCall.xlfVolatile, false);


if (arg is ExcelReference)
{
object value = (arg as ExcelReference).GetValue();

// Calling xlfRefText here requires IsMacroType=true for this
function.
// Also experiment with IsMacorType=false and removing the
xlfRefText call here.
return "Reference: " + XlCall.Excel(XlCall.xlfReftext, arg, true)
+ " with value: " + value.ToString();
}

return "Not a reference - " + arg.ToString();
}
}
]]>
</DnaLibrary>

> > > For more options, visit this group athttp://groups.google.com/group/exceldna?hl=en.-Hidequoted text -

[SFun28]

Hi Govert,

I agree. Lets reproduce this behavior with a reduced-case. I'm not
familiar with UDF in dna files - my solutions are always compiled. So
I prefer to work with a VS 2008 solution (and that will more closely
represent the environment I'm using) Let me create a simple VS2008
compiled add-in. I'll zip it up and post the code. I'll also create
an example worksheet. Please give me a day or two to work on this.

I'm not dereferencing other parts of the sheet, so I don't think
that's the issue. I'll be in touch soon...

Best,
Suraj

> > > > For more options, visit...
>
> read more »

[Govert van Drimmelen]

Hi Suraj,

You need no VS solution.
Try this:
1. Take the xml stuff in the message below and put into a text file,
which you save as Test.dna.
2. Copy ExcelDna.xll and call it Test.xll.
3. Open the Test.xll in Excel - the function will be available.

If there's an issue with ExcelDna, you'll find it already with the
TestRecalc function here. If not, we can grow the function until we
find the problem you run into.

Govert

> > > > > > > spreadsheet if needed.- Hide quoted text -
>
> - Show quoted text -...

[SFun28]

Hi Govert,

Apologies for the delayed reply. I've taken your example and stripped
it down even further, but have retained the parameters of my original
UDF and the UDF name:

<DnaLibrary Language="C#">
<Reference AssemblyPath="System.Windows.Forms.dll"/>
<![CDATA[
using System;
using System.Windows.Forms;
using ExcelDna.Integration;
public class MyFunctions
{

/// <summary>
/// Binning
/// </summary>
[ExcelFunction( IsExceptionSafe = true , IsHidden = false ,
IsMacroType = false , IsThreadSafe = true , IsVolatile = true , Name =
"BinEqualFrequency" )]
public static object BinEqualFrequency(
[ExcelArgument( AllowReference = true , Name =
"ValuesToBin" )] object valuesToBin ,
[ExcelArgument( AllowReference = false , Name =
"FirstBin" )] object firstBin ,
[ExcelArgument( AllowReference = false , Name =
"TotalBins" )] object totalBins )
{

object rangeValues =
( ( ExcelReference )valuesToBin ).GetValue();
return "Good";

} // BinEqualFrequency()


}

]]>
</DnaLibrary>

In this very simple case (I didn't test IsMacroType=true because I
would never set that), I can get Excel to crash on a test spreadsheet
generated on using my fully functional UDF (I'll email you the
spreadsheet)

BTW, the repro steps are bizarre:
1. Open Excel, install the add-in, close excel
2. Open Excel, ensure the add-in was installed (I've found that Excel
sometimes doesn't install the add-in - particularly when you had
another add-in by the same name). Close excel
3. double-click spreadsheet to open. delete column L. crash.

here's the bizarre part:
4. launch excel. excel crashes again (its trying to recover the
spreadsheet that caused the crash). "close program"
5. launch excel again. see spreadsheet with bunch of #N/A errors.
close Excel without saving.
6. double-click spreadsheet to open. delete column L. NO CRASH
7. from here, I can't determine the pattern, but some combination of
closing and opening excel without the spreadsheet, then opening the
spreadsheet will bring you back to step #3 above. rise, repeat. =)

so I guess the first task is to see if you can repro the above?

-Suraj

> > > > > > > > dependency chain of my UDF, my UDF crashes when calling...

[Govert van Drimmelen]

Hi Suraj,

I have tried the exact steps you outline, but find no Excel crash or
other unexpected behaviour :-(
I have Excel 2007 SP2 running on Windows XP, with ExcelDna version
0.25.

Maybe you can upload the spreadsheet you use to the files section of
the Google Group to let other try to reproduce the problem.

As I understand it, you say the call to ExcelReference.GetValue()
sometimes fails and throws an Exception - and because you have marked
the functions as IsExceptionSafe=true, the exception will correctly
cause Excel to terminate.

Is my understanding correct that you cannot reproduce the problem with
the TestRecalc function I sent above?


I suggest you try this:

1. Set IsExceptionSafe=false. This will _always_ prevent Excel from
crashing, and the advanced IsExceptionSafe=true setting has no
advantage for your function. There is no point in crashing Excel, or
subsequently trying to understanding the recovery behavior of Excel
when you have crashed it on purpose. Excel has all kind of attempts to
detect and recover from ill-behaved add-ins which make it harder for
us to understand what is going on.
You should see the GetValue() problem you report because your function
will return #VALUE!.

2. Maybe the problem is related to the multithreaded recalculation -
can you reproduce the problem when you remove the IsThreadSafe=true
setting. Perhaps Excel prevents access to GetValue() in the threadsafe
context. If this removes the problem, we can try some more stuff to
try to reproduce the problem here.

3. If you still find the problem without IsThreadSafe=true, you might
like to put an try...catch block in your code, and retry-the call to
GetValue().


Do you have other add-ins loaded at the same time? Maybe try without
any other add-ins loaded - there might be some unexpected interaction?
Is there anything else special on your machine or configuration that
you can think of?


Regards,
Govert

> > > > > > in one of the cells in that Range. If you set the 'Describe'...

[Govert van Drimmelen]

Sorry - I think you can't upload files.

The spreadsheet referred to in the previous post is now here:
http://groups.google.com/group/exceldna/web/SurajPost.xlsx

--Govert--

> > > > > > that is NOT in the dependency chain.  In fact, this...

[SFun28]

Hi Govert,

I replied to this but I don't see my reply posted. Hope it wasn't
lost =)

-Suraj

> > > > > > > 1. I set IsExceptionSafe=false.  Then I performed the same...
>
> read more »

--
You received this message because you are subscribed to the Google Groups "Excel-Dna" group.

[SFun28]

Ah..I guess it was lost =) I'll retype from memory:
Your understanding is correct. I cannot reproduce this using your
TestRecalc function.

1. IsExceptionSafe=false works great. That's my solution. As
mentioned before, my UDF gets called a second time after the first
crash and then GetValue() works fine. So this is an acceptable
solution.

2. unfortunately the exception is still thrown when IsThreadSafe=false

3. The try/catch with a retry still causes the exception to be
thrown. Its only after the UDF is called again that GetValue()
operates as expected.

Sorry I can't provide an easier example to reproduce. I'll continue
to explore this issue as time permits, but I don't want to take up any
more of your time.

By the way, I'm running Excel on Win7 64-bit machine. Excel has all
the latest updates. No other add-ins loaded. Perhaps this is an
environment issue.

Anyways, thanks so much for your time on this. I'll write back if I
can provide more details.

> > > > > > >                                 + " with...
>
> read more »