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

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:

1. Call out to the COM method Application.Evaluate().
2. 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;
}