Naming global variables in a structured way

Question

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?

Yeah just put it in a class module and have properties instead of constants — , Dec 17 '15 at 21:34
Maybe this question is better suited and hosted at [Code Review](http://codereview.stackexchange.com/)? — Ralph, Dec 17 '15 at 21:39
while it is not part of the question, if there is no real necessary for a value to be integer, it should be declared as [Long](http://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long) — Dirk Reichel, Dec 17 '15 at 22:01
Good point @DirkReichel and a fab question there in ur link. looks like I learnt something new today too :-) going to update my answer to use a long now lol — , Dec 17 '15 at 22:32

score 6 · Accepted Answer · edited May 08 '18 at 21:48

6

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.

edited May 08 '18 at 21:48

Community

1
1

answered Dec 17 '15 at 21:40

Did not know about `enum`. Learned something new^^ @JohnColeman – findwindow Dec 17 '15 at 21:50
2

Don't ignore the class module option, it will get you started on object oriented programming and it's a much better way to think. :) – Dec 17 '15 at 22:17

score 2 · Answer 2 · answered Dec 17 '15 at 21:39

You could use public arrays like this:

Public ColumnNum(0 To 2) As Long
Public RowNum(0 To 2) As Long

Used together with an enum:

Public Enum Category
    team
    task
    note 'etc.
End Enum

Then things like ColumnNum(team) will function like a public variable:

Sub test1()
    ColumnNum(team) = 5
End Sub

Sub test2()
    Debug.Print ColumnNum(team)
End Sub

If these two subs are run in order than 5 is printed.

Naming global variables in a structured way

2 Answers2