none
Excel fails to show formula result after adding formula from C# - Interop - Excel RRS feed

  • Question

  • Hi,

    I'm using Office 2003 running on XP SP 2, .NET FW 3.5, Visual Studio 2010

    I'm positive that I've found a bug in Excel or maybe in Interop.Excel.

    Via my Excel objects I'm writing a simple formula ta cell:

                    for (int row = numberOfDataRows; row >= 2; row--)
                    {
                        xlRow = (Excel.Range)xlSheet.Rows[row];
                        Excel.Range thisCell = (Excel.Range)xlRow.Cells[1, 2];
                        thisCell.Formula = "=MONTH(a" + row.ToString() + ")";

    When I look at the result in the Excel file there are #NAME? in all cells where I added the formula to BUT in the FORMULA bar I can see the correct formula.

    Now, if I click inside the Formula bar followed by an ENTER -> voila, the value of the cell is evaluated according to the formula. I don't know how-to solve this what I think is a bug so please, anyone here, help needed ASAP!

    Here's an old Thread from another Forum dealing with same problem (Yes IT IS AN MAJOR EXCEL BUG!!!):

    http://www.mrexcel.com/forum/showthread.php?t=19290

    No answer to why this is happening and how-to work around it. Is this hard even for MS dev people to solve ??? It seems that's the case I'm afraid. :(

    Best Regards,

    Per

    • Moved by Cookie Luo Monday, March 21, 2011 2:32 AM (From:Visual C# General)
    Thursday, March 17, 2011 10:52 AM

Answers

  • My problem is solved thanks to one of the very talented Moderators at MrExcel - Forum. He pointed me to this excellent thread (I copied the suggested solution and everything is working since then)

    http://www.dotnetmonster.com/Uwe/Forum.aspx/vs-net-office/72/FormulaLocal-FormulaR1C1Local-not-working

    As you can see, my problem is related to the environment configuration within my .NET Framework found under System.Globalization.CultureInfo

    For some reason Excel doesn't recognize some of its Function names even though my Excel is an international installation.

     

    BR,

    Per

     

    • Marked as answer by peer754 Monday, March 21, 2011 1:47 PM
    Monday, March 21, 2011 1:46 PM

All replies

  • Hi Per,

    This thread is little about C#. Move it to Visual Studio Tools forum for Office for better support.

    Thanks for your understanding.


    Cookie Luo[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Monday, March 21, 2011 2:31 AM
  • Hi Per,

    I have tried to reproduce your issue, but failed. I can get the calculation result. Well, can you show me all the code. In addition, what's the type of your project? Is it a VSTO workbook project or Add-In project? I tested with VSTO workbook project:

    using Excel = Microsoft.Office.Interop.Excel;

    namespace CSFormulaDebug
    {
        public partial class Sheet1
        {
            private void Sheet1_Startup(object sender, System.EventArgs e)
            {

                Excel.Range thisCell = this.Cells[1, 1] as Excel.Range;
                thisCell.Formula = "=MONTH(b" + 1.ToString() + ")";
            }

            private void Sheet1_Shutdown(object sender, System.EventArgs e)
            {
            }

            #region VSTO Designer generated code

            /// <summary>
            /// Required method for Designer support - do not modify
            /// the contents of this method with the code editor.
            /// </summary>
            private void InternalStartup()
            {
                this.Startup += new System.EventHandler(Sheet1_Startup);
                this.Shutdown += new System.EventHandler(Sheet1_Shutdown);
            }

            #endregion

        }
    }

    Try to use my code to see whether the problem happens.

    Regards,


    Be happy.
    Monday, March 21, 2011 9:09 AM
  • My problem is solved thanks to one of the very talented Moderators at MrExcel - Forum. He pointed me to this excellent thread (I copied the suggested solution and everything is working since then)

    http://www.dotnetmonster.com/Uwe/Forum.aspx/vs-net-office/72/FormulaLocal-FormulaR1C1Local-not-working

    As you can see, my problem is related to the environment configuration within my .NET Framework found under System.Globalization.CultureInfo

    For some reason Excel doesn't recognize some of its Function names even though my Excel is an international installation.

     

    BR,

    Per

     

    • Marked as answer by peer754 Monday, March 21, 2011 1:47 PM
    Monday, March 21, 2011 1:46 PM