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.

 

[Norie]

Does the code definitely not work if you keep the ,?

 

[Jonmo1]

It shouldn't matter...

VBA uses US settings regardless of the computers regional settings.
If you just use a comma in the formulas, Excel should AutoCorrect the list seperator character when VBA enters the formula into a cell.

 

[MarkLW]

It 'blows up'. Also it does not go into a cell its a custom format formula.

 

[Norie]

Are you sure the formula is the problem?

 

[VoG]

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)

 

[MarkLW]

It as noted above auto manages it every place else in the file. Its only when I do the VBA to custom format it has issue. I modified the , to a ; in the VBA code and ran it again and worked fine only then.

 

[Jonmo1]

It shouldn't matter...

VBA uses US settings regardless of the computers regional settings.
If you just use a comma in the formulas, Excel should AutoCorrect the list seperator character when VBA enters the formula into a cell.

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

Apparently this is not correct for entering a formula in conditional formatting (or custom format as OP has been calling it)

I'd suggest entering the formula into a cell, then you can use that cell as a reference when entering the conditional formatting.

Hang on, I'll work on that..

 

[Jonmo1]

Try

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

Change Z1 to any unused cell of your choice.

 

[MarkLW]

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. I'm not able to test it as all my international coworkers are offline... but I think it will do the trick. If it does not I will do your direct to Cell work around.

thank you so much.