Order of Calculation for Functions in Automation Add-In

[Matthew Wieder]

I have written an Automation Add-In in C# (2005). On my spreadhseet, I
have the result of one volatile function as one of the input params for
another volatile function (i.e. MyFunc1 is in cell B2, and B2 is an
input param to MyFunc2). The behavior I have noticed is that when I hit
F9, The 2nd Function (MyFunc2) tries to execute first and fails since
the 1st function (MyFunc1) has not yet been calced and only then does
Excel calc the 1st function followed by the 2nd function giving the
correct result. This is dissapointing since Excel knows that one is fed
to the other and should know to calc that one first. The consequence is
that Excel makes an expensive call into the Add-In. It begins to
evaluate the second function and only when it hits a line of code in C#
which tests if the input is valid does it throw a .NET exception which
is expensive. Is there some way to teach Excel the priority, or to
force it to be smarter?

 

[Charles Williams]

Hi Matthew,

solution Part 1: dont make your functions volatile since this is very
inefficient: instead make sure ALL the input for the function is contained
in the argument list. This will also help but not cure the execution
sequence problem.

solution part 2: check that the arguments for the function have been
calculated before executing the function: because of the way that the excel
calculation algorithm works, there is no way of preventing a function being
sometimes executed (possibly more than once) with uncalculated arguments.
(use ISEMPTY() : see http://www.decisionModels.com/calcsecretsj.htm)

solution part 3: I have been told that c# automation addins execute more
slowly than any other addin type: consider using another technology (I have
not personally verified this statement)

Charles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com