worksheet functions language

[Luc Benninger]

I have a macro that inserts worksheets functions into the active sheet.
Now I ran into troubles as this macro should run on machines with
different language versions of excel installed. The SUM function in
english must be entered as SUMME in a german office version. And there
may be other languages that must be supported too.

How can I write a language-independent macro?
Thanks for any hints!
Luc

 

[Bob Phillips]

Luc,

You have two problems, well three actually, but the third is an ancillary of
the second.

Firstly, you have the function names that you need to convert. KeepItCool
has created a workbook that you can freely download that you can get all the
values from, it is at
http://members.chello.nl/keepitcool/files/TranslateIT.zip. When I open the
workbook I get a runtime error, but just ignore that and extract the data
for your macro.

Then you have the separator issue. Some countries use semi-colon for a
separator, some use comma. Again , the aforementioned workbook has details
of which countries use which.

The third issue is one that the late Frank Kabel came across when we were
developing some multi-language tools. Even though he had a German version of
Excel, for some reason he had English language settings, so he was using
comma not semi-colon. The way we handled this was to set the array for the
separators to standard US/UK style. Then by checking the language settings
we reset if not English. This is the code that you can adapt

CFP_Formula(1) = Application.International(xlListSeparator)
CFP_Formula(12) = Application.International(xlLeftBrace)
CFP_Formula(13) = Application.International(xlRightBrace)
CFP_Formula(14) = Application.International(xlColumnSeparator)

By the way, we use an INI file to store all the language specific values,
and read that in at the start. I could share that code with you if you think
it might be helpful. This is an example from the INI files that will give
you a flavour using the functions

[CFPlus_Languages]
001=English
002=German
003=French

[CFPlus_Languages_Local]
001=English
002=Deutsch
003=Français

