Creating Office Solutions for Use in Multiple Countries/Regions

Harry Miller
Kathleen McGrath
Microsoft Corporation

October 2003

Applies to:
    Microsoft® Visual Studio® Tools for the Microsoft Office System
    Microsoft Office Excel 2003
    Microsoft Visual Studio .NET 2003

Note    For updated information about creating solutions for deployment to end users in more than one country or region using Microsoft Visual Studio 2005 Tools for the Microsoft Office System, see Creating Excel Solutions for Use in Multiple Countries/Regions Using Visual Studio Tools for Office.

Summary: Presents issues to consider when creating Microsoft Office solutions to deploy to end users who use different Windows regional settings. (9 printed pages)

Contents

Introduction
Localization and Globalization of Office Solutions
Regional Settings and Excel COM Calls: Avoiding Run-Time Errors
Considerations for Excel
Conclusion
Related Articles

Introduction

If you plan to develop a Microsoft® Office 2003 solution using Microsoft Visual Studio® .NET that will—or might—be used in multiple regions or on systems with differing Microsoft Windows® regional settings, you must keep a number of things in mind during development. In addition to globalization and localization considerations, you should be aware of other factors that might affect your solution. These factors include which language version of Office is installed on the end user's computer, whether the Microsoft Office 2003 Multilingual User Interface Pack (MUI Pack) is installed, and which locale is set in the Windows regional settings. In particular, if you are developing Microsoft Office Excel solutions, you should provide special consideration for those systems where the language version of Office installed on the user's computer might not match the regional settings.

Localization and Globalization of Office Solutions

Most aspects of globalizing and localizing Office 2003 solutions are the same as you encounter when you create other kinds of solutions using Visual Studio .NET. For more information, see Globalizing and Localizing Applications. However, because your solution is a Microsoft Office Word or Microsoft Office Excel project, your document, template, or workbook probably includes static text, which must be localized separately from the assembly and other managed resources. A straightforward way to do this is to make a copy of the document and translate the text using Word or Excel. It works even if you make no changes to the code because any number of documents can target the same assembly. You must still make sure that any part of your code that interacts with the document text continues to match the language, and that bookmarks, named ranges, and other display fields accommodate any reformatting of the Office document that was necessary to adjust for different grammar and text length.

Regional Settings and Excel COM Calls: Avoiding Run-Time Errors

It is important to keep in mind that some data, such as dates and numbers, can only be displayed correctly to an end user when the user's culture is known. To properly interpret, convert, and present data, you must consider the variety of language versions of Microsoft Office and Microsoft Windows, as well as the end user's ability to set different Windows regional settings. For example, when Excel receives a call through Automation, Excel uses the locale identifier (LCID) supplied by the Automation client to execute the call. Therefore, when you are developing solutions for multiple countries or regions, you should be mindful that the values for string literals, such as "04/08/2003" or "1,000", might be interpreted based upon the locale. If your code is not written to account for such culture-sensitive strings, you might encounter run-time errors or data conversion errors, or display incorrect data.

Microsoft Office 2003 Multilingual User Interface Pack

You can make the user interface and language-specific features of Office match the regional settings of Windows by installing the MUI Pack and setting the language to match the regional settings; however, strings passed to the application that are formatted according to a different culture might still be interpreted incorrectly.

Note   The MUI Pack is designed to enhance the English version of Office 2003; it is not supported with localized versions of Office. (The MUI Pack Setup program is available in English only; it is not localized into other languages.) 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 Excel

Whenever 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 is inherited from the user's regional settings by default. (All late-bound calls and calls to members with LCIDConversionAttribute set implicitly pass the default System.Globalization.CultureInfo.CurrentCulture property value.) The success of the call depends on the availability of the files that Excel requires to support the locale supplied by the caller.

Supporting files for a locale are available to Excel if:

• The locale matches the language version of the Office installation, or
• The locale is English United States (en-US), or
• The locale matches an installed MUI Pack.

