Microsoft Office Tutorials and References
In Depth Information
A Realistic Example That Uses Sub Procedures
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
By the way, I’m a major proponent of testing your work as you go. I tend to work in small steps
and set things up so that I’m convinced that each small step is working properly before I
continue. When you’re convinced that your code is working correctly, remove the MsgBox
statement. (These message boxes become annoying after a while.)
Rather than use the MsgBox function to test your work, you can use the Print method
of the Debug object to display information in the Immediate window. For this example,
use the following statement in place of the MsgBox statement:
This technique is much less intrusive than using MsgBox statements. Just make sure
that you remember to remove the statement when you’re finished.
At this point, the SortSheets procedure simply creates an array of sheet names corresponding
to the sheets in the active workbook. Two steps remain: Sort the elements in the SheetNames
array and then rearrange the sheets to correspond to the sorted array.
Writing the Sort procedure
It was time to sort the SheetNames array. One option was to insert the sorting code in the
SortSheets procedure, but I thought a better approach was to write a general-purpose sorting
procedure that I could reuse with other projects. (Sorting arrays is a common operation.)
You might be a bit daunted by the thought of writing a sorting procedure. The good news is that
it’s relatively easy to find commonly used routines that you can use or adapt. The Internet, of
course, is a great source for such information.
You can sort an array in many ways. I chose the bubble sort method; although it’s not a
particularly fast technique, it’s easy to code. Blazing speed isn’t really a requirement in this particular
The bubble sort method uses a nested For-Next loop to evaluate each array element. If the
array element is greater than the next element, the two elements swap positions. The code
includes a nested loop, so this evaluation is repeated for every pair of items (that is, n – 1 times).
In Chapter 11, I present some other sorting routines and compare them in terms of