Programming Excel with VBA and .NET

Record and Read Code

The best way to learn about Excel objects, properties, and methods is by recording code . It’s even better than online Help. Recording will almost always tell you what you need to know if you know how to use it. When Excel records code, it translates your actions into Visual Basic code. This lets you reverse-engineer recorded actions or simply cut and paste recorded actions into other procedures.

For example, suppose that you have a workbook containing multiple sheets of sales data as shown in Figure 1-1. You want to format the data on each of the sheets and add a chart comparing units sold and revenue. This is a great opportunity to record some code.

Figure 1-1. An example for recording code

To record your code:

1. Choose Tools → Macros → Record New Macro. Excel displays the Record Macro dialog (Figure 1-2).

   

   Figure 1-2. Step 1

2. Select the range A1:C16 and choose Format → AutoFormat. Excel displays the AutoFormat dialog (Figure 1-3).

   

   Figure 1-3. Step 2

3. Select the Simple format and click OK. Excel formats the range.

4. Press Shift-Up to deselect the Total row and then choose Insert → Chart. Excel displays the Chart Wizard (Figure 1-4).

   Figure 1-4. Step 4

5. Click Finish to insert a chart ...