Creating Excel Solutions for Use in Multiple Countries/Regions Using Visual Studio Tools for Office

Harry Miller, Microsoft Corporation

Kathleen McGrath, Microsoft Corporation

McLean Schofield, Microsoft Corporation

Revised: March 2006

Applies to: Microsoft Visual Studio 2005, Microsoft Visual Studio 2005 Tools for the Microsoft Office System, Microsoft Visual Studio .NET 2003, Microsoft Visual Studio Tools for the Microsoft Office System, Version 2003, Microsoft Office Excel 2003

Summary: Learn about issues to consider when creating Microsoft Office Excel 2003 solutions for deployment to end users who have Microsoft Windows regional settings other than English. (11 printed pages)

Contents

  • Introduction to Developing Excel Solutions for Multiple Regions

  • Visual Studio 2005 Tools for Office Solutions

  • Visual Studio Tools for Office, Version 2003 Solutions

  • Considerations for Passing Information

  • Using Reflection for COM Interop Calls

  • Writing Code to Check Regional Settings

  • Changing Visual Studio 2005 Tools for Office to Use Version 2003 Behavior

  • Conclusion

  • Additional Resources

Introduction to Developing Excel Solutions for Multiple Regions

If you plan to develop a Microsoft Office Excel 2003 solution using Microsoft Visual Studio that will—or might—be used in multiple regions or on systems with differing Microsoft Windows regional settings, you must consider several issues during development. In addition to general globalization and localization considerations, be aware that passing certain kinds of information programmatically to Excel can result in errors when the format or culture of the information does not match what Excel expects.

If you use Microsoft Visual Studio 2005 Tools for the Microsoft Office System to develop your solution, by default Excel expects all data that has locale-sensitive formatting to have the "English (United States)" data format (locale ID 1033) before you pass it to Excel or read the data from your project code. Examples of data with locale-sensitive formatting are dates and currency.

If you use Visual Studio Tools for Office, Version 2003, the following issues may cause problems:

  • Passing a culture ID other than English to an English edition of Excel without a Multilingual User Interface (MUI) pack installed.

  • Passing data using string literals, including Excel function names, when the end user's computer has a Windows regional setting other than "English (United States)" (also known as en-US).

You can fix the first issue by installing an MUI pack on the end user's computer. You can fix the second issue by using reflection to make calls when operations involve string literals. These options are discussed later in this article.

Visual Studio 2005 Tools for Office Solutions

By default, Visual Studio 2005 Tools for Office projects cause the Excel object model to expect locale ID 1033 data formatting. Visual Studio 2005 Tools for Office sends the locale ID 1033 as the format of the data, regardless of what locale is set on the computer. For example, if you get or set the Value2 property in Excel, the data must be formatted the way that locale ID 1033 expects. If you use a different data format, you might get unexpected results, because the data will be interpreted as though it had "English (United States)" format. This behavior matches the way that Microsoft Visual Basic for Applications works.

Even though you use the "English (United States)" format for data that is passed or manipulated by managed code, Excel interprets and displays the data correctly according to the end user's locale setting. Excel can format the data correctly because the managed code passes locale ID 1033 along with the data, which indicates that the data is in "English (United States)" format and therefore must be reformatted to match the user's locale setting.

For example, if end users have their regional options set to the German (Germany) locale, they expect the date June 29, 2005, to be formatted this way: 29.06.2005. However, if your solution passes the date to Excel as a string, you must format the date according to "English (United States)" format: 6/29/2005. If the cell is formatted as a Date cell, Excel will display the date in German (Germany) format.

Visual Studio Tools for Office, Version 2003 Solutions

When a managed client calls a method on a COM object and it needs to pass in culture-specific information, it does so using the CultureInfo (locale) that matches the current thread locale. The current thread locale inherits from the user's regional settings by default. (All late-bound calls and calls to members with LCIDConversionAttribute set implicitly pass the CurrentCulture property value of the current thread.) If the call uses a culture other than English, Excel searches all available LCID folders for the file xllex.dll. If there is a localized edition of Excel on the computer, or an English edition of Excel that has an MUI pack installed, this file is available and the solution continues to run. However, if an English edition of Excel is installed without an MUI pack, the file is not included and one of the following errors appears:

Note

Exception from HRESULT: 0x800A03EC.

-or-

Note

Old format or invalid type library.

To prevent a run-time error, you must install the file xllex.dll on computers that run English editions of Excel, by installing an MUI pack.

Note

The MUI pack is designed to enhance the English version of Microsoft Office 2003; it is not supported with localized versions of Office. In addition, the MUI pack works only with editions of Office obtained through a Volume License agreement or similar channel; it does not work with retail editions of Office.

Considerations for Passing Information

If you installed an MUI pack, the file required for Excel to support a call with the supplied locale is available; however, to ensure that Excel interprets and displays your data correctly, consider the following potential issues:

  • Does your application store data, including Excel worksheet formulas, in string literals (hard-coded) instead of in strongly typed objects?

  • Does your application read from or write to text files designed for earlier versions of Microsoft Visual Basic or Visual Basic for Applications (VBA), and assume en-US formatting?

  • Does your application accept input from the user and convert it to en-US, when the installed Excel expects input in native format? (For example, did you build it using an earlier version of Visual Basic or VBA?)

In cases where you cannot avoid these problem areas, use reflection to make all calls to Excel that include string literals, Excel function names, or data that is formatted assuming en-US style.

Avoid Developing Applications That Use String Literals

Possible hard-coded values include date literals written in "English (United States)" format, and Excel worksheet formulas containing localized function names. A hard-coded string also might contain a number such as "1,000"; some cultures interpret this as one thousand, but in other cultures, it represents one point zero. Calculations and comparisons performed on the wrong format might result in incorrect data.

Excel interprets any strings in accordance with the LCID passed with the string. This causes a problem if the end user does not have the localized edition of Excel or the MUI pack that corresponds to the LCID. If your application will run on computers that use different locale settings, use the English format for all data and ensure that the LCID is en-US. Excel displays the data according to the regional settings and the Excel user interface language. With a few exceptions (such as Range.FormulaLocal), VBA formats strings as en-US and passes 0 (language neutral) as the LCID. For example, the following VBA code displays a correctly-formatted value for May 12, 2004, in accordance with the current user locale setting:

Application.ActiveCell.Value = "05/12/04"

The same code, when used in a Visual Studio Tools for Office solution and passed to Excel through COM interop, may produce unexpected results.

For example:

Globals.Sheet1.Range("A1", System.Type.Missing).Value = "05/12/04"

Depending on the current user locale setting, Excel may interpret this value as any of the following:

  • May 12, 2004

  • December 05, 2004

  • December 04, 2005

If the string literal falls outside the expected boundaries (for example, "27/03/72" easily could), a run-time error appears. However, if values fall within the expected boundaries and logic errors are not detected, Excel displays incorrect data.

Work with strongly typed data instead of string literals whenever possible. For example, instead of storing a date in a string literal, store it as a double, and then convert it to a DateTime object for manipulation.

The following code example takes a date that a user enters into cell A5, stores it as a double, and then converts it to a DateTime object for display in cell A7. (You must format cell A7 to display a date.)

Code for Visual Studio Tools for Office, Version 2003

' Visual Basic
Private Sub ConvertDate_Click(ByVal sender As System.Object, ByVal _
    e As System.EventArgs) Handles ConvertDate.Click
    
    Dim SourceRange As Excel.Range = CType(ThisWorkbook.ActiveSheet, _
        Excel.Worksheet).Range("A5")
    Dim TargetRange As Excel.Range = CType(ThisWorkbook.ActiveSheet, _
        Excel.Worksheet).Range("A7")

    Try
        Dim rangeValue As Double = SourceRange.Value2
        Dim dt As System.DateTime = _
            System.DateTime.FromOADate(rangeValue)
        TargetRange.Value2 = dt
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub

