0 votes
in VBA by Beginner (23 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 (86 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 Skilled (265 points)
+2

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 (86 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 Skilled (265 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 based on the quality of your questions and/or answers. Users that ask great questions, may be given complimentary gift cards or training material.

Getting Started
Register

So, why don't you join us? It really is a neat way to reward the most dedicated members in our VBA and Python community.

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

...