HOW TO: How do I display a number in local format?

Reference: Q0051

Article Last Modified on 22-June-2008

The information in this article applies to:

XLL+ for Visual Studio 2005 - 5.0, 6.0
XLL+ for Visual Studio 2005 - 5.0, 6.0
XLL+ for Visual Studio .NET - 4.2, 4.3.1, 5.0, 6.0
XLL+ for Visual Studio 6 - 3, 4.1, 4.2, 4.3.1, 5.0

HOW TO: How do I display a number in local format?

Question

How can I display a number in the user's current format?

Answer

Use CXllApp::GetInternational(XlInternationalThousandsSeparator) and CXllApp::GetInternational(XlInternationalDecimalSeparator) to get the localized separator characters.
Create a format string containg the separator characters.
Use CXlFuncs::Text() with the format string to create locally formatted text.

CXllApp::GetInternational()

This method cannot be called during normal worksheet functions, so it is best to use it during the OnXllOpenEx() event handler.

In the application class, add some public member variables to hold the values of the separators, e.g.:

class CNumberFormatApp : public CXllApp
{
public:
...

// Formats
    CString m_stThousandsSeparator;
    CString m_stDecimalSeparator;

...
};

During OnXllOpenEx(), call CXllApp::GetInternational() to populate the member variables, e.g.:

BOOL CNumberFormatApp::OnXllOpenEx()
{
    // Get the local numeric separator characters
    CXlOper xloTmp;
    if (GetInternational(XlInternationalThousandsSeparator, xloTmp))
        m_stThousandsSeparator = xloTmp.ToString();
    else
        m_stThousandsSeparator = _T(",");
    if (GetInternational(XlInternationalDecimalSeparator, xloTmp))
        m_stDecimalSeparator = xloTmp.ToString();
    else
        m_stDecimalSeparator = _T(".");
    return TRUE;
}

CXlFuncs::Text()

Add #include <xlfuncs.h> near the top of your project's cpp file.

In an add-in function, set up a formatting string and use CXlFuncs::Text() to apply it, e.g.:

// Function:    LocalNumber
// Purpose:     Formats a number using local separators

//{{XLP_SRC(LocalNumber)
    // NOTE - the FunctionWizard will add and remove mapping code here.
    //    DO NOT EDIT what you see in these blocks of generated code!
IMPLEMENT_XLLFN2(LocalNumber, "RB", "LocalNumber", "Number", 
    "Text", "Formats a number using local separators", "is a numb"
    "er\000", "\0appscope=1\0", 1)

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

    // Create a format string, using the separators stored in the 
    // application object, e.g. "#,###.00"
    CString stFormat = _T("#") + XllGetTypedApp()->m_stThousandsSeparator
        + _T("###") + XllGetTypedApp()->m_stDecimalSeparator
        + _T("00");

    // Apply the format to the number
    CString stResult;
    if (CXlFuncs::Text(stResult, Number, stFormat) == 0)
        xloResult = stResult;
    else
        xloResult = xlerrValue;

    return xloResult.Ret();
}