Chapter 28. Programming Spreadsheets with VBA

28. 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.

You'll find that you can record only actions that you can perform yourself. 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 may be the only time you come face to face with VBA, or it could be the start of a new career path as an Excel programmer extraordinaire.

There's no way to explain a complete programming language in one chapter. Instead, this chapter walks you through the Visual Basic editor, explains some important code techniques, and demonstrates a few useful macros. By the time you finish this chapter, you'll know enough about VBA to grab a useful snippet of macro code from an Excel Web site (and understand what it's supposed to do), or dive into an advanced book that's dedicated to VBA programming.