Globalization and Localization of Office Solutions

Article
11/16/2012

Most aspects of globalizing and localizing Microsoft Office solutions are the same as you encounter when you create other kinds of solutions using Visual Studio. For general information, see Globalizing and Localizing Applications. Globalization and localization information can also be found on the MSDN Web page Globalization and Localization Issues for Solutions Created with Microsoft Visual Studio Tools for the Microsoft Office System.

Localizing Document Text

The document, template, or workbook in your project 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 Microsoft Office Word or Microsoft Office Excel. This process works even if you make no changes to the code, because any number of documents can be linked to 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 of the text, 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. For document templates that contain relatively little text, you might want to consider storing the text in resource files, and then loading the text at run time.

Text Direction

In Excel, you can set a property of the worksheet to render text right to left. Host controls, or any control that has a RightToLeft property, that are placed on the designer automatically match these settings at run time. Word does not have a document setting for bi-directional text (you just change your alignment of text), so the controls cannot be mapped to this setting. Instead, you must set the text alignment for each control. It is possible to write code to walk through all of the controls and force them to render text from right to left.

Changing Culture

Your customization code typically shares the main UI thread of Word or Excel, so any changes you make to the thread culture affects everything else that is running on that thread; the change is not restricted to your customization.

Windows Forms controls are initialized before Visual Studio Tools for Office add-ins are started by the host application. In these situations, the culture should be changed before setting the UI controls.

Installing the Language Packs

If you have non-English settings for Windows, you can install the Visual Studio Tools for Office Language Packs to see Visual Studio Tools for Office runtime messages in the same language as Windows. If any end users run your solutions with non-English settings for Windows, they must have the correct language pack to see runtime messages in the same language as Windows. The Visual Studio Tools for Office Language Packs are available from the Microsoft Download Center.

In addition, the redistributable .NET Framework Language Packs are necessary for ClickOnce messages. The .NET Framework Language Packs are available from the Microsoft Download Center.

Regional Settings and Excel COM Calls

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 CurrentCulture (locale) that matches the current thread locale. The current thread locale is inherited from the user's regional settings by default. However, when you make a call into the Excel object model, Visual Studio Tools for Office passes in a culture-invariant locale identifier (LCID) automatically. You must format all data that has locale-sensitive formatting, such as dates and currency, using the English (United States) data format (LCID 1033) before you pass it to Microsoft Office Excel or read the data from your Visual Studio Tools for Office project code. For more information, see Formatting Data in Excel with Various Regional Settings.

This behavior is controlled by the attribute ExcelLocale1033Attribute. You can change the behavior so that data is passed using the LCID of the current thread by setting ExcelLocale1033Attribute to false. You can then write your own code to handle the interaction between Excel and managed code. For more information, see How to: Make String Literals Region-safe in Excel Using Reflection.

Considerations for Storing Data

To ensure that your data is correctly interpreted and displayed, you should also consider that problems can occur when an application is storing data, including Excel worksheet formulas, in string literals (hard-coded) instead of in strongly-typed objects. You should use data that is formatted assuming a culture-invariant or English (United States) (the LCID value en-US) style.

Applications That Use String Literals

Possible values that might be hard-coded include date literals that are written in English (United States) format, and Excel worksheet formulas that contain 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.

Excel interprets any strings in accordance with the LCID that is passed with the string. This can be a problem if the format of the string does not correspond to the LCID that is passed. Visual Studio Tools for Office uses the LCID 1033 (en-US) when passing all data. Excel displays the data according to the regional settings and Excel user interface language. Visual Basic for Applications (VBA) works this way; strings are formatted as en-US and VBA almost always 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:

'VBA
Application.ActiveCell.Value2 = "05/12/04"

The same code, when used in a Visual Studio Tools for Office solution and passed to Excel through COM interop, produces the same results when the date is formatted in en-US style.

For example:

Me.Range("A1").Value2 = "05/12/04"

this.Range["A1", missing].Value2 = "05/12/04";

You should 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, 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, then converts it to a DateTime object for display in cell A7. Cell A7 must be formatted to display a date.

Private Sub ConvertDate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
    Handles ConvertDate.Click

    Try 
        Dim dbl As Double = Me.Range("A5").Value2
        Dim dt As System.DateTime = System.DateTime.FromOADate(dbl)
        Me.Range("A7").Value2 = dt

    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try 
End Sub

private void ConvertDate_Click(object sender, EventArgs e)
{
    try
    {
        double dbl = (double)(this.Range["A5", missing].Value2);
        System.DateTime dt = System.DateTime.FromOADate(dbl);
        this.Range["A7", missing].Value2 = dt;
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

Excel Worksheet Functions

Worksheet function names are translated internally for most language versions of Excel. However, due to potential language and COM interop issues it is strongly recommended that you use only English function names in your code.

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 any format besides en-US. 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.

Share via