VBA Programming Fundamentals

VBA Language Elements: An
Overview
In Chapter 7, I presented an overview of objects, properties,
and methods. But I didn’t tell you much about how to manipulate
objects so that they do meaningful things. This chapter
gently nudges you in that direction by exploring VBA’s language
elements, the keywords and control structures that you
use to write VBA routines.
To get the ball rolling, I’ll start by presenting a simple procedure.
The following procedure is stored in a VBA module, and
calculates the sum of the first 100 integers. When done, the
procedure displays a message with the result.
Sub VBA_Demo()
‘ This is a simple VBA Example
Dim Total As Integer, i As Integer
Total = 0
For i = 1 To 100
Total = Total + i
Next i
MsgBox Total
End SubThis procedure uses some common language elements, including a comment
(the line preceded by the apostrophe), a variable (Total), two assignment
statements (Total = 0 and Total = Total + i), a looping structure (For-Next),
and a VBA statement (MsgBox). All these are discussed in subsequent sections of
this chapter.
VBA procedures need not manipulate any objects. The preceding procedure, for
example, doesn’t do anything with objects. It simply works with numbers.
Comments
A comment is descriptive text embedded within your code. The text of a comment
is completely ignored by VBA. It’s a good idea to use comments liberally to
describe what you’re doing (an instruction’s purpose is not always obvious).
You can use a complete line for your comment, or you can insert a comment after
an instruction on the same line. A comment is indicated by an apostrophe. VBA
ignores any text that follows an apostrophe—except when the apostrophe is contained
within quotation marks—up until the end of the line. For example, the following
statement does not contain a comment, even though it has an apostrophe:
Msg = “Can’t continue”
The following example shows a VBA procedure with three comments:
Sub Comments()
‘ This procedure does nothing of value
x = 0 ‘x represents nothingness
‘ Display the result
MsgBox x
End Sub
Although the apostrophe is the preferred comment indicator, you can also use the
Rem keyword to mark a line as a comment. For example,
Rem -- The next statement prompts the user for a filename
The Rem keyword is essentially a holdover from old versions of BASIC; it is included
in VBA for the sake of compatibility. Unlike the apostrophe, Rem can be written only
at the beginning of a line, not on the same line as another instruction.
Using comments is definitely a good idea, but not all comments are equally beneficial.
To be useful, comments should convey information that’s not immediately
obvious from reading the code. Otherwise, you’re just chewing up valuable bytes.
The following procedure, for example, contains many comments, none of which
really adds anything of value:
Sub BadComments()
Note
Entering VBA Code
VBA code, which resides in a VBA module, consists of instructions. The accepted practice is to
use one instruction per line. This standard is not a requirement, however; you can use a colon
to separate multiple instructions on a single line. The following example combines four
instructions on one line:
Sub OneLine()
x= 1: y= 2: z= 3: MsgBox x + y + z
End Sub
Most programmers agree that code is easier to read if you use one instruction per line:
Sub OneLine()
x = 1
y = 2
z = 3
MsgBox x + y + z
End Sub
Each line can be as long as you like; the VBA module window scrolls to the left when you
reach the right side. For lengthy lines, you may want to use VBA’s line continuation sequence:
an underscore (_) preceded by a space. For example,
Sub LongLine()
SummedValue = _
Worksheets(“Sheet1”).Range(“A1”).Value + _
Worksheets(“Sheet2”).Range(“A1”).Value
End Sub
When you record macros, Excel often uses underscores to break long statements into multiple
lines.
After you enter an instruction, VBA performs the following actions to improve readability:
✦ It inserts spaces between operators. If you enter Ans=1+2 (without spaces), for example,
VBA converts it to
Ans = 1 + 2
✦ VBA adjusts the case of the letters for keywords, properties, and methods. If you enter
the following text
Result=activesheet.range(“a1”).value=12
VBA converts it to
Result = ActiveSheet.Range(“a1”).Value = 12
Notice that text within quotation marks (in this case, “a1”) is not changed.
Continued
‘ Declare variables
Dim x As Integer
Dim y As Integer
Dim z As Integer
‘ Start the routine
x = 100 ‘ Assign 100 to x
y = 200 ‘ Assign 200 to y
‘ Add x and y and store in z
z = x + y
‘ Show the result
MsgBox z
End Sub
Following are a few general tips on making the best use of comments:
✦ Use comments to describe briefly the purpose of each procedure you write.
✦ Use comments to describe changes you make to a procedure.
✦ Use comments to indicate that you’re using functions or constructs in an
unusual or nonstandard manner.
✦ Use comments to describe the purpose of variables so that you and other
people can decipher otherwise cryptic names.
✦ Use comments to describe workarounds that you develop to overcome Excel
bugs.
✦ Write comments as you code rather than after.
You may want to test a procedure without including a particular instruction or
group of instructions. Instead of deleting the instruction, simply turn it into a comment
by inserting an apostrophe at the beginning. VBA then ignores the instruction(
s) when the routine is executed. To convert the comment back to an
instruction, delete the apostrophe.
Tip
Continued
✦ Because VBA variable names are not case sensitive, the interpreter by default adjusts
the names of all variables with the same letters so that their case matches the case of
letters that you most recently typed. For example, if you first specify a variable as
myvalue (all lowercase) and then enter the variable as MyValue (mixed case), VBA
changes all other occurrences of the variable to MyValue. An exception occurs if you
declare the variable with Dim or a similar statement; in this case, the variable name
always appears as it was declared.
✦ VBA scans the instruction for syntax errors. If VBA finds an error, it changes the color of
the line and may display a message describing the problem. Use the VBE’s Tools➪
Options command to display the Options dialog box, where you control the error
color (use the Editor Format tab) and whether the error message is displayed (use the
Auto Syntax Check option in the Editor tab).
VBE’s Edit toolbar contains two very useful buttons. Select a group of instructions
and then use the Comment Block button to convert the instructions to comments.
The Uncomment Block button converts a group of comments back to instructions.
These buttons are very useful, so you may want to copy them to your Standard
toolbar.
Variables, Data Types, and Constants
VBA’s main purpose in life is to manipulate data. Some data resides in objects, such
as worksheet ranges. Other data is stored in variables that you create.
A variable is simply a named storage location in your computer’s memory. Variables
can accommodate a wide variety of data types—from simple Boolean values (True
or False) to large, double-precision values (see the following section). You assign a
value to a variable by using the equals sign operator (more about this later).
You’ll make your life easier if you get into the habit of making your variable names
as descriptive as possible. VBA does, however, have a few rules regarding variable
names:
✦ You can use alphabetic characters, numbers, and some punctuation characters,
but the first character must be alphabetic.
✦ VBA does not distinguish between case. To make variable names more readable,
programmers often use mixed case (for example, InterestRate rather
than interestrate).
✦ You cannot use spaces or periods. To make variable names more readable,
programmers often use the underscore character (Interest_Rate).
✦ Special type declaration characters (#, $, %, &, or !) cannot be embedded in a
variable name.
✦ Variable names may comprise as many as 254 characters—but no one in his
right mind would create a variable name that long!
The following list contains some examples of assignment expressions that use various
types of variables. The variable names are to the left of the equals sign. Each
statement assigns the value to the right of the equal sign to the variable on the left.
x = 1
InterestRate = 0.075
LoanPayoffAmount = 243089
DataEntered = False
x = x + 1
MyNum = YourNum * 1.25
UserName = “Bob Johnson”
DateStarted = #3/14/98#
VBA has many reserved words, which are words that you cannot use for variable or
procedure names. If you attempt to use one of these words, you get an error message.
For example, although the reserved word Next might make a very descriptive
variable name, the following instruction generates a syntax error:
Next = 132
Unfortunately, syntax error messages aren’t always very descriptive. The preceding
instruction generates this error message: Compile error: Expected variable. It
would be nice if the error message were something like Reserved word used as a
variable. So if an instruction produces a strange error message, check the online
help to make sure your variable name doesn’t have a special use in VBA.
Defining data types
VBA makes life easy for programmers because it can automatically handle all the
details involved in dealing with data. Not all programming languages make it so
easy. For example, some languages are strictly typed, which means that the programmer
must explicitly define the data type for every variable used.
Data type refers to how data is stored in memory—as integers, real numbers,
strings, and so on. Although VBA can take care of data typing automatically, it does
so at a cost: slower execution and less efficient use of memory. (There’s no such
thing as a free lunch.) As a result, letting VBA handle data typing may present problems
when you’re running large or complex applications. If you need to conserve
every last byte of memory, you need to be on familiar terms with data types. Another
advantage to explicitly declaring your variables as a particular data type is that
VBA can perform some additional error checking at the compile stage. These errors
might otherwise be difficult to locate.
Table 8-1 lists VBA’s assortment of built-in data types (note that you can also define
custom data types, which I describe later in this chapter).
Data Type Bytes Used Range of Values
Long 4 bytes –2,147,483,648 to 2,147,483,647
Single 4 bytes –3.402823E38 to –1.401298E–45 (for
negative values); 1.401298E–45 to
3.402823E38 (for positive values)
Double 8 bytes –1.79769313486232E308 to
–4.94065645841247E–324 (negative
values); 4.94065645841247E–324 to
1.79769313486232E308 (positive values)
Currency 8 bytes –922,337,203,685,477.5808 to
922,337,203,685,477.5807
Decimal 14 bytes +/–79,228,162,514,264,337,593,543,950,335
with no decimal point;
+/–7.9228162514264337593543950335
with 28 places to the right of the decimal
Date 8 bytes January 1, 0100 to December 31, 9999
Object 4 bytes Any object reference
String 10 bytes + string length 0 to approximately 2 billion
(variable-length)
String Length of string 1 to approximately 65,400
(fixed-length)
Variant 16 bytes Any numeric value up to the range of a
(with numbers) double data type
Variant 22 bytes + string length 0 to approximately 2 billion
(with characters)
User-defined Varies Varies by element
The Decimal data type was introduced in Excel 2000, and cannot be used in previous
versions. This is a rather unusual data type because you cannot actually
declare it. In fact, it is a “subtype” of a variant. You need to use VBA’s CDec function
to convert a variant to the decimal data type.
Generally, it’s best to use the data type that uses the smallest number of bytes yet
still can handle all the data assigned to it. When VBA works with data, execution
speed is a function of the number of bytes VBA has at its disposal. In other words,
the fewer bytes used by data, the faster VBA can access and manipulate the data.
For worksheet calculation, Excel uses the Double data type, so that’s a good choice
for processing numbers in VBA when you don’t want to lose any precision. For integer
calculations, you can use the Integer type if you’re sure that the values will not
Note
exceed 32,767. Otherwise, use the Long data type. When dealing with Excel worksheet
row numbers, you’ll want to use the Long data type because the number of
rows in a worksheet exceed the maximum value for the Integer data type.
Declaring variables
If you don’t declare the data type for a variable that you use in a VBA routine, VBA
uses the default data type, variant. Data stored as a variant acts like a chameleon:
It changes type, depending on what you do with it. The following procedure demonstrates
how a variable can assume different data types:
Sub VariantDemo()
MyVar = “123”
MyVar = MyVar / 2
MyVar = “Answer: “ & MyVar
MsgBox MyVar
End Sub
In the VariantDemo procedure, MyVar starts out as a three-character string. Then
this “string” is divided by two and becomes a numeric data type. Next, MyVar is
appended to a string, converting MyVar back to a string. The MsgBox statement
displays the final string: Answer: 61.5.
To further demonstrate the potential problems in dealing with variant data types,
try executing this procedure:
Sub VariantDemo2()
MyVar = “123”
MyVar = MyVar + MyVar
MyVar = “Answer: “ & MyVar
MsgBox MyVar
End Sub
The message box will display: Answer: 123123. This is probably not what you
wanted. When dealing with variants that contain text string, the + operator
performs string concatenation.
Determining a data type
You can use VBA’s TypeName function to determine the data type of a variable.
Here’s a modified version of the previous procedure. This version displays the data
type of MyVar at each step. You’ll see that it starts out as a string, then is converted
to a double, and finally ends up as a string again.
Sub VariantDemo2()
MyVar = “123”
MsgBox TypeName(MyVar)
MyVar = MyVar / 2
MsgBox TypeName(MyVar)
MyVar = “Answer: “ & MyVar
MsgBox TypeName(MyVar)
MsgBox MyVar
End Sub
Thanks to VBA, the data type conversion of undeclared variables is automatic. This
process may seem like an easy way out, but remember that you sacrifice speed and
memory.
Benchmarking Variant Data Types
To test whether data-typing is important, I developed the following routine, which performs
some meaningless calculations in a loop and then displays the procedure’s total execution
time:
Sub TimeTest()
Dim x As Integer, y As Integer
Dim A As Integer, B As Integer, C As Integer
Dim i As Integer, j As Integer
Dim StartTime As Date, EndTime As Date
‘ Store the starting time
StartTime = Timer
‘ Perform some calculations
x = 0
y = 0
For i = 1 To 5000
For j = 1 To 1000
A = x + y + i
B = y - x - i
C = x - y - i
Next j
Next i
‘ Get ending time
EndTime = Timer
‘ Display total time in seconds
MsgBox Format(EndTime - StartTime, “0.0”)
End Sub
On my system, this routine took 4.0 seconds to run (the time will vary, depending on your
system’s processor speed). I then commented out the Dim statements, which declare the
data types. That is, I turned the Dim statements into comments by adding an apostrophe at
the beginning of the lines. As a result, VBA used the default data type, variant. I ran the procedure
again. It took 8.3 seconds, more than twice as long as before.
The moral is simple: If you want your VBA applications to run as fast as possible, declare
your variables!
It’s an excellent habit to declare each variable in a procedure before you use it.
Declaring a variable tells VBA its name and data type. Declaring variables provides
two main benefits:
✦ Your programs run faster and use memory more efficiently. The default data
type, variant, causes VBA to repeatedly perform time-consuming checks and
reserve more memory than necessary. If VBA knows the data type, it doesn’t
have to investigate, and it can reserve just enough memory to store the data.
✦ You avoid problems involving misspelled variable names. This assumes that
you use Option Explict to force yourself to declare all variables (see the
next section). Say that you use an undeclared variable named CurrentRate.
At some point in your routine, however, you insert the statement CurentRate
= .075. This misspelled variable name, which is very difficult to spot, will
likely cause your routine to give incorrect results.
Forcing yourself to declare all variables
To force yourself to declare all the variables that you use, include the following as
the first instruction in your VBA module:
Option Explicit
This statement causes your program to stop whenever VBA encounters a variable
name that has not been declared. VBA issues an error message, and you must
declare the variable before you can proceed.
To ensure that the Option Explicit statement is automatically inserted whenever
you insert a new VBA module, enable the Require Variable Declaration option
in the Editor tab of the VBE’s Options dialog box. I highly recommend doing so.
Scoping variables
A variable’s scope determines which modules and procedures the variable can be
used in. A variable’s scope can be any of the following:
Local variables
A local variable is a variable declared within a procedure. Local variables can be
used only in the procedure in which they are declared. When the procedure ends,
the variable no longer exists, and Excel frees up its memory.
If you need the variable to retain its value, declare it as a Static variable (see
“Static variables” later in this section).
The most common way to declare a local variable is to place a Dim statement
between a Sub statement and an End Sub statement. Dim statements usually are
placed right after the Sub statement, before the procedure’s code.
If you’re curious about this word, Dim is a shortened form of Dimension. In old versions
of BASIC, this statement was used exclusively to declare the dimensions for
an array. In VBA, the Dim keyword is used to declare any variable, not just arrays.
The following procedure uses six local variables declared using Dim statements:
Sub MySub()
Dim x As Integer
Dim First As Long
Dim InterestRate As Single
Dim TodaysDate As Date
Dim UserName As String * 20
Dim MyValue
‘ - [The procedure’s code goes here] -
End Sub
Notice that the last Dim statement in the preceding example doesn’t declare a data
type; it simply names the variable. As a result, that variable becomes a variant.
By the way, you also can declare several variables with a single Dim statement. For
example,
Dim x As Integer, y As Integer, z As Integer
Dim First As Long, Last As Double
Unlike some languages, VBA does not let you declare a group of variables to be a
particular data type by separating the variables with commas. For example, the following
statement, although valid, does not declare all the variables as integers:
Dim i, j, k As Integer
In VBA, only k is declared to be an integer; the other variables are declared variants.
To declare i, j, and k as integers, use this statement:
Dim i As Integer, j As Integer, k As Integer
If a variable is declared with a local scope, other procedures in the same module
can use the same variable name, but each instance of the variable is unique to its
own procedure.
In general, local variables are the most efficient because VBA frees up the memory
they use when the procedure ends.
Caution
Another Way of Data-Typing Variables
Like most other dialects of BASIC, VBA lets you append a character to a variable’s name to
indicate the data type. For example, you can declare the MyVar variable as an integer by
tacking % onto the name:
Dim MyVar%
Type-declaration characters exist for most of VBA’s data types (data types not listed don’t
have type-declaration characters).
Data Type Type-Declaration Character
Integer %
Long &
Single !
Double #
Currency @
String $
This method of data typing is essentially a holdover from BASIC; it’s better to declare your
variables using the techniques described in this chapter.
Modulewide variables
Sometimes, you’ll want a variable to be available to all procedures in a module. If
so, just declare the variable before the module’s first procedure (outside of any procedures
or functions).
In the following example, the Dim statement is the first instruction in the module.
Both MySub and YourSub have access to the CurrentValue variable.
Dim CurrentValue as Integer
Sub MySub()
‘ - [Code goes here] -
End Sub
Sub YourSub()
‘ - [Code goes here] -
End Sub
The value of a modulewide variable does not change when a procedure ends.
Public variables
To make a variable available to all the procedures in all the VBA modules in a project,
declare the variable at the module level by using the Public keyword rather
than Dim. Here’s an example:
Public CurrentRate as Long
The Public keyword makes the CurrentRate variable available to any procedure
in the project, even those in other modules within the project. You must insert this
statement before the first procedure in a module. This type of declaration must also
appear in a standard VBA module, not in a code module for a sheet or a UserForm.
Static variables
Static variables are a special case. They are declared at the procedure level, and
they retain their value when the procedure ends.
You declare static variables using the Static keyword:
Sub MySub()
Static Counter as Integer
- [Code goes here] -
End Sub