Microsoft Office Tutorials and References
In Depth Information
A Realistic Example That Uses Sub Procedures
Figure 9-10: An empty procedure in a module located in the Personal Macro Workbook.
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 didn’t know yet how many sheets were 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 inserted 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.
‘ 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