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