Need C# excel automation help for formulas using PI() or bogenmass()

Question

0.00/5 (No votes)

See more:

C#

MS-Excel

localization

, +

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 26-Feb-21 4:04am

Stefan_Lang

Updated 27-Feb-21 5:41am

v3

Add a Solution

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.

[no name] 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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Accepted Answer · 2021-02-26T20:38:00

Solution 1

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!

Posted 26-Feb-21 20:38pm

Maciej Los

Updated 26-Feb-21 20:43pm

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 ;)