Calling worksheet functions from vba in Excel versions in English

Question

The following snipet code utility works in English according to the Excel version, but when you try to run this code in the same book in the Portuguese version of Excel, this leads to errors.

' Add color bars on every other row - attempt to make list ' easier to read. ' "PlaceAt" is a worksheet range passed into the function With Range(PlaceAt.offset(1, 0), PlaceAt.offset(i + 1, 7)) .FormatConditions.Add Type:=xlExpression, Formula1:="=MOD(ROW(),2)=1" .FormatConditions(1).Interior.ColorIndex = 20 End With 

I believe the problem is that in Portuguese, the ROW function is written LIN (not sure what the MOD function will be), and that since the function is inserted using vba, the Excel translation function is not able to translate function names, as usual, when opening a document.

Any ideas?

Answer 1

Yes FormatConditions must use local format .

My workaround is to write the desired formula in the cell, and then get the FormulaLocal of that cell, which should be an accurate translation in your language:

 Dim tmpCell As Range Set tmpCell = Range("IV1") tmpCell.Formula = "=mod(row(),2)=0" .FormatConditions.Add(xlExpression, Formula1:=tmpCell.FormulaLocal) 

I don’t know if there is a cleaner solution, but if so, I would like to know, so please share ...