Databases Reference
In-Depth Information
Although this all seems simple enough, there is one clincher to watch out for, and it is
especially good at getting those used to programming in a lower level language such as C. It
is possible to make multiple variable declarations on a single line such as in the following
example:
Dim Integer1, Integer2, Integer3 As Integer
In the preceding example, Integer1 and Integer2
, and only Integer3 is
declared as type Integer. Why? Because Excel VBA requires the programmer to explicitly declare
the type of each and every variable even if it is declared on the same line (unlike in C or many
other languages). To declare all three variables as type Integer would require the statement to be
written like this:
are declared as type Variant
Dim Integer1 As Integer, Integer2 As Integer, Integer3 As
Integer
Each time a subroutine or function is called, the variables within that subroutine or function
are reset to their default values (typically, a null string for string variables or variants, and zero for
numerical variables). Sometimes it is necessary to preserve the value of a variable declared within
a subroutine between calls. This can be done by means of the
statement. When the
Static
statement, the declared variable will retain its
value between subsequent calls to the subroutine or function. Thus a statement like this:
statement is utilized instead of the
Static
Dim
Static NumberofTimesRun as Integer
within a subroutine (or function) will not be reset to 0 each time the subroutine is called by the
macro. When the macro terminates, however, any Static variables will be reset to their default values.
Constants can also be declared within the VBA environment for values that will be utilized
over and over again within an algorithm. Constants are particularly useful if algorithms are being
constructed for scientific types of applications. Like any other variable, constants can be of scope
Private or Public, but it really is hard to picture the utility of a Private constant. Thus, statements
like this can be made:
Public Const SqrRootof2 as Single = 1.4142135
Private Const NoOfScans As Integer = 20
Public Const Comment_1 As String = "Unreasonable Value for
Parameter"
Arrays are the final topic of discussion for this section. An array is simply a matrix of variables.
An array can have many dimensions, much like the known universe. Most sane people will not
dimension arrays with more than three dimensions for obvious reasons — it gets very confusing
very quickly. Here are some examples of differently dimensioned arrays:
Option Base 1 'This statement will be explained later
Public SerialNumbers(2000) as Single 'One Dimension - 2000
elements
Public XYValues(10,10) as Single 'Two Dimensions - 10
×
10 =
100 elements
Public PolarCoord(10,10,10) as Single '3 Dimensions - 1000
elements
The number of dimensions an array has is the number of numbers separated by commas
contained within the parentheses following the variable declaration. The number of elements an
array has is the total number of elements variables declared within the matrix. In the case of
Public XYValues(10,10) as Single
