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

[perco754]

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

 

[perco754]

And here I found a similar thread:
http://www.mrexcel.com/forum/showthread.php?t=19290
No solution presented though

Is this hard to explain even for MS dev people??? It seems so ...

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

 

[Norie]

That code works fine for me.

What is it actually meant to do?

If you want to put a formula in a range why not use something like this?

xlrng = xlws.get_Range("A2").get_Resize(numberofdatarows);
 
xlrng.get_Offset(0, 3).Formula = "=MONTH(A2)";

Or is that not what you are trying to do?

 

[perco754]

Thanks but it is NOT the range objects or how I set them that is the problem.
What I am trying to do (and also is what I actually do) is putting the string
"=MONTH(A2)" into cell B2, "=MONTH(A3)" into cell B3 etc. via

Excel.Range thisCell = (Excel.Range)xlRow.Cells[1, 2];
thisCell.Formula = "=MONTH(a" + row.ToString() + ")";

where row is an integer inside the for-loop
The result IS written as expected to the Excel file BUT it (Excel) won't show the evaluted result from the formula but the error #NAME?.
Now if I point to one of the cells containing the formula I can see the correct formula in the formula bar. Further, if I now hit ENTER in the formula bar the formula is evaluated as a formula by Excel. It seems like Excel thinks it is a TEXT before going thru thh manual steps described.
Got it now???
Cheers!

That code works fine for me.

What is it actually meant to do?

If you want to put a formula in a range why not use something like this?

xlrng = xlws.get_Range("A2").get_Resize(numberofdatarows);
 
xlrng.get_Offset(0, 3).Formula = "=MONTH(A2)";

Or is that not what you are trying to do?

 

[RoryA]

My best guess would be an internationalisation issue since MONTH is a native function in Excel and so should not give you a #NAME error.

 

[Norie]

I tried recreating your error but I couldn't - I even intentionally, honest, left a space in the formula.

Still worked with or without the space.

Of course I couldn't completely recreate your setup, just used code to create a new workbook etc

Perhaps, as rorya suggests, it is an international thing.

 

[perco754]

If you suggest that it should be something to with international (???) settings within my Excel installation you have not been reading my post at all!!!

If that should really be the case then how come the formula is evaluating fine after hitting ENTER in the Formula Bar? It is still the same formula showing (MONTH(...)) but now I can see the VALUE in the cell.

This old THREAD deals with the same problem and still after all this time, no one has been able to come up with a valid explanation AND solution.
http://www.mrexcel.com/forum/showthread.php?t=19290

I tried recreating your error but I couldn't - I even intentionally, honest, left a space in the formula.

Still worked with or without the space.

Of course I couldn't completely recreate your setup, just used code to create a new workbook etc

Perhaps, as rorya suggests, it is an international thing.

 

[RoryA]

I was talking about an internationalisation issue with your .Net program, but I think I'll leave it there.

 

[Norie]

perco745

That thread doesn't seem to have anything to do with putting a formula into Excel using C#.

 

[perco754]

No, but since I think this IS an Excel problem, that thread deals with the exact problem.
So, if you can give an answer to that thread only, I'll be more than happy.

perco745

That thread doesn't seem to have anything to do with putting a formula into Excel using C#.