Microsoft Office Tutorials and References
In Depth Information
A Realistic Example That Uses Sub Procedures
What you know
Often, the most difficult part of a project is figuring out where to start. In this case, I started by
listing things that I know about Excel that may be relevant to the project requirements:
h Excel doesn’t have a command that sorts sheets, so I'm not re-inventing the wheel.
h I can’t create this type of macro by recording my actions. However, the macro might be
useful to provide some key information.
h I can move a sheet easily by dragging its sheet tab.
Mental note: Turn on the macro recorder and drag a sheet to a new location to find out
what kind of code this action generates.
h Excel also has a Move or Copy dialog box, which is displayed when I right-click a sheet
tab and choose Move or Copy. Would recording a macro of this command generate
different code than moving a sheet manually?
h I’ll need to know how many sheets are in the active workbook. I can get this information
h I’ll need to know the names of all the sheets. Again, I can get this information with VBA.
h Excel has a command that sorts data in worksheet cells.
Mental note: Maybe I can transfer the sheet names to a range and use this feature. Or,
maybe VBA has a sorting method that I can take advantage of.
h Thanks to the Macro Options dialog box, it’s easy to assign a shortcut key to a macro.
h If a macro is stored in the Personal Macro Workbook, it will always be available.
h I need a way to test the application while I develop it. For certain, I don’t want to be
testing it using the same workbook in which I’m developing the code.
Mental note: Create a dummy workbook for testing purposes.
h If I develop the code properly, VBA won’t display any errors.
Mental note: Wishful thinking . . .
Although I still didn’t know exactly how to proceed, I could devise a preliminary, skeleton plan
that describes the general tasks required:
Identify the active workbook.
Get a list of all the sheet names in the workbook.
Count the sheets.