international excel issues

[Anders Lindén]

Creating an excel-document using automation seems to be a national dependent process
if you ask me.

If I am doing a program that puts the IF function in a cell (using Excel.Application with IDispatch),
it works to create the document using my program on a computer with an english installation of excel.

But not if the installation language is swedish.

Then I have to use OM instead of IF. People that claims that you always can use the english translation and it will
be automatically translated to swedish, did they actually test it before they claimed it?

Am I expected to have a list with all possible translations in my program and to check which version the user
runs?

Or can I set the Excel.Application object in a certain mode where it will understand english?

/Anders

 

[keepITcool]

Anders,

I suspect you are (attempting) to assign the formula's string
to the cell's .Value or default property.

when you are writing formulas in code you should/must use
one of following:

..Formula
..FormulaR1C1
..FormulaLocal
..FormulaR1C1Local

for your purpose:
best to avoid the "local" variations and you should have no problems.

however:
I'm not familiar with C, so i cant be definitive
but that's the way it's done in VBA.


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Anders Lindén wrote :

 

[Ron de Bruin]

Hi Anders

In this Beta add-in (DataRefiner) I insert English functions with

.Formula

http://www.rondebruin.nl/datarefiner.htm

 

[Anders Lindén]

hm, do you know how to express an IF(; function with .formula then?

Or did I misunderstand your point?

/Anders

 

[keepITcool]

Use r1c1 if you need to enter relative references

Range("A1:A10").FormulaR1C1 = "=IF(RC[1]>3,RC[2],RC[3])"




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Anders Lindén wrote :

 

[Guest]

Dear Anders,

I sympathize with everybody having a hard time with non-English versions of
Excel. I use a Hungarian version, and I regularly curse those who decided to
translate English function-names into National languages, because I have to
keep in mind not only the English function set but its Hungarian version as
well (it's not so easy, because most English word can have several Hungarian
synonims, and in some cases the translators chose really funny ones).

As an assistance we have in the Hungarian version a FUNCS.XLS file in the
C:\Program Files\Microsoft Office\Office11\1038 directory containing an
ENglish-Hungarian dictionary of function names. Have a look at it, perhaps
the Swedish version also has this accessory. You always have to use the
Swedish function names when writing them directly in a sheet, and their
English originals when using them in a VBA script's FormulaR1C1= statement,
at least I didn't find any other method. (If you record a macro containing
writing a Swedish function name in a cell, the resulted script will contain
the English translation of the function name.)
Regards,
Stefi



„Anders Lindén†ezt írta:

 

[Guest]

Automation is only partly language-dependent, because a VBA script is always
in English, so transferring a sript from e.g. Swedish environment into
Hungarian the latter version will translate the FormulaR1C1 ="IF(;"
statement into the Hungarian =HA(; cell content, even if you recorded it in
Swedish as =OM(;. Thus it will work, EXCEPT if you have put a Swedish
language formula directly in the worksheet. This is a hypothesis, I didn't
test it.
Regards,
Stefi

 

[keepITcool]

NOTE:

the FormulaR1C1 property uses english function names
and NONlocalized (std USENGLISH) separators.
=IF(; will give errors! and s/b like:

=IF(rc[1]>1.23,9,-9)

Note that recording "formula entry" yields USEnglish formulas.


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Stefi wrote :

 

[Guest]

Good point, the correct version is

"... will translate the
FormulaR1C1 ="IF(,,)" statement into the Hungarian =HA(; cell
content, even if you recorded it in Swedish as =OM(; ..."
supposed that the Swedish separator is also";" (what I don't know).

Stefi


„keepITcool†ezt írta:

NOTE:

the FormulaR1C1 property uses english function names
and NONlocalized (std USENGLISH) separators.
=IF(; will give errors! and s/b like:

=IF(rc[1]>1.23,9,-9)

Note that recording "formula entry" yields USEnglish formulas.


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Stefi wrote :

Automation is only partly language-dependent, because a VBA script is
always in English, so transferring a sript from e.g. Swedish
environment into Hungarian the latter version will translate the
FormulaR1C1 ="IF(;" statement into the Hungarian =HA(; cell
content, even if you recorded it in Swedish as =OM(;. Thus it will
work, EXCEPT if you have put a Swedish language formula directly in
the worksheet. This is a hypothesis, I didn't test it.
Regards,
Stefi

 

[Anders Lindén]

I can confirm its ;



Many thanks!

/Anders

 

[keepITcool]

the point:

there is NO translation involved.
just assign to the USenglish properties/methods

and excel formula bar in the UI will display the localized string.

The localized string depends on 2 (TWO!) INDEPENDENT things:

Excel Language version for the local names of functions.

Regional Settings for separators etc
(optionally with override in xl2002,
if use system separators (Tools/Options.International) is unchecked..

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Stefi wrote :

Good point, the correct version is

"... will translate the
FormulaR1C1 ="IF(,,)" statement into the Hungarian =HA(; cell
content, even if you recorded it in Swedish as =OM(; ..."
supposed that the Swedish separator is also";" (what I don't know).

Stefi


„keepITcool†ezt írta:

NOTE:

the FormulaR1C1 property uses english function names
and NONlocalized (std USENGLISH) separators.
=IF(; will give errors! and s/b like:

=IF(rc[1]>1.23,9,-9)

Note that recording "formula entry" yields USEnglish formulas.


--
keepITcool

www.XLsupport.com | keepITcool chello nl | amsterdam

Stefi wrote :

Automation is only partly language-dependent, because a VBA
script is always in English, so transferring a sript from e.g.
Swedish environment into Hungarian the latter version will
translate the FormulaR1C1 ="IF(;" statement into the Hungarian
=HA(; cell content, even if you recorded it in Swedish as
=OM(;. Thus it will work, EXCEPT if you have put a Swedish
language formula directly in the worksheet. This is a hypothesis,
I didn't test it. Regards,
Stefi