Chapter 5: Explore Data Objects

Chapter at a Glance

In this chapter, you will learn to:

• Retrieve data from Excel lists and from external data sources.

• Create and format a table object.

• Create and format a PivotTable object.

• Use the macro recorder to learn about Excel objects.

On May 29, 1913, at the staid Théatre des Champs-Elysées in Paris, a scene of utter chaos occurred. It was the premiere performance of Igor Stravinsky's ballet “The Rite of Spring.” The Stravinsky music and Nijinsky choreography was so outrageously original that the audience couldn’t comprehend it. Today, “The Rite of Spring” is performed withoutany commotion, but Igor Stravinsky is still considered to be one of the most inventive composers ever. Stravinsky, however, acknowledged that he was influenced of others.

He once said, “Lesser artists borrow, great artists steal.” The term steal is of course used for dramatic effect, but the point is that when you borrow, the item still belongs to the original owner, but when you steal, you make it your own.

Some people think of the macro recorder as a tool for beginners-and it is. You can use the macro recorder to build usable macros without having to understand much about how Microsoft Office Excel 2007 objects really work. But the macro recorder is also a powerful reference tool for advanced developers. The secret is in whether you borrow from the recorder (leaving it essentially intact) or whether you steal (making the code your own).

In this chapter, you’ll see how you can use the macro recorder as a reference tool for learning how to work with Excel objects. You’ll record actions, but when it’s time to write the macros, you won’t modify the recorded macros. Instead, you’ll create your own macros, taking full advantage of object variables and methods and properties that the recorder doesn’t use. In essence, you’ll learn how to steal.

On The CD-Important 

Before you complete this chapter, you need to install the practice files from the book’s companion CD to their default locations. See “Using the Book’s CD” on page xv for more information.

USE the Orders.xlsx workbook for an internal source, and the Orders.accdb database as an external source. These practice files are located in the Documents\MSP\ExcelVBA07SBS folder.

BE SURE TO save a new empty workbook as a macro-enabled workbook named

Chapter05.xlsm in the trusted location you created in Chapter 1.

OPEN the Chapter05.xlsm workbook. Then select the range A1:F5, and apply a red background to the cells. As you create and close multiple workbooks in this chapter, the red cells will remind you which workbook contains the macros so that you don’t accidentally close it without saving changes and lose your work.