Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Order of Calculation for Functions in Automation Add-In
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Order of Calculation for Functions in Automation Add-In
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 "Matthew Wieder" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
automation of YTD calculation | Excel Worksheet Functions | |||
Forcing calculation through automation | Excel Programming | |||
Tab Order Automation?? | Excel Programming | |||
addIn functions in automation | Excel Programming | |||
Add-In Functions not listed in Automation | Excel Programming |