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

Motivate

You need to detect if the function wizard is visible and return out of the function if it is. I have some C++ code that does this; if it's helpful I'll post. – Hailey

@Hailey could you perhaps elaborate on that ? - unfortunately I don't really understand what you are tryint to tell me – Motivate

Briefly, when the function wizard is visible, excel will call the function while you are entering the parameters. This, of course, can be expensive if the function is slow to evaluate. At the top of every potentially slow function I check if the wizard is visible before continuing. – Hailey

@Hailey could you perhaps provide some rudimentary code sample if possible ? – Motivate

I'll suffer vast amounts of downvoting if I post a C++ answer to your question! If you were to edit your question to say something on the lines of "If anyone has a solution in another language please help: I'm happy to port" then I'll post. – Hailey

It has been done. I often am still confused why some of the down-votes happen ... – Motivate

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

Abednego

+1 Just seen this, and, having re-read your title, it's obvious you use XL DNA. Apologies for leading you up the garden path with my, soon to be deleted, answer. – Hailey

@Charles Williams - I am always wondering where you guys get this type of knowledge :) – Motivate

I spend a lot of time working on UDFs (but I mostly use VBA and C++XLLs) so I try to keep in touch with all the UDF technologies – Abednego

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;
}

Hailey

hmm somewhat more complicated than I would have thought ^^ – Motivate