On this page you will find examples of how to avoid problems when your workbooks are used in different
language versions of Excel. If you have a suggestion for this page, please let me know.
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
http://www.oaltd.co.uk/ExcelProgRef/Ch22/default.htm

As you can see, Stephen Bullen has done a great job and I do not want to duplicate this page, so I
will only add tips to this page that are not in this chapter or do it in a different way.


Index

Excel version and Office language settings

Application.International (VBA)

Week-Numbering Systems and Date/Time Representations in Excel

Analysis ToolPak add-in worksheet functions

Using Strings as worksheet function arguments

Command bars and controls in Excel 97-2003

Apply a built in 2007 Style as a cell format using VBA

Useful links



Excel version and Office language settings

It can be useful to know what the Excel version and the Excel language is of the Excel application that
opens your workbook so your code can do different things depending of the version/language.

Excel Version Number

You can use this to get the version number of Excel in any local :
Val(Application.Version)

Excel 97  = 8
Excel 2000  = 9
Excel 2002  = 10
Excel 2003  = 11
Excel 2007  = 12
Excel 2010  = 14


Country code

You can use this to get the language chosen in Windows Regional Settings :
Application.International(xlCountrySetting)

This will give you a Country code, for example 31 for Dutch and 7 for Russian.
For a list of country codes see
http://support.microsoft.com/kb/213833/en-us

You can use Select case to run the code you want like this See also this page for examples for the Ribbon in Excel 2007
http://www.rondebruin.nl/dynamic.htm



Language ID of Excel

If you want to know the exact language of the userinterface of Excel (Because you can install many different language packs) you can use this to return the language ID number:
Application.LanguageSettings.LanguageID(msoLanguageIDUI)

To know what language belong the each ID press F2 in the VBA editor to open the object browser and enter msoLanguageID in the search field and press the search button. You see a long list now with language Id's.
If you select one you can see the number on the bottom of the object browser. For example if I select "msoLanguageIDDutch" I see this on the bottom of the object browser :

Const msoLanguageIDDutch = 1043 (&H413)

You can use Select Case now in your code to run different code for a few languages, for example
to display the captions of your buttons in the correct language or anything else.

Application.International (VBA)

In the section above named "Excel version and Office language settings" I use
Application.International(xlCountryCode) to get the language chosen in Windows Regional Settings.
But you can use it to get a lot more information about the users Excel application with this in your VBA code.
If you search for Application.international in the VBA editor the Help will give a you a table with all
the XlApplicationInternational constants that you can use in your code.

For example this code line will give you the local Year, Month and Day character

    MsgBox "The Year, Month and Day Characters are " & _
           Application.International(xlYearCode) & " " & _
           Application.International(xlMonthCode) & " " & _
           Application.International(xlDayCode)

If you not want to use VBA to get this kind of information check out Get.workspace example workbook
in the section "Using Strings as worksheet function arguments"




Week Numbering Systems and Date/Time Representations in Excel

Learn about the four different week-numbering systems available in Microsoft Office Excel and how to use them. Also learn why it is important to implement the date and time representation of the ISO8601:2000 standard in your Excel applications.

MSDN article about the two pages below on my own site
http://msdn.microsoft.com/en-us/library/bb277364.aspx

Week numbers in Excel
http://www.rondebruin.nl/weeknumber.htm

ISO Date Representatation and Week Numbering
http://www.rondebruin.nl/isodate.htm




Analysis ToolPak add-in worksheet functions

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.

http://www.dicks-blog.com/archives/2004/12/18/replacing-the-analysis-toolpak-addin-part-1/

http://www.dicks-blog.com/archives/2004/12/19/replacing-the-analysis-toolpak-addin-part-2/

http://www.dicks-blog.com/archives/2004/12/20/replacing-the-analysis-toolpak-addin-part-3/

http://www.dicks-blog.com/archives/2004/12/22/replacing-the-analysis-toolpak-addin-part-4/


Excel 2007 and up :

In Excel 2007 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.

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.

But there is a problem when you use a non English version of Excel 2007 and save the workbook in the
97-2003 format so that other users can use the workbook in the Excel versions 97-2003.

See this page
http://www.rondebruin.nl/atp.htm

***This problem is Fixed in Office 2007 SP2***




Using Strings as worksheet function arguments

Read Carefully :
In the example from Kirill Lapin 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.

Download this zip-file with a workbook containing the information about every item in the Get.workspace array.
Download workspace.zip



TEXT()

Overview: TEXT() uses number format strings in its 2nd argument.

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




INDIRECT()

Overview: INDIRECT() allows "R1C1" notation in its 1st argument if the 2nd argument is equal to FALSE

Example:
EN =INDIRECT("R1C1",0)

Issue: R1C1 notation may vary depending on the locale (both letters and parenthesis),
so the English symbols won’t always work.

Example:
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)

Example:
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.

Example:
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

IR     =INDEX(GET.WORKSPACE(37),6)
IC     =INDEX(GET.WORKSPACE(37),7)

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.

Example:
EN =INDIRECT(“MyRange”)

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.

Example:
EN =INDIRECT(“database”)
NL =INDIRECT("database")  - No problem with a named range Database in a Dutch version
ES =INDIRECTO("base de datos") - Excel uses space character here.
RU =ДВССЫЛ("база_данных")

