Skip to Main Content
Excel® 2007 VBA Programmer's Reference
book

Excel® 2007 VBA Programmer's Reference

by John Green, Stephen Bullen, Rob Bovey, Michael Alexander
March 2007
Intermediate to advanced content levelIntermediate to advanced
1173 pages
26h 1m
English
Wrox
Content preview from 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. Figure 6-1

Note

Excel ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Excel® 2007 Power Programming with VBA

Excel® 2007 Power Programming with VBA

John Walkenbach
Microsoft® Office Excel 2003 Programming Inside Out

Microsoft® Office Excel 2003 Programming Inside Out

Curtis Frye, Wayne S. Freeze, Felicia K. Buckingham

Publisher Resources

ISBN: 9780470046432Purchase book