Excel 2003: The Missing Manual

Chapter 26. Programming Spreadsheets with VBA

When you use Excel’s macro recorder (covered in the previous chapter), you actually build a small program. Excel translates every action you take, from selecting a menu item to editing a cell, into a line of VBA code (short for Visual Basic for Applications), and inserts it into your new macro. The obvious benefit is that you can use the macro recorder without knowing the first thing about programming. There are limitations, however, to your free pass.

One problem is that you can record only actions that you yourself can perform. For example, if you want to create a macro that inserts a column with 100 identical cell values, you need to go through the drudgery of typing in each cell value so that the macro recorder knows what to capture. A more serious problem is that when you record a macro, you can respond only to the worksheet that’s in front of you. If you want to make a more flexible macro that has the ability to examine a variety of cells and the intelligence to respond to different conditions, you need to tap into some of VBA’s more advanced capabilities.

Sooner or later, every Excel guru delves into macro code. Often, your first encounter with macro code occurs when you need to enhance an existing recorded macro to make it more nimble or to correct a problem. Depending on your preference, these encounters might be the only time you come face to face with VBA, or it might be the start of a new career path as an Excel programmer ...