Excel® 2007 VBA Programmer's Reference

Chapter 6. Data Lists

This chapter shows you how to set up VBA code to manage data in lists, and code to filter and sort information in lists. The features examined are:

• Sorting

• Tables (called Lists in Excel 2003)

• AutoFilter

• Advanced Filter

• Data Forms

As always, you can use the macro recorder to generate some basic code for these operations. However, the recorded code needs modification to make it useful, and the recorder can even generate erroneous code in some cases. You will see that dates can be a problem, if not handled properly, especially in an international setting.

You will also see that there is more than one way to perform some tasks. Because Excel has introduced new objects that manage, filter, and sort data, there has been some duplication of the features of older objects. This can be confusing, but it does give you a wide choice of options that you can tailor to fit your needs.

Structuring the Data

Before you can apply Excel's list management tools, your data must be set up in a very specific way. The data must be structured like a database table, with headings at the top of each column, which are the field names, and the data itself must consist of single rows of information, which are the equivalent of database records. The top row holding the field names is called the header record. Figure 6-1 shows a list that holds information on students.

Figure 6-1. Figure 6-1

Note

Excel ...