Q:

Naming global variables in a structured way

My current project has global constants that define certain rows and columns in workbooks that this project will be searching through. I have defined them as such:
Public Const headRow As Integer = 1
Public Const descRow As Integer = 2
Public Const pnumCol As Integer = 1
Public Const teamCol As Integer = 2
Public Const dateCol As Integer = 3
Public Const hourCol As Integer = 4
Public Const typeCol As Integer = 5
Public Const taskCol As Integer = 6
Public Const noteCol As Integer = 7

I'm wondering if there is a cleaner way to define these that would allow me to write these in a way such as:
ColumnNums.team
ColumnNums.task
ColumnNums.note     'etc

I think something similar to this could be done by defining my own type, but that would probably not be worthwhile. I'm basically wanting this to be an easy way to remember the variable names as I write more code, as well as to be able to count how many items I have in each group. Would a Type or Collection be useful in this case?

A:

For mixed variable types, you can put it in a class module, name the class module ColumnNumbers and put the following code in:
Public Property Get Team() As Long
    Team = 1
End Property

Public Property Get TeamName() As String
    TeamName = "Team One! :-)"
End Property

Then you can use it in any module like this:
Dim colNums As New ColumnNumbers

Sub foo()
MsgBox colNums.Team
End Sub

If you only want to return long values, put it in an enum:
Enum ColumnNumbers
    Team = 1
    Description = 2
End Enum

Sub foo()
MsgBox ColumnNumbers.Team
End Sub

Chip pearson has already done a fantastic job of describing enums here it's worth a read if you have yet to discover them.

