Calling Worksheet functions from vba in foreign language versions of Excel

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?

score 2 · Accepted Answer · answered Jun 05 '13 at 19:39

2

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...

answered Jun 05 '13 at 19:39

Pragmateek

13,174
9
74
108

Thanks for the response. I sent some sample code to one of my coworkers in Brazil. I will follow-up when I hear back. – David Showalter Jun 05 '13 at 20:07
At this time he might be on the beach ;) – Pragmateek Jun 05 '13 at 20:09

score 1 · Answer 2 · answered Apr 20 '20 at 11:48

I found a cleaner solution in a different question:

Names.Add "translate", RefersTo:="=MOD(ROW(),2)=1"                                 ' Generic separator (,) and function name (ROW).
.FormatConditions.Add Type:=xlExpression, Formula1:=Names("translate").RefersToLocal ' Local separator (;) and function name (LIN).
Names("translate").Delete

score 0 · Answer 3 · edited Apr 20 '20 at 08:56

Excel 2019 - 365 version still has this issue present. I found it occurring when using the VBA in conjunction with MS Excel Named Range Manager.

Example of error I am facing, which I fixed modifying Pragmateek's 2013 answer.

In the Named Range Manager exists a named range that uses the OFFSET function. When calling this named range and using it in VBA to set another range variable USING the Application.Intersect method results in object errors because in VBA the MS Excel named range is acquired as a string. The intersect method then attempts to determine the intersect of this string and another range. This of course fails due to OFFSET working in English MS Excel versions, however not in Portuguese / Spanish .... And I imagine other languages.

Fixing this involved:

' Names!XXXXX = "=OFFSET('DummySheet'!$AO$7,0,0,1,'DummySheet'!$AM$2)""

Sub evaluate()
...
xAxis = Names!XXXXXX

Dim tempvar As Range: Set tempvar = Range("C1")
tempvar.Formula = xAxis                 ' set the range to hold the formula
tempvar.Formula = tempvar.FormulaLocal  ' set the range to hold the formula in the local language
Set rng = Application.Intersect(tempvar.EntireColumn, yAxis.RefersToRange.EntireRow)

This worked well. However if ANYONE has a cleaner solution, please post suggestions. Cheers!

Calling Worksheet functions from vba in foreign language versions of Excel

3 Answers3

Linked