Databases Reference
In-Depth Information
3
Control and Manipulation
of Worksheet Data
3.1
INTRODUCTION
The founding principle upon which this text is based is that it give the reader the tools to construct
useful applications. In order to accomplish that task, the user must have the ability to control and
manipulate data that reside in any given Worksheet within any given Workbook. For this, the
programmer must have the ability to make determinations about what any given Workbook or
Worksheet contains, and further, must have the ability to determine where specific pieces of data
actually reside within a specific Worksheet. The programmer must also have the ability to copy
information to variables where it can be stored, examined, modified, and manipulated. Finally, the
developer must also have the ability to create new Workbooks and Worksheets in which to store
information that has been analyzed and processed by a macro, most often for reporting purposes.
This chapter covers these kinds and types of processes.
3.2
SCOPE AND USE OF VARIABLES IN EXCEL VBA
Variables are probably the single most important facet of programming in any language. Without
variables there would be no programs; it is just that simple. How a programmer uses variables
determines how “clean” the code they write will be. Variables, like any other computing resource,
require space or take-up memory. Different kinds of variables take up different amounts of memory.
Looking at Table 3.1, it is clear that Variables that are of type Variant are the worst offenders
in terms of utilizing memory. If a programmer uses a variable in Excel VBA and does not declare
the variable to be of a certain type, then the variable is by default set to type Variant. This is because
such a variable could conceivably be utilized for any purpose and, therefore, must be allocated
enough memory to serve any purpose. The single biggest mistake any programmer can make with
respect to the use of variables in Excel VBA is not to declare their use. The next biggest mistake
a programmer can make with respect to variables is to “over”-declare a particular variable. For
example, if a variable is to store 3 significant digits, and the programmer has declared the variable
to be of type Double, they have just wasted 4 bytes of memory. Examine Table 3.1 to see why.
The variable could have been declared as type Single and accomplished the same function.
One way to avoid the use of variables with undeclared data types is to utilize the
Option
statement. When the
appears in a module, all variables must
Explicit
Option Explicit
be explicitly declared utilizing the
statements.
If a macro is run and attempts to use an undeclared variable name, an error will occur.
When declaring a variable, some thought should be given to what scope a variable should have.
Scope refers to the range of which a variable will be recognized. Basically, VBA supports two kinds of
variables: Public and Private. A Public Variable can be used in any procedure anywhere within the project
it is declared in (hence the name). If a Public Variable is declared in a standard module or a class module,
it can also be used in any projects that reference the project where the Public Variable is declared.
Examples:
or
Dim,
Private,
Public,
ReDim,
Static
Public ApproachCode as String
Public BondCoefficient as Double
85