You should provide special consideration for those systems where the language version of Office installed on the user's computer might not match the regional settings; for example, an Italian version of Office might be used on a computer with German (Austria) regional settings. In this situation, the behavior of your Office solution might depend on whether or not a matching MUI Pack is installed. The following sections provide recommendations for writing code for both scenarios:

• No matching MUI Pack is installed on the end user's computer.
• Matching MUI Pack is installed on the end user's computer.

Scenario 1: No Matching MUI Pack Is Installed on the End User's Computer

If your code calls Microsoft Office Excel using a CultureInfo for which no matching MUI Packs are installed on an end user's computer, the code does not work as expected; run-time errors are generated, but Excel does not show them to the user. You must explicitly write code to handle the errors, using an exception-handling technique appropriate for your code language.

If the locale selected in an end user's regional settings does not match the installed language of Office, Excel attempts to locate the correct MUI Pack. If it does not find it, the following errors might be raised when the code calls certain methods and properties:

Exception from HRESULT: 0x800A03EC.

-or-

Old format or invalid type library.

To correct this problem for users that do not have the MUI Pack available to them, you can change the CultureInfo for the thread in your managed code extension to English, United States (en-US), and then change it back to the original CultureInfo when the COM call is completed.

Changing the CultureInfo of a Thread

The following code illustrates how you can change the CultureInfo of a thread so that your COM calls succeed regardless of your regional settings.

The basic steps are:

1. Create a variable to store the current CultureInfo.
2. Change the CultureInfo to "en-US" for the COM call.
3. Make the call to Excel (ensuring that all strings containing data such as dates, numbers, and formulas are in en-US format).
4. Change the CultureInfo back to the original after the COM call.

If you do not change the CultureInfo back to the original after the call to COM, the rest of your code executes with the en-US CultureInfo. If users do not expect en-US regional settings, they might see dates and numbers displayed in an unexpected format. In addition, culture-dependent procedures such as sorting might be affected.

This example demonstrates how to change the CultureInfo before and after calls to Excel. It assumes the existence of two named ranges, expenseRange and incomeRange, which contain values in the active worksheet.

' Visual Basic
' Store the current CultureInfo for the thread and then set
' the thread's CultureInfo to en-US.
Dim thisThread As System.Threading.Thread = _
    System.Threading.Thread.CurrentThread
Dim originalCulture As System.Globalization.CultureInfo = _
    thisThread.CurrentCulture
Dim tempExpense As Object
Dim tempIncome As Object

' Use an exception block to switch back in case of a run-time error.
Try
    thisThread.CurrentCulture = New System.Globalization.CultureInfo( _
        "en-US")

    ' Make the call to Excel.
    tempExpense = expenseRange.Value2
    tempIncome = incomeRange.Value2

Finally
    ' Restore the culture information for the thread after the
    ' Excel calls have completed.
    thisThread.CurrentCulture = originalCulture

End Try
If (tempExpense < tempIncome) Then
    MsgBox(tempExpense & " is smaller")
End If

// C#
// Store the current CultureInfo for the thread and then set
// the thread's CultureInfo to en-US.
System.Threading.Thread thisThread = 
    System.Threading.Thread.CurrentThread;
System.Globalization.CultureInfo originalCulture = 
    thisThread.CurrentCulture;
object tempExpense;
object tempIncome;

// Use an exception block to switch back in case of a run-time error.
try
{
    thisThread.CurrentCulture = new System.Globalization.CultureInfo(
        "en-US");

    // Make the call to Excel.
    tempExpense = expenseRange.Value2;
    tempIncome = incomeRange.Value2;
}
finally
{
    // Restore the culture information for the thread after the
    // Excel calls have completed.
    thisThread.CurrentCulture = originalCulture;
}

if((double)tempExpense < (double)tempIncome)
{
    MessageBox.Show(tempExpense + " is smaller");
}

Scenario 2: Matching MUI Pack Is Installed on the End User's Computer

