Converting "R1C1" to "L1C1" References in French Excel

[Stephen & Marie]

I have macro code that I wrote in Excel in English that I would like to
share with users who use Excel in French. In my code there are many
references in "R1C1" style (e.g. =SELECT("R2C1") ). In Excel in French
such references need to become "L1C1" style references. When I share
this code with French users all the functions are displayed with their
French names, but all the references in "s (double quotes) remain
untranslated (with R's rather than L's). Does anyone know of any easy
way to either convert these references to French format? Or is there a
way to tell French versions of Excel to accept "R1C1" style refences?

Any suggestions would be greatly appreciated.

Thanks,

Stephen Gabe

P.S. Sorry for posting in English to a French group, but I though French
users (who can read English ;-) might be the most likely people to have
encountered this before!

[JulienW]

Hi Stephen,

If you're using a macro to enter formulas in a spreadsheet, they should be
autmatically translated.
Make sure that the reference style is set to xlRC (as opposed to XlA1)

Try stuff like FormulaR1C1:

eg: Range("B2").FormulaR1C1 = "=Max(RC[1],R[-1]C[2])"

or at the beginning of your macro: Application.ReferenceStyle = xlR1C1

If that's not the problem give me an example of a formula whose reference is
not translated (I don't think =Select("R2C1") is a valid formula anyway)

Regards,
Julien

[JulienW]

Hi Stephen,

If you're using a macro to enter formulas in a spreadsheet, they should
be
autmatically translated.
Make sure that the reference style is set to xlRC (as opposed to XlA1)

Try stuff like FormulaR1C1:

eg: Range("B2").FormulaR1C1 = "=Max(RC[1],R[-1]C[2])"

or at the beginning of your macro: Application.ReferenceStyle = xlR1C1

If that's not the problem give me an example of a formula whose
reference is
not translated (I don't think =Select("R2C1") is a valid formula anyway)

Regards,
Julien

> I have macro code that I wrote in Excel in English that I would like

[Patrick Penet]

I think you'll have to prepare a french version of your
stuff : within the VBE use the search & replace tool
(Ctrl-H) searching - "R - replacing by - "L - (replace all, or
one by one to carefully check if any misreplacement), and
save it with a different name.

Beside of this, you will have to ask french users to check on
the L1C1 reference style, in Outils > Options > Général.

OTH
Regards.
Patrick

"JulienW" <j.wie...@netcourrier.com> wrote in message
news:B8ABA430.53A2%j.wie...@netcourrier.com...

[Niek Otten]

Have a look at the FormulaR1C1Local property

--
Regards,

Niek Otten
Microsoft MVP - Excel

"Stephen & Marie" <stephe...@sympatico.ca> wrote in message
news:3C85A9B8...@sympatico.ca...

[Laurent Mortézai]

Niek Otten a écrit :

>
> Have a look at the FormulaR1C1Local property

Hi everybody,

We recently upgraded from Office95 to Office2000, and of course all our
french-VBA code was translated to english-VBA. Because I was in charge
of this migration, and despite a good preparation, I faced many
translation glitches (well, with approx 1500 users, many of them Excel
power users, what could you expect?), one of the worst being the
FormulaR1C1 issue, which drove me almost crazy. As Niek mentioned,
FormulaR1C1local is thoretically supposed to work with french Excel, but
my experience is that it works *fine* only with newer Excel 2000 files,
not with XL95 files (with french VBA) converted to XL2000. In this
latter case, somehow, Excel 2000 seems to "remember" the older Excel
format and reacts abnormaly (This observation holds true in other Excel
dimensions as well).

My advice: never, ever, use L1C1 references in your VBA code. Rewrite it
another way. If you really want to stick to L1C1 references, beware of:

"[" and "]" => "(" and ")"

";" => ","

and of course "L" => "R"

...And Good luck,

Laurent

[Stephen & Marie]

Thanks everyone for your suggestions.

Since originally posting this query I have learned more.

All my macros are written in Excel 4.0 macro code (so that I can distribute them to users with old Excel
versions). After posting I was pleased to notice that I could save the code in Excel 4.0 International format.
So far this seems to have addressed the problem. I'm told by my French users that it leads to the references
being automatically changed to L1C1 style. This was really good news to me since this is a much easier sol'n
than searching for all R's to change to L's.

Stephen


Jan Karel Pieterse wrote:

Hi,

Sounds like an ancient xl4 macro, right?
Without really knowing anything about them, just some 
suggestions:
Couldn't you change the R1C1 references to A1 style refs?
Or maybe use an International macro sheet (no idea what 
that may imply)

Regards,

Jan Karel Pieterse
Excel TA/MVP

encountered this before!

.