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

Question

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#.

Answer 1

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

Answer 2

The problem you're encountering is that the Excel Function Wizard will call the function repeatedly whilst you are entering parameter values.

To circumvent this, your function needs to detect the presence of the Function Wizard and proceed accordingly.

I have some C++ code that does this robustly in production. Hopefully you can port this to C#. It uses the Windows API. You need to take care that the Function Wizard is pertinent to a particular Excel session; taking special care of Excel2013.

typedef struct _EnumStruct
{
    bool wizard;
    DWORD pid;
} EnumStruct, FAR* LPEnumStruct;

BOOL CALLBACK EnumProc(HWND hwnd, LPEnumStruct pEnum)
{
    static const char szFunctionWizardClass[] = "bosa_sdm_XL";
    static const char szFunctionWizardCaption[] = "Function Arguments";

    char szClass[sizeof(szFunctionWizardClass)];
    char szCaption[sizeof(szFunctionWizardCaption)];

    if (GetClassName(hwnd, (LPSTR)szClass, sizeof(szFunctionWizardClass))){
        if (CompareString(MAKELCID(MAKELANGID(LANG_ENGLISH, SUBLANG_ENGLISH_US), SORT_DEFAULT), NORM_IGNORECASE, (LPSTR)szClass, (lstrlen((LPSTR)szClass) > lstrlen(szFunctionWizardClass)) ? lstrlen(szFunctionWizardClass) : -1, szFunctionWizardClass, -1) == CSTR_EQUAL){
            // Do the process IDs match? (The former way of checking parent windows doesn't work in Excel2013).
            DWORD pid = NULL;
            GetWindowThreadProcessId(hwnd, &pid);
            if (pid == pEnum->pid){
                // Check the window caption
                if (::GetWindowText(hwnd, szCaption, sizeof(szFunctionWizardCaption))){
                    if (CompareString(MAKELCID(MAKELANGID(LANG_ENGLISH, SUBLANG_ENGLISH_US), SORT_DEFAULT), NORM_IGNORECASE, (LPSTR)szCaption, (lstrlen((LPSTR)szCaption) > lstrlen(szFunctionWizardCaption)) ? lstrlen(szFunctionWizardCaption) : -1, szFunctionWizardCaption, -1) == CSTR_EQUAL){
                        pEnum->wizard = TRUE;
                        return FALSE;
                    }
                }
            }
        }
    }
    // Continue the enumeration
    return TRUE;
}

bool Excel12::calledFromFunctionWizard()
{
    EnumStruct enm;
    enm.wizard = FALSE;
    enm.pid = GetProcessId(GetCurrentProcess());
    EnumWindows((WNDENUMPROC)EnumProc, (LPARAM)((LPEnumStruct)&enm));
    return enm.wizard;
}