CODEWITHSUNDEEP
excelvbainternationalization
MirageHF
MirageHF

Reputation: 63

Excel Formula Traduction (multilingual system)

I am currently programming KPIs on Excel, my VBA code creates references to external Excel sheets (extracted from SAP) using code lines like Wb.Ws.Cells(i,j).FormulaLocal = "=IF(InternalRef = 0; None; ExternalRef)". I also use the SUM() function the same way.

Now this works perfectly and does what I want, the problem is that I work in Switzerland, and the firm is bilingual (French and German). Being French, my Excel is in French, transforming the IF() Excel function to SI() and SUM()to SOMME().
Using SI() and SOMME() makes the code work on my computer, but fails on German ones (where IF()and SUM() have to be used), and my KPIs have to work under both languages.

How do I traduct those functions to get a "universal" code ?

Thank you for your help, Mirage

Edit : IF()and SUM() are not recognized by French Excel

Upvotes: 1

Views: 229

Answers (1)

Maciej Los
Maciej Los

Reputation: 8591

@Rory is right!

Rather than this:

Wb.Ws.Cells(i,j).FormulaLocal = "=IF(InternalRef = 0; None; ExternalRef)"

use this:

Wb.Ws.Cells(i,j).Formula = "=IF(InternalRef = 0, None, ExternalRef)"

Note:

As you can see, in English notation - a comma , is used instead of ;.

Tip:

To display English notation of formula in active cell, use:

Debug.Print ActiveCell.Fomula

Upvotes: 2

Related Questions