Adding formulas by VBA; dealing with international keyboard differences

[MarkLW]

Hi. I'm scrapping some conditional formatting.

Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=AND(NOT(ISBLANK($AG3)),LEN($AG3)>12,Colors_On=TRUE)"

Code works just fine until found a coworkers that uses international / French keyboard and I found that "," needs to be ";" to work on that system when adding formula by script.

So I don't have to have multiple version thinking something like below but not sure what would go into custom function "KeyBoardType()"
If Keyboardtype = "US" Then
' Use ,
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=AND(NOT(ISBLANK($AG3)),LEN($AG3)>12,Colors_On=TRUE)"
Else
' Use ;
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=AND(NOT(ISBLANK($AG3));LEN($AG3)>12,Colors_On=TRUE)"
End if

Help with a 'KeyBoardType()' function or any other implementation suggestions would be great.

Thanks for the help.

 

[Jonmo1]

This is the better solution, And I see no reason that it wouldn't work..
You just have to concatenate that character into the formula as needed.

I Like it.

I was doing a google on Excel International found

Application.International(xlListSeparator)

I should be able to insert this in line as part of my formula.

 

[MarkLW]

That is interesting because simply writing a formula with VBA then , will be converted to ; if necessary automatically

This may help

s = Application.International(xlDecimalSeparator)

Thank you got me in the direction I needed!

 

[MarkLW]

This is the better solution, And I see no reason that it wouldn't work..
You just have to concatenate that character into the formula as needed.

Worked without issue on my system. Will find out next week how worked international. Thank you!