Page 194 - Excel Data Analysis

10 537547 Ch09.qxd 3/4/03 12:13 PM Page 180

EXCEL DATA ANALYSIS

DECLARE A VARIABLE

ou can use variable declaration to make your VBA You can select the Require Variable Declaration option in
code run more efficiently. Variable declaration means the Editor dialog box in the Visual Basic Editor to ensure
Y that you specify the data type of the variable when that variables are always declared for all created
you declare it. For example, if you intend for the variable to procedures. If you select this option, the Visual Basic Editor
contain only integer values, you declare an integer variable. places the Option Explicit statement at the top of each
created module.
Unlike some programming languages, VBA enables you to
use variables that have not been declared. However, if you Even if the variable is declared, you should also assign a
misspell a variable within your code, VBA may treat the data type to it as part of the declaration. VBA treats all
misspelled variable as a totally different variable. For variables without a data type as variants. A variant is VBA's
example, if you use the variable MthRent throughout your catchall data type because it can essentially contain any
code and inadvertently type it as MnthRent, VBA sees type of data. In fact, the same variable can contain an
MnthRent as a new variable and assumes that MthRent integer value at one point and a string value at another
and MnthRent are two different variables. To ensure that location within the same module. Because VBA is forced to
variables are always properly declared, use the Option interrogate the value in the variant variables to determine
Explicit statement as the first statement in a module the type of data, your code becomes less efficient when
before you type any procedure code. you do not explicitly specify the data type.

DECLARE A VARIABLE

⁄ Type Option Explicit at the ‹ Type Dim. ˇ Type As after the variable Á Type your variable data
top of the module. name. type.
› Type the name of the
¤ Position the cursor after variable.
the Sub statement.

180