French VBA vs English VBA

[cazdealer]

Hello,
I have create a VBA procedure that is causing me trouble because I need to use it with 2 different computers.

my laptop need to be in French(Canada) in format under: Region and Language
but my desktop needs to be in English(US)...


In the french version the , is remplace by ;

for exemple: here is a part of my code that will only work with the desktop (English)


Range("D2:D" & Range("D" & Rows.Count).End(xlUp).Row).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(D1<>"""",D2<>"""",D1+1<>D2)"

here is the part that will work with the laptop:

Range("D2:D" & Range("D" & Rows.Count).End(xlUp).Row).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(D1<>"""";D2<>"""";D1+1<>D2)"


is there something I could do to make my VBA work with both settings without having 2 differents VBA file?

 

[Andrew Poulsom]

VBA expects the comma as the argument separator irrespective of Regional Settings.

 

[cazdealer]

VBA expects the comma as the argument separator irrespective of Regional Settings.

Thank you for taking the time to answer me.

I believe you that the comma is the argument separator but my french laptop will still not be able to run the procedure with the coma

I get a run-time error "5" Invalid procedure or argument...
if I swith the coma back to the ; ... it will works
weird...there must be something run with my excel settings or my code.

 

[pgc01]

Hi

In this case of the conditional formatting I think you have to use formulas with the local settings.

Unfortunately, the only way I know to translate a formula in English to the corresponding local formula is to use an auxilliary cell.

Let's say that you are OK with using cell AA1 as auxilliary.

A way to have the conditional formatting code language independent is:

Dim r As Range, rAux As Range

Set rAux = Range("AA1")
Set r = Range("D2:D" & Range("D" & Rows.Count).End(xlUp).Row)

rAux.Formula = "=AND(D1<>"""",D2<>"""",D1+1<>D2)"

r.FormatConditions.Add Type:=xlExpression, Formula1:=rAux.FormulaLocal

Please test.

 

[Andrew Poulsom]

That sucks. For the scenario described this works for me:

Sub Test()
    Dim Sep As String
    Sep = Application.International(xlListSeparator)
    Range("D2:D" & Range("D" & Rows.Count).End(xlUp).Row).Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND(D1<>""""" & Sep & "D2<>""""" & Sep & "D1+1<>D2)"
End Sub

 

[cazdealer]

Thank you Andrew and PGC!

both codes are fixing my problem!

 

[pgc01]

Hi

I'm glad we could help.

Remark:

If the PC's that will run the code all have the English version of excel and the only problem is the separators, like in the case you posted, Andrew's solution is the one to go, it's quick and simple, easy to understand and solves the problem (don't forget you have to replace all the separators, not just the list separator).

If you think that some of the PC's may not have the English version of excel (for ex., in your case there might be PC's with the French version of excel), then my solution will solve it.
Remark inside the remark: I thought about it and you are not forced to use an auxiliary cell, you might instead create a named formula and use the RefersToLocal property, but it's more work.