If an MUI pack that matches the locale for the regional settings is installed, the files required for Excel to support a call with the supplied locale are available; however, to ensure that your data is correctly interpreted and displayed, you should also consider the following areas:

• If there are any values or Excel worksheet formulas written as string literals in the code (hard-coded).
• If an application reads from or writes to text files that were designed for earlier versions of Visual Basic or Visual Basic for Applications (VBA) and assume en-US formatting.
• If an application accepts input from the user that it converts to en-US (for example, applications built using earlier versions of Visual Basic or VBA), when the application expects input in native format.

Based on how you implement your application, these errors might not be visible to the end user. If you cannot rewrite the code to remove such problem areas, you can switch the CultureInfo before and after all COM calls, the same way you would when no matching MUI Pack is installed.

Applications That Use String Literals

Possible values that might be hard-coded include date literals that are written in the English (United States) format, and Excel worksheet formulas containing localized function names. Another possibility might be a hard-coded string that contains a number such as "1,000"; in some cultures, this is interpreted 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.

You might not know in advance if the correct MUI pack has been installed on the end user's computer. Therefore, if you switch the CultureInfo before and after all COM calls, you must examine all hard-coded strings and any text read from a file to ensure the date or number format matches the expected format of the current culture—in this case, English (United States). For example, Table 1 shows what happens when dates are passed in as hard-coded strings and the thread is set to English (United States). When the code is run with regional settings set to German (Germany), only the first example is formatted according to the user's expectations. The second example is not formatted according to the user's expectations, because "dd/mm/yyyy" is not recognized as a valid date format for English (United States).

Table 1. Example of results when dates are hard-coded

The names of some Excel worksheet functions are localized in some cultures, and Excel returns an error for any English function names written in the code that it does not understand. In cases where intermediate formulas are hidden from users, the users might see a zero end result, or some other default value, erroneously.

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 affected if such files are exported using en-US format. For example, this might be the case if an application was originally designed for use with earlier versions of Visual Basic or VBA. Database access might not be affected because all values should be in binary format, unless the database stores dates as strings or performs operations that do not use binary format. 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. You should consider using parameterized queries instead.

Applications That Accept User Input

If existing applications receive values in non-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 arises if users have been taught to use United States formats when interacting with applications, since the system now expects their native format, not United States.

If your application might be used in a circumstance where the MUI Pack does not enable the correct cultural behavior, you should write your code to change the CultureInfo of the thread in the same way as if no MUI Pack was installed on the user's computer.

Requiring a Specific Locale for Your Office Solution

In all cases, writing your code as if end users will have different regional settings with no MUI Pack gives the best chance of achieving the expected results. There might be times when you are sure that your solution will only be used on computers that have regional settings set to a single shared locale, and therefore do not want to write the extra code to switch cultures. However, you should add code to the _Open event in case someone does have a different regional setting, so that he or she will receive a warning.

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

' Visual Basic
If Not (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.", ThisApplication.Name, _
     MsgBoxStyle.OKOnly, MsgBoxStyle.Exclamation)
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.", ThisApplication.Name,
    System.Windows.Forms.MessageBoxButtons.OK,
    System.Windows.Forms.MessageBoxIcon.Information);
}

Conclusion

When developing Office solutions using Visual Studio .NET that will be used in multiple countries/regions, you need to consider how COM interop will interpret calls from the common language runtime. Be sure to consider the end user's regional settings, language version of Office, and MUI Pack installations. While switching the CultureInfo before and after all COM calls gives the best chance of achieving the expected results, you will need to additionally examine the code for formatting of hard-coded strings, external data, and required user input. You should consider having the code peer-reviewed and tested in multiple configurations before you deploy the solution.

Related Articles

Updated 2005 Information: Creating Excel Solutions for Use in Multiple Countries/Regions Using Visual Studio Tools for Office

Effects of Customizing Language Settings on Office Applications

Office 2003 MUI Pack Languages and Corresponding Locale Identifiers

Planning World-Ready Applications

Introduction to International Applications in Visual Basic and Visual C#