HOW TO: How to evaluate an Excel formula from a macro function

Reference: Q0053

Article Last Modified on 3-November-2011

The information in this article applies to:

XLL+ for Visual Studio 2010 - 6.0, 7.0
XLL+ for Visual Studio 2008 - 5.0, 6.0, 7.0
XLL+ for Visual Studio 2005 - 5.0, 6.0, 7.0

HOW TO: How to evaluate an Excel formula from a macro function

Problem

When I try to evaluate a formula using CXlOper::Evaluate or CXlMacros::Evaluate, the operation fails if the formula contains any cell addresses, such as "A1" or "A1:C2".

Summary

You can work around the problem in one of two ways:

Call out to the COM method Application.Evaluate().
Expand cell references to include the sheet name, e.g. "Sheet1!A1". If the reference is to the current sheet, then you can simply precede them with an exclamation mark, e.g. "!A1".

Using Application.Evaluate()

The function below shows how to call out from a macro function to the COM method.

#include <xlpcomhelpers.h>
#include <xlfuncs.h>

static DISPID dispidEvaluate = -1;
BOOL AppEvaluate(const TCHAR* pszFormula, CXlOper& xloResult)
{
    CVariant vntResult, vntFormula(pszFormula);
    CDispatch dispApp = CXllApp::Instance()->GetApplicationDispatch();
    HRESULT hr = ::OleMethod(dispApp, L"Evaluate", &vntResult, &dispidEvaluate, 1, &vntFormula);
    if (SUCCEEDED(hr))
    {
        CXlOper xloResult1;
        hr = VariantToXlOper(vntResult, xloResult1);
        if (SUCCEEDED(hr))
        {
            return CXlFuncs::Transpose(xloResult, xloResult1) == 0;
        }
    }
    return FALSE;
}