2

I had an issue when I was making a simple UDF in C# with ExcelDna

The function uses this:

  var reference = XlCall.Excel(XlCall.xlfCaller);
  string oldValue = ((ExcelReference)reference).GetValue().ToString();

It would cause circular reference error (GetValue tries to re evaluate the cell by calling the UDF again) unless I specified IsMacroType=true, which marks the function as class 2 (add #to a formula).

I have no idea how it would connect to circular error. But I guess ExcelReference.GetValue() sometimes would evaluate the cell, sometimes not?

It seems class 2 only affect xlfCaller,

And later I checked the source code for ExcelReference, it turns out that it is actually xlCoerce that is called.

Also, I noticed when I press F2 on a cell, GetValue() returns 0 no matter what the cell is. And when I press Ctrl+Alt+F9 force recalculate all, GetValue() returns the previous calculated value.

Could someone elaborate me on this a little further? How does xlCoerce work on Cell value/formula anyway and how would class 2 affect it?

colinfang
  • 20,909
  • 19
  • 90
  • 173

1 Answers1

2

xlCoerce is just the standard way of getting the value contained in a reference. On its own, making this call with an ExcelReference should not cause the cell to recompute.

If your function is registered as a macro sheet equivalent function (by adding IsMacroType=true in your declaration for Excel-DNA) then it will be able to read any value on the sheet while calculating, including the previous value for the calling cell. In the case where you have edited the formula with F2, it's not just a recalculation, but a whole re-entry of the cell contents so it makes sense that this behaves different compared to forcing a recalculation.

It might not answer your questions directly, but Charles Williams has a pretty good write-up of the Excel calculation process here: http://www.decisionmodels.com/calcsecretsc.htm.

Govert
  • 16,387
  • 4
  • 60
  • 70