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]

Can't see how the .Net framework international settings/configuration could shine a light on this ...

Sorry to hear that you give up already, I thought this was the place to turn to with deep Excel problems like mine.

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

 

[RoryA]

It was not the difficulty of the problem that put me off.

 

[perco754]

Ok, I see that you find my frustration a bit too much to cope with, sorry for that. You get very frustrated in a middle of a huge software company internal release and the pressure is rising and I'm desperate in finding a solution ...

Since I found this similar thread here without answers my fate in this particular community sunk, sorry again.

So sorry if I offended anyone, that was honestly not my intention at all, once again, I came here for help with a problem I haven't been able to solve on my own sorry again.

It was not the difficulty of the problem that put me off.

 

[RoryA]

I understand your frustration but it's equally frustrating for us to have our solutions dismissed out of hand or be met with suggestions that we read the question properly.
As far as I can see your issue bears no relation to the thread you link to since that had different symptoms - the formula was appearing in the cell, not an error. (I suspect that issue was simply caused by a cell formatted as Text when the formula was entered)
For you to get a #NAME error, it means that Excel does not recognise the name of a function or one of it's arguments. Given that you are using the built in MONTH function (not a function from an add-in, for example, which would be understandable), the only thing I could think of was a translation problem with the function name, which would have to be a .Net issue rather than an Excel issue if the same formula works in Excel.

 

[perco754]

Ok, I see your point so please let's refresh our relationship shall we?

I can't see what you mean when you suggest that the MONTH function needs translation? I'm in Sweden BUT I'm using an English Excel installation and .NET environment. Also, the MONTH function IS working if I e.g. write it manually within Excel. I also tried other functions such as the SUM with same result. I then tried a simple reference to another cell (=Q2) and it worked so I begun to suspect that it was the FUNCTION-call that's the problem. However, after trying the MAX function it turned out that this one worked fine so ... back to square one.

A college suggested that I investigated the generated string within C# env so next thing I tried was going back to C# (Visual Studio 2010) and trying some of the other calls using a cell object. So far I've been using thisCell.Formula = but now I also tried thisCell.FormulaR1C1 = which resulted in additional ' surrounded the string. Finally, my college suggested that I tried thisCell.FormulaR1C1Local = and .... tadaaaaa, it worked Why? I don't know, you tell me.

Thought that I post this since it turned out being the solution to my problem in case anyone else experience the same thing in the future and that is also why we're here in the first place right?

Best Regards,
Peer

I understand your frustration but it's equally frustrating for us to have our solutions dismissed out of hand or be met with suggestions that we read the question properly.
As far as I can see your issue bears no relation to the thread you link to since that had different symptoms - the formula was appearing in the cell, not an error. (I suspect that issue was simply caused by a cell formatted as Text when the formula was entered)
For you to get a #NAME error, it means that Excel does not recognise the name of a function or one of it's arguments. Given that you are using the built in MONTH function (not a function from an add-in, for example, which would be understandable), the only thing I could think of was a translation problem with the function name, which would have to be a .Net issue rather than an Excel issue if the same formula works in Excel.

 

[RoryA]

Once again, that sounds like an internationalisation issue since the only difference between Formula and FormulaLocal (or the R1C1 equivalents) is language based. I still think your .Net thread is running under a different locale.
Thanks for posting back with the solution though!

 

[perco754]

I'll be happy to post the language pack version of my .net FW (I'm using version number = 3.5.30729.01) if someone could tell me how.

Once again, that sounds like an internationalisation issue since the only difference between Formula and FormulaLocal (or the R1C1 equivalents) is language based. I still think your .Net thread is running under a different locale.
Thanks for posting back with the solution though!

 

[RoryA]

You may want to have a look at the second post in this thread.

 

[perco754]

Wow, many thanks! Now, that's the thread I was looking for

Hmm it says some posts down to avoid using the FormulaLocal construct since it restricts the application to only one language.

I did an evaluation using the VS debugger (Watch window) of System.Globalization.CultureInfo.CurrentCulture and it was set to, hmpphh yes you were right, Swedish

These other properties were set to:
.CurrentUICulture = {en-US}
.InstalledUICulture = {en-US}

So let me you sometime ok?
Btw, I tried to attach a picture to this thread, is that possible?

Have a nice weekend where ever your geographic location is

You may want to have a look at the second post in this thread.

 

[RoryA]

You can't attach pictures, you can only link to them (if they are accessible online).
Glad it's figured out anyway!