Reference styles and local/non-local formulae - international problems.

[Alan Howells]

I have a problem that I am sure isn't unique.

I have a userform with a refedit in and when I bring up the form from
a cell that holds a formula I would like to initialise the refedit
with something that is in the formula. E.g. lets say I want the second
parameter of the main function in the formula.

The easiest way to parse this is by getting rCell.Formula and looking
for paranthesis and commas (standard lists).

But then I want to use that parameter and place it into the refedit
that is both in the local language and in the users reference style. I
can use ConvertFormula to change it to the correct reference style,
but how do I convert from VBA language to the local language.

I also need to convert it back as when the refedit changes I would
like to display the result of the refedit text. I can do that easily
in VBA language (US_EN) using Application.Evaluate but I need to
convert the refedit text from the local language back to VBA language.

The refedit text could be anything, a number, actual text, a formula
or even an array (e.g. {1,2,3,4} or {1\2\3\4} if the decimal point is
a comma as it is in most european countries). It could even be a range
in R1C1 style in which the R or C is in the local language (e.g.
german would be Z1S1) so I don't really want to parse a local formula.

Thanks for any help in advance.


Alan

 

[Niek Otten]

Hi Alan,

Did you look at .FormulaLocal, .FormulaR1C1, FormulaLocalR1C1 etc?

Problems do occure with literals, like the format argument for TEXT, R1C1
style addresses in INDIRECT() functions, etc.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Alan Howells]

Thanks Nick, I have thought about them but it would make parsing them
(to get the second parameter of the second function for example) a lot
trickier.

Also since the user would write in his local language into a refedit,
this won't solve my problem of trying to get the result of a formula
written in the refedit, as a) it is in a RefEdit (not a cell so can't
get .Formula etc..) and b)Evaluate only looks in VBA language not
local language.

Hi Alan,

Did you look at .FormulaLocal, .FormulaR1C1, FormulaLocalR1C1 etc?

Problems do occure with literals, like the format argument for TEXT, R1C1
style addresses in INDIRECT() functions, etc.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

<SNIP>

 

[jwsmith]

Hi Alan,

Did you look at .FormulaLocal, .FormulaR1C1, FormulaLocalR1C1 etc?

Problems do occure with literals, like the format argument for TEXT, R1C1
style addresses in INDIRECT() functions, etc.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

FYI - Just thought I'd add my experience from following this thread ... apparently the syntax "FormulaLocalR1C1" is misstated and should actually be: "FormulaR1C1Local" ... yet it wasn't detected by either the VBE compiler or at run-time ... when I used the former in my code, it just returned nothing from the referenced cells ... no errors given ... It took me most of a day to run this down so passing this on for others.

Jeff