Page 172 - Excel Data Analysis

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

EXCEL DATA ANALYSIS

AN INTRODUCTION TO MACROS (CONTINUED)

UNDERSTANDING VBA

You use the Visual Basic for Applications programming Data Types
language to create all macros within Excel. VBA enables you to
add complex functionality to a macro. To create a VBA macro, A data type refers to how VBA stores data in
you need to be familiar with common VBA terminology. memory. VBA provides an assortment of built-in
data types that you can use to handle your macro
data, along with user-defined data types that
Variables you create.
Variables are essentially just user-defined storage spaces. With VBA, you do not need to specify the type of
You can declare a variable to contain a specific type of data stored in a variable when you declare it. You
data value. can enable VBA to automatically determine the
data type, but doing so can slow down your code
You can make variable names almost anything including any for a large macro.
combination of alphabetic characters, numbers, and some
punctuation — such as #, $, %, ., and ! — as long as the first The size of a data type is the number of bytes
character is alphabetic. You cannot use spaces as part of the required to store it. A byte is a group of bits, with a
name. bit being the smallest storage unit and having a
binary value or either 1 or 0. Knowing the number
VBA is not case-sensitive when it comes to variable names. of bytes you need to store a data type can help you
You can make the names any combination of upper- or use memory more efficiently. Although storage
lowercase characters. Keep in mind that the name cannot issues are rarely a problem with most macros, keep
exceed 254 characters. them in mind when you create complex macros.

Numeric
VBA provides several different numeric data types, and uses three floating-point data types. The one that
categorized by the type of numeric value that you want you select depends on the size of the numeric values
to save. VBA provides three integer data types for you want to store in the variable. The following table
numeric values that do not include a decimal portion lists the various numeric data types:

DATA TYPE BYTES RANGE OF VALUES
Byte 1 0 to 255
Integer 2 -32,768 to 32,767
Long 4 -2,147,483,648 to 2,147,483,647
Single 4 -3.402823E38 to -1.401298E-45 for negative values
1.401298E-45 to 3.40283E38 for positive values
Double 8 -1.79769313486232E308 to -4.94065645841247E-324 for negative values
4.94065645841247E-324 to 1.79769313486232E308 for positive values
Currency 8 -922,337,203,685,477.5808 to 922,337,203,685,477

158