Introduction to VBA

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:

• Project Explorer This pane displays a hierarchical tree of all open workbooks. Expand the tree to see the worksheets and code modules present in the workbook. If the Project Explorer is not visible, enable it with Ctrl+R.

• Properties window The Properties window is important when you begin to program user forms. It has some use when writing normal code, so enable it with F4.

• Code window This is the area where you will write your code. Code is stored in one or more code modules attached to your workbook. To add a code module to a workbook, select Insert, Code Module from the application menu.

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:

• As you begin to type code, Excel may offer a drop-down with valid choices. This feature, known as AutoComplete, allows you to type code faster and eliminate typing mistakes.

• For assistance on any keyword, put the cursor in the keyword and press the F1 key. You might need your installation CDs because the VBA help file is not in the default install.

• Excel checks each line of code as you finish it. Lines in error will appear in red. Comments will appear in green. You can add a comment by typing a single apostrophe. Use lots of comments so you can remember what each section of code is doing.

• Despite the aforementioned error checking, Excel may still encounter an error at runtime. If this happens, click the Debug button. The line that caused the error will be highlighted in yellow. Hover your cursor over any variable to see the current value of the variable.

• When you are in Debug mode, use the Debug menu to step line by line through code. You can toggle back and forth between Excel and VBA to see the effect of running a line of code on the worksheet.

• Other great debugging tools are breakpoints, the Watch window, the Object Browser, and the Immediate window. Read about these in the Excel help menu.

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.