PRB: 'Do not call in Formula Wizard' does not work in Excel 2013 and above

The 'Do not call in Formula Wizard' option does not work in Excel 2013 and above

Issue

I have marked my add-in function as 'Do not call in Formula Wizard', as shown below. This option works properly in Excel 2010 and below, but does not have any effect in Excel 2013 or above. When the user edits the function in the Excel Formula Wizard, the function is called every time the user changes a value.

Summary

The runtime libraries for XLL+ version 7.0.8 and below do not always correct handle the detection of the Excel Formula Wizard in Excel 2013 and above. A new detector class, CXlFormulaWizardDetector2, is available for download. A helper class (CXlFormulaWizardDetector2Installer) ensures that an instance of CXlFormulaWizardDetector2 will be plugged into the framework when the add-in is opened.

Solution

1. Download the header file XlpFormWiz2.h contained in q0061_header.zip.

2. Add the file to your project.

3. Include the header file in your main C++ source file, e.g.:

   #ifdef XLL_LIB_MFC
   #include "stdafx.h"
   #endif
   #include "MyAddin.h"
   #include "XlpFormWiz2.h"
   
   #ifdef _DEBUG
   #ifdef XLL_LIB_MFC
   #define new DEBUG_NEW
   #endif
   #undef THIS_FILE
   static char THIS_FILE[] = __FILE__;
   #endif
   
   

Alternatively, copy the code below and save it as a new header file in your project.

/*-------------------------------------------------------------------------*\
|                                                                           |
|   FILE:       XlpFormWiz2.h                                               |
|   PURPOSE:    Formula Wizard language support for XLL+ classes.           |
|   DATE:       24 July 2016                                                |
|   VERSION:    7.0.9 NT                                                    |
|   COPYRIGHT:  (c) Planatech Solutions Ltd, 2010-2016                      |
|                                                                           |
|   This software is the property of Planatech Solutions Ltd.  It is        |
|   licensed for the use of the purchaser only, and no license is granted   |
|   for the further distribution of this source code whether for sale or    |
|   otherwise, whether in its original form or amended. Any binary files    |
|   generated from this source code can be distributed for sale to third    |
|   parties only with the express written permission of Planatech Solutions |
|   Ltd. Planatech Solutions Ltd and their agents accept no liability for   |
|   losses incurred of any kind through the use of this software.           |
|                                                                           |
\*-------------------------------------------------------------------------*/

#ifndef __XLPFORMWIZ2_H__
#define __XLPFORMWIZ2_H__

#ifndef XLP_FORMWIZ2_NOT_REQUIRED

#ifndef _WINBASE_
#error This file requires winbase.h
#endif
#ifndef __XLLPLUS_H__
#error This file requires xllplus.h
#endif
#include <XlpFormWiz.h>

class CXlFormulaWizardDetector2 : public CXlFormulaWizardDetector
{
public:
    CXlFormulaWizardDetector2() : CXlFormulaWizardDetector()
    {
        m_dwPid = GetProcessId(GetCurrentProcess());
        m_nVersion = CXllApp::GetXlVersion();
    }
public:
    virtual bool MatchWindow(HWND hwnd, short hwndXLMain) const
    {
        // If Excel 2010 or earlier, use the older technique in the base class
        if (m_nVersion < 15)
        {
            return CXlFormulaWizardDetector::MatchWindow(hwnd, hwndXLMain);
        }

        // For Excel 2013 and above, we need to inspect the process ID instead of 
        // looking up the window tree.
        static const TCHAR szFunctionWizardClass[] = _T("bosa_sdm_XL");

        TCHAR szClass[sizeof(szFunctionWizardClass)/sizeof(TCHAR)];
        TCHAR szCaption[256];

        if (::GetClassName(hwnd, (LPTSTR)szClass, sizeof(szFunctionWizardClass)/sizeof(TCHAR))
          && (_tcsnicmp(szClass, szFunctionWizardClass, 11) == 0))
        {
            // Compare the PID of the window against the PID of this instance of Excel
            DWORD dwWindowPid = NULL;
            GetWindowThreadProcessId(hwnd, &dwWindowPid);
            if (dwWindowPid == m_dwPid)
            {
                // Use the base class to check the window caption
                if (::GetWindowText(hwnd, szCaption, sizeof(szCaption)/sizeof(TCHAR))) 
                {
                    return MatchWindowTitle(szCaption);
                }
            }
        }
        // Not matched
        return false;
    }
private:
    DWORD m_dwPid;
    int m_nVersion;
};

#ifndef XLP_FORMWIZ2_EXCLUDE_INSTALLER

// Event observers
class CXlFormulaWizardDetector2Installer : public CXllOpenEventStaticObserver {
public:
    CXlFormulaWizardDetector2* m_detector;
public:
    CXlFormulaWizardDetector2Installer() : m_detector(0) { }
    virtual void Update(CXllOpenEventArgs* /* e */)
    {
        if (!m_detector)
        {
            m_detector = new CXlFormulaWizardDetector2();
            CXllApp::Instance()->SetFormulaWizardDetector(m_detector);
        }
    }
};

CXlFormulaWizardDetector2Installer g_xlFormulaWizardDetector2Installer;

#endif // XLP_FORMWIZ2_EXCLUDE_INSTALLER

#endif // XLP_FORMWIZ2_NOT_REQUIRED
#endif // __XLPFORMWIZ2_H__