Fixing the problems

Fixing the screen-updating problem was a breeze. I inserted the following instruction at the beginning of SortSheets to turn off screen updating:

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.

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. That way, all the comparisons were made by using uppercase versions of the sheet names. The corrected line read as follows:

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. If no active workbook exists, an error occurred. I used On Error Resume Next to ignore the error and then checked the value of Err. If Err is not equal to 0, 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:

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

To reactivate the original active sheet after the sorting was performed, I wrote code that assigned the original sheet to an object variable (OldActive) and then activated that sheet when the routine was finished.

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 needed to insert 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

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.

After I made all these corrections, the SortSheets procedure looked like Listing 9-1.

Listing 9-1: The Final Build for the SortSheets Procedure

Option Explicit Sub SortSheets()

This routine sorts the sheets of the ' active workbook in ascending order.

Dim SheetNames() As String

Dim i As Integer

Dim SheetCount As Integer

Dim Item As Object

Dim OldActive 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

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 OldActive = 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


Listing 9-1: The Final Build for the SortSheets Procedure (Continued)

' Reactivate the original active sheet

OldActive.Activate End Sub

Was this article helpful?

0 0

Post a comment