Solution: Avoid using the name “Database” as a string argument of INDIRECT()




CELL() and INFO()

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.

Example:
EN =CELL("filename",A1)

Issue: Local versions of the keywords won’t work in the English locale

Example:
NL =CEL("bestandsnaam";A1)
ES =CELDA("nombrearchivo";A1)
RU =ЯЧЕЙКА("имяфайла";A1)

Solution: Use the English version of the keywords in all locales




SUMIF() and COUNTIF()

Overview: In their 2nd argument, SUMIF() and COUNTIF() allow error values
or strings representing error values.

Example:
EN =COUNTIF(A1:A10,#VALUE!)
EN =COUNTIF(A1:A10,"#VALUE!")

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.

Example:
NL =AANTAL.ALS(A1:A10;"<>#Waarde!")
ES =CONTAR.SI(A1:A10;"<>#¡VALOR!")
RU =СЧЁТЕСЛИ(A1:A10;"<>#ЗНАЧ!")

Solution 1:

This example counts all cells that do not have the #VALUE! error.
EN  =ROWS(A1:A10)-COUNTIF(A1:A10,#VALUE!)
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.

Download GetCell.zip




MATCH(), VLOOKUP(), HLOOKUP() and LOOKUP()

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)

Example:
EN =MATCH("zzzzz",A1:A10)
EN =MATCH(REPT("z",5),A1:A10)

Issue: In the non-Latin-based languages a string like “zzzzz” might not work.

Example:
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:
EN =MATCH("*",A1:A10,-1)




DATEDIF()

Read this webpage first :

Introduction To The DATEDIF Function (Chip Pearson)
http://www.cpearson.com/Excel/datedif.aspx

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)


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”

Example:
EN =DATEDIF(DATE(1968,9,13),TODAY(),"ym")

Issue: One might be tempted to replace the English date symbols by the local ones,
which would lead to an error.

Example:
NL =DATUMVERSCHIL(DATUM(1968;9;13);VANDAAG();"jm")
ES =SIFECHA(FECHA(1968;9;13);HOY();"am")
RU =РАЗНДАТ(ДАТА(1968;9;13);СЕГОДНЯ();"гм")

Solution: Use only “y”, “ym”, “m”, “md” and “d” as DATEDIF() uses English symbols in all locales




DATEVALUE(),VALUE(), etc.

Overview: You can coerce date-strings into numeric values by using the functions DATEVALUE()
and VALUE(), binary negation or any basic math operation (*,/,+,-,^)

Example:
EN =DATEVALUE("01/03/2009")   for 3-Jan-2009
EN =--"01-Aug-09"

Issue: Month literals, date separators as well as the order of year, month and day vary
depending on the localization.

EN =DATEVALUE("01/03/2009")
NL =DATUMWAARDE("03/01/2009")
ES =FECHANUMERO("03/01/2009")
RU =ДАТАЗНАЧ("03.01.2009")

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()

Example:
EN =DATEVALUE("2009-03-01")
NL =DATUMWAARDE("2009-03-01")
ES =FECHANUMERO("2009-03-01")
RU =ДАТАЗНАЧ("2009-03-01")




Command bars and controls in Excel 97-2003

If you want to add a menu item or disable a Command bar or Menu/Control you must always use the English
name of the Command bar in the code. If you use the local name of the Command bar it is not working.
But for a Menu/Control you must use the local name (to make it easy <g>).

To avoid problems use the Menu/Control Id's instead of the captions of the Menu/Controls

This is not working in a non English version to disable the File menu
Application.CommandBars("Worksheet Menu Bar").Controls("File").Enabled = False

This is always working :
Application.CommandBars("Worksheet Menu Bar").FindControl(ID:=30002).Enabled = False

Because there is only one control with the ID 30002 you can use this line also because
FindControl will find the first occurrence of the ID.
Application.CommandBars.FindControl(ID:=30002).Enabled = False

See this page for more examples and how you can find all the Id's
http://www.rondebruin.nl/menuid.htm




Apply a built in 2007 Style as a cell format using VBA

To apply Excel 2007's new built-in styles with code you must use the style
name of the local language. This presents difficulties if you do not know the
user's language version. For example this code will only work for English users:

Selection.Style = "Bad"

The workaround is to apply the style to a cell that will never change. This
could be a hidden cell in the workbook you are working with, or in any open
workbook, even in an Addin. It does not matter which language version the
style was originally applied with. When the file is loaded in the user's system
the style name will update to the equivalent name in the user's language.

The following example assumes cell A1 of the active-sheet had been applied with
your given style, let's say with the style named "Bad", or the equivalent in any language:

Selection.Style = Range("A1").Style

If A1 is not on the active-sheet you will need to qualify it with its parent Worksheet and possibly
workbook. You may find it easier to define a Name for this cell, and call it say "StyleBad"

Selection.Style = Range("StyleBad").Style

After you run the workaround Dutch, German and French users, for example,
will find "Ongeldig", "Schlecht" and "Insatisfaisant" respectively.




Useful links


Nice Add-ins for formula translations

See this add-in: TranslateIT  from KeepItCool (Jurgen Volkerink)
http://members.chello.nl/jvolk/keepitcool/download.html

Or the Analysis ToolPak Translator add-in from Eric Desart
http://www.rondebruin.nl/atptranslator.htm