How to: Make String Literals Region-safe in Excel Using Reflection

Applies to
The information in this topic applies only to the specified Visual Studio Tools for Office projects and versions of Microsoft Office. Project type Document-level projects Application-level projects Microsoft Office version Excel 2003 Excel 2007 For more information, see Features Available by Application and Project Type.

When you use default Visual Studio Tools for Office settings, always format locale-sensitive data strings as English (United States) and Excel will display the correctly formatted data automatically. If you change the default by setting ExcelLocale1033Attribute to false and you pass string literals to Excel, the thread's current LCID is automatically used and data might be formatted incorrectly. For more information, see Formatting Data in Excel with Various Regional Settings.

If you do set ExcelLocale1033Attribute to false, you can use reflection to force specific calls to properties and methods of Excel objects to use English (United States) data format (locale ID 1033). When you do this, you can use English (United States) formatting for any strings that are passed to the property or method, and the code will work correctly in all locales.

The following examples demonstrate how to set or get a property of a NamedRange control in a document-level project for Excel, but the same concepts also apply to Excel objects in application-level projects.

To set a property in an Excel range using reflection

1. Create a helper method that uses the InvokeMember method to set a property value of an Excel object. Include parameters for the Excel object, the property name, and the parameters of the property. In the helper method, use an InvokeMember overload that has a CultureInfo parameter, and pass the locale ID for English (United States) to this parameter.

   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
   

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

2. Create a NamedRange control on cell A5 and name it NamedRange1.

   Dim NamedRange1 As Microsoft.Office.Tools.Excel.NamedRange = _
       Me.Controls.AddNamedRange(Me.Range("A5"), "NamedRange1")
   

   Microsoft.Office.Tools.Excel.NamedRange NamedRange1 =
       this.Controls.AddNamedRange(this.Range["A5", missing], "NamedRange1");
   

3. Instead of setting the Formula property of NamedRange1 directly, call the SetPropertyInternational helper method and pass in the underlying Range object for the NamedRange control, the property name and the value.

   Note

   You cannot pass in the NamedRange control directly. Instead, you must get the underlying Excel Range object by using the InnerObject property, and then pass this object to the method. For more information, see Programmatic Limitations of Host Items and Host Controls.

   SetPropertyInternational(NamedRange1.InnerObject, "Formula", "=SUM(12, 34)")
   

   SetPropertyInternational(NamedRange1.InnerObject, "Formula", "=SUM(12, 34)");
   

To retrieve a property value from an Excel range using reflection

1. Create a helper function that uses the InvokeMember method to get a property value of an Excel object. The object, the property name, and the parameters of the property are passed in to the helper method.

   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
   

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

2. Call the GetPropertyInternational function and pass in the NamedRange and the property name.

   Note

   The following code example assumes you have a NamedRange control named NamedRange1 on the worksheet.

   Dim formula As String = CType( _
       GetPropertyInternational(NamedRange1.InnerObject, "Formula"), String)
   
   MessageBox.Show(formula)
   

   string formula = (string)
       GetPropertyInternational(NamedRange1.InnerObject, "Formula");
   
   MessageBox.Show(formula);
   

See Also

Tasks

How to: Target the Office Multilingual User Interface

Concepts

Dynamically Loading and Using Types

Formatting Data in Excel with Various Regional Settings

Globalization and Localization of Office Solutions

Creating Office Solutions in Visual Studio

Other Resources

Deploying Office Solutions