Microsoft Office Tutorials and References
In Depth Information
Forcing Yourself to Declare All Variables
To force yourself to declare all the variables that you use, include the following as the
first instruction in your VBA module:
Option Explicit
This statement causes your procedure to stop whenever VBA encounters an undeclared
variable name. VBA issues an error message ( Compile error: Variable not
defined ), and you must declare the variable before you can proceed.
Using an Option Explicit statement can prevent errors that might be very difficult
to identify. For example, your procedure might use a variable named Interest . But
what if you misspell the variable — for example, Intrest ? VBA will consider it to be a
new variable, with a value of 0, and no error will be generated. If an Option
Explicit statement is present, this error will be flagged, and you can correct it.
To ensure that the Option Explicit statement appears in every new VBA module,
enable the Require Variable Declaration option on the Editor tab of the VB Editor’s
Options dialog box.
VBA has many built-in constants that you can use in your code. For example, the
following statement changes the chart type of the active chart to a line chart:
ActiveChart.ChartType = 3
Many people find it difficult to remember that a value of 3 represents a line
chart. Thankfully, you can use a built-in constant instead. The constant xlLine has
a value of 3 and can be used as follows:
ActiveChart.ChartType = xlLine
When you record a macro, the macro recorder almost always uses built-in
constants rather than the actual values.
Using dates
You can use a string variable to store a date, of course, but then you can’t perform
date calculations using the variable. Using the Date data type is a better way to
work with dates.
A variable defined as a Date uses 8 bytes of storage and can hold dates ranging
from January 1, 0100, to December 31, 9999. That’s a span of nearly 10,000 years —
more than enough for even the most aggressive financial forecast! The Date data
Search JabSto ::

Custom Search