Int'l R1C1 or R1K1 or L1C1...

[Guest]

Hi,
In a cell I used a formula like =INDIRECT("R[-1]C",FALSE).
This works fine on an English version of Excel, but if you open the workbook
on a system where the language is set to French for instance, the formula
returns a #REF! error.
In French, the formula should be =INDIRECT("L[-1]C",FALSE), (i.e. L for
Ligne and C for Colonne).
On a Dutch system, Excel translates the *function* to
=INDIREKT("R[-1]C",FALSE) but the #REF! error remains because it should be
=INDIREKT("R[-1]K",FALSE) (i.e. R for Rij and K for Kolom).
On a Germans system or a Spanish system or ...(I can go on like that...),
the problem is similar.

Can anyone provide me with a reliable way to determine how the words "Row"
and "Column" are abbreviated on the current system where Excel is running so
I can use these characters in the INDIRECT function?

I searched this newsgroup and I found a zillion messages for the R1C1
reference style problem, but no one seems to have encountered this problem
(or maybe they found the solution themselves

Any help appreciated.
-=Wim=-

 

[Harlan Grove]

Can anyone provide me with a reliable way to determine how the words "Row"
and "Column" are abbreviated on the current system where Excel is running
so I can use these characters in the INDIRECT function?

No. Internationalization just doesn't work well with R1C1 indirect
addressing or the CELL function.

Since you're already using the volatile INDIRECT, no great harm changing to
the volatile OFFSET. For example, the following formulas in cell X99 are
equivalent.

=INDIRECT("R[-1]C",0)

=OFFSET(X99,-1,0)

but =X98 would be better still. Why are you using INDIRECT with R1C1
addressing? There may be more robust alternatives.

 

[Niek Otten]

Hi Wim,

The easiest way is to use the A1 reference style.
But if you have a cell with a simple formula, like =A2 in A1, you can use this VBA function to find out what it looks like in the
local language:

Function RefStyle()
RefStyle = [a1].FormulaR1C1Local
End Function

BTW, All text literals can cause problems in other language versions: format codes, arguments for DATEDIF functions, for EVALUATE,
for DATEVALUE, etc.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| Hi,
| In a cell I used a formula like =INDIRECT("R[-1]C",FALSE).
| This works fine on an English version of Excel, but if you open the workbook
| on a system where the language is set to French for instance, the formula
| returns a #REF! error.
| In French, the formula should be =INDIRECT("L[-1]C",FALSE), (i.e. L for
| Ligne and C for Colonne).
| On a Dutch system, Excel translates the *function* to
| =INDIREKT("R[-1]C",FALSE) but the #REF! error remains because it should be
| =INDIREKT("R[-1]K",FALSE) (i.e. R for Rij and K for Kolom).
| On a Germans system or a Spanish system or ...(I can go on like that...),
| the problem is similar.
|
| Can anyone provide me with a reliable way to determine how the words "Row"
| and "Column" are abbreviated on the current system where Excel is running so
| I can use these characters in the INDIRECT function?
|
| I searched this newsgroup and I found a zillion messages for the R1C1
| reference style problem, but no one seems to have encountered this problem
| (or maybe they found the solution themselves
|
| Any help appreciated.
| -=Wim=-

 

[Guest]

Hi Harlan,

No. Internationalization just doesn't work well with R1C1 indirect
addressing or the CELL function.

Since you're already using the volatile INDIRECT, no great harm changing to
the volatile OFFSET. For example, the following formulas in cell X99 are
equivalent.

=INDIRECT("R[-1]C",0)

=OFFSET(X99,-1,0)

Actually the formula used in the original post was not complete. It should
have been =INDIRECT("R[-1]C",FALSE) + 1
I use this to sequentially number the items in a list. When I would use =X98
+ 1 in cell X99 etc... and I would insert some rows, then the formulas below
the inserted rows would all be wrong. I solved this with the above formula.
The better way is indeed to use the OFFSET function.
Thanks for the suggestion!

but =X98 would be better still. Why are you using INDIRECT with R1C1
addressing? There may be more robust alternatives.

I have a named range "IsLocked" which refers to
=GET.CELL(14,INDIRECT("RC",FALSE)). I conditionally format cells with
=IsLocked to automatically set the background color of the cell to grey. The
unlocked cells remain white so the user has a visual clue as to which cells
he can modify.
Any workarounds for this one?
Btw, I use Excel 2002 SP3.
-=Wim=-

 

[Harlan Grove]

I have a named range "IsLocked" which refers to
=GET.CELL(14,INDIRECT("RC",FALSE)). I conditionally format cells with
=IsLocked to automatically set the background color of the cell to grey.
The unlocked cells remain white so the user has a visual clue as to
which cells he can modify.

....

Finally a valid use for INDIRECT(ADDRESS(...)) - internationalization.

=GET.CELL(14,INDIRECT(ADDRESS(0,0,4,0),0))

 

[Guest]

Hi Harlan,

....
....

Finally a valid use for INDIRECT(ADDRESS(...)) - internationalization.

=GET.CELL(14,INDIRECT(ADDRESS(0,0,4,0),0))

Brilliant!
The ADDRESS function did the trick.
I now defined two names: mR which refers to =LEFT(ADDRESS(0,0,4,NOW()*0),1)
and mC =RIGHT(ADDRESS(0,0,4,NOW()*0),1).
NOW()*0 always evaluates to 0 but it forces Excel to recalculate. Otherwise
the ADDRESS function does not get updated when opening the workbook on a
system with a different language. I could have used Application.CalculateFull
in the Workbook_Open event, but this solution does not need VBA.

So, mR & "[-1]" & mC can be used instead of "R[-1]C" and it's
language-independent.

Thanks for the assistance.
-=Wim=-

 

[Dave Peterson]

I don't use multiple languages, but if I wanted to check to see if A1 is locked,
I could use this in English:

=CELL("protect",A1)

I'm gonna guess that "protect" would change with different language
versions--protekt????

It may be worth a look to verify.

Hi Harlan,

....
....

Finally a valid use for INDIRECT(ADDRESS(...)) - internationalization.

=GET.CELL(14,INDIRECT(ADDRESS(0,0,4,0),0))

Brilliant!
The ADDRESS function did the trick.
I now defined two names: mR which refers to =LEFT(ADDRESS(0,0,4,NOW()*0),1)
and mC =RIGHT(ADDRESS(0,0,4,NOW()*0),1).
NOW()*0 always evaluates to 0 but it forces Excel to recalculate. Otherwise
the ADDRESS function does not get updated when opening the workbook on a
system with a different language. I could have used Application.CalculateFull
in the Workbook_Open event, but this solution does not need VBA.

So, mR & "[-1]" & mC can be used instead of "R[-1]C" and it's
language-independent.

Thanks for the assistance.
-=Wim=-

 

[Guest]

Hi Dave,

I don't use multiple languages, but if I wanted to check to see if A1 is locked,
I could use this in English:

=CELL("protect",A1)

I'm gonna guess that "protect" would change with different language
versions--protekt????

It may be worth a look to verify.

First of all, it may be worth noting that I have an *english* version of
WINXP, with several language packs installed, so I can change the language of
the menus and dialogs.
I've heard that this situation is different than say an original French or
Dutch or German version of XP, with a localised version of Office.

I have verified and found that each language has a more or less translated
word for "protect".
However, it seems that they all know "protect" too, but I don't know whether
this is because my base version of Office is English.
If I use the translated word for "protect", these words (*) only work when
the system is switched to that particular language.
Strange...

(*)
In Dutch: "bescherming" (which means "protection" in English)
In German: "Schutz" (also means "protection" in English)
In French: "protege" (which means "protected" in English), allthough in
_real_ French and according to the help files it should be "protégé" (with
accents), but that doesn't work !
According to
http://office.microsoft.com/fr-fr/excel/HP052090081036.aspx?pid=CH062528261036 , in Excel 2003 the French translation has been changed to "protection".
Even stranger...

-=Wim=-

 

[Dave Peterson]

I don't know of any excel translators for this kind of thing--I have seen
function translators, but they don't seem to translate the strings passed to
these special type functions.

If you're looking for all possible languages, then I bet you won't find it here.

But if you're looking for a few languages, maybe you can post that list of
languages. I bet lots of people would help.

 

[Dave Peterson]

ps.

John Green, Stephen Bullen, Rob Bovey and Robert Rosenberg have a chapter in one
of their books that deals with international issues.

They share that chapter here:
http://www.bmsltd.co.uk/ExcelProgRef/default.htm

Look for this link:
Chapter 22 - International Issues

 

[Harlan Grove]

I don't know of any excel translators for this kind of thing--I
have seen function translators, but they don't seem to translate
the strings passed to these special type functions.

....

And they shouldn't. But when we get to shoulds and shouldn'ts, Microsoft
shouldn't have copied 123's @CELL function so closely. There's a syntactic
device that Microsoft could have used but didn't: using # followed by words
or phrases as tokens that could have been automatically translated just like
error values (I'm assuming here that, e.g., #VALUE! differs from language
version to language version). So in English the formula

=CELL(#CONTENTS,X99)

would become, maybe,

=ZELLE(#INHALT;X99)

in German. The advantage of XLM functions is that they use numbers for this,
and numbers don't need translating. They're equally cryptic in all language
versions.

Anyway, if internationalization is important, one must avoid using CELL and
INFO *or* use a table lookup in which the top row would contain the language
ID (I'd prefer the 2-letter abbreviations, e.g., en, de, fr, es), the 2nd
row a particular valid 1st args to either CELL or INFO in the given language
CHOSEN TO DIFFER BETWEEN ALL LANGUAGES (not necessary to use the same arg
for all languages, would be OK to use Ligne for French, Parenthese for
English, Inhalt for German, etc.), the 3rd row formulas like =CELL(R[-1]C)
to evaluate the args in the 2nd row. Then lookup the nonerror value in the
3rd row of the table to determine current language (column index in the
table). Subsequent rows would then contain all the valid CELL and INFO first
arguments where all columns in each row contain translations for the same
thing. Put a column of formulas in the column TO THE LEFT of this table that
would evaluate to the current language.

This isn't the same thing that Green et al suggest in the linked chapter in
your other response. That chapter covers VBA internationalization.

 

[Dave Peterson]

Thanks for the thoughts.

And for the correction on that link I posted--it's been a while since I looked
at it and forgot what was there.

I don't know of any excel translators for this kind of thing--I
have seen function translators, but they don't seem to translate
the strings passed to these special type functions.

...

And they shouldn't. But when we get to shoulds and shouldn'ts, Microsoft
shouldn't have copied 123's @CELL function so closely. There's a syntactic
device that Microsoft could have used but didn't: using # followed by words
or phrases as tokens that could have been automatically translated just like
error values (I'm assuming here that, e.g., #VALUE! differs from language
version to language version). So in English the formula

=CELL(#CONTENTS,X99)

would become, maybe,

=ZELLE(#INHALT;X99)

in German. The advantage of XLM functions is that they use numbers for this,
and numbers don't need translating. They're equally cryptic in all language
versions.

Anyway, if internationalization is important, one must avoid using CELL and
INFO *or* use a table lookup in which the top row would contain the language
ID (I'd prefer the 2-letter abbreviations, e.g., en, de, fr, es), the 2nd
row a particular valid 1st args to either CELL or INFO in the given language
CHOSEN TO DIFFER BETWEEN ALL LANGUAGES (not necessary to use the same arg
for all languages, would be OK to use Ligne for French, Parenthese for
English, Inhalt for German, etc.), the 3rd row formulas like =CELL(R[-1]C)
to evaluate the args in the 2nd row. Then lookup the nonerror value in the
3rd row of the table to determine current language (column index in the
table). Subsequent rows would then contain all the valid CELL and INFO first
arguments where all columns in each row contain translations for the same
thing. Put a column of formulas in the column TO THE LEFT of this table that
would evaluate to the current language.

This isn't the same thing that Green et al suggest in the linked chapter in
your other response. That chapter covers VBA internationalization.