Excel 2003 VBA Programmer's Reference

23

Filtered Lists

In this chapter we will see how to set up VBA code to manage data in lists and code to filter information from lists. The features we will be looking at are the Data Form, AutoFilter, and Advanced Filter. We will demonstrate techniques for creating and managing filtered lists, generating supporting code using the macro recorder, how to use controls to display the lists, and how to side step possible problems relative to internationalization issues.

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. Figure 23-1 shows a list that holds information on the sales of fruit products.

Excel should never be considered a fully equipped database application. It is limited in the amount of data it can handle and it cannot efficiently handle multiple related database tables. However, Excel can work with other database applications to provide you with the data you need and it has some powerful tools, such as Data Form, AutoFilter, Advanced Filter, SubTotal and PivotTables, for analyzing, manipulating, and presenting that data. Examples of these elements are included in this chapter and Chapter 22.

Data Form

Excel has a builtin form that you can use to view, find, and edit data ...