3

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?

noobmaster69
  • 2,985
  • 3
  • 27
  • 44
  • Just a small note - probably it is a slightly better idea to use `public`, instead of `global` - http://stackoverflow.com/questions/3815547/what-is-the-difference-between-dim-global-public-and-private-as-modular-field – Vityata Jan 11 '17 at 15:49

1 Answers1

5

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.

Vityata
  • 42,633
  • 8
  • 55
  • 100