Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet functions language
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet functions language
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/...ranslateIT.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) "Luc Benninger" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet functions language
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 : 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet functions language
Bob Phillips wrote :
http://members.chello.nl/keepitcool/...ranslateIT.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.. -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet functions language
"keepITcool" wrote in message ft.com... 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet functions language
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/...rogRefCh22.htm -- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... "keepITcool" wrote in message ft.com... 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet functions language
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 : "keepITcool" wrote in message ft.com... 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet functions language
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet functions language
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 : 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel functions and language pack | Excel Discussion (Misc queries) | |||
language of built-in functions | Excel Discussion (Misc queries) | |||
Language of Functions | Excel Discussion (Misc queries) | |||
International Language Functions | Excel Worksheet Functions | |||
Changing the language of built-in functions? | New Users to Excel |