Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Excel VBA language difficulties

Tags:

excel

vba

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
like image 284
Maciej Matuszczak Avatar asked Jul 09 '15 22:07

Maciej Matuszczak


People also ask

How difficult is it to learn Excel VBA?

VBA is a coding language used by millions of people across the world to automate tasks in Microsoft Office products. It's a language that has been around for decades and is one of the easiest coding languages to learn if you don't have a computer science background.

Is VBA a difficult language?

VBA is a coding language that's relied on by millions of people around the globe for automating various tasks in Microsoft Office. This language is considered to be one of the easiest coding languages to learn for those who don't have a background in computer science.

Is VBA still relevant 2022?

– Yes, absolutely! VBA is certainly not the most modern programming language, but due to the huge prevalence of Microsoft Office it is still a very relevant language in the year 2022.

Is VBA more difficult than Python?

Python is easier to learn and master, unlike Excel, which includes a personalized language known as VBA that is complex to master and execute. Transitioning from Excel to Python enables users to enjoy various benefits, such as an open-source coding platform, many volunteer contributors, and free libraries.


2 Answers

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.

like image 146
André Chalella Avatar answered Oct 20 '22 03:10

André Chalella


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.

like image 34
Sama Tori Avatar answered Oct 20 '22 03:10

Sama Tori