Language independent VBA Programming

[Soly]

Hello,

I am creating an excel tool that is used by people all working with the same Excel version (2007), but various language settings (English (UK), German, French, Spanish...)
I created the code using English (U.S.) settings. Formulas in the worksheets are translated automatically, however problems arise with formulas created by a vba script.

Here is a piece of code to illustrate the problem:

Mycond1= "=AND(A1>=A2; A2>=A3)"
Selection.FormatConditions.Add Type:=xlExpression, Formula1:=Mycond1

The condition is written to the selected cell in English, no matter how the local language settings are.

Is there any possibilty to tell vba to translate this to the local language? I read about "FormatLocal", but it does not seem to be applicablen here.

I would appreciate any ideas! Thanks a lot in advance!

Best regards,
Soly

 

[AlphaFrog]

I'm curious to find out if there is some sort of elegant solution to this as well.

If you meant by "creating an excel tool", you are creating an Add-In, here's sort of a kludge.

You're add-in will have its own worksheet the user wouldn't see. You could store a dummy formula on that sheet. Excel will automatically convert that dummy formula to the local language setting. Have your code read that formula and then use it elsewhere in the code.

 

[Soly]

Hello,

I found a workaround for my problem. I write my formula to a field and read it in again, now with local settings. This works. However, there shouldt be a more elegant solution (so I do not mark this threat as solved yet)...

Mycond1="=AND(A1>=A2, A2>=A3"
Range("tempfield").FormulaR1C1 = Mycond1
Mycond1 = Range("tempfield").FormulaLocal
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= Mycond1

Regards,
Soly