Constants in VBA

  • Thread starter Thread starter Don
  • Start date Start date
D

Don

What is the best way to handle constants in VBA. For example in C a #define
statement could be used to define a department name and then it could be
used through out the code. The benefit being that it is hard coded in only
one place and can be changed relatively easily.

Any suggestions would be appreciated!

Thanks!

Don
 
Don said:
What is the best way to handle constants in VBA. For example in C a
#define statement could be used to define a department name and then
it could be used through out the code. The benefit being that it is
hard coded in only one place and can be changed relatively easily.

Any suggestions would be appreciated!

Thanks!

Don
Click to expand...

Declare it as a constant in a standard module.

Public Const DEPT_NAME = "Sales"
 
It is a good idea to create a module containing constants, although some
constants might be better declared within the procedure or module where they
are used if the utility of that constant is somewhat limited.

An alternative would be to create a class module and declare Enum values,
i.e.,

Public Enum lsItemType
lsService = 1
lsInventory = 2
lsNonInventory = 3
lsTax = 4
End Enum

Whether or not you use an Enum depends on the nature of the constant, but it
is especially useful when you want to limit values assigned to a variable.
i.e., you could define a procedure as:

Function GetCurrentInventory(ItemID as String, ItemType as lsItemType) As
Long
....
End Function

Bonus: You'd get IntelliSense help for the ItemType parameter when calling
the function.
 
Back
Top