Programming Fundamentals Using VBA - Declaring Variables

(Page 3 of 9 )

Within a procedure, you will find two basic components: variables and methods. Quite simply, a variable is a piece of information stored somewhere in the computer’s memory. It could be a number, a letter, or an entire sentence. The location where it is stored in memory is known by the variable name. As an example, let’s say that you have a line of code like this:

number = 23

From that point on, every time the code refers to the variable name number, 23 will be put in its place. Of course, later on, you could assign a different value to the variable name of number. This example is a simplification.

In order for the variable to function properly, you should also declare what type of information is going to be kept by the variable. (The reasons for this will become obvious as we progress.) Table 6-1 lists variable types and how they are used.

To declare a variable, you use the keyword Dim. As an example:

Dim number As Integer

This declares that the variable name number will hold only data of type Integer. You will notice that we have not assigned a value yet (and in fact the editor will not allow us to do so). We only declared the name and type. At some future point in the code, we would have a line like this:

number = 32

NOTE

---

Remember that VBA is not case sensitive. The variable names number, Number, and numBer would all be recognized as the same name.

---

Variable Type	Description
Boolean	A Boolean type returns True or False. You could also frame it in terms of the numbers, with 0 = False and -1 = True.
Byte	This is one of the least used of the variable types. It can only hold a single value between 0 and 255.
Currency	This does just what it says. It holds a currency value with four decimal places, from -922,337,203,685,477.5808 to 922,337,203,685,477.5807.
Date	This stores both dates and time. Interestingly, the years range from 100 to 9999.
Double	This is one of the two variable types that use decimal points. The other is Single. Doubles are for very large numbers. The range runs from -4.940656458411247 * 10-324 to 4.94065645841247 * 10-324 .
Integer	This is one of the two nondecimal variable types. The other is Long. Integer handles the range of numbers -32,768 to 32,767.
Long	Long is the other of the two nondecimal variable types, with Integer being the first. Long handles the number range -2,147,483,648 to 2,147,483,657.
Object	You can store an object as a variable for later use.
Single	This is the second decimal point type, the other being Double.
String	This is the most common variable type. A String variable can hold up to 2 billion characters.

Table 6-1  Types of Variables

There are a couple of things you should be aware of when assigning values to a variable. First of all, a variable of type String must have its value enclosed in quotation marks. For instance, this would be a proper assignment:

Dim lastName as String
lastName = “Smith”

The second thing you must be aware of is that variables of type Date must have their values enclosed in # signs. A proper assignment would be as follows:

Dim thisDate as Date
thisDate = #10/08/03#

Chapter 5 discussed naming conventions in conjunction with the objects of your database. The same rules apply to variables. Remember, these are not requirements, but conventions adopted by most programmers. The prefixes associated with variables are listed in Table 6-2.

It is a good idea when naming objects of any kind to use descriptive names. This will have the benefit of making the code self-documenting. Using the date example, the proper way of declaring the variable would be as follows:

Dim datThisDate as Date

Getting back to the original procedure, addNumbers, let’s go ahead and add three variables as shown here:

Sub addNumbers()
   'Declare the variables
  Dim intNumber1 As Integer
  Dim intNumber2 As Integer
  Dim intSum As Integer
End Sub

You should be able to see where this is going. There will be two numbers entered and stored in variables intNumber1 and intNumber2. They will be added and stored in intSum.

Variable Type	Prefix
Boolean	bln
Byte	byt
Currency	cur
Date	dat
Double	dbl
Integer	int
Long	lng
Object	obj
Single	sng
String	str

Table 6-2  Variable Prefixes

Variant

There is one other variable type that hasn’t been discussed yet: the Variant. Depending on which programmer you talk to, it is either a powerful programming tool or an excuse for sloppy programming.

A Variant allows VBA to make its own decision as to what type of data it is holding. It is the default variable type and is used automatically if you leave the “as type” clause off the variable’s declaration. It uses the prefix of var.

As an example, let’s say we declare a variable as follows:

Dim varMyData

Because we left out the “as type” parameter, this defaults to the type Variant and will be the same as if you typed:

Dim varMyData as Variant

Let’s say you assign it as follows:

varMyData = “This is a Variant”

VBA will convert varMyData into a String type. If, later on, you assign the following:

varMyData = 12

VBA will now convert varMyData into type Integer.

As we progress, you will see situations where a variant could end up being a type other than what was wanted or, even worse, could result in an error. Many programmers also argue that too many variants take up too much memory “overhead” and slow the code down. So before you make the decision to work with variants, you want to carefully weigh the pros and cons.

As a beginner, it is best to approach variants with great caution and stay with one of the standard type declarations.

Next: Constants >>
 

More Visual Basic.NET Articles
More By McGraw-Hill/Osborne

Comments On: Programming Fundamentals Using VBA

>>> Be the FIRST to comment on this article!

Buy this book...

This article is excerpted from chapter six of the book Access VBA Programming, written by Charles E. Brown and Ron Petusha (McGraw-Hill/Osborne, 2004; ISBN: 0072231971). Check it out at your favorite bookstore today. Buy this book now.