Microsoft Office Tutorials and References
In Depth Information
A Realistic Example That Uses Sub Procedures
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 wasn’t perfect. In
fact, it was far from perfect. I identified the following problems:
h Workbooks with many sheets took a long time to sort because the screen was continually
updated during the move operations.
 
Search JabSto ::




Custom Search