Think about a single cell in Excel. There are many properties that describe a cell. A cell might contain a value such as 4, but the cell also has a font size, font color, a row, a column, possibly a formula, possibly a comment, a list of precedents, and more. It is possible in VBA to create a super-variable that contains all the information about a cell or about any object. A statement to create a typical variable such as x = Range("A1") will assign the current value of A1 to the variable x. However, use the Set keyword to create an object variable:

Set x = Range("A1")

You've now created a super-variable that contains all the properties of the cell. Instead of having a variable with only one value, you have a variable where you can access the value of many properties associated with the variable. You can reference x.Formula to learn the formula in A1, or x.Font.ColorIndex to learn the color of the cell.

Versions

Pivot tables have been evolving. They were introduced in Excel 5 and perfected in Excel 97. In Excel 2000, pivot table creation in VBA was dramatically altered. Some new parameters were added in Excel 2002. Therefore, you need to be extremely careful when writing code in Excel 2003 that might be run in Excel 2000 or Excel 97.

Just a few simple tweaks make 2003 code run in 2000, but a major overhaul is required to make 2003 code run in Excel 97. Because it has been eight years since the release of Excel 97 (and because Microsoft has not supported that product for 3+ years), this tutorial will focus on using only the pivot cache method introduced in Excel 2000. At the end of the tutorial, you will briefly learn the PivotTable Wizard method, which is your only option if you need code to run in Excel 97.