Yes, You Can Program in VBA
Justin D. Stein · Journal of accountancy online/Journal of accountancy · 2000
Writing macro code is easier than you think. If 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 colunm heading. Once we've created the macros, they can be used for other Excel worksheets as well. The colunm heading will have a navy blue background and a bold white font (exhibit 1, below). [Exhibit 1 ILLUSTRATION OMITTED] 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) [Exhibit 2 ILLUSTRATION OMITTED] 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). [Exhibit 3 ILLUSTRATION OMITTED] 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. [Exhibit 4 ILLUSTRATION OMITTED] 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. …