Page 174 - Excel Data Analysis

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

EXCEL DATA ANALYSIS

AN INTRODUCTION TO MACROS (CONTINUED)

UNDERSTANDING VBA (CONTINUED)

Variant
The Variant data type is the default data type that VBA by VBA to determine the data type, it is a good idea to
uses. Because a variant can contain any type of data, use variants only for values that you cannot type with
VBA treats all variables that you do not assign a data the standard VBA data types.
type as variants. But because of the processing required

DATA TYPE BYTES RANGE OF VALUES
Decimal 14 +/-79,228,162,514,264,337,593,543,950,335
Variant 16 -1.79769313486232E308 to -4.94065645841247E-324 (with numbers)
for negative values
4.94065645841247E-324 to 1.79769313486232E308 for positive values
Variant 22 + string length 0 to 2,000,000,000 (with characters)
Arrays
An array is a group of variables with the same name and array. For example, with the aforementioned States
data type. For example, if you have a list of the 50 U.S. array, you can have a corresponding list of cities in each
states, you can place the state names in an array called state. If a user selects Texas, a list of the cities in Texas
States. You refer to each value in an array as an becomes available:
element. You access elements of the array using index
numbers that correspond to their positions in the array. Example:
States(43,5) = "Dallas"
Using an array reduces the number of variables in your
code because you declare only one variable to manage VBA allows for up to 60 dimensions in an array, but
all your data values. Otherwise, storing all 50 states most developers rarely use more than two or three
requires declaring and managing 50 different variables. dimensions.

Example: You can declare an array either as fixed-length, where
you specify the number of elements, or as dynamic,
Dim States(50)
with an unknown number of elements:
States(43) = "Texas"
An array with one list of data is called a one-dimensional Example:
array. With VBA, you can declare multidimensional arrays Dim States()
in which each array element has a corresponding

User-Defined
You can create user-defined data types to describe data type definition. For example, this data type stores
specific types of data. User-defined data types resemble information about individual books:
an array because you can store multiple values using one
variable name. But unlike arrays, which must contain Example:
values of the same data type, you create user-defined Type BookReview
data types as a combination of standard VBA data types. Title as String
Pages as Byte
ReviewDate as Date
You specify a user-defined data type with the Type and End Type
End Type statements followed by the name of the new

160