How to stop Excel-DNA function when calculating when entering values

Question

I implemented some complex and computationally expensive features in C #. To use it in Excel, I created Excel-AddIn via Excel-DNA.

Now, when I call the function in Excel and start entering values, it starts to calculate even before I finish giving all the input. Moreover, when I click on a cell and change some inputs, the function also recounts. Usually I do not mind. But due to slow work it turns into a test

Is there any way to suppress this behavior? (Setting up the calculation in manual mode does not seem to work). Basically, I want Excel-DNA (re) formulas to be calculated only by pressing F9.

If someone has a solution in another language, I would love to use it as 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 can work:

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

    // do the real work....
} 

Its worth a look at Excel DNA DNA groups for problems and answers related to XLDANA https://groups.google.com/forum/#!forum/exceldna

Answer 2

The problem you are facing is that the Excel function wizard will call the function again while you enter the parameter values.

To get around this, your function must detect the presence of the Function Wizard and act accordingly.

++, . , #. Windows API. , Excel; 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;
}