locked
Localization problem with Names.Add Method (Excel) RRS feed

  • Question

  • Hello,

    I have a problem with Names.Add Method in Excel with German localization. The MSDN description (http://msdn.microsoft.com/en-us/library/office/ff835300(v=office.15).aspx) for this function above says that parameter “RefersTo” should be in English, but then I try to add valid Excel formula in en-US like this:

    “=IF(SUM(5,5,5,5,5)=25,TRUE,FALSE)”,

    I get exception that the formula isn’t correct. I found out that this method expects a localized formula in de-DE:

    “=WENN(SUMME(5;5;5;5;5)=25;WAHR;FALSCH)”.

    After playing around, I found that acceptation of list separator depends on the system wide region settings, so then I switched to en-US I was able to add this formula, but there was another problem with TRUE and FALSE. Then I try to add this formula to some active cell in Excel it works like charm. I tried to switch CurrentCulture and  CurrentUICulture to en-US for the CurrentThread, but this doesn’t help.

    Therefore, I wrote test case, see the code below:

    using System;
    using System.Windows.Forms;
    using System.Globalization;
    using System.Threading;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Microsoft.Office.Tools.Ribbon;
    using Worksheet = Microsoft.Office.Interop.Excel.Worksheet;
    using Range = Microsoft.Office.Interop.Excel.Range;
    
    namespace Names_Add_Test
    {
        public partial class MyRibbon
        {
            private void MyRibbon_Load(object sender, RibbonUIEventArgs e)
            {
                this.button1.Click += new Microsoft.Office.Tools.Ribbon.RibbonControlEventHandler(this.button1_Click);
            }
    
            private void button1_Click(object sender, RibbonControlEventArgs e)
            {
                CultureInfo cliBefore = Thread.CurrentThread.CurrentCulture;
                CultureInfo cliUiBefore = Thread.CurrentThread.CurrentUICulture;
    
                Worksheet ws = (Worksheet)Globals.ThisAddIn.Application.ActiveSheet;
                Range cell = (Range)Globals.ThisAddIn.Application.ActiveCell;
    
                string wsName = ws.Name;
                string name = "MyFormula";
                // localized variant for de-DE "=WENN(SUMME(5;5;5;5;5)=25;WAHR;FALSCH)"
                //string formula = "=WENN(SUMME(5;5;5;5;5)=25;WAHR;FALSCH)";
                string formula = "=IF(SUM(5,5,5,5,5)=25,TRUE,FALSE)";
                
                try
                {
                    Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
                    Thread.CurrentThread.CurrentUICulture = new System.Globalization.CultureInfo("en-US");
    
                    // work
                    cell.Value = formula;
    
                    // doesn't work
                    // http://msdn.microsoft.com/en-us/library/office/ff835300(v=office.15).aspx
                    // RefersTo parameter should be in English? 
                    ws.Names.Add(Name: name, RefersTo: formula);
                }
                catch (Exception ex)
                {
                    string message = ex.ToString();
                    string caption = "Exception in Worksheet.Names.Add() Method!";
                    MessageBoxButtons buttons = MessageBoxButtons.OK;
    
                    // Displays the error
                    MessageBox.Show(message, caption, buttons);
                }
                finally 
                {                                
                    Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
                    Thread.CurrentThread.CurrentUICulture = new System.Globalization.CultureInfo("en-US");
                }
            }
        }
    }
    

    Is this a bug or expected behavior? Can somebody explain to me, why this method behaves dissident from the MSDN description? How can I programmatically add named formulas written in English?

    Wednesday, September 24, 2014 1:50 PM

All replies

  • Very, very odd. I know there is a bug in the RefersToLocal property of the Name Object where the RefersToLocal property of Excel's Name object only accepts US syntax in the formula you pass to the RefersTo.I've never seen it happen the other way around however.

    What happens if you try setting the RefersToLocal  property instead, using the US syntax?


    Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.com

    Wednesday, September 24, 2014 3:47 PM
  • Hi Jan,

    I tried to use RefersToLocal parameter like this in my code above: 
    ws.Names.Add(Name: name, RefersTo: formula);
    and it works as expected and needs localized formula in German, and doesn’t accepts US syntax formulas. After what, I tried to use reflection
    static object InvokeMethodInternational(object target, string name, params object[] args){
        return target.GetType().InvokeMember(name,
          BindingFlags.InvokeMethod |
          BindingFlags.Public |
          BindingFlags.Instance,
          null, target, args, ConnectHelper.en_US);
    }
    from this example (http://msdn.microsoft.com/en-us/library/aa537159(v=office.11).aspx) but that doesn’t help. I think this is a bug. There is no way to directly add named formula in en-US syntax if the Excel itself has different localization, in my case lcid is 1031.
    Globals.ThisAddIn.Application.LanguageSettings.get_LanguageID(Microsoft.Office.
        Core.MsoAppLanguageID.msoLanguageIDUI) != 1033 // (lcid for en-US) 
    Finylly I found a workaround to do that, first add a name with dummy RefersTo: and after what access this name by using Item(name) and set it to formula in en-US syntax.
    // Both properties works like expected
    ws.Names.Add(Name: nameUS, RefersTo: "bla_bla");
    ws.Names.Item(nameUS).RefersTo = formula_enUS;
    
    ws.Names.Add(Name: nameDE, RefersTo: "bla_bla");
    ws.Names.Item(nameDE).RefersToLocal = formula_deDE;
    I got such behavior with German Excel 2010 64bit and Excel 2013 32bit / 64bit. On English Excel 2010 64bit ifthe system region settings was not en-US, has the same problem too. It complains about list separator.
    _____________________________________

    Regards, Zurab Khadikov, Jedox AG, www.jedox.com

    • Edited by zkhadikov Thursday, September 25, 2014 9:38 AM
    Thursday, September 25, 2014 9:38 AM
  • Hi,

    I found another problem with the RefersTo property of the Name Object. MSDN documentation says that the RefersTo should use A1-style notation. Let us change the en-US formula from my first code example, to use some cell reference in A1-style notation.

    string formula_enUS_A1 = "=IF(SUM(5,5,5,5,5)=25,$A$1,FALSE)";

    Now if you try to set RefersTo property to this formula, you would get an exception. Therefore, it doesn’t excepts A1-style. Is this a bug? However, if you change the cell reference to R1C1-style it works like a charm.

    string nameUS = "nameUS";
    string formula_enUS_R1C1 = "=IF(SUM(5,5,5,5,5)=25,R[1]C[1],FALSE)";
    ws.Names.Add(Name: nameUS, RefersTo: "bla_bla");
    ws.Names.Item(nameUS).RefersTo = formula_enUS_R1C1;

    Why RefersTo doesn’t except A1-style? For R1C1 there is extra property RefersToR1C1.

    _____________________________________

    Regards, Zurab Khadikov, Jedox AG, www.jedox.com



    • Edited by zkhadikov Thursday, September 25, 2014 1:56 PM
    Thursday, September 25, 2014 1:45 PM
  •  
    Hi Zkhadikov,
     
    It is very odd that from .NET, the bug appears to be the exact opposit as from VBA.
     
     
     

    Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.com
    Thursday, September 25, 2014 1:54 PM
  • Hi,

    >>I have a problem with Names.Add Method in Excel with German localization.<<

    Did you mean the location of Operater System is Germany like figure below?

    Which version of Excel are you using? As far as I test in English version Excel and set the location to Germany, the code works well for me.

    I suggest that you create a name using Name Manager manually like figure below to see wehther it colud work:

    Bset regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, September 26, 2014 7:33 AM
  • Hi Zkhadikov,
     
    Duh. This whole Name object is buggy as hell. So does it work reliably if you
    use the RefersToR1C1 property to set a R1C1 US syntax formula?
     
    That would be the better choice rather than using the RefersTo property and
    seeing your code break if MSFT decides to fix the A1/R1C1 bug there.
     
     

    Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.com
    Friday, September 26, 2014 9:26 AM
  • Hi Fei,

    No, I mean Formats Tab in Region Window dialog (first fig. you posted). Set Format to German (Germany) and apply, now the list separator is “;” system wide. After that try to compile and execute my code in Excel. I can send you complete test case with Excel Add-In, if you need.

    Exception message:

    The formula you typed contains an error.
    
    • For information about fixing common formula problems,
      click Help.
    • To get assistance in entering a function,
      click Function Wizard (Formulas tab, Function Library group).
    • If you are not trying to enter a formula,
      avoid using an equal sign (=) or minus sign (-),
      or precede it with a single quotation mark (').

    I do my tests with English MS Excel Version: 14.0.6023.1000 (64-Bit) and German MS Excel Version: 15.0.4649.1000 (32-Bit) on machine with English Windows 7 Pro. 64-Bit. 

    Your suggestion, to add formulas manually throw Name Manager, does not work for me, because I need to do this programmatically in our Excel Add-In. Moreover, this should work for any Excel localization.

    _____________________________________

    Regards, Zurab Khadikov, Jedox AG, www.jedox.com


    • Edited by zkhadikov Friday, September 26, 2014 1:25 PM
    Friday, September 26, 2014 1:24 PM
  • Hi Jan,

    You are right about your suggestion to use RefersToR1C1 property and it works like expected, bit only with my workaround.  I do like this:

    string nameUS = "nameUS";
    string formula_enUS_R1C1 = "=IF(SUM(5,5,5,5,5)=25,R[1]C[1],FALSE)";
    ws.Names.Add(Name: nameUS, RefersToR1C1: "bla_bla");
    ws.Names.Item(nameUS).RefersToR1C1 = formula_enUS_R1C1;

    in my code, and and Excel converts this formula automatically to needed localization.

    _____________________________________

    Regards, Zurab Khadikov, Jedox AG, www.jedox.com



    Friday, September 26, 2014 1:36 PM
  • Hi Zurab,

    Thanks for the detail information.

    After I changed the format as you motioned above, I was able to reproduce this issue.

    Since this issue is complex, I'm trying to involve some senior engineers into this issue and it will take some time. Your patience will be greatly appreciated.

    Sorry for any inconvenience and have a nice day!

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, September 30, 2014 6:09 AM
  • Hi Zurab,

    I was able to reproduce the issue. I modified the code and the issue no more reproduces. Below is the modified code: 

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using Microsoft.Office.Tools.Ribbon;
    using Microsoft.Office.Interop.Excel;
    using System.Threading;
    using System.Windows.Forms;
    using System.Globalization;
    
    namespace ExcelAddIn1
    {
        public partial class Ribbon1
        {
            private void Ribbon1_Load(object sender, RibbonUIEventArgs e)
            {
    
            }
         //Set the current culture info as English
            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));
            }
            private void button1_Click(object sender, RibbonControlEventArgs e)
            {
                Worksheet ws = (Worksheet)Globals.ThisAddIn.Application.ActiveSheet;
                Range cell = (Range)Globals.ThisAddIn.Application.ActiveCell;
                string wsName = ws.Name;
                string name = "MyFormula5";
                string formula = "=IF(SUM(5,5,5,5,5)=25,TRUE,FALSE)";
                try
                {
                  ws.Names.Add(Name: name,RefersTo : formula);
                   SetPropertyInternational(ws.Names.Item(name), "RefersTo", formula);
                }
                catch (Exception ex)
                {
                    string message = ex.ToString();
                    string caption = "Exception in Worksheet.Names.Add() Method!";
                    MessageBoxButtons buttons = MessageBoxButtons.OK;
                 MessageBox.Show(message, caption, buttons);
                }
                finally
                {
                    Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
                    Thread.CurrentThread.CurrentUICulture = new System.Globalization.CultureInfo("en-US");
                }
    
            }
        }
    }
    

    I would like to you to try the code and let me know if you have any concerns.

    Thanks,

    Anush.

    • Proposed as answer by AnushRudaa Wednesday, October 1, 2014 10:29 PM
    Wednesday, October 1, 2014 10:29 PM
  • Hi Anush,

    I tried your code but it does not solve the problem. The exceptions is raised at:

    ws.Names.Add(Name: name, RefersTo: formula);

    On my system and the execution does not comes to:

     SetPropertyInternational(ws.Names.Item(name), "RefersTo", formula);
    Like I wrote before, I already tried to use invoke international method:
    static object InvokeMethodInternational(object target, string name, params object[] args){
        return target.GetType().InvokeMember(name,
          BindingFlags.InvokeMethod |
          BindingFlags.Public |
          BindingFlags.Instance,
          null, target, args, System.Globalization.CultureInfo(1033));
    }
    add this dose not help too.

    ________________________________________________

    Best regards Zurab Khadikov, Jedox AG, www.jedox.com

    • Edited by zkhadikov Monday, October 6, 2014 12:55 PM
    Monday, October 6, 2014 12:54 PM
  • Hi Zurab,

    As the above code works fine at my end but doesn't work at your end ,we might need to do advanced troubleshooting on this issue at your end to find out how to avoid this issue. Because of its complexity, your question falls into the paid support category which requires a more in-depth level of support.  Please visit the below link to see the various paid support options that are available to better meet your needs. http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone.

    Thanks,

    Anush.

    Tuesday, October 7, 2014 4:03 PM