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

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!

Best Regards,
peer754Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
peer754Connect With a Mentor Author Commented:
@fromer: Yes you were on the right track, please see below:

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.

 
Best Regards!
0
 
peer754Author Commented:
Still no reply???

I tested this further and so far I've found out this much:

Ok if I instead of =MONTH(...) writes e.g. =Q + row.ToString(). All values from column Q shows ok.

Also Ok if I =MAX(...) i.e. it is not the call to an Excel-function that seems to be the issue BUT certain functions as MONTH and also SUM won't work.

Dunno if this info helps but I'm starting to be desperate so ... please!!!
0
 
Todd GerbertIT ConsultantCommented:
>> Still no reply???

You only posted this question a couple hours ago - patience, grasshopper. ;)

Anyway, I haven't got Excel 2003 to be able to attempt to duplicate your issue.  I can confirm no such problem seems to exist with Excel 2010, using this test code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;

namespace ConsoleApplication1
{
	class Program
	{
		static void Main(string[] args)
		{
			Excel.Application excel = new Excel.Application();
			Excel.Workbook workbook = excel.Workbooks.Add();
			Excel.Worksheet xlSheet = workbook.Sheets[1];

			for (int i = 1; i < 13; i++)
				((Excel.Range)xlSheet.Cells[i, 1]).Formula = "=DATE(2011," + i + ",1)";

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

			excel.Visible = true;
		}
	}
}

Open in new window


Things I might suggest is explicitly calling Excel.Calculate(), and also check your Excel options - make sure Workbook calculation is automatic.

It's probably also worth pointing out that the MONTH() function in Excel expects a serial number, "40544", not a date-string (40544 is how Excel internally represents the date 1/1/2011).
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
fromerCommented:
it can be a language issue...
I am from Turkey, and in the formula bar "=MONTH(40544)" is not valid where "=AY(40544)" is VALID..
0
 
fromerCommented:
Addition To Previous Note:

"=MONTH(40544)" generates "#AD?",

AD = NAME in turkish...
0
 
peer754Author Commented:
Since I was in a hurry (aren't we always) I was forced to get a solution fast, this one worked completely for me.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.