This documentation is archived and is not being maintained.

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

When you use default Microsoft Visual Studio 2005 Tools for the Microsoft Office System 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 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 name of any strings and it will work correctly in all locales.

To set a property in an Excel range using reflection

  1. Create a helper function called SetPropertyInternational to pass the LCID for English (United States) whenever you set property values in Excel ranges by passing a string literal.

    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
    
    
  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")
    
    
  3. Instead of setting the Formula property of NamedRange1 directly, call the SetPropertyInternational function and pass in the NamedRange, the property name and the value.

    NoteNote

    You cannot pass in the NamedRange control directly, you must pass the underlying Range object by calling the InnerObject method. For more information, see Programmatic Limitations of Host Items and Host Controls.

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

To retrieve a property value from an Excel range using reflection

  1. Create a function called GetPropertyInternational to set the LCID whenever you retrieve property values from Excel ranges.

    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
    
    
  2. Call the GetPropertyInternational function and pass in the NamedRange and the property name.

    NoteNote

    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)
    
    

See Also

Show: