The first thing I suggest you should do is read the "International
Issues" chapter from the following link:
Excel 2002 VBA Programmer's Reference
Written by John Green, Stephen Bullen, Rob Bovey and Robert Rosenberg
Note: see also the other pages about International Excel Issues on my site
Excel 97-2003 :
ATP Formulas will not be translated if you open your workbook in another language version. The best thing to do is to avoid them, see Dick's blog for formulas that do not use the ATP add-in.
These are all default Excel functions that always translate correct.
Excel 2007 and up :
In Excel 2007 and up the Analysis ToolPak add-in with the extra worksheet
functions does not exist anymore. The old ATP functions are now standard
worksheet functions in Excel 2007 and up.
This a great change because:
1) No problem if a user does not have the add-in installed
2) The formulas will be translated if you open the workbook in another language version of Excel 2007.
Many thanks to Kirill Lapin and Hector Miguel Orozco Diaz for helping me with this section of the page.
Read Carefully :
In the example below about the Text() function we use the Get.workspace function to get the Year, Month and Day symbol. If you understand this example you can use a similar trick for Row, Column, Date and Decimal separator symbols and many more so you can use them in other worksheet functions.
Tip : Download this workbook containing the information about every item in the Get.workspace array : Download workspace.zip
Overview: TEXT() uses number format strings in its 2nd
Issue: If you use English date, time and decimal number formats as well as 1000 separators, your formulas might not work in other locales and vice versa. Unlike number formats applied to cells, the number format strings used as function’s argument are not translated automatically from one locale to another.
If you use for example a formula like this in an English version of Excel
="Today is " & TEXT(TODAY(),"yyyy-mm-dd")
The result is : Today is 2009-09-16
But if I open the workbook with this formula for a example in a Dutch version of Excel
the result is : Today is yyyy-09-16
The reason is that we use a J (Year = Jaar) instead of the y in the Netherlands.
You can download a zip file here with two examples of how to avoid problems like this.
One trick from Kirill Lapin (KL) and one from Stephen Bullen.
Download the two example workbooks
Overview: INDIRECT() allows "R1C1" notation in its 1st
argument if the 2nd argument is equal to FALSE
Issue: R1C1 notation may vary depending on the locale (both letters and parenthesis),
so the English symbols won’t always work.
NL =INDIRECT("R1K1";0) - the English string won’t work
ES =INDIRECTO("F1C1",0) - the English string won’t work
RU =ДВССЫЛ("R1C1";0) - the English string will work
Solution 1: The function ADDRESS() allows you to get string-reference in local style.
So you could use it inside INDIRECT() (Trick from Hector Miguel Orozco Diaz)
EN =INDIRECT(ADDRESS(1,1,1,0),0) - for absolute reference
EN =INDIRECT(ADDRESS(1,1,3,0),0) - for relative reference
You could also extract local style symbols.
EN =LEFT(ADDRESS(1,1,1,0)) - letter for rows
EN =MID (ADDRESS(1,1,1,0),3,1) - letter for columns
EN =MID(ADDRESS(1,1,3,0),2,1) - left relativity symbol (parenthesis, bracket, etc.)
EN =MID(ADDRESS(1,1,3,0),4,1) - right relativity symbol (parenthesis, bracket, etc.)
Solution 2: Use defined names with the Excel4 macro-function GET.WORKSPACE()
Define two names in your workbook
You can use this then to get the value of A1 in any local
=INDIRECT(IR &1 & IC & 1,0)
Overview: INDIRECT() allows defined names as strings in the 1st argument if the name refers to a non-calculated range.
Issue: If you have named a range “Database”, its name will be automatically translated (!) into local language depending on the locale. Moreover, Excel will sometimes use space, which is an illegal character in names, in the translated name (!). Thus the formula =INDIRECT(“database”) will fail in a non-English locale as there wouldn’t be a range with such a name.
NL =INDIRECT("database") - No problem with a named range Database in a Dutch version
ES =INDIRECTO("base de datos") - Excel uses space character here.
Solution: Avoid using the name “Database” as a string argument of INDIRECT()
Overview: CELL() in its 1st argument and INFO() in its
only argument use predefined keywords.
These keywords can be expressed both in English and local language.
Issue: Local versions of the keywords won’t work in the English locale
Solution: Use the English version of the keywords in all locales
Overview: In their 2nd argument, SUMIF() and COUNTIF()
allow error values or strings representing error values.
Note : In the first example the COUNTIF function and the Error value will be automatically translated
when you open your workbook in another language version of Excel.
Issue: If you need to use <> (unequal) operator with an error value, you must use error strings for concatenation (the error value itself won't work) and the error string must be in the local language,
thus English strings won’t work in other locales and vice versa.
This example counts all cells that do not have the #VALUE! error.
Since there are no strings involved, the functions and error values will translate correctly in another language version of Excel.
Solution 2 : Use defined names with Excel4 macro-function GET.CELL() with intermediate cells as in the attached workbook. Trick from Hector Miguel Orozco Diaz.
Overview: Many people use lookup-type functions to find
the last number or the last string in an array.
When using approximate search, those functions return the last value in an array if the searched value
is impossibly high (for text strings – alphabetically high)
Issue: In the non-Latin-based languages a string like “zzzzz” might not work.
RU =ПОИСКПОЗ("яяяяя";A1:A10) - will work for both Russian and English strings
RU =ПОИСКПОЗ("zzzzz";A1:A10) - won't work for Russian strings
Solution: Use the following formula instead:
Read this webpage first :
Introduction To The DATEDIF Function (Chip Pearson)
Note: This function is not supported by Microsoft and in Excel 2007 SP2 there seems to be a new bug.
=DATEDIF(DATE(2009,6,27),DATE(2012,1,5),"md") gives a wrong result there (122 instead of 9), correct in Excel 2010-2013.
Overview: in its 3rd argument, DATEDIF() uses predefined strings that indicate the date unit to
be used for the result: “y”, “ym”, “m”, “md” and “d”
Issue: One might be tempted to replace the English date symbols by the local ones, which would lead to an error.
Solution: Use only "y", "ym", "m", "md" and "d" as DATEDIF() uses English symbols in all locales
Overview: You can coerce date-strings into numeric
values by using the functions DATEVALUE()
and VALUE(), binary negation or any basic math operation (*,/,+,-,^)
EN =DATEVALUE("01/03/2009") for 3-Jan-2009
Issue: Month literals, date separators as well as the order of year, month and day vary
depending on the localization.
NL =--"01-mei-09" – for May
ES =--"01-ago-09" – for Aug
RU =--"01-янв-09" – for Jan
Solution: Never use month literals. If you absolutely have to use date-strings, then use the ISO-format strings: "YYYY-MM-DD". Otherwise use the function DATE()
Check out this add-in if you want to tranlate formulas: TranslateIT from
KeepItCool (Jurgen Volkerink)