Chapter 9: Working with VBA Sub Procedures
IN THIS CHAPTER
• Declaring and creating VBA Sub procedures
• Executing procedures
• Passing arguments to a procedure
• Using error-handling techniques
• An example of developing a useful procedure
About Procedures
A procedure is a series of VBA statements that resides in a VBA module, which you access in the Visual Basic Editor (VBE). A module can hold any number of procedures. A procedure holds a group of VBA statements that accomplishes a desired task. Most VBA code is contained in 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 and that is passed to the procedure when it is executed. Procedure arguments work much like the arguments that 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.
Although this chapter focuses on Sub procedures, VBA also supports Function procedures, which I discuss in Chapter 10. Chapter 11 has many additional examples of procedures, both Sub and Function, that you can incorporate into your work.
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
Here's a description of the elements that make up a Sub procedure:
• 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).
Scoping a procedure
In the preceding chapter, I note that a variable's scope determines the modules and procedures in which you can use the variable. 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 a user displays the Macro dialog box, Excel shows 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 those procedures are declared as Private. Doing so prevents the user from running these procedures from the Macro dialog box.
The following example declares a private procedure named MySub:
Private Sub MySub()
‘ ... [code goes here] ...
End Sub
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:
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. Unless you modify the recorded code, these procedures are all public procedures, and they will never use any arguments.
Executing Sub Procedures
In this section, I describe the various ways to execute, or call, a VBA Sub procedure:
• With the Run⇒Run Sub/UserForm command (in the VBE menu). Or you can press the F5 shortcut key, or click the Run Sub/UserForm button on the Standard toolbar.
• From Excel's Macro dialog box.
• By using the Ctrl key shortcut assigned to the procedure (assuming that 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 that you write. Sub and Function procedures can execute other procedures.
• From a custom control in the Ribbon. In addition, built-in Ribbon controls can be “repurposed” to execute a macro.
• From a customized shortcut menu.
• When an event occurs. These events include opening the workbook, saving the workbook, closing the workbook, changing a cell's value, 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.
I discuss these methods of executing procedures in the following sections.
In many cases, a procedure won't work properly unless it's executed 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 Sub/UserForm command
The VBE Run⇒Run Sub/UserForm menu command is used primarily to test a procedure while you're developing it. You would never require a user to activate the VBE to execute a procedure. Choose Run⇒Run Sub/UserForm in the VBE to execute the current procedure (in other words, the procedure that contains the cursor). Or, press F5, or use the Run Sub/UserForm button on the Standard toolbar.
If the cursor isn't located within a procedure when you issue the 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 Developer⇒Code⇒Macros command displays the Macro dialog box, as shown in Figure 9-1. (You can also press Alt+F8 to access this dialog box.) 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
• Sub procedures declared with the Private keyword
• Sub procedures that require one or more arguments
• Sub procedures contained in add-ins
Even though procedures stored in an add-in are not listed in the Macro dialog box, you still can execute such a procedure if you know the name. Simply type the procedure name in the Macro Name field in the Macro dialog box and then click Run.
FIGURE 9-1: The Macro dialog box.
Executing a procedure with 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 UpdateCustomerList, for example, pressing Ctrl+U executes that 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 to change a procedure's shortcut key), follow these steps:
1. Activate Excel and choose Developer⇒Code⇒Macros.
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 Ctrl+ text box.
Note: The character that you enter into the Ctrl+ text box 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's 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 then click Cancel 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 the Excel 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 keyboard keys are not used by Excel 2010 for Ctrl+key combinations: E, J, M, and Q. Excel doesn't use too many Ctrl+Shift+key combinations. In fact, you can use any of them except F, L, N, O, P, and W.
Executing a procedure from the Ribbon
Excel's Ribbon user interface was introduced in Excel 2007. In that version, customizing the Ribbon required writing XML code to add a new button (or other control) to the Ribbon. Note that you modify the Ribbon in this way outside of Excel, and you can't do it using VBA.
Excel 2010 allows users to modify the Ribbon directly from Excel. It's a simple matter to add a new control to the Ribbon and assign a VBA macro to the control.
Refer to Chapter 22 for more information about customizing the Ribbon.
Executing a procedure from a customized shortcut menu
You can also execute a macro by clicking a menu item in a customized shortcut menu. A shortcut menu appears when you right-click an object or range in Excel.
Refer to Chapter 23 for more information about customizing shortcut menus.
Executing a procedure from another procedure
One of the most common ways to execute a procedure is from another VBA procedure. You have three ways to do this:
• Enter the procedure's name, followed by its arguments (if any) separated by commas.
• Use the Call keyword followed by the procedure's name and then its arguments (if any) enclosed in parentheses and separated by commas.
• Use the Run method of the Application object. The Run method is useful when you need to run a procedure whose name is assigned to a variable. You can then pass the variable as an argument to the Run method.
The following example demonstrates the first method. In this case, the MySub procedure processes some statements (not shown), executes the UpdateSheet procedure, and then executes the rest of the statements.
Sub MySub()
‘ ... [code goes here] ...
UpdateSheet
‘ ... [code goes here] ...
End Sub
Sub UpdateSheet()
‘ ... [code goes here] ...
End Sub
The following example demonstrates the second method. The Call keyword executes the Update procedure, which requires one argument; the calling procedure passes the argument to the called procedure. I discuss procedure arguments later in this chapter (see “Passing Arguments to Procedures”).
Sub MySub()
MonthNum = InputBox(“Enter the month number: “)
Call UpdateSheet(MonthNum)
‘ ... [code goes here] ...
End Sub
Sub UpdateSheet(MonthSeq)
‘ ... [code goes here] ...
End Sub
Even though it's optional, some programmers always use the Call keyword just to make it perfectly clear that another procedure is being called.
The next example uses the Run method to execute the UpdateSheet procedure and then to pass MonthNum as the argument.
Sub MySub()
MonthNum = InputBox(“Enter the month number: “)
Application.Run “UpdateSheet”, MonthNum
‘ ... [code goes here] ...
End Sub
Sub UpdateSheet(MonthSeq)
‘ ... [code goes here] ...
End Sub
Perhaps the best reason to use the Run method is when the procedure name is assigned to a variable. In fact, it's the only way to execute a procedure in such a way. The following example demonstrates this. The Main procedure uses the VBA WeekDay function to determine the day of the week (an integer between 1 and 7, beginning with Sunday). The SubToCall variable is assigned a string that represents a procedure name. The Run method then calls the appropriate procedure (either WeekEnd or Daily).
Sub Main()
Dim SubToCall As String
Select Case WeekDay(Now)
Case 1, 7: SubToCall = “WeekEnd”
Case Else: SubToCall = “Daily”
End Select
Application.Run SubToCall
End Sub
Sub WeekEnd()
MsgBox “Today is a weekend”
‘ Code to execute on the weekend
‘ goes here
End Sub
Sub Daily()
MsgBox “Today is not a weekend”
‘ Code to execute on the weekdays
‘ goes here
End Sub
Calling a procedure in a different module
If VBA can't locate a called procedure in the current module, it looks for public procedures in other modules in the same project.
If you need to call a private procedure from another procedure, both procedures must reside in the same module.
You can't have two procedures with the same name in the same module, but you can have identically named procedures in different modules within the project. You can force VBA to execute an ambiguously named procedure — that is, another procedure in a different module that has the same name. To do so, precede the procedure name with the module name and a dot. For example, say that you define procedures named MySub in Module1 and Module2. If you want a procedure in Module2 to call the MySub in Module1, you can use either of the following statements:
Module1.MySub
Call Module1.MySub
If you do not differentiate between procedures that have the same name, you get an Ambiguous name detected error message.
Calling a procedure in a different workbook
In some cases, you may need your procedure to execute another procedure defined in a different workbook. To do so, you have two options: Either establish a reference to the other workbook or use the Run method and specify the workbook name explicitly.
To add a reference to another workbook, choose the VBE's Tools⇒References command. Excel displays the References dialog box (see Figure 9-3), which lists all available references, including all open workbooks. Simply check the box that corresponds to the workbook that you want to add as a reference and then click OK. After you establish a reference, you can call procedures in the workbook as if they were in the same workbook as the calling procedure.
A referenced workbook doesn't have to be open when you create the reference; it's treated like a separate object library. Use the Browse button in the References dialog box to establish a reference to a workbook that isn't open.
FIGURE 9-3: The References dialog box lets you establish a reference to another workbook.
When you open a workbook that contains a reference to another workbook, the referenced workbook is opened automatically.
the workbook names that appear in the list of references are listed by their VBE project names. By default, every project is initially named VBAProject. Therefore, the list may contain several identically named items. To distinguish a project, change its name in the Project Properties dialog box. Click the project name in the Project window and then choose Tools⇒xxxx Properties (where xxxx is the current project name). In the Project Properties dialog box, click the General tab and change the name displayed in the Project Name field.
The list of references displayed in the References dialog box also includes object libraries and ActiveX controls that are registered on your system. Your Excel workbooks always include references to the following object libraries:
• Visual Basic for Applications
• Microsoft Excel 14.0 Object Library
• OLE Automation
• Microsoft Office 14.0 Object Library
• Microsoft Forms 2.0 Object Library (this reference is included only if your project includes a UserForm)
Any additional references to other workbooks that you add are also listed in your project outline in the Project Explorer window in the VBE. These references are listed under a node called References.
If you've established a reference to a workbook that contains the procedure MySub, for example, you can use either of the following statements to call MySub:
YourSub
Call YourSub
To precisely identify a procedure in a different workbook, specify the project name, module name, and procedure name by using the following syntax:
MyProject.MyModule.MySub
Alternatively, you can use the Call keyword:
Call MyProject.MyModule.MySub
Another way to call a procedure in a different workbook is to use the Run method of the Application object. This technique doesn't require that you establish a reference, but the workbook that contains the procedure must be open. The following statement executes the Consolidate procedure located in a workbook named budget macros.xlsm:
Application.Run “'budget macros.xlsm'!Consolidate”
Executing a procedure by clicking an object
Excel provides a variety of objects that you can place on a worksheet or chart sheet, and you can attach a macro to any of these objects. These objects fall into several classes:
• ActiveX controls
• Forms controls
• Inserted objects (Shapes, SmartArt, WordArt, charts, and pictures)
The Developer⇒Controls⇒Insert drop-down list contains two types of controls that you can insert on a worksheet: Form controls and ActiveX controls. The ActiveX controls are similar to the controls that you use in a UserForm. The Forms controls were designed for Excel 5 and Excel 95, but you can still use them in later versions (which may be preferable in some cases).
Unlike the Form controls, you can't use the ActiveX controls to execute an arbitrary macro. An ActiveX control executes a specially named macro. For example, if you insert an ActiveX button control named CommandButton1, clicking the button executes a macro named CommandButton1_Click, which must be located in the code module for the sheet on which the control was inserted.
Refer to Chapter 13 for information about using controls on worksheets.
To assign a procedure to a Button object from the Form controls, follow these steps:
1. Select Developer⇒Controls⇒Insert and click the button icon in the Form Controls group.
2. Click the worksheet to create the button.
Or, you can drag your mouse on the worksheet to change the default size of the button.
Excel jumps right in and displays the Assign Macro dialog box (see Figure 9-4). It proposes a macro that's based on the button's name.
3. Select or enter the macro that you want to assign to the button and then click OK.
You can always change the macro assignment by right-clicking the button and choosing Assign Macro.
To assign a macro to a Shape, SmartArt, WordArt, chart, or picture, right-click the object and choose Assign Macro from the shortcut menu.
FIGURE 9-4: Assigning a macro to a button.
Executing a procedure when an event occurs
You might want a procedure to execute when a particular event occurs. Examples of events include opening a workbook, entering data into a worksheet, saving a workbook, clicking a CommandButton ActiveX control, and many others. A procedure that is executed when an event occurs is an event handler procedure. Event handler procedures are characterized by the following:
• They have special names that are made up of an object, an underscore, and the event name. For example, the procedure that is executed when a workbook is opened is Workbook_Open.
• They're stored in the Code module for the particular object.
Chapter 19 is devoted to event handler procedures.
Executing a procedure from the Immediate window
You also can execute a procedure by entering its name in the Immediate window of the VBE. If the Immediate window isn't visible, press Ctrl+G. The Immediate window executes VBA statements while you enter them. To execute a procedure, simply enter the name of the procedure in the Immediate window and press Enter.
This method can be quite useful when you're developing a procedure because you can insert commands to display results in the Immediate window. The following procedure demonstrates this technique:
Sub ChangeCase()
Dim MyString As String
MyString = “This is a test”
MyString = UCase(MyString)
Debug.Print MyString
End Sub
Figure 9-5 shows what happens when you enter ChangeCase in the Immediate window: The Debug.Print statement displays the result immediately.
FIGURE 9-5: Executing a procedure by entering its name in the Immediate window.
Passing Arguments to Procedures
A procedure's arguments provide it with data that it uses in its instructions. The data that's passed by an argument can be any of the following:
• A variable
• A constant
• An array
• An object
The use of arguments by procedures is very similar to their use of worksheet functions in the following respects:
• A procedure may not require any arguments.
• A procedure may require a fixed number of arguments.
• A procedure may accept an indefinite number of arguments.
• A procedure may require some arguments, leaving others optional.
• A procedure may have all optional arguments.
For example, a few of Excel's worksheet functions, such as RAND and NOW, use no arguments. Others, such as COUNTIF, require two arguments. Others still, such as SUM, can use up to 255 arguments. Still other worksheet functions have optional arguments. The PMT function, for example, can have five arguments (three are required; two are optional).
Most of the procedures that you've seen so far in this book have been declared without arguments. They were declared with just the Sub keyword, the procedure's name, and a set of empty parentheses. Empty parentheses indicate that the procedure does not accept arguments.
The following example shows two procedures. The Main procedure calls the ProcessFile procedure three times (the Call statement is in a For-Next loop). Before calling ProcessFile, however, a three-element array is created. Inside the loop, each element of the array becomes the argument for the procedure call. The ProcessFile procedure takes one argument (named TheFile). Notice that the argument goes inside parentheses in the Sub statement. When ProcessFile finishes, program control continues with the statement after the Call statement.
Sub Main()
Dim File(1 To 3) As String
Dim i as Integer
File(1) = “dept1.xlsx”
File(2) = “dept2.xlsx”
File(3) = “dept3.xlsx”
For i = 1 To 3
Call ProcessFile(File(i))
Next i
End Sub
Sub ProcessFile(TheFile)
Workbooks.Open FileName:=TheFile
‘ ...[more code here]...
End Sub
You can also, of course, pass literals (that is, not variables) to a procedure. For example:
Sub Main()
Call ProcessFile(“budget.xlsx”)
End Sub
You can pass an argument to a procedure in two ways:
• By reference: Passing an argument by reference simply passes the memory address of the variable. Changes to the argument within the procedure are made to the original variable. This is the default method of passing an argument.
• By value: Passing an argument by value passes a copy of the original variable. Consequently, changes to the argument within the procedure are not reflected in the original variable.
The following example demonstrates this concept. The argument for the Process procedure is passed by reference (the default method). After the Main procedure assigns a value of 10 to MyValue, it calls the Process procedure and passes MyValue as the argument. The Process procedure multiplies the value of its argument (named YourValue) by 10. When Process ends and program control passes back to Main, the MsgBox function displays MyValue: 100.
Sub Main()
Dim MyValue As Integer
MyValue = 10
Call Process(MyValue)
MsgBox MyValue
End Sub
Sub Process(YourValue)
YourValue = YourValue * 10
End Sub
If you don't want the called procedure to modify any variables passed as arguments, you can modify the called procedure's argument list so that arguments are passed to it by value rather than by reference. To do so, precede the argument with the ByVal keyword. This technique causes the called routine to work with a copy of the passed variable's data — not the data itself. In the following procedure, for example, the changes made to YourValue in the Process procedure do not affect the MyValue variable in Main. As a result, the MsgBox function displays 10 and not 100.
Sub Process(ByVal YourValue)
YourValue = YourValue * 10
End Sub
In most cases, you'll be content to use the default reference method of passing arguments. However, if your procedure needs to use data passed to it in an argument — and you must keep the original data intact — you'll want to pass the data by value.
A procedure's arguments can mix and match by value and by reference. Arguments preceded with ByVal are passed by value; all others are passed by reference.
If you pass a variable defined as a user-defined data type to a procedure, it must be passed by reference. Attempting to pass it by value generates an error.
Because I didn't declare a data type for any of the arguments in the preceding examples, all the arguments have been of the Variant data type. But a procedure that uses arguments can define the data types directly in the argument list. The following is a Sub statement for a procedure with two arguments of different data types. The first is declared as an integer, and the second is declared as a string.
Sub Process(Iterations As Integer, TheFile As String)
When you pass arguments to a procedure, the data that is passed as the argument must match the argument's data type. For example, if you call Process in the preceding example and pass a string variable for the first argument, you get an error: ByRef argument type mismatch.
Arguments are relevant to both Sub procedures and Function procedures. In fact, arguments are more often used in Function procedures. In Chapter 10, where I focus on Function procedures, I provide additional examples of using arguments with your routines, including how to handle optional arguments.
Error-Handling Techniques
When a VBA procedure is running, errors can (and probably will) occur. These include either syntax errors (which you must correct before you can execute a procedure) or runtime errors (which occur while the procedure is running). This section deals with runtime errors.
for error-handling procedures to work, the Break on All Errors setting must be turned off. In the VBE, choose Tools⇒Options and click the General tab in the Options dialog box. If Break on All Errors is selected, VBA ignores your error-handling code. You'll usually want to use the Break on Unhandled Errors option.
Normally, a runtime error causes VBA to stop, and the user sees a dialog box that displays the error number and a description of the error. A good application doesn't make the user deal with these messages. Rather, it incorporates error-handling code to trap errors and take appropriate actions. At the very least, your error-handling code can display a more meaningful error message than the one VBA pops up.
Appendix C lists all the VBA error codes and descriptions.
Trapping errors
You can use the On Error statement to specify what happens when an error occurs. Basically, you have two choices:
• Ignore the error and let VBA continue. Your code can later examine the Err object to determine what the error was and then take action, if necessary.
• Jump to a special error-handling section of your code to take action. This section is placed at the end of the procedure and is also marked by a label.
To cause your VBA code to continue when an error occurs, insert the following statement in your code:
On Error Resume Next
Some errors are inconsequential, and you can ignore them without causing a problem. But you might want to determine what the error was. When an error occurs, you can use the Err object to determine the error number. You can use the VBA Error function to display the text that corresponds to the Err.Number value. For example, the following statement displays the same information as the normal Visual Basic error dialog box (the error number and the error description):
MsgBox “Error “ & Err & “: “ & Error(Err.Number)
Figure 9-6 shows a VBA error message, and Figure 9-7 shows the same error displayed in a message box. You can, of course, make the error message a bit more meaningful to your end users by using more descriptive text.
Referencing Err is equivalent to accessing the Number property of the Err object. Therefore, the following two statements have the same effect:
MsgBox Err
MsgBox Err.Number
You also use the On Error statement to specify a location in your procedure to jump to when an error occurs. You use a label to mark the location. For example:
On Error GoTo ErrorHandler
FIGURE 9-6: VBA error messages aren't always user friendly.
FIGURE 9-7: You can create a message box to display the error code and description.
Error-handling examples
The first example demonstrates an error that you can safely ignore. The SpecialCells method selects cells that meet a certain criterion.
The SpecialCells method is equivalent to choosing the Home⇒Editing⇒Find & Select⇒Go To Special command. The Go To Special dialog box provides you with a number of choices. For example, you can select cells that contain a numeric constant (nonformula).
In the example that follows, which doesn't use any error handling, the SpecialCells method selects all the cells in the current range selection that contain a formula that returns a number. If no cells in the selection qualify, VBA displays the error message shown in Figure 9-8.
FIGURE 9-8: The SpecialCells method generates this error if no cells are found.
Sub SelectFormulas()
Selection.SpecialCells(xlFormulas, xlNumbers).Select
‘ ...[more code goes here]
End Sub
Following is a variation that uses the On Error Resume Next statement to prevent the error message from appearing:
Sub SelectFormulas2()
On Error Resume Next
Selection.SpecialCells(xlFormulas, xlNumbers).Select
On Error GoTo 0
‘ ...[more code goes here]
End Sub
The On Error GoTo 0 statement restores normal error handling for the remaining statements in the procedure.
The following procedure uses an additional statement to determine whether an error did occur. If so, the user is informed by a message.
Sub SelectFormulas3()
On Error Resume Next
Selection.SpecialCells(xlFormulas, xlNumbers).Select
If Err.Number = 1004 Then MsgBox “No formula cells were found.”
On Error GoTo 0
‘ ...[more code goes here]
End Sub
If the Number property of Err is equal to anything other than 0, then an error occurred. The If statement checks to see if Err.Number is equal to 1004 and displays a message box if it is. In this example, the code is checking for a specific error number. To check for any error, use a statement like this:
If Err.Number <> 0 Then MsgBox “An error occurred.”
The next example demonstrates error handling by jumping to a label.
Sub ErrorDemo()
On Error GoTo Handler
Selection.Value = 123
Exit Sub
Handler:
MsgBox “Cannot assign a value to the selection.”
End Sub
The procedure attempts to assign a value to the current selection. If an error occurs (for example, a range isn't selected or the sheet is protected), the assignment statement results in an error. The On Error statement specifies a jump to the Handler label if an error occurs. Notice the use of the Exit Sub statement before the label. This statement prevents the error-handling code from being executed if no error occurs. If this statement is omitted, the error message is displayed even if an error does not occur.
Sometimes, you can take advantage of an error to get information. The example that follows simply checks whether a particular workbook is open. It doesn't use any error handling.
Sub CheckForFile1()
Dim FileName As String
Dim FileExists As Boolean
Dim book As Workbook
FileName = “BUDGET.XLSX”
FileExists = False
‘ Cycle through all open workbooks
For Each book In Workbooks
If UCase(book.Name) = FileName Then FileExists = True
Next book
‘ Display appropriate message
If FileExists Then
MsgBox FileName & “ is open.”
Else
MsgBox FileName & “ is not open.”
End If
End Sub
Here, a For Each-Next loop cycles through all objects in the Workbooks collection. If the workbook is open, the FileExists variable is set to True. Finally, a message is displayed that tells the user whether the workbook is open.
You can rewrite the preceding routine to use error handling to determine whether the file is open. In the example that follows, the On Error Resume Next statement causes VBA to ignore any errors. The next instruction attempts to reference the workbook by assigning the workbook to an object variable (by using the Set keyword). If the workbook isn't open, an error occurs. The If-Then-Else structure checks the value property of Err and displays the appropriate message. This procedure uses no looping, so it's slightly more efficient.
Sub CheckForFile()
Dim FileName As String
Dim x As Workbook
FileName = “BUDGET.XLSX”
On Error Resume Next
Set x = Workbooks(FileName)
If Err = 0 Then
MsgBox FileName & “ is open.”
Else
MsgBox FileName & “ is not open.”
End If
On Error GoTo 0
End Sub
Chapter 11 presents several additional examples that use error handling.
A Realistic Example That Uses Sub Procedures
In this chapter, I describe the basics of creating Sub procedures. Most of the previous examples, I will admit, have been rather wimpy. The remainder of this chapter is a real-life exercise that demonstrates many of the concepts covered in this and the preceding two chapters.
This section describes the development of a useful utility that qualifies as an application as defined in Chapter 5. More important, I demonstrate the process of analyzing a problem and then solving it with VBA. I wrote this section with VBA newcomers in mind. As a result, I don't simply present the code, but I also show how to find out what you need to know to develop the code.
You can find the completed application, named sheet sorter.xlsm, on the companion CD-ROM.
The goal
The goal of this exercise is to develop a utility that rearranges a workbook by alphabetizing its sheets (something that Excel can't do on its own). If you tend to create workbooks that consist of many sheets, you know that locating a particular sheet can be difficult. If the sheets are ordered alphabetically, however, it's easier to find a desired sheet.
Project requirements
Where to begin? One way to get started is to list the requirements for your application. When you develop your application, you can check your list to ensure that you're covering all the bases.
Here's the list of requirements that I compiled for this example application:
1. It should sort the sheets (that is, worksheets and chart sheets) in the active workbook in ascending order of their names.
2. It should be easy to execute.
3. It should always be available. In other words, the user shouldn't have to open a workbook to use this utility.
4. It should work properly for any workbook that's open.
5. It should not display any VBA error messages.
What you know
Often, the most difficult part of a project is figuring out where to start. In this case, I started by listing things that I know about Excel that may be relevant to the project requirements:
• Excel doesn't have a command that sorts sheets, so I'm not re-inventing the wheel.
• I can't create this type of macro by recording my actions. However, the macro might be useful to provide some key information.
• I can move a sheet easily by dragging its sheet tab.
Mental note: Turn on the macro recorder and drag a sheet to a new location to find out what kind of code this action generates.
• Excel also has a Move or Copy dialog box, which is displayed when I right-click a sheet tab and choose Move or Copy. Would recording a macro of this command generate different code than moving a sheet manually?
• I'll need to know how many sheets are in the active workbook. I can get this information with VBA.
• I'll need to know the names of all the sheets. Again, I can get this information with VBA.
• Excel has a command that sorts data in worksheet cells.
Mental note: Maybe I can transfer the sheet names to a range and use this feature. Or, maybe VBA has a sorting method that I can take advantage of.
• Thanks to the Macro Options dialog box, it's easy to assign a shortcut key to a macro.
• If a macro is stored in the Personal Macro Workbook, it will always be available.
• I need a way to test the application while I develop it. For certain, I don't want to be testing it using the same workbook in which I'm developing the code.
Mental note: Create a dummy workbook for testing purposes.
• If I develop the code properly, VBA won't display any errors.
Mental note: Wishful thinking . . .
The approach
Although I still didn't know exactly how to proceed, I could devise a preliminary, skeleton plan that describes the general tasks required:
1. Identify the active workbook.
2. Get a list of all the sheet names in the workbook.
3. Count the sheets.
4. Sort the sheet names (somehow).
5. Rearrange the sheets so they correspond to the sorted sheet names.
What you need to know
I saw a few holes in the plan. I knew that I had to determine the following:
• How to identify the active workbook
• How to count the sheets in the active workbook
• How to get a list of the sheet names
• How to sort the list
• How to rearrange the sheets according to the sorted list
When you lack critical information about specific methods or properties, you can consult this book or the VBA Help system. You may eventually discover what you need to know. Your best bet, however, is to turn on the macro recorder and examine the code that it generates when you perform some relevant actions. You'll almost always get some clues as to how to proceed.
Some preliminary recording
Here's an example of using the macro recorder to learn about VBA. I started with a workbook that contained three worksheets. Then I turned on the macro recorder and specified my Personal Macro Workbook as the destination for the macro. With the macro recorder running, I dragged the third worksheet to the first sheet position. Here's the code that was generated by the macro recorder:
Sub Macro1()
Sheets(“Sheet3”).Select
Sheets(“Sheet3”).Move Before:=Sheets(1)
End Sub
I searched the VBA Help for Move and discovered that it's a method that moves a sheet to a new location in the workbook. It also takes an argument that specifies the location for the sheet. This information is very relevant to the task at hand. Curious, I then turned on the macro recorder to see whether using the Move or Copy dialog box would generate different code. It didn't.
Next, I needed to find out how many sheets were in the active workbook. I searched Help for the word Count and found out that it's a property of a collection. I activated the Immediate window in the VBE and typed the following statement:
? ActiveWorkbook.Count
Error! After a little more thought, I realized that I needed to get a count of the sheets within a workbook. So I tried this:
? ActiveWorkbook.Sheets.Count
Success. Figure 9-9 shows the result. More useful information.
FIGURE 9-9: Use the VBE Immediate window to test a statement.
What about the sheet names? Time for another test. I entered the following statement in the Immediate window:
? ActiveWorkbook.Sheets(1).Name
This told me that the name of the first sheet is Sheet3, which is correct (because I'd moved it). More good information to keep in mind.
Then I remembered something about the For Each-Next construct: It's useful for cycling through each member of a collection. After consulting the Help system, I created a short procedure to test it:
Sub Test()
For Each Sht In ActiveWorkbook.Sheets
MsgBox Sht.Name
Next Sht
End Sub
Another success. This macro displayed three message boxes, each showing a different sheet name.
Finally, it was time to think about sorting options. From the Help system, I learned that the Sort method applies to a Range object. So one option was to transfer the sheet names to a range and then sort the range, but that seemed like overkill for this application. I thought that a better option was to dump the sheet names into an array of strings and then sort the array by using VBA code.
Initial setup
Now I knew enough to get started writing some serious code. Before doing so, however, I needed to do some initial setup work. To re-create my steps, follow these instructions:
1. Create an empty workbook with five worksheets, named Sheet1, Sheet2, Sheet3, Sheet4, and Sheet5.
2. Move the sheets around randomly so that they aren't in any particular order.
3. Save the workbook as Test.xlsx.
4. Activate the VBE and select the Personal.xlsb project in the Project Window.
If Personal.xlsb doesn't appear in the Project window in the VBE, it means that you've never used the Personal Macro Workbook. To have Excel create this workbook for you, simply record a macro (any macro) and specify the Personal Macro Workbook as the destination for the macro.
5. Insert a new VBA module in Personal.xlsb (choose Insert⇒Module).
6. Create an empty Sub procedure called SortSheets (see Figure 9-10).
Actually, you can store this macro in any module in the Personal Macro Workbook. However, keeping each group of related macros in a separate module is a good idea. That way, you can easily export the module and import it into a different project later on.
7. Activate Excel and choose Developer⇒Code⇒Macros to display the Macro dialog box.
8. In the Macro dialog box, select the SortSheets procedure and click the Options button to assign a shortcut key to this macro.
The Ctrl+Shift+S key combination is a good choice.
FIGURE 9-10: An empty procedure in a module located in the Personal Macro Workbook.
Code writing
Now it's time to write some code. I knew that I needed to put the sheet names into an array of strings. Because I didn't know yet how many sheets were in the active workbook, I used a Dim statement with empty parentheses to declare the array. I knew that I could use ReDim afterward to redimension the array for the actual number of elements.
I entered the following code, which inserted the sheet names into the SheetNames array. I also added a MsgBox function within the loop just to assure me that the sheets' names were indeed being entered into the array.
Sub SortSheets()
‘ Sorts the sheets of the active workbook
Dim SheetNames() as String
Dim i as Long
Dim SheetCount as Long
SheetCount = ActiveWorkbook.Sheets.Count
ReDim SheetNames(1 To SheetCount)
For i = 1 To SheetCount
SheetNames(i) = ActiveWorkbook.Sheets(i).Name
MsgBox SheetNames(i)
Next i
End Sub
To test the preceding code, I activated the Test.xlsx workbook and pressed Ctrl+Shift+S. Five message boxes appeared, each displaying the name of a sheet in the active workbook. So far, so good.
By the way, I'm a major proponent of testing your work as you go. I tend to work in small steps and set things up so that I'm convinced that each small step is working properly before I continue. When you're convinced that your code is working correctly, remove the MsgBox statement. (These message boxes become annoying after a while.)
Rather than use the MsgBox function to test your work, you can use the Print method of the Debug object to display information in the Immediate window. For this example, use the following statement in place of the MsgBox statement:
This technique is much less intrusive than using MsgBox statements. Just make sure that you remember to remove the statement when you're finished.
At this point, the SortSheets procedure simply creates an array of sheet names corresponding to the sheets in the active workbook. Two steps remain: Sort the elements in the SheetNames array and then rearrange the sheets to correspond to the sorted array.
Writing the Sort procedure
It was time to sort the SheetNames array. One option was to insert the sorting code in the SortSheets procedure, but I thought a better approach was to write a general-purpose sorting procedure that I could reuse with other projects. (Sorting arrays is a common operation.)
You might be a bit daunted by the thought of writing a sorting procedure. The good news is that it's relatively easy to find commonly used routines that you can use or adapt. The Internet, of course, is a great source for such information.
You can sort an array in many ways. I chose the bubble sort method; although it's not a particularly fast technique, it's easy to code. Blazing speed isn't really a requirement in this particular application.
The bubble sort method uses a nested For-Next loop to evaluate each array element. If the array element is greater than the next element, the two elements swap positions. The code includes a nested loop, so this evaluation is repeated for every pair of items (that is, n – 1 times).
In Chapter 11, I present some other sorting routines and compare them in terms of speed.
Here's the sorting procedure I developed (after consulting a few programming Web sites to get some ideas):
Sub BubbleSort(List() As String)
‘ Sorts the List array in ascending order
Dim First As Long, Last As Long
Dim i As Long, j As Long
Dim Temp As String
First = LBound(List)
Last = UBound(List)
For i = First To Last - 1
For j = i + 1 To Last
If List(i) > List(j) Then
Temp = List(j)
List(j) = List(i)
List(i) = Temp
End If
Next j
Next i
End Sub
This procedure accepts one argument: a one-dimensional array named List. An array passed to a procedure can be of any length. I used the LBound and UBound functions to assign the lower bound and upper bound of the array to the variables First and Last, respectively.
Here's a little temporary procedure that I used to test the BubbleSort procedure:
Sub SortTester()
Dim x(1 To 5) As String
Dim i As Long
x(1) = “dog”
x(2) = “cat”
x(3) = “elephant”
x(4) = “aardvark”
x(5) = “bird”
Call BubbleSort(x)
For i = 1 To 5
Debug.Print i, x(i)
Next i
End Sub
The SortTester routine creates an array of five strings, passes the array to BubbleSort, and then displays the sorted array in the Immediate window. I eventually deleted this code because it served its purpose.
After I was satisfied that this procedure worked reliably, I modified SortSheets by adding a call to the BubbleSort procedure, passing the SheetNames array as an argument. At this point, my module looked like this:
Sub SortSheets()
Dim SheetNames() As String
Dim SheetCount as Long
Dim i as Long
SheetCount = ActiveWorkbook.Sheets.Count
ReDim SheetNames(1 To SheetCount)
For i = 1 To SheetCount
SheetNames(i) = ActiveWorkbook.Sheets(i).Name
Next i
Call BubbleSort(SheetNames)
End Sub
Sub BubbleSort(List() As String)
‘ Sorts the List array in ascending order
Dim First As Long, Last As Long
Dim i As Long, j As Long
Dim Temp As String
First = LBound(List)
Last = UBound(List)
For i = First To Last - 1
For j = i + 1 To Last
If List(i) > List(j) Then
Temp = List(j)
List(j) = List(i)
List(i) = Temp
End If
Next j
Next i
End Sub
When the SheetSort procedure ends, it contains an array that consists of the sorted sheet names in the active workbook. To verify this, you can display the array contents in the VBE Immediate window by adding the following code at the end of the SortSheets procedure (if the Immediate window is not visible, press Ctrl+G):
For i = 1 To SheetCount
Debug.Print SheetNames(i)
Next i
So far, so good. Next step: Write some code to rearrange the sheets to correspond to the sorted items in the SheetNames array.
The code that I recorded earlier proved useful. Remember the instruction that was recorded when I moved a sheet to the first position in the workbook?
Sheets(“Sheet3”).Move Before:=Sheets(1)
After a little thought, I was able to write a For-Next loop that would go through each sheet and move it to its corresponding sheet location, specified in the SheetNames array:
For i = 1 To SheetCount
Sheets(SheetNames(i)).Move Before:=Sheets(i)
Next i
For example, the first time through the loop, the loop counter i is 1. The first element in the SheetNames array is (in this example) Sheet1. Therefore, the expression for the Move method within the loop evaluates to
Sheets(“Sheet1”).Move Before:= Sheets(1)
The second time through the loop, the expression evaluates to
Sheets(“Sheet2”).Move Before:= Sheets(2)
I then added the new code to the SortSheets procedure:
Sub SortSheets()
Dim SheetNames() As String
Dim SheetCount as Long
Dim i as Long
SheetCount = ActiveWorkbook.Sheets.Count
ReDim SheetNames(1 To SheetCount)
For i = 1 To SheetCount
SheetNames(i) = ActiveWorkbook.Sheets(i).Name
Next i
Call BubbleSort(SheetNames)
For i = 1 To SheetCount
ActiveWorkbook.Sheets(SheetNames(i)).Move _
Before:=ActiveWorkbook.Sheets(i)
Next i
End Sub
I did some testing, and it seemed to work just fine for the Test.xlsx workbook.
Time to clean things up. I made sure that all the variables used in the procedures were declared, and then I added a few comments and blank lines to make the code easier to read. The SortSheets procedure looked like the following:
Sub SortSheets()
‘ This routine sorts the sheets of the
‘ active workbook in ascending order.
‘ Use Ctrl+Shift+S to execute
Dim SheetNames() As String
Dim SheetCount As Long
Dim i As Long
‘ Determine the number of sheets & ReDim array
SheetCount = ActiveWorkbook.Sheets.Count
ReDim SheetNames(1 To SheetCount)
‘ Fill array with sheet names
For i = 1 To SheetCount
SheetNames(i) = ActiveWorkbook.Sheets(i).Name
Next i
‘ Sort the array in ascending order
Call BubbleSort(SheetNames)
‘ Move the sheets
For i = 1 To SheetCount
ActiveWorkbook.Sheets(SheetNames(i)).Move _
Before:= ActiveWorkbook.Sheets(i)
Next i
End Sub
Everything seemed to be working. To test the code further, I added a few more sheets to Test.xlsx and changed some of the sheet names. It worked like a charm.
More testing
I was tempted to call it a day. However, just because the procedure worked with the Test.xlsx workbook didn't mean that it would work with all workbooks. To test it further, I loaded a few other workbooks and retried the routine. I soon discovered that the application wasn't perfect. In fact, it was far from perfect. I identified the following problems:
• Workbooks with many sheets took a long time to sort because the screen was continually updated during the move operations.
• The sorting didn't always work. For example, in one of my tests, a sheet named SUMMARY (all uppercase) appeared before a sheet named Sheet1. This problem was caused by the BubbleSort procedure — an uppercase U is “greater than” a lowercase h.
• If Excel had no visible workbook windows, pressing the Ctrl+Shift+S shortcut key combo caused the macro to fail.
• If the workbook's structure was protected, the Move method failed.
• After sorting, the last sheet in the workbook became the active sheet. Changing the user's active sheet isn't a good practice; it's better to keep the user's original sheet active.
• If I interrupted the macro by pressing Ctrl+Break, VBA displayed an error message.
• The macro can't be reversed (that is, the Undo command is always disabled when a macro is executed). If the user accidentally presses Ctrl+Shift+S, the workbook sheets are sorted, and the only way to get them back to their original order is by doing it manually.
Fixing the problems
Fixing the screen-updating problem was a breeze. I inserted the following instruction to turn off screen updating while the sheets were being moved:
Application.ScreenUpdating = False
This statement causes Excel's windows to freeze while the macro is running. A beneficial side effect is that it also speeds up the macro considerably. After the macro completes it operation, screen updating is turned back on automatically.
It was also easy to fix the problem with the BubbleSort procedure: I used VBA's UCase function to convert the sheet names to uppercase for the comparison. This caused all the comparisons to be made by using uppercase versions of the sheet names. The corrected line read as follows:
If UCase(List(i)) > UCase(List(j)) Then
Another way to solve the “case” problem is to add the following statement to the top of your module:
This statement causes VBA to perform string comparisons based on a case-insensitive text sort order. In other words, A is considered the same as a.
To prevent the error message that appears when no workbooks are visible, I added some error checking. I used On Error Resume Next to ignore the error and then checked the value of Err. If Err is not equal to 0, it means that an error occurred. Therefore, the procedure ends. The error-checking code is
On Error Resume Next
SheetCount = ActiveWorkbook.Sheets.Count
If Err <> 0 Then Exit Sub ‘ No active workbook
It occurred to me that I could avoid using On Error Resume Next. The following statement is a more direct approach to determining whether a workbook isn't visible and doesn't require any error handling. This statement can go at the top of the SortSheets procedure:
If ActiveWorkbook Is Nothing Then Exit Sub
There's usually a good reason that a workbook's structure is protected. I decided that the best approach was to not attempt to unprotect the workbook. Rather, the code should display a message box warning and let the user unprotect the workbook and re-execute the macro. Testing for a protected workbook structure was easy — the ProtectStructure property of a Workbook object returns True if a workbook is protected. I added the following block of code:
‘ Check for protected workbook structure
If ActiveWorkbook.ProtectStructure Then
MsgBox ActiveWorkbook.Name & “ is protected.”, _
vbCritical, “Cannot Sort Sheets.”
Exit Sub
End If
If the workbook's structure is protected, the user sees a message box like the one shown in Figure 9-11.
FIGURE 9-11: This message box tells the user that the sheets cannot be sorted.
To reactivate the original active sheet after the sorting was performed, I wrote code that assigned the original sheet to an object variable (OldActiveSheet) and then activated that sheet when the routine was finished. Here's the statement that assigns the variable:
Set OldActive = ActiveSheet
This statement activates the original active worksheet:
OldActive.Activate
Pressing Ctrl+Break normally halts a macro, and VBA usually displays an error message. But because one of my goals was to avoid VBA error messages, I inserted a command to prevent this situation. From the VBA Help system, I discovered that the Application object has an EnableCancelKey property that can disable Ctrl+Break. So I added the following statement at the top of the routine:
Application.EnableCancelKey = xlDisabled
Be very careful when you disable the Cancel key. If your code gets caught in an infinite loop, you can't break out of it. For best results, insert this statement only after you're sure that everything is working properly.
To prevent the problem of accidentally sorting the sheets, I added the following statement to the procedure, before the Ctrl+Break key is disabled:
If MsgBox(“Sort the sheets in the active workbook?”, _
vbQuestion + vbYesNo) <> vbYes Then Exit Sub
When the user executes the SortSheets procedure, he sees the message box in Figure 9-12.
FIGURE 9-12: This message box appears before the sheets are sorted.
After I made all these corrections, the SortSheets procedure looked like this:
Option Explicit
Sub SortSheets()
‘ This routine sorts the sheets of the
‘ active workbook in ascending order.
‘ Use Ctrl+Shift+S to execute
Dim SheetNames() As String
Dim i As Long
Dim SheetCount As Long
Dim OldActiveSheet As Object
If ActiveWorkbook Is Nothing Then Exit Sub ‘ No active workbook
SheetCount = ActiveWorkbook.Sheets.Count
‘ Check for protected workbook structure
If ActiveWorkbook.ProtectStructure Then
MsgBox ActiveWorkbook.Name & “ is protected.”, _
vbCritical, “Cannot Sort Sheets.”
Exit Sub
End If
‘ Make user verify
If MsgBox(“Sort the sheets in the active workbook?”, _
vbQuestion + vbYesNo) <> vbYes Then Exit Sub
‘ Disable Ctrl+Break
Application.EnableCancelKey = xlDisabled
‘ Get the number of sheets
SheetCount = ActiveWorkbook.Sheets.Count
‘ Redimension the array
ReDim SheetNames(1 To SheetCount)
‘ Store a reference to the active sheet
Set OldActiveSheet = ActiveSheet
‘ Fill array with sheet names
For i = 1 To SheetCount
SheetNames(i) = ActiveWorkbook.Sheets(i).Name
Next i
‘ Sort the array in ascending order
Call BubbleSort(SheetNames)
‘ Turn off screen updating
Application.ScreenUpdating = False
‘ Move the sheets
For i = 1 To SheetCount
ActiveWorkbook.Sheets(SheetNames(i)).Move _
Before:=ActiveWorkbook.Sheets(i)
Next i
‘ Reactivate the original active sheet
OldActiveSheet.Activate
End Sub
Utility availability
Because the SortSheets macro is stored in the Personal Macro Workbook, it's available whenever Excel is running. At this point, you can execute the macro by selecting the macro's name from the Macro dialog box (Alt+F8 displays this dialog box) or by pressing Ctrl+Shift+S. Another option is to add a command to the Ribbon.
To add a command, follow these steps:
1. Right-click any area of the Ribbon and choose Customize the Ribbon.
2. In the Customize Ribbon tab of the Excel Options dialog box, choose Macros from the drop-down list labeled Choose Commands From.
3. Click the item labeled PERSONAL.XLSB!SortSheets.
4. Use the controls in the box on the right to specify the ribbon tab and create a new group.
(You can't add a command to an existing group.)
I created a group named Worksheets in the View tab, and renamed the new item to Short Sheets (see Figure 9-13).
Evaluating the project
So there you have it. The utility meets all the original project requirements: It sorts all sheets in the active workbook, it can be executed easily, it's always available, it seems to work for any workbook, and I have yet to see it display a VBA error message.
The procedure still has one slight problem: The sorting is strict and may not always be “logical.” For example, after sorting, Sheet10 is placed before Sheet2. Most would want Sheet2 to be listed before Sheet10. Solving that problem is the beyond the scope of this introductory exercise.
FIGURE 9-13: Adding a new command to the ribbon.