New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Use Enums for Efficient Naming of Numbers

Free Excel Tips and Tutorials

Use Enums for Efficient Naming of Numbers

Postby Sisyphus » Sat Feb 04, 2012 12:15 am

In programming you often need to give names to numbers, such as FirstRow, LastRow, DateColumn, NameColumn etc. You can declare such numbers individually as constants. However, in its Enum (from ‘enumerate’) VB provides an alternative that is much simpler and more efficient. Compare for yourself:
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’. :cry:
Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Return to Excel Tips and Tutorials

Who is online

Users browsing this forum: No registered users and 15 guests

cron