In This Chapter
A procedure is a series of VBA statements that resides in a VBA module, which you access in 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.
Some procedures are written to receive arguments. An argument is 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 perform operations using these arguments, and the results of the procedure are usually based on those arguments.
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:
In the preceding chapter, we 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.
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 can be called by other procedures in the same module but not by procedures in other modules.
The following example declares a private procedure named MySub:
Private Sub MySub() ' ... [code goes here] ... End Sub
Excel’s macro recorder 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.
In this section, we describe the various ways to execute, or call, a VBA Sub procedure:
We discuss these methods of executing procedures in the following sections.
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 VBE to execute a procedure. Choose Run ➜ Run Sub/UserForm in 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, VBE displays its Macro dialog box so that you can select a procedure to execute.
Choose Excel’s View ➜ Macros ➜ Macros command to display the Macro dialog box, as shown in Figure 4-1. You can also press Alt+F8, or choose Developer ➜ Code ➜ Macros 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:
You can assign a Ctrl+shortcut key combination to any Sub 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:
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.
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.
Beginning with Excel 2010, users can modify the Ribbon directly from Excel. Just right-click any part of the Ribbon and choose Customize the Ribbon from the shortcut menu. It’s a simple matter to add a new control to the Ribbon and assign a VBA macro to the control. However, this must be done manually. In other words, it’s not possible to use VBA to add a control to the Ribbon.
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. It’s fairly easy to write VBA code that adds a new item to any of Excel’s shortcut menus.
One of the most common ways to execute a procedure is to call it from another VBA procedure. You have three ways to do this:
Here’s a simple Sub procedure that takes two arguments. The procedure displays the product of the two arguments.
Sub AddTwo (arg1, arg2) MsgBox arg1 + arg2 End Sub
The following three statements demonstrate three different ways to execute the AddTwo procedure and pass two arguments. All three have the same result.
AddTwo 12, 6 Call AddTwo (12, 6) Run"AddTwo", 12, 6
Even though it’s optional, some programmers always use the Call keyword just to make it perfectly clear that another procedure is being called.
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 oversimplified 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
If VBA can’t locate a called procedure in the current module, it looks for public procedures in other modules in the same workbook.
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, assume 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 the aptly named Ambiguous name detected error message.
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 VBE’s Tools ➜ References command. Excel displays the References dialog box (see Figure 4-3), which lists all available references, including all open workbooks. Select 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; the referenced workbook is 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.
When you open a workbook that contains a reference to another workbook, the referenced workbook is opened automatically.
The list of references displayed in the References dialog box also includes object libraries and ActiveX controls that are registered on your system. Excel 2016 workbooks always include references to the following object libraries:
If you’ve established a reference to a workbook that contains the YourSub procedure, for example, you can use either of the following statements to call YourSub:
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:
YourProject.YourModule.YourSub
Alternatively, you can use the Call keyword:
Call YourProject.YourModule.YourSub
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"
Note that the workbook name is enclosed in single quotes. That syntax is necessary only if the filename includes one or more space characters. Here’s an example of calling a procedure in a workbook that doesn’t have any spaces:
Application.Run"budgetmacros.xlsm!Consolidate"
Excel provides a variety of objects that you can place on a worksheet or chart sheet; you can attach a macro to any of these objects. These objects fall into several classes:
To assign a procedure to a Button object from the Form controls, follow these steps:
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 4-4).
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, or picture, right-click the object and choose Assign Macro from the shortcut menu.
To assign a macro to an embedded chart, press Ctrl and click the chart (to select the chart as an object). Then right-click and choose Assign Macro from the shortcut menu.
You might want a procedure to execute when a particular event occurs, such as opening a workbook, entering data into a worksheet, saving a workbook, or clicking a CommandButton ActiveX control. A procedure that is executed when an event occurs is an event-handler procedure. Event-handler procedures are characterized by the following:
You also can execute a procedure by entering its name in the Immediate window of 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 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 4-5 shows what happens when you enter ChangeCase in the Immediate window: The Debug.Print statement displays the result immediately.
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:
You are probably familiar with many of Excel’s worksheet functions. Arguments for procedures are similar:
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). Note 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 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:
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 12 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 120.
Sub Main() Dim MyValue As Integer MyValue = 12 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 displays12 and not 120.
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.
Because we 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.
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.
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.
You can use the On Error statement to specify what happens when an error occurs. Basically, you have two choices:
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"Oops! Can't find the object being referenced. " & _ "Error" & Err &":" & Error(Err.Number)
Figure 4-6 shows a VBA error message, and Figure 4-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.
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
The first example demonstrates an error that you can safely ignore. The SpecialCells method selects cells that meet a certain criterion.
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. If no cells in the selection qualify, VBA displays the error message shown in Figure 4-8.
Sub SelectFormulas() Selection.SpecialCells(xlFormulas).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).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 a specific error did occur. If so, the user is informed by a message.
Sub SelectFormulas3() On Error Resume Next Selection.SpecialCells(xlFormulas).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, 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. Note 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
Up to this point, the code examples covered in this chapter have been demonstrational in nature, and not very useful on their own. The remainder of this chapter will walk you through 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. More important, you will explore the process of analyzing a problem and then solving it with VBA.
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.
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 for this example application:
Often, the most difficult part of a project is figuring out where to start. It’s often helpful to start by listing things that you know about Excel that may be relevant to the project requirements. For this scenario, we know that:
After detailing what you know, you can start listing the series of steps needed to accomplish the actual task. In this case, we will need VBA to:
The best place to start any VBA procedure is the macro recorder; it’s a developer’s best friend. Let’s start by figuring out the VBA syntax for moving sheets around.
We can turn on the macro recorder and specify that the macro should be placed in the Personal Macro Workbook (because we want to test the code on workbooks other than the one we’re working in). Once the macro starts recording, we can drag Sheet3 before Sheet1 and then stop recording. A review of the recorded macro code shows that Excel used the Move method.
Sub Macro1() Sheets("Sheet3").Select Sheets("Sheet3").Move Before:=Sheets(1) End Sub
A quick search in the VBA Help system tells us that the Move method moves a sheet to a new location in the workbook. It also takes an argument that specifies the location for the sheet. This must be why the recorded macro included Before:=Sheets(1).
So far so good. Now we need to find out how many sheets are in the active workbook. Searching VBA Help for the word Count tells us that it’s a property of a collection. This means all collections such as Sheets, Rows, Cells, and Shapes have a Count property. Good to know.
To test out this newly acquired piece of information, we can fire up the Visual Basic Editor, activate the Immediate window, and then type:
? ActiveWorkbook.Sheets.Count
Figure 4-9 shows the result. Success!.
Okay. What about the sheet names? Time for another test. We can enter the following statement in the Immediate window:
? ActiveWorkbook.Sheets(1).Name
This results in the name of the first sheet is Sheet3, which is correct (because we moved it while recording our macro). More good information to keep in mind.
We can now take this information to construct a simple For Each-Next construct (covered in Chapter 3 of this book).
Sub Test() For Each Sht In ActiveWorkbook.Sheets MsgBox Sht.Name Next Sht End Sub
Running this procedure displays three message boxes, each showing a different sheet name. Great. Now we know how to get a list of sheet names.
So what about sorting? A quick search of the VBA Help system tells us that the Sort method applies to a Range object. So one option is to transfer the sheet names to a range and then sort the range, but that seems like overkill for this application. A better option would be to dump the sheet names into an array of strings and then sort the array by using VBA code.
At this point we know enough to start writing the procedure. But before doing so, we need to set up a test workbook. This test workbook will allow us to re-create the steps we determined at the start of this endeavor.
Activate VBE and select the Personal.xlsb project in the Project window.
If Personal.xlsb doesn’t appear in the Project window in 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.
Create an empty Sub procedure called SortSheets (see Figure 4-10).
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.
The Ctrl+Shift+S key combination is a good choice.
Now it’s time to write some code. We know we need to put the sheet names into an array of string, but because we don’t know yet how many sheets there will be in any given workbook, we use a Dim statement with empty parentheses to declare the array. We can use ReDim once we know the actual number of sheets.
As you can see in the code (see below) we loop through all the sheets in the active workbook and insert each sheet’s name into the SheetNames array. We also add a MsgBox function within the loop just to give ourselves a visual indicator that the sheets’ names are 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
It’s a best-practice to test as you go. So test the code to see five message boxes appear, each displaying the name of a sheet in the active workbook. So far, so good.
We can now remove the MsgBox statement. (These message boxes become annoying after a while.)
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.
Now that we have the sheet names in the SheetNames array, we can start thinking about sorting. One option is to insert the sorting code in the SortSheets procedure, but a better approach is to write a general-purpose sorting procedure that we can reuse with other projects. (Sorting arrays is a common operation.)
The thought of writing a sorting procedure seems daunting, but we can search the Internet to find commonly used routines that we can use or adapt. A quick search of VBA sorting procedures leads us to the bubble sort method. Although it’s not a fast technique, it’s easy to code. Blazing speed isn’t a requirement in this 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).
Here’s the sorting procedure pulled together with the help of a few examples found on the Internet.
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. It uses the LBound function to assign the lower bound of the array and the UBound function to assign the upper bound of the array to the variables First and Last, respectively.
Here’s a little temporary procedure we can use 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 (see Figure 4-11). By the way, it’s often helpful to create temporary procedures for testing. Once you’re done with testing, you can simply delete them.
Now that we’re satisfied that the BubbleSort procedure works reliably, we can modify SortSheets by adding a call to the BubbleSort procedure, passing the SheetNames array as an argument. At this point, our code looks 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, we 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 we 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)
We can 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 sorted SheetNames array is (in this example) Sheet1. Therefore, the expression for the Move method in 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)
This is what the SortSheets procedure looks like with the added code:
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
Time to clean things up. Let’s make sure all the variables used in the procedures are declared. Let’s also add some comments and blank lines to make the code easier to read.
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
We can test the code by adding a few more sheets to Test.xlsx and changing some of the sheet names.
Just because the procedure works with the Test.xlsx workbook doesn’t mean it will work with all workbooks. To test it further, we can open a few other workbooks and try running the sort procedure on each workbook.
It soon becomes apparent that there are few issues with the code:
Fixing the screen-updating problem is a breeze. We can insert the following instruction to turn off screen updating while the sheets are 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 its operation, screen updating is turned back on automatically.
It is also easy to fix the problem with the BubbleSort procedure. We can use VBA’s UCase function to convert the sheet names to uppercase for the comparison. This causes all the comparisons to be made by using uppercase versions of the sheet names. The corrected line reads as follows:
If UCase(List(i)) > UCase(List(j)) Then
To prevent the error message that appears when no workbooks are visible, we can add a simple check to see if an active workbook is available. If no active workbook is available, we simply exit the procedure. 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. The best approach is 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 is easy — the ProtectStructure property of a Workbook object returns True if a workbook is protected.
' 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 4-12.
To reactivate the original active sheet after the sorting is performed, we can add some code to assign the original sheet to an object variable (OldActiveSheet) and then activate that sheet when the routine is 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 we want to avoid VBA error messages, we can insert a command to prevent this situation. From the VBA Help system, we discover that the Application object has an EnableCancelKey property that can disable Ctrl+Break. So we can add the following statement at the top of the routine:
Application.EnableCancelKey = xlDisabled
To prevent the problem of inadvertently starting the sort procedure, we can add a simple message box asking the user to confirm the action. The following statement is placed before the Ctrl+Break key is disabled:
If MsgBox("Sort the sheets in the active workbook?", _ vbQuestion + vbYesNo) <> vbYes Then Exit Sub
When users execute the SortSheets procedure, they see the message box in Figure 4-13.
After all these adjustments are implemented, 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
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:
In Figure 4-14, we created a group named Sheets in the View tab, and renamed the new item to Short Sheets.
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, and it’s always available for use with any workbook.
3.137.178.169