Demonstrates how to get and use Excel's international settings

Overview

This add-in uses the CXlApp::GetInternational function to get the current list separator. It then inserts a formula into a cell, using the correct separator.

In English the formula is =MyFunc(101,99) . In German the formula will be =MyFunc(101;99) .

BOOL PasteFormulaIntl()
{
    XLL_FIX_STATE;
    //}}XLP_SRC

    CXlOper xloListSep, xloActive;
    char achFormula[256];

    if (CXllApp::GetInternational(XlInternationalListSeparator, xloListSep))
    {
        // Print the formula using the current separator 
        // e.g.: "=MyFunc(101,99)" in English 
        //    or "=MyFunc(101;99)" in German
        _snprintf(achFormula, sizeof(achFormula), "=MyFunc(%.0f%s%.0f)",
        101.0,(LPCSTR)xloListSep.ToString(), 99.0);
        if (xloActive.GetActiveCell()
         && xloActive.SetFormula(achFormula))
            return TRUE;
    }
    return FALSE;
}

You may need to use a similar technique for number formats etc.

Calling GetInternational() from a worksheet function

The simple worksheet function GetInternational() also calls CXlApp::GetInternational() and returns the value found. Note that the add-in function is marked as "Defer recalculation". CXllApp::GetInternational() will always fail if called from worksheet functions that have not been marked as "Defer recalculation".

Note also that the add-in function is marked as "Volatile". This causes the formula to be automatically recalculated whenever Excel recalculates the current sheet. Under most versions of Windows and Excel, Excel recalculates whenever the user changes the regional settings, so the formula will update automatically.

See the workbook International.xls for examples of the add-in function in use.

Classes and functions used

CXllApp::GetInternational | CXlOper::GetActiveCell | CXlOper::SetFormula

Sample project

Each sample project is located in a sub-directory of the Samples directory of the XLL+ installation. To use the sample project, open the solution file International.sln or the project file International.vcproj.

You can enable debugging under Excel by using the Setup Debugging command in the XLL+ ToolWindow.

When delivered, the help files are excluded from the build. You can enable the help build by selecting the files International.help.xml and International.chm in the Solution Explorer, and using the right-click menu to view Properties. Select the page "Configuration Properties/General" and set the "Excluded from build" property to "No". See Generating help in the User Guide for more information.

See Also

List of Sample Projects