Excel VBA 24-Hour Trainer, 2nd Edition

Lesson 6Variables, Data Types, and Constants

Many of the macros you develop will involve the need for referencing an item you are working on without specifying that item by its name, amount, or location. This concept may sound strange at first, but you will quickly discover with your macros that in many situations it makes sense, and indeed is necessary, to manipulate or analyze data in one part of your macro, and hold the results in virtual memory for later use.

What is a Variable?

VBA stores data in memory using a variable. A variable is a name given by you, to which you assign a piece of data that is stored in an area of the computer's memory, allowing you to refer to that data when you need to later in the macro. VBA handles the task of finding an appropriate place in the computer's memory to store your variable data, and dutifully retrieves the data when you ask for it by its variable name.

Variables hold values of different data types (more on this later) that are specified when the variable is declared. When you declare a variable, you do so by entering a declaration statement that includes four keywords in a particular order:

1. The Dim statement (VBA's abbreviation for Dimension), which all variable declarations start with.
2. The name of your variable, which you create, such as myValue.
3. The word As.
4. The type of data being stored.

One common data type is called Integer, which, as you see in Table 6.1, refers to whole numbers within a certain range. Using the preceding ...