Search code examples
c#performanceexcelexcel-dna

How to stop Excel-DNA function from calculating while inputting values

I have implemented some elaborate and computationally expensive function in c#. To use it in Excel I have created an Excel-AddIn via Excel-DNA.

Now when I call the function within Excel and start inputting values it starts calculating even before I have finished giving it all the inputs. What is more, when I click into the cell and change some of the inputs the function also recalculates. Usually I wouldn't mind. But due to the slow performance it turns working into an ordeal

Is there a way to suppress this behavior ? (Setting calculation to manual doesn't seem to work) Basically I want the Excel-DNA formulas to (re)calculate only when F9 is pressed.

If anyone has a solution in another language I will gladly use it as an inspiration and port it to c#.

Solution

  • According to Govert (author of XL DNA) you can do this:

    You can call ExceDnaUtil.IsInFunctionWizard() to check.

    So you function might go:

    public static object SlowFunction()
{
    if (ExcelDnaUtil.IsInFunctionWizard()) return "!!! In Function
Wizard";

    // do the real work....
}

    Its worth looking at the Excel DNA Google groups for XLDANA related problems and answers https://groups.google.com/forum/#!forum/exceldna