Excel VBA language difficulties

Question

I have this code and my question is, will it work on English version of excel? I mean local formulas will work on English version of excel? (ORAZ mean AND in my language).

When I put ORAZ it works only on my PC, but on English PC it doesn't, when I put AND it doesn't give error but it doesn't work, any ideas how to help me ?

With Range("$H$6:$FH$50").FormatConditions _
 .Add(xlExpression, xlFormula, "=ORAZ(H$7<=$G$7,(H$7+7)>$G$7)")
With .Interior
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorLight2
    .TintAndShade = 0.799981688894314
End With
End With

Test for version of Excel then execute code as appropriate. Kind of like testing for 64bit. — findwindow, Jul 09 '15 at 22:07
In this particular case, change the formula to `"=(H$7<=$G$7)*((H$7+7)>$G$7)"` and avoid language issues altogether. — , Jul 09 '15 at 22:09
You may also want to have a look at the [Range.FormulaLocal property](https://msdn.microsoft.com/en-us/library/office/ff838851.aspx) and use it in place of the [.Formula property](https://msdn.microsoft.com/en-us/library/office/ff838835.aspx). If you use `.Formula = ...` then it will *always* be `AND`, not `ORAZ` . — , Jul 09 '15 at 22:12
What is the system's default list separator for the PL-PL regional setting? I thought it was a semicolon; not a comma. — , Jul 09 '15 at 23:45
I have worked in more than 10 different countries with more than 10 different languages in Excel. English works always in VBA. I highly doubt your statement that `AND` didn't work. For example, if you ask VBA to enter `=SUM(A1:B2)` in a cell formula it will actually automatically convert to the formula formula of the language you installed (on the sheet). And if you're wondering about the English formula then simply ask for it `Debug.Print Selection.Formula` which will always show you the English formula. — Ralph, Jul 10 '15 at 08:15

score 1 · Answer 1 · edited May 23 '17 at 11:44

I tested in my (Brazilian Portuguese) Excel 2013 and found that, indeed, FormatConditions.Add() expects its formulas in the localized version. It doesn't understand "canonical formulas" like Range.Formula does.

The following should work, even though the method used to translate the formula isn't the most proper ever:

Dim temp As Range

' The temp cell is just for translating our formula.
' Set it to some cell your sheet will never use.
Set temp = ActiveSheet.Range("Z1000")
temp.Formula = "=AND(E$1<=$E$2,(E$1+2)>$E$2)" ' use English formula here

With Range("$A$1:$D$4").FormatConditions _
    .Add(Type:=xlExpression, Formula1:=temp.FormulaLocal)

    With .Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = 0.799981688894314
    End With
End With

Call temp.Clear ' do away with the temp cell

Note that I also removed the useless xlFormula argument of the .Add() call, and explicited the optional parameters names.

Also, after the fact I discovered that this question is a duplicate of this and this.

score 1 · Answer 2 · edited Oct 20 '15 at 23:24

1

The problem is that you are just inserting a text within a cell, so it will only work if excel is set up for that language. Instead you can assign the worksheet function from VBA code like this:

ActiveCell = WorksheetFunction.And(H$7<=$G$7,(H$7+7)>$G$7)

This way Excel will translate the formula to the current language the user has set up.

edited Oct 20 '15 at 23:24

josliber

43,891
12
98
133

answered Oct 20 '15 at 22:23

Sama Tori

121
1
5

Excel VBA language difficulties

2 Answers2