Click here to Skip to main content
14,828,424 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more: , +
In my excel worksheet I am using automation to insert formulas like this:
C#
worksheet.Cells[X_index][current_row_index].Formula = "=AC17+ABS(100*$Y$10* COS(((G17+J17+M17+Q17-180)*PI/180)))";

or
C#
worksheet.Cells[X_index][current_row_index].Formula = "=AC17+ABS(100*$Y$10* COS(Bogenmass(G17+J17+M17+Q17-180)))";

(The function Bogenmass() corresponds to Radians() - who t. f. thought of localizing excel function names? Seriously?)

The problem I have that for some reason the excel com object inserts a '@' character right in front of every reference to PI() or Bogenmass().

I would use "3.1415" instead of "PI()", but for some reason the excel com object keeps converting the decimal '.' separator into ',', although both my windows installation (english) and my localization setting (swiss german) use a decimal '.'!

The MS documentation doesn't help and I am out of ideas how to fix this. Does anyone have an idea what is causing the unwanted '@' insertion and how to prevent it? Alternately, does anyone know how to tell the excel formula interpreter to use the appropriate localization? I've read that the excel com object is always using the default localization (english), but apparently that isn't true!



P.S.:
1. As pointed out in the comments, I actually used "PI" in my formula rather than "PI()". The latter works just fine - no '@' gets inserted. Problem solved.

2. The suggested solution using the FormulaLocal property works just as well in C# as it does in VB. However, when you deliver such a program to a client, you shouldn't do that: it may not work if the client has a different language Excel installation than you do, or if he installs such a version in the future.

3. Something I've missed in my tests using alternately german and english formulas: the english version uses ',' to separate function arguments, whereas german uses ';' (only in Excel formulas). I didn't recognize this as part of the localization, and therefore haven't thought of replacing ';' with ',' when switching to english formulas...

Thanks again to all the contributors who pointed out these valuable bits of information!

What I have tried:

When setting a cell formula in C#, the excel COM formula setter converts:
- "PI()" to "@PI()"
- "Bogenmass()" or "BOGENMASS()" to "@Bogenmass()"
- "3.1415" to "3,1415"

None of these conversions produce a legitimate formula, and the last one even causes an excel exception at runtime!

using "PI()" in a formula gets interpreted as "@PI()" by the excel com object
using "Bogenmass() in a formula gets interpreted
Posted
Updated 27-Feb-21 5:41am
v3
Comments
[no name] 26-Feb-21 11:11am
   
No idea, but maybe using FormulaLocal instead of Formula may help... some background here: VBA – Formulas in Excel LocalFormula – Useful code[^]
Stefan_Lang 26-Feb-21 11:42am
   
Thank you, that's a very useful link.
Gerry Schmitz 26-Feb-21 11:22am
   
If Bogenmass() is equivalent to radians, then use RADIANS.
You show PI as PI/180 in your question, not PI()/180.
As for the "thousands separator", one can set that at the (format) cell level.
Stefan_Lang 26-Feb-21 11:39am
   
Hey, thanks - with PI() the formula works. I may check RADIANS() later.

*thumbsup*

1 solution

As @embar.k.adero mentioned, when you use local names of functions (in your nativve language), you have to use FormulaLocal[^], but when you want general solution (no matter of regional settings), you have to use Formula with English names and notations (decimal separator).

For example:
=WYSZUKAJ.PIONOWO(range1; range2; ...)
and
=VLOOKUP(range, range2, ...)
are equivalent, but the first one is using Polish name and notation.
In Poland we're using [;] to separate ranges, when universal is [,].

Good luck!
   
v2
Comments
Stefan_Lang 27-Feb-21 11:08am
   
Yes, you are right. I shouldn't use FormulaLocal unless I can be sure the users are all using the same language installation of Excel. I wouldn't bet on that, given that I myself prefer to install english versions of any program whenever I can... (Office wasn't installed by me)

Unfortunately, I have solved my problem using FormulaLocal for one formula that uses IF(...) which for some reason didn't work in english, but did work with FormulaLocal in german. Thanks to your posting I realized I forgot to replace the function argument separators (';') with ',' for the IF statement - now it works in english! :-)

Thank you very much for posting this - it helped me find the right solution. Have a 5!
Maciej Los 28-Feb-21 6:59am
   
Thank you, Stefan.
Cheers!
Maciej
Maciej Los 28-Feb-21 7:02am
   
To resolve this issue: one formula that uses IF(...) which for some reason didn't work in english, but did work with FormulaLocal in german. - use Debug.Print(CellWithWorkingGermanFormulaLocal.Formula). Then use that formula in your code ;)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900