Yes, You Can Program in VBA

f the thought of having to perform some software programming leaves you anxious, then you must read this article. You’ll find—much to your surprise and delight—that if you’ve created macros in Excel or Word, you’ve probably done some basic programming already. You’ll also find that you can step up easily and edit those macros in Visual Basic for Applications (VBA) to perform more sophisticated automated tasks.

The code looks daunting but you’ll soon see that it’s quite intuitive. Once you learn how to read and write the code, a new world of software tools will open to you, enhancing your productivity mightily. This article introduces you to simple macro writing and then shows you how to edit the code to fine-tune and customize the program.

VBA is a programming language that is supported by many popular business applications, including the apps in the Microsoft Office Suite. All that’s required to start programming is to turn on the VBA Macro Recorder, which is built into all the apps, perform the tasks you want the macro to do, and voila! You’ve created Visual Basic code for a task that can be repeated whenever you wish, saving you loads of time.

For this tutorial, we’ll create special Excel column headings. As you know, Excel column headings don’t contain visual cues to help users differentiate headings from data. So we’ll create a routine that produces a distinctive column heading. Once we’ve created the macros, they can be used for other Excel worksheets as well. The column heading will have a navy blue background and a bold white font ( exhibit 1 ).

To begin, select all the cells in the range to be formatted. Now go to the toolbar and click on Tools, Macro and Record New Macro, which will evoke the Record Macro dialog box ( exhibit 2 )

Select a macro name that’s descriptive, such as: FormatColumnTitle. Leave no spaces between the words—a quirk of the Microsoft language. If you want to be able to run this macro using a combination of keys, move your cursor to the Shortcut key field and select a key combination. If you want to use Ctrl+H to run the macro, enter H in the Ctrl+ box; if you want to use Ctrl+Shift+H, press the Shift and H keys simultaneously; the Shortcut key field automatically updates with your selection. For our example, let’s assign Ctrl+Shift+H.

Caution. When assigning shortcut keys, be careful you don’t replace one of the standard Excel shortcuts, such as Copy (Ctrl+C), Paste (Ctrl+V), Cut (Ctrl+X), Undo (Ctrl+Z), Save (Ctrl+S), Open (Ctrl+O) or Print (Ctrl+P).

Next, decide where to store the macro. If the macro will be accessed only from the current file, save it to This Workbook. If you want to access the macro from any workbook, click on the drop-down arrow at the Store macro in field and select Personal Macro Workbook; that’s a special file named “Personal.xls” which automatically loads each time Excel starts, making it available in any spreadsheet. Let’s store the macro in the Personal Macro Workbook and type a description of what we expect this macro to do and then click OK ( exhibit 3 ).

Notice that the status bar at the lower left portion of your screen now reads “Recording” ( exhibit 4 ). That tells you the macro recorder is on, storing each keystroke and mouse action. So be careful that you perform only those steps necessary to format the columns, because every step is saved and will be repeated each time you run the macro.

Now we’re ready to tell Excel what we want this routine to do. Right-click on any cell in the selected range and choose Format Cells. In the Alignment tab, change Horizontal to Center Across Selection and check the box for Wrap text ( exhibit 5 ). In the Font tab, change Font style to Bold and Color to White. In the Border tab, choose the solid line style, change the Color to white, then click Outline and Inside .

Finally, select the Patterns tab and select navy (exhibit 6). Click OK when finished and you will see your spreadsheet update with the formatting you just applied.

Remember that the macro recorder is still operating, indicated by the Stop Recording toolbar floating over your workbook. If it’s not there, right-click on any toolbar, choose Stop Recording and then click on the blue square.

To see how the macro worked out, open the Visual Basic Editor (VBE) by pressing Alt+F11—the shortcut to clicking on Tools, Macro, Visual Basic Editor —and you’ll see a screen that resembles exhibit 7.

The Project window (the upper right section) contains two projects: Personal.xls and Spreadsheet Smarts Examples.xls, representing the open Excel workbooks. Below that is a Properties window that describes the selected object, which in this case is the worksheet shown in exhibit 1 . Since we told Excel to store the FormatColumnTitle macro in the Personal Macro Workbook, expand the Personal.xls file by clicking on the plus sign (+) to the left of that project. That will open two folders, Objects and Modules . Click on these folders and you will see all of the sheets in the Personal Macro Workbook in addition to a code module named Module1. (If you created any other macros and saved them to your Personal Macro Workbook, you may have more than one module. If this is the case, open the last module to find the routine you just created.) Double-click on Module1 to open it and view its contents. You should now see many indented lines with blue, black and green words that represent the VBA code that Excel wrote when you created the macro.

Notice that even though we didn’t set the font style while recording the macros, Excel still wrote the code: . Fontstyle = “Bold” and .Size = 10. Excel is programmed to record all properties of all fields within a dialog box even though you changed only one item during the recording session. Thus our code contains properties that are irrelevant to our desired task. Therefore, remove specific lines of code that you don’t want your routine to perform the next time. To do that, place your cursor at the left edge of the VBE window so that the cursor points to the right. Position your cursor on the line where we set the font to Arial, then click to select the entire line.

Press Delete and the line will disappear. We removed that code because you may use different fonts in your documents and we don’t want to override them within this macro. Our next step is to remove all remaining lines describing the font so that only .FontStyle and . ColorIndex are left.

This macro will change the format of any cells you select to indicate a column heading. However, if you want to apply this format to a single cell, rather than to a range of cells, you’ll have to insert a couple of additional lines. Add If Selection.Cells.Count > 1 Then and End If around the code section beginning with With Selection.Borders(xlInsideVertical) to permit a specific section of code to run only if more than one cell is selected. The finished code is shown in exhibit 9 .

What if you don’t like using the assigned keyboard strokes (CtrlL+Shift+H) to evoke the macro but prefer to use toolbar buttons instead? For that option, click on Tools , choose Customize and then select the Commands tab. Scroll down the list and choose Macros and you will see the selections change in the adjacent Commands column. Click on the Custom button and drag it to a toolbar. Right-click on the button, type Column Heading in the Name field, then click EditButton Image to change the image to something a bit more descriptive, perhaps text or an icon representing the appearance of the final format of the cell: .

Finally, click Assign macro, choose “Personal. xls!FormatColumnTitle,” click OK and Close . You now have an icon that can access the same routine as your keystrokes.

Now that you know the fundamentals of writing macros in VBA, you’re sure to come up with plenty of applications that you can launch with just a few keystrokes. A small investment of energy now can save you loads of time later.