VBA Error when run by users with different language, runs fine when user language set to English

[CCoffey]

I created this macro enabled file in Excel 2016 and show that it passes compatibility checks without warnings down to 2010, 2007 or lower starts to run into issues.

Most users are able to use the file with a Form, several buttons with code behind and a single module.

When run on systems with French and Russian languages (so far) an error is encountered citing: "Execution error '5'. Argument or call of procedure incorrect". (Running Excel 2010)

In the past I had a similar issue with French systems where some VBA code simply would not run and had to find alternative means to accomplish the need.

Is there any resource out there to identify which VBA/Excel functions require "translation" from English to other languages?

The code that is failing is fairly simple, just setting a value for one or more named ranges...but there is a fair bit of more complex code that is not even running yet due to this error...so fear the fun has only just begun.

This code is currently failing (again, runs fine in English, just French & Russian language systems fail):

Private Sub cmdApply_Click()Dim WS As Worksheet
Dim strAssessments As String


  Application.ScreenUpdating = False
  Set WS = Worksheets("AssessmentBuild")
  WS.Unprotect (...)
  
  If frmAssessments.chkGeneral = True Then
    WS.Range("ShowGeneral").Value = "X"
    strAssessments = "General & Pre-Inspection"
  Else
    WS.Range("ShowGeneral").Value = ""
  End If
  
  If frmAssessments.chkSafety = True Then
    WS.Range("ShowSafety").Value = "X"
    If strAssessments <> "" Then
      strAssessments = strAssessments & ", Safety"
    Else
      strAssessments = "Safety"
    End If
  Else
    WS.Range("ShowSafety").Value = ""
  End If
  
  If frmAssessments.chkEquipment = True Then
    WS.Range("ShowEquipment").Value = "X"
    If strAssessments <> "" Then
      strAssessments = strAssessments & ", Equipment & Supplies"
    Else
      strAssessments = "Equipment & Supplies"
    End If
  Else
    WS.Range("ShowEquipment").Value = ""
  End If
...

 

[CCoffey]

It turns out the code above does successfully run, it is a following procedure that fails.

wsAudit.Cells(iAuditRow, 5).SelectWith Selection
 .FormatConditions.Delete
 .FormatConditions.Add Type:=xlExpression, Formula1:="=OR($D" & iAuditRow & "=1,$D" & iAuditRow & "=2)"
 .FormatConditions(1).Interior.Color = RGB(255, 255, 193)
 .FormatConditions(1).StopIfTrue = True

Turns out that the Formula1 setting is not "Translated" to the proper argument in the local language like formulas/functions in the actual spreadsheet. So, at runtime this errors out as there is no matching function in French, Russian, etc...

How can I get the system to insert the correct local language OR function here:

.FormatConditions.Add Type:=xlExpression, Formula1:="=OR($D" & iAuditRow & "=1,$D" & iAuditRow & "=2)"

The argument is dynamic for each row so can't simply put this function into a cell on the worksheet and reference range.formula tried and won't accept the OR function with dynamic/by reference arguments...maybe could do this with bogus arguments and then parse the resulting formula to return the text prior to the ( and then fill in the rest?

Any solutions greatly appreciated.

 

[RoryA]

I'm not aware of any easier way than putting the formula into a row. You can then use the R1C1 version of the formula ("=OR(R[0]C4=1,R[0]C4=2)" which is the same for every row and you can read it using FormulaR1C1Local property and then put into your conditional formatting.

 

[CCoffey]

I found a way to make it work - but is not pretty, may take some time to work through your suggestion...thank you.

Here is what I did.

In one of my hidden sheets @ X1 have put the formula: =OR(D1=1,D1=2)
Then in my code:

sCondFormat = Mid(WS.Range("x1").Formula, 1, Application.WorksheetFunction.Find("(", WS.Range("x1").Formula) - 1) & ("($D" & iAuditRow & "=1,$D" & iAuditRow & "=2)")
.FormatConditions.Add Type:=xlExpression, Formula1:=sCondFormat

This is now working on my system where language is English, so I am hopeful it will work for the other systems for French, Russian, etc... where X1 will be properly translated to the local language version of the OR function.

Will try the other option if this one fails and/or find some time...

Many thanks for the suggestion and time to review.

 

[CCoffey]

Closing this one out - by reference is a failure, does not correct to the local language and still generates an error. I just accepted that I have to create two different rules for each row, creates a large number of conditional formatting rules which will hurt the file size and performance - but it works.

.FormatConditions.Add Type:=xlExpression, Formula1:="=$D" & iAuditRow & "=1"
 .FormatConditions(1).Interior.Color = RGB(255, 255, 193)
 .FormatConditions(1).StopIfTrue = True
 .FormatConditions.Add Type:=xlExpression, Formula1:="=$D" & iAuditRow & "=2"
 .FormatConditions(2).Interior.Color = RGB(255, 255, 193)
 .FormatConditions(2).StopIfTrue = True

Many thanks for the response...