0 votes
in VBA by Beginner (123 points)

Values like 1, 4, 6 etc. are given against each of the names of the operators (like xlAnd, etc.) in auto filter. Can we use these values in the vba code, instead of the names of the operators & if so how?

1 Answer

0 votes
by Beginner (186 points)

Dear sandeep kothari, 

    each constant defined in VBA, like the one you mentioned, can be replaced by its value anywhere, e.g. when you pass it as a parameter to a function or subroutine, or when you assign it to a variable. But it is not advisable, since that makes your code less readable

So, while it is perfectly fine to write something like this:

With sheet
    .AutoFilterMode = False
    .Range("A1:D1").AutoFilter Field:=3, Criteria1:=">=1", _
        Operator:=1, Criteria2:="<=10"
End With

it is less readable than:

With sheet
    .AutoFilterMode = False
    .Range("A1:D1").AutoFilter Field:=3, Criteria1:=">=1", _
        Operator:=xlAnd, Criteria2:="<=10"
End With

both for you and to the people that read your code.

by Beginner (204 points)

Actually, the choice to use constant names or their values depend on whether you plan to distribute your code to other machines. Not everyone is using the latest version of Excel and it is quite possible that an older version may not recognize a pre-defined constant.

This is especially true if you like to use API calls.

Now, rather than using the values, you could consider creating an enumeration (unless you are targeting versions earlier then Excel 2000.) Enumerations have these benefits:

  • You still use meaningful names
  • The values are all defined in one place, making corrections easy
  • You can use Intellisense to select the proper value

Even if you don't feel like using enumerations, you can declare the constants in your code with the same name, to ensure that every version recognizes them.

by Beginner (186 points)

That's an excellent piece of advice. I use LibreOffice VB more often than Microsoft VB, and I must use enumerations to provide many constants that are not predefined in LibreOffice. That said, compatibility between the two interpreters is still a nightmare. I don't know why the user asked for replacing constants with numerical values in the first place, but constants are the way to go, provided that you take all the precautions that you just said.

by Beginner (204 points)
Thanks, Alberto. I tried to use LibreOffice a few years ago and never warmed up to it. :)

Welcome to wellsr Q&A
wellsr Q&A is the VBA and Python programming community that rewards you for learning how to code.

Earn free prizes for asking VBA and Python questions and for answering questions asked by others in our community.

What makes us different?
Our points system rewards you with a chance for free gifts as your contributions grow. The more points you earn, the better the prizes.

Getting Started

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.

Top Users Mar 2019
  1. YasserKhalil

    306 Points

  2. ParserMonster

    204 Points

  3. Alberto Semat

    186 Points

  4. danmcg

    127 Points

  5. Abdan

    124 Points

Prizes for March 2019
Terms and Conditions
1st place (1) 1-year MyExcelOnline Academy Membership
(2) My First Add-in Dev Pack & Course
(3) AutoMacro: VBA Code Generator (Developer)
(4) VBA Cheat Sheet Bundle
(5) $35 Amazon Gift Card
2nd place (1) My First Add-in Dev Pack & Course
(2) 101 Most Popular Excel Formulas E-Book
(3) VBA Cheat Sheet Bundle
(4) $25 Amazon Gift Card
3rd place (1) Mouse to Macro
(2) VBA Cheat Sheet Bundle
(3) $15 Amazon Gift Card