[CFPlus_English]
f001=,
f002=COUNTIF(
f003=MOD(ROW()-ROW(
f004=AND(
f005=OR(
f006=MOD(COLUMN()-COLUMN(
f007=ISERROR(
f008=LARGE(
f009=SMALL(
f010=TODAY()
f011=WEEKDAY(
f012={
f013=}
f014=,
f015=OR(
f016=DAY(
f017=MONTH(
f018=AND(
f019=DATE(
f020=YEAR(
f021=WORKDAY(
f022=INT(
f023=MOD(
f024=MAX(
f025=ISNUMBER(
f026=ISTEXT(
f027=ISLOGICAL(
f028=ISBLANK(
f029=OFFSET(
f030=ROW(
f031=COLUMN(
f032=SUM(

[CFPlus_German]
f001=;
f002=ZÄHLENWENN(
f003=REST(ZEILE()-ZEILE(
f004=UND(
f005=ODER(
f006=REST(SPALTE()-SPALTE(
f007=ISTFEHLER(
f008=KGRÖSSTE(
f009=KKLEINSTE(
f010=HEUTE()
f011=WOCHENTAG(
f012={
f013=}
f014=.
f015=ODER(
f016=TAG(
f017=MONAT(
f018=UND(
f019=DATUM(
f020=JAHR(
f021=ARBEITSTAG(
f022=GANZZAHL(
f023=REST(
f024=MAX(
f025=ISTZAHL(
f026=ISTTEXT(
f027=ISTLOG(
f028=ISTLEER(
f029=BEREICH.VERSCHIEBEN(
f030=ZEILE(
f031=SPALTE(
f032=SUMME(

[CFPlus_French]
f001=,
f002=NB.SI(
f003=MOD(LIGNE()-LIGNE(
f004=ET(
f005=OU(
f006=MOD(COLONNE()-COLONNE(
f007=ESTERREUR(
f008=GRANDE.VALEUR(
f009=PETITE.VALEUR(
f010=AUJOURDHUI()
f011=JOURSEM(
f012={
f013=}
f014=,
f015=OU(
f016=JOUR(
f017=MOIS(
f018=ET(
f019=DATE(
f020=ANNEE(
f021=SERIE.JOUR.OUVRE(
f022=ENT(
f023=MOD(
f024=MAX(
f025=ESTNUM(
f026=ESTTEXTE(
f027=ESTLOGIQUE(
f028=ESTVIDE(
f029=DECALER(
f030=LIGNE(
f031=COLONNE(
f032=SOMME(

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[keepITcool]

Luc,

when writing code that inserts functions you should use the
..Formula ( or .FormulaR1c1) property of ranges and enter the functions
in english with USenglish separators, then everything should work fine.

Range("a1").Formula = "=SUM($b$3:$b$13,$d$3:$d$13)"

when you'r programming and dont know the usfunction name,
just create the formula in your local Excel worksheet
Then in the VBE pull the translation to the immediate ("DIREKT") window
like: ?activecell.formula


For international "transportable" workbooks avoid functions from the
addin Analysis ToolPak or "Analyse Funktionen"

And for some functions e.g. =ZELLE() or CELL() that have string
arguments you must enter the "english" argument rather than the
localized.

=CELL("filename",a1) will work regardless of which language versions
in german you'll see =ZELLE("filename";A1).

when you enter english argument strings in your german worksheet,
you'll see they work fine.







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


Luc Benninger wrote :

 

[keepITcool]

Bob Phillips wrote :

http://members.chello.nl/keepitcool/files/TranslateIT.zip. When I
open the workbook I get a runtime error, but just ignore that and
extract the data for your macro.

Bob,

It seems I have a problem too. Well in fact I now have two.

Problem1.
on which Excel version do you get the runtime error when starting
translateIT (and WHERE) ?

Problem2:
I think I'd merited a email indicating the above iso you telling
someone to simply rip some (researched) data from my addin.



Also due to writing translateIT I consider myself well versed on
international use of Excel. But I general let VBA read and write in
USenglish and let Excel handle the rest.

Your advice seems to stem from the need to "proof" local formula strings
in user interaction, which isn't what the OP was asking..

 

[Bob Phillips]

Problem2:
I think I'd merited a email indicating the above iso you telling
someone to simply rip some (researched) data from my addin.

A bit rich to claim that it is ripping data that isn't yours in the first
place. You may have researched it but it is freely available. To claim some
intellectual rights to it is quite astonishing. Are you saying that when I
looked up the German for VLOKUP in the thing I should have asked your
permission to use that translation?

Also due to writing translateIT I consider myself well versed on
international use of Excel. But I general let VBA read and write in
USenglish and let Excel handle the rest.

Yes that was something I didn't know, but which I now do. Before I apply
that knowledge I will be sure to ask your permission.

 

[Tom Ogilvy]

Stephen Bullen has his international excel developer issues chapter posted
at his site (I know you [Bob] have his book but the OP might find it
useful).

http://www.oaltd.co.uk/ExcelProgRef/Ch22/ProgRefCh22.htm

 

[keepITcool]

Bob,

First:
I researched and gathered it. that's all. and I dont and didnt claim
anything else.

Apparently my use of "ripping" versus your "extracting" was
unfortunate. But that would be all in the translation, wouldn't it?

My annoyance was that you never informed me to point out that
apparently there's a compile error in a particular excel version
that I'm not aware of.
If the OP can normally use my addin, he has the option to
create a wrokbook with all the translations in the addin.
and he doesnt need to "rip" them, he can copy and paste them..

so:
WHERE, HOW and WHEN do you get the compile error?



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


Bob Phillips wrote :

 

[Bob Phillips]

Apparently my use of "ripping" versus your "extracting" was
unfortunate. But that would be all in the translation, wouldn't it?

No, ripping is an idiomatic use of the language that I would not expect to
see in a translation of extracted.

If the OP can normally use my addin, he has the option to
create a wrokbook with all the translations in the addin.
and he doesnt need to "rip" them, he can copy and paste them..

So what's the beef? Where is the difference between copying and pasting from
a workbook that he has created from you add-in, or by extracting them
(whether that be by copy and paste, or simply copying the ones that he
wants) from the original workbook? If you don't mind that he uses the
translations (and you have no right to IMO), then I don't see what you are
complaining about.

Rest assured that I will not cause you further offence by ever mentioning
your add-in.

 

[keepITcool]

Bob,

Let's get back to the beginning:

"...When I open the workbook I get a runtime error, but just ignore
that and extract the data for your macro."

The casualness of "just ignore that" irked me.

I wish I had reacted like: Hi Bob, thanks for mentioning my addin. I'm
not aware of a compile error, why didn't you send me an email of how,
where and when this occurs.."

Alas, I didn't and it looks like you're easily irked too.
<g>

I can see the humour of a misunderstanding getting out of hand.
BTW what about that email?



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


Bob Phillips wrote :