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

Article
02/04/2013

In Office projects, Excel displays locale-sensitive data strings correctly if you format the strings in your project as English (United States). You can change this behavior in an Office project that targets the .NET Framework 3.5 by setting Microsoft.Office.Tools.Excel.ExcelLocale1033Attribute to false. However, when you pass string literals to Excel after changing this attribute, 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.

Applies to: The information in this topic applies to document-level projects and application-level projects for Excel 2007 and Excel 2010. For more information, see Features Available by Office Application and Project Type.

If you do set Microsoft.Office.Tools.Excel.ExcelLocale1033Attribute to false in an Office project that targets the .NET Framework 3.5, 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

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));
}

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");

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

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));
}

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);

Share via

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

To set a property in an Excel range using reflection

To retrieve a property value from an Excel range using reflection

See Also

Tasks

Concepts

Other Resources

Additional resources