Code writing

Now it's time to write some code. I knew that I needed to put the sheet names into an array of strings. Because I don't know yet how many sheets are in the active workbook, I used a Dim statement with empty parentheses to declare the array. I knew that I could use ReDim afterward to redimension the array for the actual number of elements.

I entered the following code, which inserts the sheet names into the SheetNames array. I also added a MsgBox function within the loop just to assure me that the sheets' names were indeed being entered into the array.

Sub SortSheets()

' Sorts the sheets of the active workbook Dim SheetNames() as String Dim i as Long Dim SheetCount as Long

SheetCount = ActiveWorkbook.Sheets.Count ReDim SheetNames(1 To SheetCount) For i = 1 To SheetCount

SheetNames(i) = ActiveWorkbook.Sheets(i).Name MsgBox SheetNames(i) Next i End Sub

To test the preceding code, I activated the Test.xlsx workbook and pressed Ctrl+Shift+S. Five message boxes appeared, each displaying the name of a sheet in the active workbook. So far, so good.

By the way, I'm a major proponent of testing your work as you go. When you're convinced that your code is working correctly, remove the MsgBox statement. (These message boxes become annoying after a while.)

Tip Rather than use the MsgBox function to test your work, you can use the Print method of the Debug object to display information in the Immediate window. For this example, use the following statement in place of the MsgBox statement:

Debug.Print SheetNames(i)

This technique is much less intrusive than using MsgBox statements. Just make sure that you remember to remove the statement when you're finished.

At this point, the SortSheets procedure simply creates an array of sheet names corresponding to the sheets in the active workbook. Two steps remain: Sort the elements in the SheetNames array and then rearrange the sheets to correspond to the sorted array.

0 0

Post a comment