Microsoft Office Tutorials and References
In Depth Information
Dim UserRange As Range
Dim CurrentChart As Chart
Dim MySeries As Series
To create an object variable, use the Set statement. Here are a few examples:
Set MyRange = Range(“A1:A100”)
Set CurrentChart = ActiveChart
Set MySeries = ActiveChart.SeriesCollection(1)
A variable’s value may — and often does — 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; in other words, a constant .
You declare a constant by using the Const statement. Here are some examples:
Const NumQuarters as Integer = 4
Const Rate = .0725, Period = 12
Const CompanyName as String = “Acme Snapholytes”
The second statement declares two constants with a single statement, but it
doesn’t declare a data type. Consequently, the two constants are variants. Because a
constant never changes its value, you normally want to declare your constants as a
specific data type.
The scope of a constant depends on where it is declared within your module:
To make a constant available within a single procedure only, declare it
after the Sub or Function statement to make it a local constant.
To make a constant available to all procedures in a module, declare it
before the first procedure in the module.
To make a constant available to all modules in the workbook, use the
Public keyword and declare the constant before the first procedure in a
Using constants throughout your code in place of hard-coded values or strings is
a good 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 makes your code more readable and makes it easier to change should
the need arise — you have to change only one instruction rather than several.