Microsoft Office Tutorials and References
In Depth Information
A Realistic Example That Uses Sub Procedures
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.
Search JabSto ::

Custom Search