 Back to jkp-ads.com |
Ron de Bruin
|
 |
|
Back to jkp-ads.com |
Ron de Bruin
|
|
Ron de Bruin decided to remove all Windows Excel content from his website for personal reasons. If you want to know why, head over to rondebruin.nl.
Luckily, Ron was kind enough to allow me to publish all of his Excel content here.
Most of these pages are slightly outdated and may contain links that don 't work. Please inform me if you find such an error and I'll try to fix it.
Kind regards
Jan Karel Pieterse
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
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.
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 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
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
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()
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
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
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)
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), 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”
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
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")
Check out this add-in if you want to tranlate formulas: TranslateIT from
KeepItCool (Jurgen Volkerink)
http://members.chello.nl/jvolk/keepitcool/download.html