Excel 2013: The Missing Manual

Chapter 30. 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. However, this free pass has limitations.

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, you first work with macro code 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 ...