Things like xlValue, xlUp and so on are simply constants. They are
descriptive names that have a specific numeric value associated with
them. For example, xlUp is equal to the number -4162, so you can use
the name xlUp anywhere you would normally use the numeric equivalent.
This makes the code much easier to write and maintain. For example,
which is easier to understand?
Set R = Range("A100").End(xlUp)
' or
Set R = Range("A100").End(-4162)
These two lines are absolutely identical when executed, but using xlUp
is a whole lot easier to understand.
Most constants are elements of what are called Enums, or Enumerated
Values. An Enum is a named group of values that are used in a related
manner. For example, there is an Enum named XlDirection which contains
the named values xlUp, xlDown, xlToLeft, and xlToRight. The End
property of a Range is defined to take as input an XlDirection value.
Therefore, when using the End property of a Range, you can use xlUp,
xlDown, xlToLeft or xlToRight by name, without ever needing to know
what the numeric equivalents are.
Note that just because an Enum is specified as an input parameter to a
function, there is no validation taken to ensure that the value is in
fact a valid value of the Enum. For example, there is no element of
the XlDirection enum whose value is 123. However, the following code
is perfectly legal.
Set R= Range("A100").End(123)
This will compile without error and execute normally. What the code
does, though, when an invalid value is used is determined by the
programmer who wrote the code. Normally, a run time error will occur.
Very often you will get an error 1004, which is a generic catch-all
error than can any number of causes. Better written code would raise
an error 5, "Invalid Procdure Call Or Argument".
In the Enums provided by Excel, if the first letter is capitalized,
the name is that of an Enum group (e.g., XlDirection). If the first
letter is not capitalized (e.g., xlUp), it is the name of a value
within an Enum. Excel enums and values begin with the letters "xl".
Word uses "wd", and other programs may (or may not) follow such
conventions.
In addition to using the Enums and constants provided by Excel, you
can create your own enums. For example,
Public Enum Fruit
Apple = 1
Orange = 2
Banana = 3
End Enum
Now, you can declare variables of that type:
Dim MyFruit As Fruit
MyFruit = Banana
One of the advantages of using Enum types is that you get the
"intellisense" support when you are writing code. For example, in the
code above, when you type
MyFruit =
a list of valid values for MyFruit pops up, letting you pick the name
of a valid value. This helps enforce that the value is valid and in
general makes coding easier.
In the VBA Editor, you can press F2 to display the Object Browser
which will list (along with a LOT of other stuff) the various enum
groups and the names and values of their elements. You can search for
an enum value (e.g,. xlUp) to find the enum group of which it is a
member (XlDirection) and its numeric equivalent.
You can read a lot more about Enum values at
http://www.cpearson.com/Excel/Enums.aspx
Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
