Microsoft Office Tutorials and References
In Depth Information
A Realistic Example That Uses Sub Procedures
Finally, it was time to think about sorting options. From the Help system, I learned that the Sort
method applies to a Range object. So one option was to transfer the sheet names to a range and
then sort the range, but that seemed like overkill for this application. I thought that a better
option was to dump the sheet names into an array of strings and then sort the array by using
Now I knew enough to get started writing some serious code. Before doing so, however, I needed
to do some initial setup work. To re-create my steps, follow these instructions:
Create an empty workbook with five worksheets, named Sheet1 , Sheet2 , Sheet3 ,
Sheet4 , and Sheet5 .
Move the sheets around randomly so that they aren’t in any particular order.
Save the workbook as Test.xlsx .
Activate the VBE and select the Personal.xlsb project in the Project Window.
If Personal.xlsb doesn’t appear in the Project window in the VBE, it means that
you’ve never used the Personal Macro Workbook. To have Excel create this workbook for
you, simply record a macro (any macro) and specify the Personal Macro Workbook as the
destination for the macro.
Insert a new VBA module in Personal.xlsb (choose Insert➜Module).
Create an empty Sub procedure called SortSheets (see Figure 9-10).
Actually, you can store this macro in any module in the Personal Macro Workbook.
However, keeping each group of related macros in a separate module is a good idea.
That way, you can easily export the module and import it into a different project later on.
Activate Excel and choose Developer➜Code➜Macros to display the Macro dialog box.
In the Macro dialog box, select the SortSheets procedure and click the Options button to
assign a shortcut key to this macro.
The Ctrl+Shift+S key combination is a good choice.