// C#
private void ConvertDate_Click(object sender, EventArgs e)
{
    object missing = System.Type.Missing;
    Excel.Range SourceRange = ((Excel.Worksheet)ThisWorkbook.ActiveSheet).
        get_Range("A5", missing);
    Excel.Range TargetRange = ((Excel.Worksheet)ThisWorkbook.ActiveSheet).
        get_Range("A7", missing);

    try
    {
        double rangeValue = (double)SourceRange.Value2;
        System.DateTime dt = System.DateTime.FromOADate(rangeValue);
        TargetRange.Value2 = dt;
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

Excel Worksheet Functions

Most language versions of Excel translate worksheet function names internally. However, due to potential language and COM interop issues, we strongly recommend that you use only English function names in your code, and use reflection to access relevant properties (such as Range.Formula).

Evaluate Applications That Use External Data

Any code that opens or otherwise uses external data, such as files that include comma-separated values (CSV files) exported from a legacy system, might also be interpreted incorrectly if exported using en-US format. For example, this might be the case if you originally designed an application for use with earlier versions of Visual Basic or VBA. This issue does not affect database access if all values are in binary format. However, if your database stores dates as strings or performs operations that do not use binary format, access may be affected. Also, if you construct SQL queries using data from Excel, you might need to ensure they are in en-US format, depending on the function you use.

Evaluate Applications That Accept User Input

If existing applications receive values that are not in "English (United States)" format, the application itself might convert values entered by the user to en-US before passing them to Excel. The conversion step causes errors because Excel expects the values in the user's culture, not en-US. The same problem occurs if users interact with applications using United States formats, because the system now expects their native format, not United States.

Using Reflection for COM Interop Calls

Use reflection to ensure that any COM call (IDispatch::Invoke) that passes string literals uses the desired LCID. This has the effect of giving the same run-time experience as Microsoft Office solutions that are created by using Visual Studio 2005 Tools for Office or by using VBA, which always pass a language-neutral LCID. When you use reflection as shown below, you must make sure to format all data, including Excel formula names, as "English (United States)". This ensures that the information appears or behaves correctly for the end user.

Create helper methods to encapsulate code that you will use multiple times. The following examples use reflection to set or get a property or invoke a method in the target range, passing in a CultureInfo of "English (United States)" so that you can use the English names of any strings and the code works correctly in all locales.

' Visual Basic
Shared Function SetPropertyInternational(ByVal target As Object, _
    ByVal name As String, ByVal ParamArray parameters() As Object) _
    As Object
    Return target.GetType.InvokeMember(name, _
        Reflection.BindingFlags.Instance Or _
        Reflection.BindingFlags.SetProperty, _
        Nothing, target, parameters, _
        System.Globalization.CultureInfo.GetCultureInfo(1033))
End Function

Shared Function GetPropertyInternational(ByVal target As Object, _
    ByVal name As String, ByVal ParamArray parameters() As Object) _
    As Object
    Return target.GetType.InvokeMember(name, _
        Reflection.BindingFlags.Instance Or _
        Reflection.BindingFlags.GetProperty, _
        Nothing, target, parameters, _
        System.Globalization.CultureInfo.GetCultureInfo(1033))
End Function

Shared Function InvokeMethodInternational(ByVal target As Object, _
    ByVal name As String, ByVal ParamArray parameters() As Object) _
    As Object
    Return target.GetType.InvokeMember(name, _
        Reflection.BindingFlags.Instance Or _
        Reflection.BindingFlags.InvokeMethod, _
        Nothing, target, parameters, _
        System.Globalization.CultureInfo.GetCultureInfo(1033))
End Function

// C#
static object SetPropertyInternational(object target, string name, 
    params object[] args)
{
    return target.GetType().InvokeMember(name,
        System.Reflection.BindingFlags.SetProperty |
        System.Reflection.BindingFlags.Public |
        System.Reflection.BindingFlags.Instance,
        null, target, args, new System.Globalization.CultureInfo(1033));
}

static object GetPropertyInternational(object target, string name,
    params object[] args)
{
    return target.GetType().InvokeMember(name,
        System.Reflection.BindingFlags.GetProperty |
        System.Reflection.BindingFlags.Public |
        System.Reflection.BindingFlags.Instance,
        null, target, args, new System.Globalization.CultureInfo(1033));
}

static object InvokeMethodInternational(object target, string name,
    params object[] args)
{
    return target.GetType().InvokeMember(name,
        System.Reflection.BindingFlags.InvokeMethod |
        System.Reflection.BindingFlags.Public |
        System.Reflection.BindingFlags.Instance,
        null, target, args, new System.Globalization.CultureInfo(1033));
}

To use the helper methods, call the functions to set the LCID correctly. For example, to set an Excel worksheet formula in cell A5, call the SetPropertyInternational function.

Code for Visual Studio Tools for Office, Version 2003

' Visual Basic
SetPropertyInternational(Me.Range("A5"), "Formula", "=SUM(12, 34)")

// C#
SetPropertyInternational(Me.Range("A5"), "Formula", "=SUM(12, 34)");

Writing Code to Check Regional Settings

In all cases, writing your code as if you expect end users' regional settings to be different gives the greatest probability of achieving the expected results. You may believe that only end users who have computers with regional settings set to a single shared locale will use your solution, and you therefore may not want to write the extra code to use reflection. However, you should add code to check the regional settings, so that the user receives a warning if the solution might not function correctly because of regional differences.

The following code checks whether the current thread for the Microsoft Office application is set to en-US; if it is not, a warning appears.

' Visual Basic
If System.Threading.Thread.CurrentThread.CurrentCulture.LCID <> _
    New System.Globalization.CultureInfo("en-US").LCID Then

    MessageBox.Show("Your current Microsoft Windows regional " & _
    "settings do not match the settings required by the custom " & _
    "functions in this document. Macros in this document might not "& _
    "run or might return incorrect data. Do not change your " & _
    "regional settings. Contact your administrator for " & _
    "more information.", Globals.ThisWorkbook.Name, _
    System.Windows.Forms.MessageBoxButtons.OK, _
    System.Windows.Forms.MessageBoxIcon.Information)
End If

// C#
if (System.Threading.Thread.CurrentThread.CurrentCulture.LCID !=
    new System.Globalization.CultureInfo("en-US").LCID)
{
    MessageBox.Show("Your current Microsoft Windows regional " + 
    "settings do not match the settings required by the macros " +
    "in this document. Macros in this document might not run or " +
    "might return incorrect data. Do not change your " +
    "regional settings. Contact your administrator for " +
    "more information.", Globals.ThisWorkbook.Name,
    System.Windows.Forms.MessageBoxButtons.OK,
    System.Windows.Forms.MessageBoxIcon.Information);
}

Changing Visual Studio 2005 Tools for Office to Use Version 2003 Behavior

The ExcelLocale1033Attribute controls the Excel object model behavior in your Visual Studio 2005 Tools for Office solution. By default the attribute is set to true, which causes the Excel object model to expect locale ID 1033 formatting and Visual Studio 2005 Tools for Office to pass locale ID 1033 as the data format. If you set the attribute to false, the Excel object model uses the end user's locale settings, which is the way that Visual Studio Tools for Office, Version 2003, works. You might set the attribute to false during debugging so that the Watch window displays information from the primary interop assemblies instead of from the locale ID proxy assembly.

ExcelLocale1033Attribute is in the file AssemblyInfo.vb (for Visual Basic projects) or AssemblyInfo.cs (for Visual C# projects).

The ExcelLocale1033Proxy class in the Microsoft.Office.Tools.Excel namespace provides two methods you can use to modify the locale ID behavior of individual Excel objects (that is, objects that are defined in the Microsoft.Office.Interop.Excel namespace). If you want to modify an Excel object so that it uses the locale ID of the current thread instead of locale ID 1033, call the Unwrap method. Call the Wrap method to modify the Excel object so that it uses locale ID 1033 instead of the locale ID of the current thread.

Conclusion

When using Visual Studio Tools for Office to develop Excel solutions for end users in multiple countries or regions, you need to consider how Excel interprets calls from your managed code. If you use the default settings of Visual Studio 2005 Tools for Office, remember that data with locale-sensitive formatting must be formatted to match locale ID 1033. If you use Visual Studio Tools for Office, Version 2003, consider the end user's regional settings, language version of Office, and MUI Pack installations. While using reflection for all COM calls that use string literals gives the greatest probability of achieving the expected results in Visual Studio Tools for Office, Version 2003 solutions, you also need to examine your code for use of external data and for required user input. Consider having your code peer-reviewed and test in multiple configurations before you deploy your solution.

Additional Resources

For more information, see the following resources:

Globalization

Visual Studio Tools for Office, Version 2003

Visual Studio 2005 Tools for the Microsoft Office System