Version 5 of Excel introduced a powerful new macro language called Visual Basic for Application (VBA). Every copy of Excel shipped since 1993 has had a copy of the powerful VBA language hiding behind the worksheets.
Enable VBA in Your Copy of Excel
If you are using Excel 2000 or newer, VBA may be disabled. Before you can start using VBA, you need to enable macros on the Security dialog box. From the application menu, choose Tools, Macro, Security. Set the macro security level to medium. This will allow VBA macros to run, but you will have to explicitly enable them when you open Excel.
Visual Basic Editor
From Excel, type Alt+F11 or from the application menu, select Tools, Macro, Visual Basic Editor to open the Visual Basic Editor, as shown in Figure 12.1. The three main sections of the VBA Editor are described here. If this is your first time using VBA, some of these items may be disabled. Follow the instructions given in the following list to make sure that each is enabled:
Figure 12.1. The Visual Basic Editor window is lurking behind every copy of Excel shipped since 1993.
Visual Basic Tools
Visual Basic is a powerful development environment. Although this chapter cannot offer a complete course on VBA, if you are new to VBA, you will want to take advantage of these important tools in VBA:
The Macro Recorder
Excel offers a macro recorder that is about 90% perfect. Unfortunately, the last 10% is frustrating. Code that you record to work with one dataset will be hard-coded to work only with that dataset. This might work fine if your transactional database occupies cells A1:K41550 every single day, but if you are pulling in a new invoice register every day, it is unlikely that you will have the same number of rows each day. Given that you might need to work with other data, it would be a lot better if Excel could record selecting cells using the End key. This is one of the shortcomings of the macro recorder.
In reality, Excel pros will use the macro recorder to record code, but then expect to have to clean up the recorded code.
Understanding Object-Oriented Code
If you took a class in BASIC a long time ago, the recorded code in VBA is going to appear rather foreign to you. Whereas BASIC is a procedural language, VBA is an object-oriented language. Most lines of VBA code follow the Noun.Verb syntax. Except, in VBA, it is called Object.Method. Objects can be workbooks, worksheets, cells, or ranges of cells. Methods can be typical Excel actions, such as .Copy, .Paste, .PasteSpecial. Many methods allow adverbsparameters you use to specify how to perform the method. If you see a construct with a colon/equal sign, you know that the macro recorder is describing how the method should work. The final type of code that you might see is where you assign a value to the adjectives of an object. In VBA, adjectives are called properties. If you set ActiveCell.Font.ColorIndex = 3, you are setting the font color of the active cell to red. Note that when you are dealing with properties, there is only an equal sign, not a colon/equal sign.