Microsoft Office Tutorials and References
In Depth Information
Variables, Data Types, and Constants
To make a variable available to all the procedures in all the VBA modules in a project, declare the
variable at the module level (before the first procedure declaration) by using the Public
keyword rather than Dim . Here’s an example:
Public CurrentRate as Long
The Public keyword makes the CurrentRate variable available to any procedure in the VBA
project, even those in other modules within the project. You must insert this statement before
the first procedure in a module (any module). This type of declaration must appear in a standard
VBA module, not in a code module for a sheet or a UserForm.
Static variables are a special case. They’re declared at the procedure level, and they retain their
value when the procedure ends normally. However, if the procedure is halted by an End
statement, static variables do lose their values.
You declare static variables by using the Static keyword:
Static Counter as Long
‘- [Code goes here] -
Working with constants
A variable’s value may change while a procedure is executing (that’s why it’s called a variable .
Sometimes, you need to refer to a named value or string that never changes: a constant.
Using constants throughout your code in place of hard-coded values or strings is an excellent
programming practice. For example, if your procedure needs to refer to a specific value (such as
an interest rate) several times, it’s better to declare the value as a constant and use the
constant’s name rather than its value in your expressions. This technique not only makes your code
more readable, it also makes it easier to change should the need arise — you have to change only
one instruction rather than several.
You declare constants with the Const statement. Here are some examples:
Const NumQuarters as Integer = 4
Const Rate = .0725, Period = 12
Const ModName as String = “Budget Macros”
Public Const AppName as String = “Budget Application”