In This Chapter
VBA (Visual Basic for Applications) is Excel’s programming language, and it is used to create macros and custom worksheet functions that you can employ in formulas. In its broadest sense, a macro is a sequence of instructions that automates some aspect of Excel so that you can work more efficiently and with fewer errors.
Excel programming terminology can be a bit confusing. For example, VBA is a programming language, but it also serves as a macro language. What do you call something written in VBA and executed in Excel? Is it a macro or is it a program? Excel’s Help system often refers to VBA procedures as macros, so this is the terminology used in this book.
Over the next few chapters, we will introduce you to the world of VBA through the prism of creating worksheet functions. But before you can create custom functions by using VBA, you need to have some basic background knowledge of VBA as well as some familiarity with the Visual Basic Editor (VBE).
Most Excel users think of macros as a way of recording actions so that Excel can duplicate those actions on demand. Recording a macro is like programming a phone number into your cell phone. You first manually dial and save a number. Then when you want, you can redial those numbers with the touch of a button. Just as on a cell phone, you can record your actions in Excel while you perform them.
While you record, Excel gets busy in the background, translating your keystrokes and mouse clicks to written VBA code.
If you plan to work with VBA macros, you need to make sure that the Developer tab is visible. To display this tab, do the following:
Now that you have the Developer tab showing in the Excel Ribbon, you can start working with VBA. You have the option of either manually creating a macro or recording a macro. It’s often useful to start programming a procedure by recording a macro and letting Excel write the initial code for you.
Activate the Macro Recorder by selecting Record Macro from the Developer tab. This activates the Record Macro dialog box, as shown in Figure 23.1.
Here are the four parts of the Record Macro dialog box:
Shortcut Key: Every macro needs an event, or something to happen, for it to run.
This event can be a button press, a workbook opening, or in this case, a keystroke combination. When you assign a shortcut key to your macro, entering that combination of keys triggers your macro to run. This is an optional field.
With the Record Macro dialog box open, follow these steps to create a simple macro that enters your name into a worksheet cell:
The macro was recorded in a new module named Module1. To view the code in this module, you must activate the VB Editor. You can activate the VB Editor in either of two ways:
In the VB Editor, the Project window displays a list of all open workbooks and add-ins. This list is displayed as a tree diagram, which you can expand or collapse. The code that you recorded previously is stored in Module1 in the current workbook. When you double-click Module1, the code in the module appears in the code window.
The macro should look something like this:
Sub MyName() ' ' MyName Macro ' ' Keyboard Shortcut: Ctrl+Shift+N ' ActiveCell.FormulaR1C1 = "Michael Alexander" End Sub
The macro recorded is a Sub procedure that is named MyName. The statements tell Excel what to do when the macro is executed.
Notice that Excel inserted some comments at the top of the procedure. These comments are some of the information that appeared in the Record Macro dialog box. These comment lines (which begin with an apostrophe) aren’t really necessary, and deleting them has no effect on how the macro runs. If you ignore the comments, you’ll see that this procedure has only one VBA statement:
ActiveCell.FormulaR1C1 = "Michael Alexander"
This single statement causes the name you typed while recording to be inserted into the active cell.
Before you recorded this macro, you set an option that assigned the macro to the Ctrl+Shift+N shortcut key combination. To test the macro, return to Excel by using either of the following methods:
When Excel is active, activate a worksheet. (It can be in the workbook that contains the VBA module or in any other workbook.) Select a cell and press Ctrl+Shift+N. The macro immediately enters your name into the cell.
After you record a macro, you can make changes to it. For example, assume that you want your name to be bold. You can rerecord the macro, but this modification is simple, so editing the code is more efficient. Press Alt+F11 to activate the VB Editor window. Then activate Module1 and insert the following statement before the End Sub statement:
ActiveCell.Font.Bold = True
The edited macro appears as follows:
Sub MyName() ' ' MyName Macro ' ' Keyboard Shortcut: Ctrl+Shift+N ' ActiveCell.FormulaR1C1 = "Michael Alexander" ActiveCell.Font.Bold = True End Sub
Beginning with Excel 2007, Excel workbooks were given the standard file extension .xlsx. Files with the .xlsx extension cannot contain macros. If your workbook contains macros and you then save that workbook as an .xlsx file, your macros are removed automatically. Excel warns you that macro content will be disabled in that case.
If you want to retain the macros, you must save your file as an Excel Macro-Enabled Workbook. This gives your file an .xlsm extension. The idea is that all workbooks with an .xlsx file extension are automatically known to be safe, whereas you can recognize .xlsm files as a potential threat.
With the release of Office 2010, Microsoft introduced significant changes to its Office security model. One of the most significant changes is the concept of trusted documents. Without getting into the technical minutia, a trusted document is essentially a workbook you have deemed safe by enabling macros.
If you open a workbook that contains macros in Excel, you see a yellow bar message under the Ribbon stating that macros have been disabled.
If you click Enable content, it automatically becomes a trusted document. This means you no longer are prompted to enable the content as long as you open that file on your computer. The basic idea is that if you told Excel that you “trust” a particular workbook by enabling macros, it is highly likely you will enable macros each time you open it. Thus, Excel remembers that you’ve enabled macros before and inhibits any further messages about macros for that workbook.
This is great news for you and your clients. After enabling your macros just one time, your clients won’t be annoyed at the constant messages about macros, and you won’t have to worry that your macro-enabled dashboard will fall flat because macros have been disabled.
If the thought of any macro message coming up (even one time) unnerves you, you can set up a trusted location for your files. A trusted location is a directory that is deemed a safe zone in which only trusted workbooks are placed. A trusted location allows you and your clients to run a macro-enabled workbook with no security restrictions as long as the workbook is in that location.
To set up a trusted location, follow these steps:
After you specify a trusted location, any Excel file that is opened from this location will have macros automatically enabled.
Most user-created macros are designed for a specific workbook, but you may want to use some macros in all your work. You can store these general-purpose macros in the Personal Macro Workbook so that they’re always available to you. The Personal Macro Workbook is loaded whenever you start Excel. This file, named personal.xlsb, doesn’t exist until you record a macro using Personal Macro Workbook as the destination.
To record the macro in your Personal Macro Workbook, select the Personal Macro Workbook option in the Record Macro dialog box before you start recording. This option is in the Store Macro In drop-down list (refer to Figure 23.1).
If you store macros in the Personal Macro Workbook, you don’t have to remember to open the Personal Macro Workbook when you load a workbook that uses macros. When you want to exit, Excel asks whether you want to save changes to the Personal Macro Workbook.
When you create macros, you may want to have a clear and easy way to run each macro. A basic button can provide a simple but effective user interface.
As luck would have it, Excel offers a set of form controls designed specifically for creating user interfaces directly on spreadsheets. There are several types of form controls, from buttons (the most commonly used control) to scrollbars.
The idea behind using a form control is simple. You place a form control on a spreadsheet and then assign a macro to it—that is, a macro you’ve already recorded. When a macro is assigned to the control, that macro is executed, or played, when the control is clicked.
Here’s how:
Click the location where you want to place your button.
When you drop the button control onto your spreadsheet, the Assign Macro dialog box, as shown in Figure 23.4, activates and asks you to assign a macro to this button.
At this point, you have a button that runs your macro when you click it. Keep in mind that all the controls in the Form Controls group (shown in Figure 23.3) work in the same way as the command button in that you assign a macro to run when the control is selected.
You can also assign a macro to a button in Excel’s Quick Access toolbar. The Quick Access toolbar sits either above or below the Ribbon. You can add a custom button that runs your macro by following these steps:
The VBE is actually a separate application that runs when you open Excel. To see this hidden VBE environment, you need to activate it. The quickest way to activate the VBE is to press Alt+F11 when Excel is active. To return to Excel, press Alt+F11 again.
You can also activate the VBE by using the Visual Basic command found on Excel’s Developer tab.
Figure 23.6 shows the VBE program with some of the key parts identified. Chances are your VBE program window doesn’t look exactly like what you see in the figure. The VBE contains several windows and is highly customizable. You can hide windows, rearrange windows, dock windows, and so on.
The VBE menu bar works just like every other menu bar you’ve encountered. It contains commands that you use to do things with the various components in the VBE. Many of the menu commands have shortcut keys associated with them.
The VBE also features shortcut menus. You can right-click virtually anything in the VBE and get a shortcut menu of common commands.
The Standard toolbar, which is directly under the menu bar by default, is one of four VBE toolbars available. You can customize the toolbars, move them around, display other toolbars, and so on. If you’re so inclined, use the View ➜ Toolbars command to work with VBE toolbars. Most people just leave them as they are.
The Project window displays a tree diagram that shows every workbook currently open in Excel (including add-ins and hidden workbooks). Double-click items to expand or contract them. You’ll explore this window in more detail in the “Working with the Project window” section later in this chapter.
If the Project window is not visible, press Ctrl+R or use the View ➜ Project Explorer command. To hide the Project window, click the Close button in its title bar. Alternatively, right-click anywhere in the Project window and select Hide from the shortcut menu.
A code window contains VBA code. Every object in a project has an associated code window. To view an object’s code window, double-click the object in the Project window. For example, to view the code window for the Sheet1 object, double-click Sheet1 in the Project window. Unless you’ve added some VBA code, the code window will be empty.
You find out more about code windows later in this chapter’s “Working with a code window” section.
The Immediate window may or may not be visible. If it isn’t visible, press Ctrl+G or use the View ➜ Immediate Window command. To close the Immediate window, click the Close button in its title bar (or right-click anywhere in the Immediate window and select Hide from the shortcut menu).
The Immediate window is most useful for executing VBA statements directly and for debugging your code. If you’re just starting out with VBA, this window won’t be all that useful, so feel free to hide it and free up some screen space for other things.
When you’re working in the VBE, each Excel workbook and add-in that’s open is a project. You can think of a project as a collection of objects arranged as an outline. You can expand a project by clicking the plus sign (+) at the left of the project’s name in the Project window. Contract a project by clicking the minus sign (–) to the left of a project’s name. Or you can double-click the items to expand and contract them.
Figure 23.7 shows a Project window with two projects listed: a workbook named Book1 and a workbook named Book2.
Every project expands to show at least one node called Microsoft Excel Objects. This node expands to show an item for each sheet in the workbook (each sheet is considered an object) and another object called ThisWorkbook (which represents the Workbook object). If the project has VBA modules, the project listing also shows a Modules node.
When you record a macro, Excel automatically inserts a VBA module to hold the recorded code. The workbook that holds the module for the recorded macro depends on where you chose to store the recorded macro, just before you started recording.
In general, a VBA module can hold three types of code:
A single VBA module can store any number of Sub procedures, Function procedures, and declarations. The way you organize a VBA module is completely up to you. Some people prefer to keep all their VBA code for an application in a single VBA module; others like to split up the code into several modules. It’s a personal choice, just like arranging furniture.
Follow these steps to manually add a new VBA module to a project:
Or you can do the following:
The new module is added to a Modules folder in the Project window (see Figure 23.8). Any modules you create in a given workbook are placed in this Modules folder.
You may want to remove a code module that is no longer needed. To do so, follow these steps:
Or do the following:
As you become proficient with VBA, you spend lots of time working in code windows. Macros that you record are stored in a module that is visible in the code window, and you can type VBA code directly into a VBA module.
Code windows are much like workbook windows in Excel. You can minimize them, maximize them, resize them, hide them, rearrange them, and so on. It’s often much easier to maximize the code window that you’re working on. Doing so lets you see more code and keeps you from getting distracted.
To maximize a code window, click the Maximize button in its title bar (right next to the X). Or just double-click its title bar to maximize it. To restore a code window to its original size, click the Restore button. When a window is maximized, its title bar isn’t really visible, so you’ll find the Restore button to the right of the Help box.
Sometimes you may want to have two or more code windows visible. For example, you may want to compare the code in two modules or copy code from one module to another. You can arrange the windows manually or use the Window ➜ Tile Horizontally or Window ➜ Tile Vertically commands to arrange them automatically.
You can quickly switch among code windows by pressing Ctrl+Tab. If you repeat that key combination, you keep cycling through all the open code windows. Pressing Ctrl+Shift+Tab cycles through the windows in reverse order.
Minimizing a code window gets it out of the way. You can also click the window’s Close button in a code window’s title bar to close the window completely. (Closing a window just hides it; you won’t lose anything.) To open it again, just double-click the appropriate object in the Project window. Working with these code windows sounds more difficult than it really is.
Before you can do anything meaningful, you must have some VBA code in the VBA module. You can get VBA code into a VBA module in three ways:
You have discovered the excellent method for creating code by using the Excel Macro recorder. However, not all tasks can be translated to VBA by recording a macro. You often have to enter your code directly into the module. Entering code directly basically means either typing the code yourself or copying and pasting code you have found somewhere else.
Entering and editing text in a VBA module works as you might expect. You can select, copy, cut, paste, and do other things to the text.
A single line of VBA code can be as long as you like. However, you may want to use the line continuation character to break up lengthy lines of code. To continue a single line of code (also known as a statement) from one line to the next, end the first line with a space followed by an underscore (_). Then continue the statement on the next line. Here’s an example of a single statement split into three lines:
Selection.Sort Key1:=Range("A1"), _ Order1:=xlAscending, Header:=xlGuess, _ Orientation:=xlTopToBottom
This statement would perform the same way if it were entered in a single line (with no line-continuation characters). Notice that the second and third lines of this statement are indented. Indenting is optional, but it helps clarify the fact that these lines are not separate statements.
The VBE has multiple levels of undo and redo. If you deleted a statement that you shouldn’t have, use the Undo button on the toolbar (or press Ctrl+Z) until the statement appears again. After undoing, you can use the Redo button to perform the changes you’ve undone.
Ready to enter some real, live code? Try the following steps:
Type the following code into the module:
Sub GuessName() Dim Msg as String Dim Ans As Long Msg = "Is your name " & Application.UserName & "?" Ans = MsgBox(Msg, vbYesNo) If Ans = vbNo Then MsgBox "Oh, never mind." If Ans = vbYes Then MsgBox "I must be clairvoyant!" End Sub
When you enter the code listed in step 5, you might notice that the VBE makes some adjustments to the text you enter. For example, after you type the Sub statement, the VBE automatically inserts the End Sub statement. And if you omit the space before or after an equal sign, the VBE inserts the space for you. Also, the VBE changes the color and capitalization of some text. This is all perfectly normal. It’s just the VBE’s way of keeping things neat and readable.
If you followed the previous steps, you just created a VBA Sub procedure, also known as a macro. When you press F5 with the cursor in the procedure, Excel executes the code and follows the instructions. In other words, Excel evaluates each statement and does what you told it to do. You can execute this macro any number of times, although it tends to lose its appeal after a few dozen executions.
This simple macro uses the following concepts:
As mentioned previously, you can copy and paste code into a VBA module. For example, a Sub or Function procedure that you write for one project might also be useful in another project. Instead of wasting time reentering the code, you can activate the module and use the normal copy-and-paste procedures (Ctrl+C to copy and Ctrl+V to paste). After pasting it into a VBA module, you can modify the code as necessary.
As with any application, you should save your work frequently while working in the VB Editor. To do so, use File ➜ Save xxxx (where xxxx is the name of the active workbook), press Ctrl+S, or click the Save button on the standard toolbar.
The VB Editor does not have a Save As command. If you save a workbook for the first time from the Editor, you are presented with Excel’s standard Save As dialog box. If you want to save your project with a different name, you need to activate Excel and use Excel’s Save As command.
If you’re serious about becoming an Excel programmer, you’ll spend a lot of time with VBA modules on your screen. To help make things as comfortable as possible, the VBE provides quite a few customization options.
When the VBE is active, choose Tools ➜ Options. You’ll see a dialog box with four tabs: Editor, Editor Format, General, and Docking. Take a moment to explore some of the options found on each tab.
Figure 23.9 shows the options accessed by clicking the Editor tab of the Options dialog box. Use the options in the Editor tab to control the way certain things work in the VBE.
The Auto Syntax Check setting determines whether the VBE pops up a dialog box if it discovers a syntax error while you’re entering your VBA code. The dialog box tells roughly what the problem is. If you don’t choose this setting, VBE flags syntax errors by displaying them in a different color from the rest of the code, and you don’t have to deal with any dialog boxes popping up on your screen.
If the Require Variable Declaration option is set, VBE inserts the following statement at the beginning of each new VBA module you insert:
Option Explicit
Changing this setting affects only new modules, not existing modules. If this statement appears in your module, you must explicitly define each variable you use. Using a Dim statement is one way to declare variables.
If the Auto List Members option is set, VBE provides some help when you’re entering your VBA code. It displays a list that logically completes the statement you’re typing. This is one of the best features of the VBE.
If the Auto Quick Info option is selected, VBE displays information about functions and their arguments as you type. This is similar to the way Excel lists the arguments for a function as you start typing a new formula.
If the Auto Data Tips option is set, VBE displays the value of the variable over which your cursor is placed when you’re debugging code. This is turned on by default and is often quite useful. There is no reason to turn this option off.
The Auto Indent setting determines whether VBE automatically indents each new line of code the same as the previous line. Most Excel developers are keen on using indentations in their code, so this option is typically kept on.
Figure 23.10 shows the Editor Format tab of the Options dialog box. With this tab, you can customize the way the VBE looks.
The Code Colors option lets you set the text color and background color displayed for various elements of VBA code. This is largely a matter of personal preference. Most Excel developers stick with the default colors. But if you like to change things up, you can play around with these settings.
The Font option lets you select the font that’s used in your VBA modules. For best results, stick with a fixed-width font such as Courier New. In a fixed-width font, all characters are the same width. This makes your code more readable because the characters are nicely aligned vertically and you can easily distinguish multiple spaces (which is sometimes useful).
The Size setting specifies the point size of the font in the VBA modules. This setting is a matter of personal preference determined by your video display resolution and how good your eyesight is.
This option controls the display of the vertical margin indicator bar in your modules. You should keep this turned on; otherwise, you can’t see the helpful graphical indicators when you’re debugging your code.
Figure 23.11 shows the options available under the General tab in the Options dialog box. In almost every case, the default settings are just fine.
The most important setting on the General tab is Error Trapping. If you are just starting your Excel macro writing career, it’s best to leave the Error Trapping set to Break on Unhandled Errors. This ensures Excel can identify errors as you type your code.
Figure 23.12 shows the Docking tab. These options determine how the various windows in the VBE behave. When a window is docked, it is fixed in place along one of the edges of the VBE program window. This makes it much easier to identify and locate a particular window. If you turn off all docking, you have a big, confusing mess of windows. Generally, the default settings work fine.
18.217.12.218