Microsoft Office Tutorials and References
In Depth Information
A Realistic Example That Uses Sub Procedures
Chapter 11 presents several additional examples that use error handling.
A Realistic Example That Uses Sub Procedures
In this chapter, I describe the basics of creating Sub procedures. Most of the previous examples, I
will admit, have been rather wimpy. The remainder of this chapter is a real-life exercise that
demonstrates many of the concepts covered in this and the preceding two chapters.
This section describes the development of a useful utility that qualifies as an application as
defined in Chapter 5. More important, I demonstrate the process of analyzing a problem and then
solving it with VBA. I wrote this section with VBA newcomers in mind. As a result, I don’t simply
present the code, but I also show how to find out what you need to know to develop the code.
You can find the completed application, named sheet sorter.xlsm , on the
companion CD-ROM.
The goal
The goal of this exercise is to develop a utility that rearranges a workbook by alphabetizing its
sheets (something that Excel can’t do on its own). If you tend to create workbooks that consist of
many sheets, you know that locating a particular sheet can be difficult. If the sheets are ordered
alphabetically, however, it’s easier to find a desired sheet.
Project requirements
Where to begin? One way to get started is to list the requirements for your application. When
you develop your application, you can check your list to ensure that you’re covering all the bases.
Here’s the list of requirements that I compiled for this example application:
1.
It should sort the sheets (that is, worksheets and chart sheets) in the active workbook in
ascending order of their names.
2.
It should be easy to execute.
3.
It should always be available. In other words, the user shouldn’t have to open a workbook
to use this utility.
4.
It should work properly for any workbook that’s open.
5.
It should not display any VBA error messages.
 
Search JabSto ::




Custom Search