VBA Programming Examples and Techniques
Working with Ranges
The examples in this section demonstrate how to manipulate worksheet ranges
with VBA.
The examples in this section are available on the companion CD-ROM.
Copying a range
Excel’s macro recorder is useful not so much for generating usable code as for
discovering the names of relevant objects, methods, and properties. The code
that’s generated by the macro recorder isn’t always the most efficient, but it can
usually provide you lots of useful insights.
For example, recording a simple copy-and-paste operation generates five lines of
VBA code:
Sub Macro1()
Range(“A1”).Select
Selection.Copy
Range(“B1”).Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub
Notice that the generated code selects the cells. But in VBA, it’s not necessary to
select an object to work with it. You would never learn this important point by
mimicking the preceding recorded macro code, where two lines incorporate the
Select method. This procedure can be replaced with the following much simpler
routine, which takes advantage of the fact that the Copy method can use an argument
that represents the destination for the copied range:
Sub CopyRange()
Range(“A1”).Copy Range(“B1”)
End Sub
On the
CD-ROM
Both of these macros assume that a worksheet is active and that the operation
takes place on the active worksheet. To copy a range to a different worksheet or
workbook, simply qualify the range reference for the destination. The following
example copies a range from Sheet1 in File1.xls to Sheet2 in File2.xls. Because the
references are fully qualified, this example works regardless of which workbook
is active.
Sub CopyRange2()
Workbooks(“File1.xls”).Sheets(“Sheet1”).Range(“A1”).Copy _
Workbooks(“File2.xls”).Sheets(“Sheet2”).Range(“A1”)
End Sub
Another way to approach this task is to use object variables to represent the
ranges, as the following example demonstrates:
Sub CopyRange3()
Set Rng1 = Workbooks(“File1.xls”). _
Sheets(“Sheet1”).Range(“A1”)
Set Rng2 = Workbooks(“File2.xls”). _
Sheets(“Sheet2”).Range(“A1”)
Rng1.Copy Rng2
End Sub
As you might expect, copying is not limited to one single cell at a time. The following
procedure, for example, copies a large range. Notice that the destination consists of
only a single cell (which represents the upper left cell for the destination).
Sub CopyRange4()
Range(“A1:C800”).Copy Range(“D1”)
End Sub
Moving a range
The VBA instructions for moving a range are very similar to those for copying a
range, as the following example demonstrates. The difference is that you use the
Cut method instead of the Copy method. Note that you need to specify only the
upper-left cell for the destination range.
The following example moves 18 cells (in A1:C6) to a new location, beginning at
cell H1:
Sub MoveRange1()
Range(“A1:C6”).Cut Range(“H1”)
End Sub
Copying a variably sized range
In many cases, you need to copy a range of cells, but you don’t know the exact row
and column dimensions of the range. For example, you might have a workbook that
tracks weekly sales. The number of rows changes weekly as you add new data.
Figure 11-1 shows a very common type of worksheet. This range consists of several
rows, and the number of rows changes each week. Because you don’t know the
exact range address at any given time, writing a macro to copy the range requires
some additional coding.
Figure 11-1: This range can consist of any number of rows.
The following macro demonstrates how to copy this range from Sheet1 to Sheet2
(beginning at cell A1). It uses the CurrentRegion property, which returns a Range
object that corresponds to the block of cells around a particular cell (in this
case, A1).
Sub CopyCurrentRegion2()
Range(“A1”).CurrentRegion.Copy _
Sheets(“Sheet2”).Range(“A1”)
End Sub
Using the CurrentRegion property is equivalent to choosing the Edit➪Go To
command, clicking the Special button, and selecting the Current Region option. To
see how this works, record your actions while you issue that command. Generally,
the CurrentRegion property setting consists of a rectangular block of cells
surrounded by one or more blank rows or columns.
Selecting or otherwise identifying various
types of ranges
Much of the work you will do in VBA will involve working with ranges—either
selecting a range or identifying a range so you can do something with the cells.
In previous versions of Excel, recording a macro that selects cells (such as
Ctrl+Shift+→) was a hit or miss proposition. The macro recorder in Excel 2002
seems to handle these types of selections much better than in previous versions.
However, it’s always a good idea to check your recorded code very carefully to
make sure that the selection code works as you intended.
In addition to the CurrentRegion property (discussed above), you should also be
aware of the End method of the Range object. The End method takes one argument,
Feature
Note
which determines the direction in which the selection is extended. The following
statement selects a range from the active cell to the last nonempty cell:
Range(ActiveCell, ActiveCell.End(xlDown)).Select
As you may expect, three other constants simulate key combinations in the other
directions: xlUp, xlToLeft, and xlToRight.
Be careful when using the End method. If the active cell is at the perimeter of a
range, or the range contains one or more empty cells, the End method may not
produce the desired results.
The companion CD-ROM includes a workbook that demonstrates several common
types of range selections. When you open this workbook, you’ll see a new menu
command, Selection Demo. This menu contains commands that enable the user to
make various types of selections, as shown in Figure 11-2.
Figure 11-2: This workbook demonstrates how to select variably sized ranges
using VBA.
The following macro is in the example workbook. The SelectCurrentRegion
macro simulates pressing Ctrl+Shift+*.
Sub SelectCurrentRegion()
ActiveCell.CurrentRegion.Select
End Sub
Caution
Often, you won’t want to actually select the cells. Rather, you’ll want to work with
them in some way (for example, format them). The cell-selecting procedures can
easily be adapted. The following procedure was adapted from SelectCurrentRegion.
This procedure doesn’t select cells; it creates a Range object and then applies
formatting to the range. The other procedures in the example workbook can also
be adapted in this manner.
Sub FormatCurrentRegion()
Set WorkRange = ActiveCell.CurrentRegion
WorkRange.Font.Bold = True
End Sub
Prompting for a cell value
The following procedure demonstrates how to ask the user for a value and then
insert it into cell A1 of the active worksheet:
Sub GetValue1()
Range(“A1”).Value = InputBox(“Enter the value”)
End Sub
Figure 11-3 shows how the input box looks.
Figure 11-3: The InputBox function gets a
value from the user to be inserted into a cell.
This procedure has a problem, however. If the user clicks the Cancel button in the
input box, the procedure deletes any data already in the cell. The following modification
checks for the Cancel button clicks and takes no action:
Sub GetValue2()
UserEntry = InputBox(“Enter the value”)
If UserEntry <> “” Then Range(“A1”).Value = UserEntry
End Sub
In many cases, you’ll need to validate the user’s entry in the input box. For example,
you may require a number between 1 and 12. The following example demonstrates
one way to validate the user’s entry. In this example, an invalid entry is ignored and
the input box is displayed again. This cycle keeps repeating until the user enters a
valid number or clicks Cancel.
Sub GetValue3()
Dim MinVal As Integer, MaxVal As Integer
Dim UserEntry As String
Dim Msg As String
Dim IntEntry As Integer
MinVal = 1
MaxVal = 12
Msg = “Enter a value between “ & MinVal & “ and “ & MaxVal
Do
UserEntry = InputBox(Msg)
If UserEntry = “” Then Exit Sub
If IsNumeric(UserEntry) Then
IntEntry = CInt(UserEntry)
If IntEntry >= MinVal And IntEntry <= MaxVal Then
Exit Do
End If
End If
Msg = “Your previous entry was INVALID.”
Msg = Msg & vbNewLine
Msg = Msg & “Enter a value between “ & _
MinVal & “ and “ & MaxVal
Loop
ActiveSheet.Range(“A1”).Value = UserEntry
End Sub
Notice that the loop continues indefinitely. I use Exit Sub statements to get out of
the loop when the user clicks Cancel.
To keep things simple, this procedure doesn’t perform any validation.
Notice the statement that determines the value of the NextRow variable. If you don’t
understand how this works, try the manual equivalent: Activate cell A65536 (the
last cell in column A). Then press End, followed by Up Arrow. At this point, the last
nonblank cell in column A will be selected. The Row property returns this row number,
and it is incremented by 1 in order to get the row of the cell below it (the next
empty row).
Note that this technique of selecting the next empty cell has a slight glitch. If the
column is completely empty, it will calculate Row 2 as the next empty row.
Pausing a macro to get a user-selected range
You may create a macro that needs to pause while the user specifies a range of
cells. The procedure in this section describes how to do this using Excel’s
InputBox function.
Do not confuse Excel’s InputBox function with VBA’s InputBox function.
Although these two functions have the same name, they are not the same.
The Sub procedure that follows demonstrates how to pause a macro and let the
user select a cell:
Sub GetUserRange()
Dim UserRange As Range
Output = 565
Prompt = “Select a cell for the output.”
Title = “Select a cell”
‘ Display the Input Box
On Error Resume Next
Set UserRange = Application.InputBox( _
Prompt:=Prompt, _
Title:=Title, _
Default:=ActiveCell.Address, _
Type:=8) ‘Range selection
On Error GoTo 0
Note
Note
If UserRange Is Nothing Then
MsgBox “Canceled.”
Else
UserRange.Range(“A1”) = Output
End If
End Sub
The input box is shown in Figure 11-6.
Figure 11-6: Using an input box
to pause a macro
Specifying a Type argument of 8 is the key to this procedure. Also, note the use of
On Error Resume Next. This statement ignores the error that occurs if the user
clicks the Cancel button. If so, the UserRange object variable is not defined. This
example displays a message box with the text Canceled. If the user clicks OK, the
macro continues. Using On Error GoTo 0 resumes normal error handling.
By the way, it’s not necessary to check for a valid range selection. Excel takes care
of this for you.
Make sure ScreenUpdating is turned on. Otherwise, you won’t be able to select a
cell.
Counting selected cells
You may create a macro that works with the selected range of cells. You can use the
Count property of the Range object to determine how many cells are contained in a
range selection (or any range, for that matter). For example, the following statement
displays a message box that contains the number of cells in the current selection:
MsgBox Selection.Count
If the active sheet contains a range named data, the following statement assigns
the number of cells in the data range to a variable named CellCount:
CellCount = Range(“data”).Count
You can also determine how many rows or columns are contained in a range. The
following expression calculates the number of columns in the currently selected
range:
Selection.Columns.Count
And, of course, you can also use the Rows property to determine the number of
rows in a range. The following statement counts the number of rows in a range
named data and assigns the number to a variable named RowCount:
RowCount = Range(“data”).Rows.Count
Sunday, December 9, 2007
Working with VBA Sub Procedures
About Procedures
A procedure is a series of VBA statements that resides in a
VBA module, which you access in the VBE. A module can hold
any number of procedures.
You have a number of ways to call, or execute, procedures. A
procedure is executed from beginning to end (but it can also
be ended prematurely).
A procedure can be any length, but many people prefer
to avoid creating extremely long procedures that perform
many different operations. You may find it easier to write
several smaller procedures, each with a single purpose.
Then design a main procedure that calls those other
procedures. This approach can make your code easier to
maintain.
Some procedures are written to receive arguments. An argument
is simply information that is used by the procedure that
is “passed” to the procedure when it is executed. Procedure
arguments work much like the arguments you use in Excel
worksheet functions. Instructions within the procedure
generally perform logical operations on these arguments, and the results of the
procedure are usually based on those arguments.
Declaring a Sub procedure
A procedure declared with the Sub keyword must adhere to the following syntax:
[Private | Public][Static] Sub name ([arglist])
[instructions]
[Exit Sub]
[instructions]
End Sub
Private (Optional) Indicates that the procedure is accessible only to other
procedures in the same module.
Public (Optional) Indicates that the procedure is accessible to all other
procedures in all other modules in the workbook. If used in a module that
contains an Option Private Module statement, the procedure is not available
outside the project.
Static (Optional) Indicates that the procedure’s variables are preserved
when the procedure ends.
Sub (Required) The keyword that indicates the beginning of a procedure.
name (Required) Any valid procedure name.
arglist (Optional) Represents a list of variables, enclosed in parentheses,
that receive arguments passed to the procedure. Use a comma to separate
arguments. If the procedure uses no arguments, a set of empty parentheses is
required.
instructions (Optional) Represents valid VBA instructions.
Exit Sub (Optional) A statement that forces an immediate exit from the
procedure prior to its formal completion.
End Sub (Required) Indicates the end of the procedure.
With a few exceptions, all VBA instructions in a module must be contained within
procedures. Exceptions include module-level variable declarations, user-defined
data type definitions, and a few other instructions that specify module-level
options (for example, Option Explicit).
Note
Scoping a procedure
In the preceding chapter, I noted that a variable’s scope determines the modules
and procedures in which the variable can be used. Similarly, a procedure’s scope
determines which other procedures can call it.
Public procedures
By default, procedures are public—that is, they can be called by other procedures
in any module in the workbook. It’s not necessary to use the Public keyword, but
programmers often include it for clarity. The following two procedures are both
public:
Sub First()
‘ ... [code goes here] ...
End Sub
Public Sub Second()
‘ ... [code goes here] ...
End Sub
Private procedures
Private procedures can be called by other procedures in the same module, but not
by procedures in other modules.
When you choose Excel’s Tools➪Macro➪Macros command, the Macro dialog box
displays only the public procedures. Therefore, if you have procedures that are
designed to be called only by other procedures in the same module, you should
make sure that the procedure is declared as Private. This prevents the user from
running the procedure from the Macro dialog box.
The following example declares a private procedure, named MySub:
Private Sub MySub()
‘ ... [code goes here] ...
End Sub
Note
Naming Procedures
Every procedure must have a name. The rules governing procedure names are generally the
same as for variable names. Ideally, a procedure’s name should describe what its contained
processes do. A good rule of thumb is to use a name that includes a verb and a noun (for
example, ProcessDate, PrintReport, Sort_Array, or CheckFilename). Avoid meaningless
names such as DoIt, Update, and Fix.
Some programmers use sentence-like names that describe the procedure (for example,
WriteReportToTextFile and Get_Print_Options_ and_Print_Report). Although
long names are very descriptive and unambiguous, they are also more difficult to type.
You can force all procedures in a module to be private—even those declared with
the Public keyword—by including the following statement before your first Sub
statement:
Option Private Module
If you write this statement in a module, you can omit the Private keyword from
your Sub declarations.
Excel’s macro recorder normally creates new Sub procedures called Macro1,
Macro2, and so on. These procedures are all public procedures, and they will
never use any arguments.
Executing Procedures
In this section I describe the many ways to execute, or call, a VBA Sub procedure:
✦ With the Run➪Run Sub/UserForm command (in the VBE). Or you can press
the F5 shortcut key. Excel executes the procedure at the cursor position. This
method doesn’t work if the procedure requires one or more arguments.
✦ From Excel’s Macro dialog box (which you open by choosing Tools➪Macro➪
Macros). Or you can press the Alt+F8 shortcut key to access the Macro
dialog box.
✦ Using the Ctrl key shortcut assigned to the procedure (assuming you
assigned one).
✦ By clicking a button or a shape on a worksheet. The button or shape must
have the procedure assigned to it.
✦ From another procedure you write.
✦ From a Toolbar button.
✦ From a custom menu that you develop.
✦ When an event occurs. These events include opening the workbook, saving
the workbook, closing the workbook, making a change to a cell, activating a
sheet, and many other things.
✦ From the Immediate window in the VBE. Just type the name of the procedure,
including any arguments that may apply, and press Enter.
Excel 5 and Excel 95 made it very easy to assign a macro to a new menu item on
the Tools menu. This feature was removed beginning with Excel 97.
I discuss these methods of executing procedures in the following sections.
In many cases, a procedure will not work properly unless it is in the appropriate
context. For example, if a procedure is designed to work with the active worksheet,
it will fail if a chart sheet is active. A good procedure incorporates code that checks
for the appropriate context and exits gracefully if it can’t proceed.
Executing a procedure with the Run➪Run Sub/
UserForm command
VBE’s Run➪Run Sub/UserForm menu command is used primarily to test a procedure
while you are developing it. You would never expect a user to have to activate
the VBE to execute a procedure. Use the Run➪Run Sub/UserForm command (or
F5) in the VBE to execute the current procedure (in other words, the procedure
that contains the cursor).
If the cursor is not located within a procedure when you issue the Run➪Run Sub/
UserForm command, VBE displays its Macro dialog box so that you can select a
procedure to execute.
Executing a procedure from the Macro dialog box
Choosing Excel’s Tools➪Macro➪Macros command displays the Macro dialog
box, shown in Figure 9-1 (you can also press Alt+F8 to access this dialog box). The
Macro dialog box lists all available Sub procedures. Use the Macros In drop-down
box to limit the scope of the macros displayed (for example, show only the macros in
the active workbook). The Macro dialog box does not display Function procedures. In
addition, it does not display Sub procedures declared with the Private keyword,
Sub procedures that require one or more arguments, or Sub procedures contained
in add-ins.
Procedures stored in an add-in are not listed in the Macro dialog box, but you still
can execute such a procedure if you know the name. Simply type the procedure
name into the Macro name field in the Macro dialog box, and click Run.
Figure 9-1: The Macro dialog box lists all
available procedures.
Tip
Executing a procedure using a Ctrl+
shortcut key combination
You can assign a Ctrl+shortcut key combination to any procedure that doesn’t use
any arguments. If you assign the Ctrl+U key combo to a procedure named Update,
for example, pressing Ctrl+U executes the Update procedure.
When you begin recording a macro, the Record Macro dialog box gives you the
opportunity to assign a shortcut key. However, you can assign a shortcut key at any
time. To assign a Ctrl shortcut key to a procedure (or change a procedure’s shortcut
key), follow these steps:
1. Activate Excel and choose the Tools➪Macro➪Macros command.
2. Select the appropriate procedure from the list box in the Macro dialog box.
3. Click the Options button to display the Macro Options dialog box (see
Figure 9-2).
Figure 9-2: The Macro Options
dialog box lets you assign a Ctrl
key shortcut and an optional
description to a procedure.
4. Enter a character into the text box labeled Ctrl+.
The character that you enter into the text box labeled Ctrl+ is case-sensitive.
If you enter a lowercase s, the shortcut key combo is Ctrl+S. If you enter an
uppercase S, the shortcut key combo is Ctrl+Shift+S.
5. Enter a description (optional). If you enter a description for a macro, it is displayed
at the bottom of the Macro dialog box when the procedure is selected
in the list box.
6. Click OK to close the Macro Options dialog box, and click Close to close the
Macro dialog box.
If you assign one of Excel’s predefined shortcut key combinations to a procedure,
your key assignment takes precedence over the predefined key assignment. For
example, Ctrl+S is Excel’s predefined shortcut key for saving the active workbook.
But if you assign Ctrl+S to a procedure, pressing Ctrl+S no longer saves the active
workbook.
The following Ctrl+key combinations are not used by Excel: E, J, L, M, Q, and T.
Excel doesn’t use too many Ctrl+Shift+key combinations. In fact, you can safely
use any of them except F, O, and P.
Executing a procedure from a custom menu
As I describe in Chapter 23, Excel provides two ways for you to customize its menus:
using the View➪Toolbars➪Customize command or writing VBA code. The latter
method is preferable, but you can use either technique to assign a macro to a new
menu item.
Excel 5 and Excel 95 include a menu editor, which was removed beginning with
Excel 97.
Following are the steps required to display a new menu item on a menu and to
assign a macro to the menu item. It assumes that the new menu item is on the Data
menu, that the menu item text is Open Customer File, and that the procedure is
named OpenCustomerFile.
1. Choose the View➪Toolbars➪Customize command. Excel displays the
Customize dialog box.
When the Customize dialog box is displayed, Excel is in a special “customization”
mode. The menus and toolbars are not active, but they can be
customized.
2. Click the Commands tab in the Customize dialog box.
3. Scroll down and click Macros in the Categories list.
4. In the Commands list, drag the first item (labeled Custom Menu Item) to the
bottom of the Data menu (after the Refresh Data menu item). The Data menu
drops down when you click it.
5. Right-click the new menu item (which is labeled Custom Menu Item) to
display a shortcut menu.
6. Enter a new name for the menu item: &Open Customer File in the text box
labeled Name (see Figure 9-3).
Note
Tip
7. Click Assign Macro on the shortcut menu.
8. In the Assign Macro dialog box, select the OpenCustomerFile procedure from
the list of macros.
9. Click OK to close the Assign Macro dialog box, and click Close to close the
Customize dialog box.
After you follow the process mentioned above, the new menu item always
appears on the menu, even when the workbook that contains the macro is not
open. In other words, changes you make using the View➪Toolbars➪Customize
command are “permanent.” Selecting the new menu item opens the workbook if
it’s not already open.
Refer to Chapter 23 to learn how to use VBA to create menu items that are
displayed only when a particular workbook is open.
Cross-
Reference
Caution
A procedure is a series of VBA statements that resides in a
VBA module, which you access in the VBE. A module can hold
any number of procedures.
You have a number of ways to call, or execute, procedures. A
procedure is executed from beginning to end (but it can also
be ended prematurely).
A procedure can be any length, but many people prefer
to avoid creating extremely long procedures that perform
many different operations. You may find it easier to write
several smaller procedures, each with a single purpose.
Then design a main procedure that calls those other
procedures. This approach can make your code easier to
maintain.
Some procedures are written to receive arguments. An argument
is simply information that is used by the procedure that
is “passed” to the procedure when it is executed. Procedure
arguments work much like the arguments you use in Excel
worksheet functions. Instructions within the procedure
generally perform logical operations on these arguments, and the results of the
procedure are usually based on those arguments.
Declaring a Sub procedure
A procedure declared with the Sub keyword must adhere to the following syntax:
[Private | Public][Static] Sub name ([arglist])
[instructions]
[Exit Sub]
[instructions]
End Sub
Private (Optional) Indicates that the procedure is accessible only to other
procedures in the same module.
Public (Optional) Indicates that the procedure is accessible to all other
procedures in all other modules in the workbook. If used in a module that
contains an Option Private Module statement, the procedure is not available
outside the project.
Static (Optional) Indicates that the procedure’s variables are preserved
when the procedure ends.
Sub (Required) The keyword that indicates the beginning of a procedure.
name (Required) Any valid procedure name.
arglist (Optional) Represents a list of variables, enclosed in parentheses,
that receive arguments passed to the procedure. Use a comma to separate
arguments. If the procedure uses no arguments, a set of empty parentheses is
required.
instructions (Optional) Represents valid VBA instructions.
Exit Sub (Optional) A statement that forces an immediate exit from the
procedure prior to its formal completion.
End Sub (Required) Indicates the end of the procedure.
With a few exceptions, all VBA instructions in a module must be contained within
procedures. Exceptions include module-level variable declarations, user-defined
data type definitions, and a few other instructions that specify module-level
options (for example, Option Explicit).
Note
Scoping a procedure
In the preceding chapter, I noted that a variable’s scope determines the modules
and procedures in which the variable can be used. Similarly, a procedure’s scope
determines which other procedures can call it.
Public procedures
By default, procedures are public—that is, they can be called by other procedures
in any module in the workbook. It’s not necessary to use the Public keyword, but
programmers often include it for clarity. The following two procedures are both
public:
Sub First()
‘ ... [code goes here] ...
End Sub
Public Sub Second()
‘ ... [code goes here] ...
End Sub
Private procedures
Private procedures can be called by other procedures in the same module, but not
by procedures in other modules.
When you choose Excel’s Tools➪Macro➪Macros command, the Macro dialog box
displays only the public procedures. Therefore, if you have procedures that are
designed to be called only by other procedures in the same module, you should
make sure that the procedure is declared as Private. This prevents the user from
running the procedure from the Macro dialog box.
The following example declares a private procedure, named MySub:
Private Sub MySub()
‘ ... [code goes here] ...
End Sub
Note
Naming Procedures
Every procedure must have a name. The rules governing procedure names are generally the
same as for variable names. Ideally, a procedure’s name should describe what its contained
processes do. A good rule of thumb is to use a name that includes a verb and a noun (for
example, ProcessDate, PrintReport, Sort_Array, or CheckFilename). Avoid meaningless
names such as DoIt, Update, and Fix.
Some programmers use sentence-like names that describe the procedure (for example,
WriteReportToTextFile and Get_Print_Options_ and_Print_Report). Although
long names are very descriptive and unambiguous, they are also more difficult to type.
You can force all procedures in a module to be private—even those declared with
the Public keyword—by including the following statement before your first Sub
statement:
Option Private Module
If you write this statement in a module, you can omit the Private keyword from
your Sub declarations.
Excel’s macro recorder normally creates new Sub procedures called Macro1,
Macro2, and so on. These procedures are all public procedures, and they will
never use any arguments.
Executing Procedures
In this section I describe the many ways to execute, or call, a VBA Sub procedure:
✦ With the Run➪Run Sub/UserForm command (in the VBE). Or you can press
the F5 shortcut key. Excel executes the procedure at the cursor position. This
method doesn’t work if the procedure requires one or more arguments.
✦ From Excel’s Macro dialog box (which you open by choosing Tools➪Macro➪
Macros). Or you can press the Alt+F8 shortcut key to access the Macro
dialog box.
✦ Using the Ctrl key shortcut assigned to the procedure (assuming you
assigned one).
✦ By clicking a button or a shape on a worksheet. The button or shape must
have the procedure assigned to it.
✦ From another procedure you write.
✦ From a Toolbar button.
✦ From a custom menu that you develop.
✦ When an event occurs. These events include opening the workbook, saving
the workbook, closing the workbook, making a change to a cell, activating a
sheet, and many other things.
✦ From the Immediate window in the VBE. Just type the name of the procedure,
including any arguments that may apply, and press Enter.
Excel 5 and Excel 95 made it very easy to assign a macro to a new menu item on
the Tools menu. This feature was removed beginning with Excel 97.
I discuss these methods of executing procedures in the following sections.
In many cases, a procedure will not work properly unless it is in the appropriate
context. For example, if a procedure is designed to work with the active worksheet,
it will fail if a chart sheet is active. A good procedure incorporates code that checks
for the appropriate context and exits gracefully if it can’t proceed.
Executing a procedure with the Run➪Run Sub/
UserForm command
VBE’s Run➪Run Sub/UserForm menu command is used primarily to test a procedure
while you are developing it. You would never expect a user to have to activate
the VBE to execute a procedure. Use the Run➪Run Sub/UserForm command (or
F5) in the VBE to execute the current procedure (in other words, the procedure
that contains the cursor).
If the cursor is not located within a procedure when you issue the Run➪Run Sub/
UserForm command, VBE displays its Macro dialog box so that you can select a
procedure to execute.
Executing a procedure from the Macro dialog box
Choosing Excel’s Tools➪Macro➪Macros command displays the Macro dialog
box, shown in Figure 9-1 (you can also press Alt+F8 to access this dialog box). The
Macro dialog box lists all available Sub procedures. Use the Macros In drop-down
box to limit the scope of the macros displayed (for example, show only the macros in
the active workbook). The Macro dialog box does not display Function procedures. In
addition, it does not display Sub procedures declared with the Private keyword,
Sub procedures that require one or more arguments, or Sub procedures contained
in add-ins.
Procedures stored in an add-in are not listed in the Macro dialog box, but you still
can execute such a procedure if you know the name. Simply type the procedure
name into the Macro name field in the Macro dialog box, and click Run.
Figure 9-1: The Macro dialog box lists all
available procedures.
Tip
Executing a procedure using a Ctrl+
shortcut key combination
You can assign a Ctrl+shortcut key combination to any procedure that doesn’t use
any arguments. If you assign the Ctrl+U key combo to a procedure named Update,
for example, pressing Ctrl+U executes the Update procedure.
When you begin recording a macro, the Record Macro dialog box gives you the
opportunity to assign a shortcut key. However, you can assign a shortcut key at any
time. To assign a Ctrl shortcut key to a procedure (or change a procedure’s shortcut
key), follow these steps:
1. Activate Excel and choose the Tools➪Macro➪Macros command.
2. Select the appropriate procedure from the list box in the Macro dialog box.
3. Click the Options button to display the Macro Options dialog box (see
Figure 9-2).
Figure 9-2: The Macro Options
dialog box lets you assign a Ctrl
key shortcut and an optional
description to a procedure.
4. Enter a character into the text box labeled Ctrl+.
The character that you enter into the text box labeled Ctrl+ is case-sensitive.
If you enter a lowercase s, the shortcut key combo is Ctrl+S. If you enter an
uppercase S, the shortcut key combo is Ctrl+Shift+S.
5. Enter a description (optional). If you enter a description for a macro, it is displayed
at the bottom of the Macro dialog box when the procedure is selected
in the list box.
6. Click OK to close the Macro Options dialog box, and click Close to close the
Macro dialog box.
If you assign one of Excel’s predefined shortcut key combinations to a procedure,
your key assignment takes precedence over the predefined key assignment. For
example, Ctrl+S is Excel’s predefined shortcut key for saving the active workbook.
But if you assign Ctrl+S to a procedure, pressing Ctrl+S no longer saves the active
workbook.
The following Ctrl+key combinations are not used by Excel: E, J, L, M, Q, and T.
Excel doesn’t use too many Ctrl+Shift+key combinations. In fact, you can safely
use any of them except F, O, and P.
Executing a procedure from a custom menu
As I describe in Chapter 23, Excel provides two ways for you to customize its menus:
using the View➪Toolbars➪Customize command or writing VBA code. The latter
method is preferable, but you can use either technique to assign a macro to a new
menu item.
Excel 5 and Excel 95 include a menu editor, which was removed beginning with
Excel 97.
Following are the steps required to display a new menu item on a menu and to
assign a macro to the menu item. It assumes that the new menu item is on the Data
menu, that the menu item text is Open Customer File, and that the procedure is
named OpenCustomerFile.
1. Choose the View➪Toolbars➪Customize command. Excel displays the
Customize dialog box.
When the Customize dialog box is displayed, Excel is in a special “customization”
mode. The menus and toolbars are not active, but they can be
customized.
2. Click the Commands tab in the Customize dialog box.
3. Scroll down and click Macros in the Categories list.
4. In the Commands list, drag the first item (labeled Custom Menu Item) to the
bottom of the Data menu (after the Refresh Data menu item). The Data menu
drops down when you click it.
5. Right-click the new menu item (which is labeled Custom Menu Item) to
display a shortcut menu.
6. Enter a new name for the menu item: &Open Customer File in the text box
labeled Name (see Figure 9-3).
Note
Tip
7. Click Assign Macro on the shortcut menu.
8. In the Assign Macro dialog box, select the OpenCustomerFile procedure from
the list of macros.
9. Click OK to close the Assign Macro dialog box, and click Close to close the
Customize dialog box.
After you follow the process mentioned above, the new menu item always
appears on the menu, even when the workbook that contains the macro is not
open. In other words, changes you make using the View➪Toolbars➪Customize
command are “permanent.” Selecting the new menu item opens the workbook if
it’s not already open.
Refer to Chapter 23 to learn how to use VBA to create menu items that are
displayed only when a particular workbook is open.
Cross-
Reference
Caution
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
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
The Basics of VBA
Before I get into the meat of things, I suggest that you read through the material in
this section to get a broad overview of where I’m heading. These are the topics that
I cover in the remainder of this chapter.
Following is a quick-and-dirty summary of what VBA is all about:
✦ You perform actions in VBA by executing VBA code.
✦ You write (or record) VBA code, which is stored in a VBA module.
VBA modules are stored in an Excel workbook, but you view or edit a module
using the Visual Basic Editor (VBE).
✦ A VBA module consists of procedures.
A procedure is basically a unit of computer code that performs some action.
Here’s an example of a simple Sub procedure called Test: This procedure
calculates a simple sum and then displays the result in a message box.
Sub Test()
Sum = 1 + 1
MsgBox “The answer is “ & Sum
End Sub
✦ Besides Sub procedures, a VBA module can also have Function procedures.
A Function procedure returns a single value (or possibly an array). A function
can be called from another VBA procedure, or used in a worksheet formula.
Here’s an example of a function named AddTwo:
Function AddTwo(arg1, arg2)
AddTwo = arg1 + arg2
End Function
✦ VBA manipulates objects contained in its host application (in this case, Excel
is the host application).
Excel provides you with more than 100 classes of objects to manipulate. Examples
of objects include a workbook, a worksheet, a range on a worksheet, a
chart, and a drawn rectangle. Many more objects are at your disposal, and
you can manipulate them using VBA code.
✦ Object classes are arranged in a hierarchy.
Objects can act as containers for other objects. For example, Excel is an
object called Application, and it contains other objects, such as Workbook
and CommandBar objects. The Workbook object can contain other objects,
such as Worksheet objects and Chart objects. A Worksheet object can
contain objects such as Range objects, PivotTable objects, and so on. The
arrangement of these objects is referred to as Excel’s object model.
✦ Like objects form a collection.
For example, the Worksheets collection consists of all the worksheets in a
particular workbook. The CommandBars collection consists of all CommandBar
objects. Collections are objects in themselves.
✦ When you refer to a contained or member object, you specify its position in
the object hierarchy using a period (also known as a “dot”) as a separator
between the container and the member.
For example, you can refer to a workbook named Book1.xls as
Application.Workbooks(“Book1.xls”)
This refers to the Book1.xls workbook in the Workbooks collection. The
Workbooks collection is contained in the Excel Application object.
Extending this to another level, you can refer to Sheet1 in Book1 as
Application.Workbooks(“Book1.xls”).Worksheets(“Sheet1”)
You can take it to still another level and refer to a specific cell as follows:
Application.Workbooks(“Book1.xls”).Worksheets(“Sheet1”).Range
(“A1”)
✦ If you omit a specific reference to an object, Excel uses the active objects.
If Book1 is the active workbook, the preceding reference can be simplified as
Worksheets(“Sheet1”).Range(“A1”)
If you know that Sheet1 is the active sheet, you can simplify the reference
even more:
Range(“A1”)
✦ Objects have properties.
A property can be thought of as a setting for an object. For example, a range
object has properties such as Value and Name. A chart object has properties
such as HasTitle and Type. You can use VBA to determine object properties
and also to change them.
✦ You refer to properties by combining the object with the property, separated
by a period.
For example, you can refer to the value in cell A1 on Sheet1 as
Worksheets(“Sheet1”).Range(“A1”).Value
You can assign values to VBA variables. Think of a variable as a name that you
can use to store a particular value.
To assign the value in cell A1 on Sheet1 to a variable called Interest, use the
following VBA statement:
Interest = Worksheets(“Sheet1”).Range(“A1”).Value
✦ Objects have methods.
A method is an action that is performed with the object. For example, one of
the methods for a Range object is ClearContents. This method clears the
contents of the range.
✦ You specify methods by combining the object with the method, separated by
a period.
For example, to clear the contents of cell A1 on the active worksheet, use this:
Range(“A1”).ClearContents
✦ VBA also includes all the constructs of modern programming languages,
including arrays, looping, and so on.
Believe it or not, the preceding section pretty much describes VBA. Now it’s just a
matter of learning the details, which is what I cover in the rest of this chapter.
Introducing the Visual Basic Editor
In Excel 5 and Excel 95, a VBA module appeared as a separate sheet in a workbook.
Beginning with Excel 97, VBA modules no longer show up as sheets in a workbook.
Rather, you use the Visual Basic Editor (VBE) to view and work with VBA modules.
VBA modules are still stored with workbook files; they just aren’t visible unless you
activate the VBE.
The VBE is a separate application that works seamlessly with Excel. By seamlessly, I
mean that Excel takes care of the details of opening the VBE when you need it. You
can’t run VBE separately; Excel must be running in order for the VBE to run.
Activating the VBE
When you’re working in Excel, you can switch to the VBE using any of the following
techniques:
✦ Press Alt+F11.
✦ Select Tools➪Macro➪Visual Basic Editor.
✦ Click the Visual Basic Editor button, which is located on the Visual Basic
toolbar.
Don’t confuse the Visual Basic Editor with the Microsoft Script Editor. These are
two entirely different animals. The Script Editor is used to edit HTML scripts written
in VBScript or JavaScript. The Script Editor is not covered in this book.
Figure 7-3 shows the VBE. Chances are, your VBE window won’t look exactly like
the window shown in the figure. This window is highly customizable—you can
hide windows, change their sizes, “dock” them, rearrange them, and so on.
Figure 7-3: The Visual Basic Editor window
The VBE windows
The VBE consists of a number of parts. I briefly describe some of the key components
in the sections that follow.
Menu bar
The VBE menu bar, of course, works like every other menu bar you’ve encountered.
It contains commands that you use to work with the various components in the
VBE. Also, you’ll find that many of the menu commands have shortcut keys associated
with them. For example, the View➪Immediate Window command has a shortcut
key of Ctrl+G.
Don’t confuse the Visual Basic Editor with the Microsoft Script Editor. These are
two entirely different animals. The Script Editor is used to edit HTML scripts written
in VBScript or JavaScript. The Script Editor is not covered in this book.
Figure 7-3 shows the VBE. Chances are, your VBE window won’t look exactly like
the window shown in the figure. This window is highly customizable—you can
hide windows, change their sizes, “dock” them, rearrange them, and so on.
The VBE windows
The VBE consists of a number of parts. I briefly describe some of the key components
in the sections that follow.
Menu bar
The VBE menu bar, of course, works like every other menu bar you’ve encountered.
It contains commands that you use to work with the various components in the
VBE. Also, you’ll find that many of the menu commands have shortcut keys associated
with them. For example, the View➪Immediate Window command has a shortcut
key of Ctrl+G.
The VBE also features shortcut menus. As you’ll discover, you can right-click
virtually anything in a VBE window and you’ll get a shortcut menu of common
commands.
Toolbars
The Standard toolbar, which is directly under the menu bar by default, is one of six
VBE toolbars available (the menu bar is also considered a toolbar). VBE toolbars
work just like those in Excel: You can customize toolbars, move them around, display
other toolbars, and so forth. Use the View➪Toolbars➪Customize command
to work with VBE toolbars.
Project Explorer window
The Project Explorer window displays a tree diagram that consists of every workbook
that is currently open in Excel (including add-ins and hidden workbooks).
Each workbook is known as a project. I discuss the Project Explorer window in
more detail in the next section (“Working with the Project Explorer”).
If the Project Explorer window is not visible, press Ctrl+R. To hide the Project
Explorer window, click the Close button in its title bar (or right-click anywhere
in the Project Explorer window and select Hide from the shortcut menu).
Code window
A code window (sometimes known as a Module window) contains VBA code. Every
item in a project has an associated code window. To view a code window for an
object, double-click the object in the Project Explorer window. For example, to view
the code window for the Sheet1 object, double-click Sheet1 in the Project Explorer
window. Unless you’ve added some VBA code, the code window will be empty.
Another way to view the code window for an object is to select the object in the
Project Explorer window and then click the View Code button in the toolbar at the
top of the Project Explorer window.
I discuss code windows later on in this chapter (see “Working with Code
Windows”).
Immediate window
The Immediate window is most useful for executing VBA statements directly, testing
statements, and debugging your code. This window may or may not be visible.
If the Immediate window isn’t visible, press Ctrl+G. To close the Immediate window,
click the Close button in its title bar (or right-click anywhere in the Immediate window
and select Hide from the shortcut menu).
this section to get a broad overview of where I’m heading. These are the topics that
I cover in the remainder of this chapter.
Following is a quick-and-dirty summary of what VBA is all about:
✦ You perform actions in VBA by executing VBA code.
✦ You write (or record) VBA code, which is stored in a VBA module.
VBA modules are stored in an Excel workbook, but you view or edit a module
using the Visual Basic Editor (VBE).
✦ A VBA module consists of procedures.
A procedure is basically a unit of computer code that performs some action.
Here’s an example of a simple Sub procedure called Test: This procedure
calculates a simple sum and then displays the result in a message box.
Sub Test()
Sum = 1 + 1
MsgBox “The answer is “ & Sum
End Sub
✦ Besides Sub procedures, a VBA module can also have Function procedures.
A Function procedure returns a single value (or possibly an array). A function
can be called from another VBA procedure, or used in a worksheet formula.
Here’s an example of a function named AddTwo:
Function AddTwo(arg1, arg2)
AddTwo = arg1 + arg2
End Function
✦ VBA manipulates objects contained in its host application (in this case, Excel
is the host application).
Excel provides you with more than 100 classes of objects to manipulate. Examples
of objects include a workbook, a worksheet, a range on a worksheet, a
chart, and a drawn rectangle. Many more objects are at your disposal, and
you can manipulate them using VBA code.
✦ Object classes are arranged in a hierarchy.
Objects can act as containers for other objects. For example, Excel is an
object called Application, and it contains other objects, such as Workbook
and CommandBar objects. The Workbook object can contain other objects,
such as Worksheet objects and Chart objects. A Worksheet object can
contain objects such as Range objects, PivotTable objects, and so on. The
arrangement of these objects is referred to as Excel’s object model.
✦ Like objects form a collection.
For example, the Worksheets collection consists of all the worksheets in a
particular workbook. The CommandBars collection consists of all CommandBar
objects. Collections are objects in themselves.
✦ When you refer to a contained or member object, you specify its position in
the object hierarchy using a period (also known as a “dot”) as a separator
between the container and the member.
For example, you can refer to a workbook named Book1.xls as
Application.Workbooks(“Book1.xls”)
This refers to the Book1.xls workbook in the Workbooks collection. The
Workbooks collection is contained in the Excel Application object.
Extending this to another level, you can refer to Sheet1 in Book1 as
Application.Workbooks(“Book1.xls”).Worksheets(“Sheet1”)
You can take it to still another level and refer to a specific cell as follows:
Application.Workbooks(“Book1.xls”).Worksheets(“Sheet1”).Range
(“A1”)
✦ If you omit a specific reference to an object, Excel uses the active objects.
If Book1 is the active workbook, the preceding reference can be simplified as
Worksheets(“Sheet1”).Range(“A1”)
If you know that Sheet1 is the active sheet, you can simplify the reference
even more:
Range(“A1”)
✦ Objects have properties.
A property can be thought of as a setting for an object. For example, a range
object has properties such as Value and Name. A chart object has properties
such as HasTitle and Type. You can use VBA to determine object properties
and also to change them.
✦ You refer to properties by combining the object with the property, separated
by a period.
For example, you can refer to the value in cell A1 on Sheet1 as
Worksheets(“Sheet1”).Range(“A1”).Value
You can assign values to VBA variables. Think of a variable as a name that you
can use to store a particular value.
To assign the value in cell A1 on Sheet1 to a variable called Interest, use the
following VBA statement:
Interest = Worksheets(“Sheet1”).Range(“A1”).Value
✦ Objects have methods.
A method is an action that is performed with the object. For example, one of
the methods for a Range object is ClearContents. This method clears the
contents of the range.
✦ You specify methods by combining the object with the method, separated by
a period.
For example, to clear the contents of cell A1 on the active worksheet, use this:
Range(“A1”).ClearContents
✦ VBA also includes all the constructs of modern programming languages,
including arrays, looping, and so on.
Believe it or not, the preceding section pretty much describes VBA. Now it’s just a
matter of learning the details, which is what I cover in the rest of this chapter.
Introducing the Visual Basic Editor
In Excel 5 and Excel 95, a VBA module appeared as a separate sheet in a workbook.
Beginning with Excel 97, VBA modules no longer show up as sheets in a workbook.
Rather, you use the Visual Basic Editor (VBE) to view and work with VBA modules.
VBA modules are still stored with workbook files; they just aren’t visible unless you
activate the VBE.
The VBE is a separate application that works seamlessly with Excel. By seamlessly, I
mean that Excel takes care of the details of opening the VBE when you need it. You
can’t run VBE separately; Excel must be running in order for the VBE to run.
Activating the VBE
When you’re working in Excel, you can switch to the VBE using any of the following
techniques:
✦ Press Alt+F11.
✦ Select Tools➪Macro➪Visual Basic Editor.
✦ Click the Visual Basic Editor button, which is located on the Visual Basic
toolbar.
Don’t confuse the Visual Basic Editor with the Microsoft Script Editor. These are
two entirely different animals. The Script Editor is used to edit HTML scripts written
in VBScript or JavaScript. The Script Editor is not covered in this book.
Figure 7-3 shows the VBE. Chances are, your VBE window won’t look exactly like
the window shown in the figure. This window is highly customizable—you can
hide windows, change their sizes, “dock” them, rearrange them, and so on.
Figure 7-3: The Visual Basic Editor window
The VBE windows
The VBE consists of a number of parts. I briefly describe some of the key components
in the sections that follow.
Menu bar
The VBE menu bar, of course, works like every other menu bar you’ve encountered.
It contains commands that you use to work with the various components in the
VBE. Also, you’ll find that many of the menu commands have shortcut keys associated
with them. For example, the View➪Immediate Window command has a shortcut
key of Ctrl+G.
Don’t confuse the Visual Basic Editor with the Microsoft Script Editor. These are
two entirely different animals. The Script Editor is used to edit HTML scripts written
in VBScript or JavaScript. The Script Editor is not covered in this book.
Figure 7-3 shows the VBE. Chances are, your VBE window won’t look exactly like
the window shown in the figure. This window is highly customizable—you can
hide windows, change their sizes, “dock” them, rearrange them, and so on.
The VBE windows
The VBE consists of a number of parts. I briefly describe some of the key components
in the sections that follow.
Menu bar
The VBE menu bar, of course, works like every other menu bar you’ve encountered.
It contains commands that you use to work with the various components in the
VBE. Also, you’ll find that many of the menu commands have shortcut keys associated
with them. For example, the View➪Immediate Window command has a shortcut
key of Ctrl+G.
The VBE also features shortcut menus. As you’ll discover, you can right-click
virtually anything in a VBE window and you’ll get a shortcut menu of common
commands.
Toolbars
The Standard toolbar, which is directly under the menu bar by default, is one of six
VBE toolbars available (the menu bar is also considered a toolbar). VBE toolbars
work just like those in Excel: You can customize toolbars, move them around, display
other toolbars, and so forth. Use the View➪Toolbars➪Customize command
to work with VBE toolbars.
Project Explorer window
The Project Explorer window displays a tree diagram that consists of every workbook
that is currently open in Excel (including add-ins and hidden workbooks).
Each workbook is known as a project. I discuss the Project Explorer window in
more detail in the next section (“Working with the Project Explorer”).
If the Project Explorer window is not visible, press Ctrl+R. To hide the Project
Explorer window, click the Close button in its title bar (or right-click anywhere
in the Project Explorer window and select Hide from the shortcut menu).
Code window
A code window (sometimes known as a Module window) contains VBA code. Every
item in a project has an associated code window. To view a code window for an
object, double-click the object in the Project Explorer window. For example, to view
the code window for the Sheet1 object, double-click Sheet1 in the Project Explorer
window. Unless you’ve added some VBA code, the code window will be empty.
Another way to view the code window for an object is to select the object in the
Project Explorer window and then click the View Code button in the toolbar at the
top of the Project Explorer window.
I discuss code windows later on in this chapter (see “Working with Code
Windows”).
Immediate window
The Immediate window is most useful for executing VBA statements directly, testing
statements, and debugging your code. This window may or may not be visible.
If the Immediate window isn’t visible, press Ctrl+G. To close the Immediate window,
click the Close button in its title bar (or right-click anywhere in the Immediate window
and select Hide from the shortcut menu).
Subscribe to:
Posts (Atom)