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

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 :

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

Hi Anders

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

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




--
Regards Ron de Bruin
http://www.rondebruin.nl


"keepITcool" wrote in message ft.com...
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 :

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

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

Or did I misunderstand your point?

/Anders

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 :

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

Or did I misunderstand your point?

/Anders

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:

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

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

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

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

I can confirm its ;

:)

Many thanks!

/Anders

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