A Realistic Example That Uses Sub Procedures | Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

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.

CD-ROM

The completed application can be found 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 cannot do on its own). If you tend to create workbooks that consist of many sheets, you know that it can be difficult to locate a particular sheet. 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:

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 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. Therefore, recording a macro to alphabetize the sheets is not an option.
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:

Identify the active workbook.
Get a list of all the sheet names in the workbook.
Count the sheets.
Sort the sheet names (somehow).
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

Tip

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

Create an empty workbook with five worksheets, named Sheet1, Sheet2, Sheet3, Sheet4, and Sheet5.
Move the sheets around randomly so that they aren't in any particular order.
Save the workbook as Test.xlsx .
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.
Insert a new VBA module in Personal.xlsb (choose Insert Module).
Create an empty Sub procedure called SortSheets (see Figure 9-10).

Figure 9-10: An empty procedure in a module located in the Personal Macro Workbook.

Actually, you can store this macro in any module in the Personal Macro Workbook. However, it's a good idea to keep each macro (or group of related macros) in a separate module. That way, you can easily export the module and import it into a different project later on.
Activate Excel. Choose Developer Code Macros to display the Macro dialog box.
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. I knew that I needed to put the sheet names into an array of strings. Because I don't know yet how many sheets are 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 inserts 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. When you're convinced that your code is working correctly, remove the MsgBox statement. (These message boxes become annoying after a while.)

Tip

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:

 Debug.Print SheetNames(i)

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 is not 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 . This evaluation is repeated for every pair of items (that is, n “ 1 times).

CROSS-REFERENCE

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)     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 define 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 was not 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 is not 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 cannot be reversed (that is, the Undo command is disabled). 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 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 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 were made by using uppercase versions of the sheet names. The corrected line read as follows :

 If UCase(List(i)) > UCase(List(j)) Then

Tip	Another way to solve the "case" problem is to add the following statement to the top of your module: Option Compare Text 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 , 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 is not 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 the message box 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 online help, 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

Caution

Be very careful when you disable the Cancel key. If your code gets caught in an infinite loop, there is no way to 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 is available whenever Excel is running. At this point, the macro can be executed by selecting the macro's name from the Macro dialog box (Alt+F8 displays this dialog box) or by pressing Ctrl+Shift+S.

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.

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