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. Forces an immediate exit from the procedure prior to its formal completion.
• End Sub: Required. Indicates the end of the procedure.

Scoping a 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.

Sub First()
'    ... [code goes here] ...
End Sub
 
Public Sub Second()
'    ... [code goes here] ...
End Sub

Private Sub MySub()
'    ... [code goes here] ...
End Sub

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 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.

Executing a procedure from the Macro dialog box

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:

• Function procedures
• Sub procedures declared with the Private keyword
• Sub procedures that require one or more arguments
• Sub procedures contained in add-ins
• Event procedures stored in code modules for objects such as ThisWorkbook, Sheet1, or UserForm1

Executing a procedure with a Ctrl+shortcut key combination

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:

1. Activate Excel and display the Macro dialog box (Alt+F8 is one way to do that).
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 4.2).

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.

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.

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.

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. It’s fairly easy to write VBA code that adds a new item to any of Excel’s shortcut menus.

Executing a procedure from another procedure

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:

• Enter the procedure’s name, followed by its arguments (if any) separated by commas. Do not enclose the argument list in parentheses.
• 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.

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

Module1.MySub
Call Module1.MySub

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 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:

• Visual Basic for Applications
• Microsoft Excel 16.0 Object Library
• OLE Automation
• Microsoft Office 16.0 Object Library
• Microsoft Forms 2.0 Object Library (This reference is included only if your project includes a UserForm.)

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"

Executing a procedure by clicking an object

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:

• ActiveX controls
• Forms controls
• Inserted objects (Shapes, SmartArt, WordArt, charts, and pictures)

To assign a procedure to a Button object from the Form controls, follow these steps:

1. Choose 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 4-4).

3. Select 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, 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.

Executing a procedure when an event occurs

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:

• 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 (for example, ThisWorkbook or Sheet1).

Executing a procedure from the Immediate window

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.

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"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

Error-handling examples

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

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 for this example application:

• It should sort the sheets (that is, worksheets and chart sheets) in the active workbook in ascending order of their names.
• It should be easy to execute.
• It should always be available. In other words, the user shouldn’t have to open a workbook to use this utility.
• It should work properly for any workbook that’s open.
• It should trap errors gracefully, and not display any cryptic VBA error messages.

What you know

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:

• Excel doesn’t have a command that sorts sheets, so we’re not reinventing the wheel.
• The macro recorder can’t be used to record the sorting of worksheets, as new worksheets (that didn’t exist at the time of recording) will likely be added by the user sometime in the future. That being said, a recorded macro might provide some helpful guidance on the correct syntax to use.
• Sorting the sheets will require moving some or all of them. We can manually 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 we 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?
• We’ll need to know how many sheets are in the active workbook. We can get this information with VBA.
• We’ll need to know the names of all the sheets. Again, we can get this information with VBA.
• Excel has a command that sorts data in worksheet cells.
• Mental note: Maybe we can transfer the sheet names to a range and use this feature. Or maybe VBA has a sorting method that we can take advantage of.
• We will need a way to test the code on workbooks other than the one we’re working in. This means we will need to store the macro in the Personal Macro workbook so that we can use it with other workbooks.
• Mental note: Create a dummy workbook for testing.

The approach

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:

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.

Some preliminary recording

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.

Initial setup

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.

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. Just click and drag the sheet tabs.
3. Save the workbook as Test.xlsx.

4. 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.

5. Insert a new VBA module in Personal.xlsb (choose Insert ➜ Module).

6. 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.

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.

Code writing

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.

Writing the Sort procedure

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.

More testing

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:

• Workbooks with many sheets take a long time to sort because the screen continually updates during the move operations.
• The sorting procedure we chose seems to be case-sensitive. For example, a sheet named SUMMARY (all uppercase) appears before a sheet named Sheet1. According to our BubbleSort procedure, an uppercase U is “greater than” a lowercase h.
• If there are no workbooks open, the code fails.
• If the workbook’s structure is protected, the Move method fails.
• After sorting, the last sheet in the workbook becomes 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 the code is interrupted by pressing Ctrl+Break, VBA displays an error message.
• The macro can’t be reversed (that is, the Undo command is disabled when a macro is executed). If the user accidentally triggers the sorting procedure, the only way to get back to the original sheet order is by doing it manually.

Fixing the problems

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

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 Choose Commands From drop-down list.
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.)

In Figure 4-14, we created a group named Sheets in the View tab, and renamed the new item to Short Sheets.

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, and it’s always available for use with any workbook.