Listing Alphabetizing Worksheets in a Workbook

' performs a simple bubble sort to ' sort the worksheets in the workbook Sub A1phabetizeWorksheets(wb As Workbook) Dim bSorted As Boolean Dim nSheetsSorted As Integer Dim nSheets As Integer Dim n As Integer nSheets = wb.Worksheets.Count nSheetsSorted = 0

Do While (nSheetsSorted < nSheets) And Not bSorted bSorted = True nSheetsSorted = nSheetsSorted + 1 For n = 1 To nSheets - nSheetsSorted If StrComp(wb.Worksheets(n).Name, _

wb.Worksheets(n + 1).Name, _ vbTextCompare) > 0 Then

' out of order - swap the sheets wb.Worksheets(n + 1).Move _ before:=wb.Worksheets(n) bSorted = False

End If


Loop End Sub

The most difficult part of this listing is the sorting algorithm. This routine uses a bubble sort algorithm—not the most efficient sorting algorithm in terms of performance, but one of the easier ones to understand. Because I'm not sorting a great number of items, I can live with easier to understand here over fastest performance.

A bubble sort works by comparing pairs of items in a list (in this case, a list of worksheet names). If the first item in the pair is greater than the second item, the items are swapped. If you have four elements in a list, the first two items are compared, then item 2 is compared to item 3, and finally item 3 is compared to item 4. After one pass through all of the items in the list, the last element is the one with the greatest value (A<Z). Because the last item is guaranteed to have the greatest value (it "bubbles" up), you don't need to check it again, so the next time through, you only need to consider the first three items in the list. This process is repeated until all of the items in the list have been considered or the items need no further sorting.

0 0

Post a comment