Calling Worksheet functions from vba in foreign la

Question

The following code snipet run OK in an English Language verion of Excel, however when attempting to run this code in the same workbook in a Portuguese version of Excel it errors out.

   ' 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 that the problem is, in Portuguese, the ROW function is spelled LIN (not sure what the MOD function would be) and that since the function is inserted using vba, Excel's translation function does not have the opportunity to translate the function names as it normally would when opening the document.

Any ideas?

Answer 1

Yes FormatConditions formulas must use the local format.

My workaround is to write the wanted formula into a cell an then get the FormulaLocal of this cell which should be the exact translation in your language:

Dim tmpCell As Range
Set tmpCell = Range("IV1")
tmpCell.Formula = "=mod(row(),2)=0"

.FormatConditions.Add(xlExpression, Formula1:=tmpCell.FormulaLocal)

Don't know if there is a cleaner solution, but if so I'd like to know, so please share...