Function called twice in manual calculation mode

[bertrand]

Hi,

I created a function that dumps an Object[,] in Excel, modifying the
excel object.

It works fine, but the function is called twice when the calculation
mode is set to manual.

I am using Excel 2007.

This is a simplified function that reproduce the problem:

[ExcelFunction(IsMacroType = true)]
public static string DumpTest()
{
if (ExcelDnaUtil.IsInFunctionWizard())
return "Waiting for clic on wizard ok button to calculate.";

Application xlApp = (Application)ExcelDnaUtil.Application;

Type xlAppType = xlApp.GetType();
object activeCell = xlAppType.InvokeMember("ActiveCell",
BindingFlags.GetProperty, null, xlApp, null);
Type xlActiveCellType = activeCell.GetType();
Range cellBelow = xlActiveCellType.InvokeMember("Offset",
BindingFlags.GetProperty, null, activeCell, new object[] { 1, 0 }) as
Range;

Type xlCellBelowType = cellBelow.GetType();
xlCellBelowType.InvokeMember("Value", BindingFlags.SetProperty,
null, cellBelow, new[] { Type.Missing, "DumpTest" });

return "Dump done";
}

Does someone already had this problem ?

Thanks,

Bertrand

[Govert van Drimmelen]

Hi Bertrand,

You are setting other parts of the sheet from within a user-defined
functions, on the main calculation thread.
Excel tries very hard to prevent this from being done with either the
C API or from VBA.

I recommend extreme care when using the Excel Automation object model
from within UDFs.
Microsoft says (http://support.microsoft.com/kb/301443):
"A function that is defined in an XLL can be called under three
circumstances:
1. During the recalculation of a workbook
2. As the result of Excel's Function Wizard being called on to help
with the XLL function
3. As the result of a VBA macro calling Excel's Application.Run
Automation method
Under the first two circumstances, Excel's Object Model does not
expect, and is not prepared for, incoming Automation calls.
Consequently, unexpected results or crashes may occur."

That said, I think Excel adds to its dependency tree when you access
other cells from a macro-type function. The when the other cell
changes, I guess Excel think your function should be calculated again.
I'm pretty sure Excel can't sensibly deal with your setting other
cells from inside a function.

I think the usual ways of doing this would be to use timers or
separate threads for the updates.

Regards,
Govert

[bertrand]

Hi Govert,

Thanks for your answer.

I agree with you, there is an issue in the dependency tree in manual
calculation mode. As my function do not depend on the updated cell, it
should not be recalculated. This does not occurs in automatic mode.


I confirm that I never had any problem when the dump function was
called from vba with Application.Run (point 3).

I recently had some users starting to use =Dump(SomeFunction())
directly from Excel and noticed the function was called twice in
manual calculation mode.


I followed your advice, setting another cell value from a thread fix
the problem.

[ExcelFunction(IsMacroType = true)]
public static string DumpTest()
{
if (ExcelDnaUtil.IsInFunctionWizard())
return "Waiting for clic on wizard ok button to calculate.";

Application xlApp = (Application)ExcelDnaUtil.Application;

Type xlAppType = xlApp.GetType();
object activeCell = xlAppType.InvokeMember("ActiveCell",
BindingFlags.GetProperty, null, xlApp, null);
Type xlActiveCellType = activeCell.GetType();

Object cellBelow = xlActiveCellType.InvokeMember("Offset",
BindingFlags.GetProperty, null, activeCell, new object[] { 1, 0 });

Thread t = new Thread(DumpFromThread);
t.Start(cellBelow);

return "Dump done";
}

private static void DumpFromThread(Object range)
{
Type xlRangeType = range.GetType();
xlRangeType.InvokeMember("Value", BindingFlags.SetProperty, null,
range, new[] { Type.Missing, "Dump test from thread" });
}


I know Excel is not designed for this, but since I am using delegates
to access Excel Object properties and method, I haven't had any
crash.

Thanks again for ExcelDna, it is a fantastic project.

Bertrand