HOW TO: How do I define a named range on an Excel worksheet?

HOW TO: How do I define a named range on an Excel worksheet?

Question

How do I define a named range on an Excel Worksheet?

Answer

Use the CXlMacros::DefineName method.

Construct a reference to the range which you wish to name. Pass the reference to CXlMacros::DefineName along with the name you want to apply to it.

Note that this code can only be used during Macro functions or event handlers. It cannot be called from a worksheet function.

Example

//{{XLP_SRC(MyDefineName)
    // NOTE - the FunctionWizard will add and remove mapping code here.
    //    DO NOT EDIT what you see in these blocks of generated code!
IMPLEMENT_XLLFN2(MyDefineName, "R", "MyDefineName", "", 
    "User Defined", "No description provided", "", "appscope=1\0"
    , 2)

extern "C" __declspec( dllexport )
LPXLOPER MyDefineName()
{
    XLL_FIX_STATE;
    CXlOper xloResult;
//}}XLP_SRC

    CXlOper xloActive, xloRef, xloRef2;

    // Get a reference to cell B2 in the active worksheet
    if (!xloActive.GetActiveCell())
        return CXlOper::RetError(xlerrNA);
    CXlRef xlrB2(1, 1);
    xloRef.FromMRef(&xlrB2, 1, xloActive.GetSheetId());

    // Define a name in cell B2
    if (CXlMacros::DefineName("MyName", xloRef) != 0)
    {
        CXllApp::XlMessageBox("Failed to define name", XlMessageBoxTypeExclamation);
        return CXlOper::RetError(xlerrNA);
    }

    // Insert a formula that uses it in cell C2
    CString formula = "=MyName*2";
    CXlRef xlrC2(1, 2);
    xloRef2.FromMRef(&xlrC2, 1, xloActive.GetSheetId());
    xloRef2.SetFormulaIntl(formula);

    return xloResult.Ret();
}

Getting the current value of a named range

The code below shows how to retrieve the range referred to by a named range.

//{{XLP_SRC(MyGetName)
    // NOTE - the FunctionWizard will add and remove mapping code here.
    //    DO NOT EDIT what you see in these blocks of generated code!
IMPLEMENT_XLLFN2(MyGetName, "R", "MyGetName", "", "User Defined",
    "No description provided", "", "appscope=1\0", 2)

extern "C" __declspec( dllexport )
LPXLOPER MyGetName()
{
    XLL_FIX_STATE;
    CXlOper xloResult;
//}}XLP_SRC

    // Get the name 'MyName'
    CString nameValue;
    if (CXlMacros::GetName(nameValue, CXlOper("!MyName")) != 0)
    {
        CXllApp::XlMessageBox("Failed to get name", XlMessageBoxTypeExclamation);
        return CXlOper::RetError(xlerrNA);
    }

    // Display the name
    CXllApp::XlMessageBox("MyName:'" + nameValue + "'", XlMessageBoxTypeInformation);

    return xloResult.Ret();
}

See also

CXlMacros::DefineName() in the online documentation.
CXlMacros::GetName() in the online documentation.