CODEWITHSUNDEEP
vbaexcel
noobmaster69
noobmaster69

Reputation: 3115

Using Global Constants to store environment variables

I'm creating an add-in for Microsoft Excel and I'm using a module to store variables (which do not change during runtime) which are accessed by classes and other modules in the app. This is a more convenient way of hard coding values which I may have to find and replace at different points in the development cycle.

The variables are things such as the app version, or the workbook password:

Option Explicit
Option Private Module

Global Const APP_ID = "XXY"
Global Const APP_VERSION = "1.00"
Global Const WORKSHEET_PASSWORD = "s0Me_pa$$w0rD"
Global Const DATA_TABLE = "tblStockData"
Global Const FORM_DEFAULT_VALUE = 200

I was wondering if anyone can tell me if this is bad practice and if so, what a better course of action would be?

Upvotes: 3

Views: 4121

Answers (1)

Vityata
Vityata

Reputation: 43585

I usually do exactly the same or two other things, depending on the size of the application. If it is something small, I use your approach. However,these are the other approaches I use.

First Approach:

If the user is supposed to change the password or the default_value, I make a separate sheet, called "Settings" and I write them there. Then I make a public function for the password like this:

public function GetPassword as string
   GetPassword = [set_password]
end function

Second Approach:

If this is a big application with lots of constant and public variables, the scope gets somehow "dirty" and it is difficult to find the variables. Thus, I make a class of the Constants and I declare them there. Then, it is easier to call them.

Something like this:

Private Const p_First_COLUMN_TO_FILL = 8

Public Property Get FIRST_COLUMN_TO_FILL() As Long
    FIRST_COLUMN_TO_FILL = p_First_COLUMN_TO_FILL
End Property

Thus, if my class is clsConstants, I declare it as Public objCon As clsConstants and now with objCon.FIRST_COLUMN_TO_FILL I get what I want. The scope is somehow cleaner this way. You may also build more than one class of constants, depending on what are they used for.

Upvotes: 5

Related Questions