1.3 Why Learn Visual Basic for Applications?

Because you can use the Macro Recorder in Excel to record your actions in a macro and then later play them back, it might at first seem unnecessary to learn VBA. Recorded macros alone, however, cannot always fill your needs. A recorded macro can only play back each action you perform, in the same sequence you originally performed the actions, without deviation. You can use Visual Basic for Applications to enhance your recorded macros, greatly increasing their flexibility and power.

Recorded macros are inflexible, so they cannot respond to changed or changing conditions. You can create a macro programmed in VBA, however, that will evaluate various predetermined conditions and choose a different series of actions based on those conditions. If you execute an Excel macro, for example, that attempts to select a worksheet named Sales Chart when there is no such worksheet in the current workbook, your recorded macro will fail to execute correctly, and Excel will display an error message dialog. By adding VBA programming to this recorded macro, you could make it first test for the existence of the specified worksheet before selecting it, or even insert and rename a new worksheet if the desired worksheet does not exist.

When it comes to repetitive actions within the macro itself, recorded macros are rather limited. If you want a recorded macro to repeat an action several times, you must manually repeat that action the desired number of times when you record the macro. The macro then always repeats the action the same number of times, every time you execute the macro, until you edit the macro or re-record it. In contrast, a macro programmed in VBA can use predetermined conditions – or input from the macro's user – to repeat an action for a flexible number of times, or to choose whether the action should be performed at all.

As an example, you might want to record a macro to change the width of several adjacent columns in an Excel worksheet. If you want the macro to change the width of the first three columns in the worksheet, you must manually repeat the resizing operation for each of the three columns as you record the macro. The recorded macro only (and always) changes the width of the first three columns of a worksheet – you cannot use the same macro to change the width of two columns or four columns. Also, if you changed the width of the first three columns, your recorded macro only operates on the first three columns – you cannot use the same macro to change the width of the second through fourth columns rather than the first through third. By adding Visual Basic for Applications programming to this recorded macro, you can create a macro that asks you how many, and which, columns to resize, and even allows you to specify the new column width.

These two examples represent a couple of the simplest tasks that you can perform with VBA in your macros. There are many circumstances under which you will want to add decision-making and efficient repetition to recorded macros. The only way to get these features is to manually add VBA program statements to your recorded macro.

In addition to enhancing specific recorded macros, you can also use VBA to connect, organize, and control several recorded macros that you use to perform a complex overall task made up of several smaller tasks. For example, you might regularly import data from a database program into an Excel worksheet, format the data for display, generate a chart from the data, and then print the chart and formatted report.

To pull all these individual tasks together to create a single task performed by a single macro, you might first record a separate macro for each of the individual tasks – a macro to import the data, another macro to format the data for display, another macro to create the chart, and yet another macro to print the data. Next, you would organize the recorded macros so that they are executed in the proper sequence by a single macro that you write with VBA.

You can also use Visual Basic for Applications to control the execution of other applications and to automate the sharing of data between applications. The ability to use VBA to control other applications that also have VBA is called Automation. Data-sharing between applications typically uses Object Linking and Embedding (OLE). You will learn more about Automation and OLE starting in Chapter 37, "Working with Other Applications."