[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-03-17
6
Medium Priority
?
1,578 Views
Last Modified: 2013-12-17
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,
0
Comment
Question by:peer754
  • 3
  • 2
6 Comments
 

Author Comment

by:peer754
ID: 35157154
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
 
LVL 33

Expert Comment

by:Todd Gerbert
ID: 35158138
>> 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
 
LVL 4

Expert Comment

by:fromer
ID: 35166269
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 4

Expert Comment

by:fromer
ID: 35166300
Addition To Previous Note:

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

AD = NAME in turkish...
0
 

Accepted Solution

by:
peer754 earned 0 total points
ID: 35182967
@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
 

Author Closing Comment

by:peer754
ID: 35221216
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

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
High user turnover can cause old/redundant user data to consume valuable space. UserResourceCleanup was developed to address this by automatically deleting user folders when the user account is deleted.
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month20 days, 11 hours left to enroll

865 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question