Microsoft Office Tutorials and References
In Depth Information
A Realistic Example That Uses Sub Procedures
h 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.
h If Excel had no visible workbook windows, pressing the Ctrl+Shift+S shortcut key combo
caused the macro to fail.
h If the workbook’s structure was protected, the Move method failed.
h After sorting, the last sheet in the workbook became the active sheet. Changing the
user’s active sheet isn’t a good practice; it’s better to keep the user’s original sheet active.
h If I interrupted the macro by pressing Ctrl+Break, VBA displayed an error message.
h The macro can’t be reversed (that is, the Undo command is always disabled when a
macro is executed). If the user accidentally presses Ctrl+Shift+S, the workbook sheets are
sorted, and the only way to get them back to their original order is by doing it manually.
Fixing the problems
Fixing the screen-updating problem was a breeze. I inserted the following instruction to turn off
screen updating while the sheets were being moved:
Application.ScreenUpdating = False
This statement causes Excel’s windows to freeze while the macro is running. A beneficial side
effect is that it also speeds up the macro considerably. After the macro completes it operation,
screen updating is turned back on automatically.
It was also easy to fix the problem with the BubbleSort procedure: I used VBA’s UCase
function to convert the sheet names to uppercase for the comparison. This caused all the
comparisons to be made by using uppercase versions of the sheet names. The corrected line read as
If UCase(List(i)) > UCase(List(j)) Then
Another way to solve the “case” problem is to add the following statement to the top
of your module:
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.