Const FirstRow as Long = 3
Const LastRow as Long = 65536
Const DateColumn as Long = 1
Const NameColumn as Long = 2
- Code: Select all
Private Enum SheetParameters
DateColumn = 1
NameColumn
FirstRow
LastRow = 65536
End Enum
Enums are declared at the top of a module and will be valid within that module if declared Private, global if declared Public. They are Long by default. The first enum in the declaration is always 0 unless given another value as shown above. After that each following enum is assigned a value automatically at an increment of 1 unless another value is assigned. Values don’t have to be consecutive and can be negative. After assigning a value automatic incrementing is continued from there.
You can refer to an enum by its own name, say, LastRow or by its full name which, in the above example, would be SheetParameters.LastRow. If the names you chose are unique within their scope you won’t ever need the full name. However, just in case you do, it may be better to assign short names. Microsoft uses another way as is demonstrated by enums like xlUp, xlDown, xlLeft and xlRight all of which are members of the xlDirection enum. Try it out: xlDirection.xlUp = xlUp.
I use a hybrid method. All my enum collections are given names of 3 characters' length of which the first is an N (for ‘enum’) followed by some mnemonics describing its purpose. For worksheet parameters I would use a name like Nws or Nwp. Next, I prefix that name to the names of all enums in that collection, like this:
- Code: Select all
Private Enum Nws
NwsDateCol = 1
NwsNameCol
NwsFirstRow
NwsLastRow = 65536
End Enum
The difference between a full name like Nws.DateCol and my name of NwsDateCol seems insignificant, but having too many periods in your code doesn’t enhance its readability and a name like NwsName is trouble-free while Nws.Name isn’t on account of ‘Name’ being a word widely used by Excel itself.
My only regret in regard to enums is that they assume the capitalization last applied. Used, as I am, to writing the variable name LastRow as ‘lastrow’ and let VB change the capitalization by way of confirming that I spelled it correctly, with enums I achieve the opposite: VB will change the capitalization of the name and all its other occurrences to ‘nwslastrow’.