Chapter 23: Introducing VBA

In This Chapter

• An introduction to VBA

• How to use the Visual Basic Editor

• How to work in the code windows of the Visual Basic Editor

This chapter introduces you to Visual Basic for Applications (VBA). VBA is Excel's programming language, and it is used to create macros and custom worksheet functions that you can use in formulas. Creating worksheet functions is the focus of this and the next three chapters. 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.

About VBA

VBA is best thought of as Microsoft's common application scripting language. VBA is included with most Office applications, and it's also available in applications from other vendors. In Excel, VBA has two primary uses:

• Automating spreadsheet tasks

• Creating custom functions that you can use in your worksheet formulas

Excel also includes another way of creating custom functions by using the XLM macro language. XLM is pretty much obsolete, but it is still supported for compatibility purposes. This book completely ignores the XLM language and focuses on VBA. By the way, the XLM macro language has absolutely nothing to do with XML, which is a mark-up language for storing structured data.

VBA is a complex topic — far too complex to be covered completely in this book. Because this book deals with formulas, I hone in on one important (and useful) aspect of VBA — creating custom worksheet functions. You can use a custom worksheet function (sometimes known as a “user-defined function” or UDF) in formulas.

If your goal is to become a VBA expert, this book nudges you in that direction, but it does not get you to your final destination. You may want to check out another book of mine: Excel 2013 Power Programming with VBA (Wiley). That book covers all aspects of VBA programming for Excel.

Displaying the Developer Tab

If you plan to work with VBA macros, make sure that the Developer tab is displayed in Excel. To display this tab

1. Right-click anywhere on the Ribbon and then choose Customize the Ribbon to display the Customize Ribbon tab of the Excel Options dialog box.

2. In the list box on the right, place a check mark next to Developer.

3. Click OK to return to Excel.

After completing these steps, the Developer tab will always be displayed. Figure 23-1 shows how the Ribbon looks when the Developer tab is selected.

Figure 23-1: The Developer tab, which does not display by default, contains commands that are useful for VBA users.

About Macro Security

Macros have the potential to cause serious damage to your computer. Consequently, Microsoft has added macro security features to help prevent macro-related problems.

Figure 23-2 shows the Macro Settings section of the Trust Center dialog box. To display this dialog box, choose Developer⇒Code⇒Macro Security.

By default, Excel uses the Disable All Macros with Notification option. With this setting in effect, if you open a workbook that contains VBA macros (and the file is not digitally “signed” or is from an untrusted location), Excel displays a Security Warning above the Formula bar (see Figure 23-3). If you are certain that the workbook comes from a trusted source and that the macros are safe, click the Enable Content button to enable the macros in the workbook.

Figure 23-2: The Macro Settings section of the Trust Center dialog box.

Excel remembers your decision; if you enable the macros in a workbook, you will not see the Security Warning the next time you open that file.

Figure 23-3: Excel's Security Warning that the file contains macros.

If the VBA window is open when you open a workbook that contains macros, Excel does not display the Security Warning in the workbook. Rather, it displays the dialog box shown in Figure 23-4. You can use this dialog box to enable or disable the macros.

Figure 23-4: You see this warning when the VBA window is open and a workbook contains macros.

Perhaps the best way to handle macro security is to designate one or more folders as trusted locations. All workbooks in a trusted location are opened without a macro warning. You designate trusted folders in the Trusted Locations section of the Trust Center dialog box.

Saving Workbooks That Contain Macros

If you store one or more VBA macros in a workbook, you must save the file with macros enabled. This is a file with an XLSM extension (or XLAM extension if it's an add-in). This file format is not the default format, so you need to make sure that you save the file with the correct extension.

For example, assume that you create a new workbook containing one or more macros. The first time you save the workbook, the file format defaults to XLSX, which is a file format that cannot contain macros. Unless you change the file format to XLSM, Excel displays the warning shown in Figure 23-5. You need to click No and then select Excel Macro-Enabled Workbook (*.xlsm) from the Save as Type drop-down list.

Be careful because Excel makes it very easy to accidentally delete all your macros with a single button click. If you accidentally click Yes instead of No (or press Enter), Excel deletes the macros from the saved workbook. The macros are still available in the copy that you're working on, however. So if you catch your mistake, it's still not too late to resave the workbook with an XLSM extension.

Figure 23-5: Excel warns you if your workbook contains macros and you attempt to save it in a nonmacro file format.

Introducing the Visual Basic Editor

Before you can begin creating custom functions, you need to become familiar with the Visual Basic Editor, or VB Editor for short. The VB Editor enables you to work with VBA modules, which are containers for your VBA code.

Activating the VB Editor

When you work in Excel, you can switch to the VB Editor by using either of the following techniques:

• Press Alt+F11.

• Choose Developer⇒Code⇒Visual Basic.

Figure 23-6 shows the VB Editor. Chances are that your VB Editor window won't look exactly like the window shown in the figure. This window is highly customizable. You can hide windows, change their sizes, “dock” them, rearrange them, and so on.

Figure 23-6: The Visual Basic Editor window.

The VB Editor components

The VB Editor consists of a number of components. I briefly describe some of the key components in the following sections.

The VB Editor in Excel 2013 still uses the old-style menu and toolbar interface instead of the newer Ribbon interface.

Menu bar

The VB Editor menu bar is like the menu bar for most Windows applications. It contains commands that you use to work with the various components in the VB Editor.

Shortcut menus

The VB Editor also features shortcut menus. Right-click virtually anything in a VB Editor window to get a shortcut menu of common commands.

Toolbars

The standard toolbar, directly under the menu bar by default, is one of six VB Editor toolbars. You can customize toolbars, move them around, dock them, display additional toolbars, and so forth.

Project window

The Project window displays a tree diagram that consists of every workbook that's open in Excel (including add-ins and hidden workbooks). In the VB Editor, each workbook is a project. I discuss the Project window in more detail in the upcoming section, “Using the Project window.” If the Project window is not visible, press Ctrl+R.

Code window

A code window contains VBA code. Just about every item in a project has an associated code window. To view a code window for an object, either double-click the object in the Project window, or select the item and then click the View Code button at the top of the Project window.

For example, to view the code window for the Sheet1 object for a particular workbook, double-click Sheet1 in the Project window. Unless you've added some VBA code, the code window will be empty. I discuss code windows later in this chapter (see the “Using code windows” section).

Properties window

The Properties window contains a list of all properties for the selected object. Use this window to examine and change properties. You can use the F4 shortcut key to display the Properties window.

Immediate window

The Immediate window is most useful for executing VBA statements directly, testing statements, and debugging your code. This window may or may not be visible. If the Immediate window is not visible, press Ctrl+G. To close the Immediate window, click the Close button on its title bar.

Using the Project window

When you work in the VB Editor, 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. To contract a project, click the minus sign (–) to the left of a project's name. Figure 23-7 shows the Project window with two projects listed. One of the projects (MyFunction.xlsm) is expanded to show its components.

Figure 23-7: A Project window with two projects listed.

If you try to expand a project that is password protected, Excel prompts you to enter the password.

Every project expands to show at least one node called Microsoft Excel Objects. This node expands to show an item for each worksheet and chart sheet in the workbook: Each sheet is considered an object, and ThisWorkbook represents the Workbook object. If the project has any VBA modules, the project listing also shows a Modules node with the modules listed there. A project may also contain a node called Forms (which contains UserForm objects) and a node called Class Modules (which contains Class Module objects). This book focuses exclusively on standard VBA modules and does not cover the objects contained in the Microsoft Excel Objects node, UserForms node, or Class Modules node.

A project may have another node called References. This node contains a list of all references that the project uses. You can add or remove References by choosing Tools⇒References. Unlike other items listed in the Project window, Reference items don't have an associated code module.

Renaming a project

By default, all projects are named VBAProject. In the Project window, the workbook name appears (in parentheses) next to the project name. For example, a project may appear as follows:

VBAProject (budget.xlsm)

You may prefer to change the name of your project to a more descriptive name. To do so, follow these steps:

1. Select the project in the Project window.

2. Make sure that the Properties window is displayed (press F4 if it's not displayed).

3. Use the Properties window to change the name from VBAProject to something else.

After making the change, the Project window displays the new name.

Adding a new VBA module

A new Excel workbook does not have any VBA modules. To add a VBA module to a project, select the project's name in the Project window and choose Insert⇒Module.

When you create custom worksheet functions, they must reside in a standard VBA module and not in a code window for a Sheet object (for example, Sheet1) or the ThisWorkbook object. If the code for your custom function does not reside in a VBA module, it won't work. Putting VBA code in the wrong place is perhaps the most common error made by users who are learning to write custom worksheet functions.

Renaming a module

VBA modules have default names, such as Module1, Module2, and so on. To rename a VBA module, select it in the Project window and then change the Name property by using the Properties window. (A VBA module has only one property — Name.) If the Properties window is not visible, press F4 to display it.

Figure 23-8 shows a VBA module named Module1 that is being renamed to CustomFunctions.

Exporting a VBA module

If you create macros that you want to share with others, one way to share the macros is to export the module that contains them. Choose File⇒Export File, and you are prompted for a filename and location. Exported modules are text files. You (or another user) can import these files by choosing File⇒Import File.

Figure 23-8: Use the Properties window to change the name of a VBA module.

Removing a VBA module

If you want to remove a VBA module from a project, select the module's name in the Project window and choose File⇒Remove xxx (where xxx is the name of the module). You are asked whether you want to export the module before removing it.

Using code windows

With the exception of Reference objects, each object in a project has an associated code window. To summarize, these objects can be

• The workbook itself (the item named ThisWorkbook in the Project window)

• A worksheet or chart sheet in a workbook (for example, Sheet1 or Chart1 in the Project window)

• A VBA module (a module that contains general VBA code, including the code for custom worksheet functions)

• A UserForm (a module that contains code for a custom dialog box)

• A Class module (a special type of module that enables you to create new object classes)

This book focuses exclusively on VBA modules — also known as standard modules — which is where Excel stores custom worksheet functions.

Minimizing and maximizing windows

At any given time, the VB Editor may have lots of code windows. Figure 23-9 shows an example.

Figure 23-9: Code window overload.

Code windows are much like worksheet windows in Excel. You can minimize them, maximize them, hide them, rearrange them, and so on. Most people find that it's much easier to maximize the code window that they're working on. Sometimes, however, 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.

Minimizing a code window gets it out of the way. You also can click the Close button in a code window's title bar to close the window completely. To open it again, just double-click the appropriate object in the Project window.

You can't close a workbook from the VB Editor. You must reactivate Excel and close it from there.

Storing VBA code

In general, a module can hold three types of code:

• Sub procedures: A procedure is a set of instructions that performs some action. For example, you may have a Sub procedure that combines various parts of a workbook into a concise report.

• Function procedures: A function is a set of instructions that returns a single value or an array. You can use Function procedures in worksheet formulas.

• Declarations: A declaration is information about a variable that you provide to VBA. For example, you can declare the data type for variables that you plan to use. Declarations go at the top of the module.

A single VBA module can store any number of procedures and declarations.

This book focuses exclusively on Function procedures, which are the only type of procedure that you can use in worksheet formulas.

Entering VBA code

This section describes the various ways of entering VBA code in a code window. For Function procedures, the code window is always a VBA module. You can add code to a VBA module in three ways:

• Use your keyboard to type it.

• Use the Excel macro-recorder feature to record your actions and convert them into VBA code.

• Copy the code from another module and paste it into the module that you are working on.

Entering code manually

Sometimes, the most direct route is the best one. Type the code by using your keyboard. Entering and editing text in a VBA module works just as you expect. You can select text and copy it, or cut and paste it to another location.

Use the Tab key to indent the lines that logically belong together — for example, the conditional statements between an If and an End If statement. Indentation is not necessary, but it makes the code easier to read.

A single instruction in VBA can be as long as you want. For the sake of readability, however, you may want to break a lengthy instruction into two or more lines. To do so, end the line with a space followed by an underscore character, and then press Enter and continue the instruction on the following line.

Here's a statement on one line:

If IsNumeric(MyCell) Then Result = “Number” Else Result = “Non-Number”

Here's the same statement split over three lines:

If IsNumeric(MyCell) Then _

    Result = “Number” Else _

    Result = “Non-Number”

Notice that I indented the last two lines of this statement. Doing this is optional, but it helps to clarify the fact that these three lines make up a single statement.

After you enter an instruction, the VB Editor performs the following actions to improve readability:

• It inserts spaces between operators. If you enter Ans=1+2 (without any spaces), for example, VBA converts it to

Ans = 1 + 2

• The VB Editor adjusts the case of the letters for keywords, properties, and methods. If you enter the following text:

user=application.username

VBA converts it to

user = Application.UserName

• Because variable names are not case sensitive, the VB Editor adjusts the names of all variables with the same letters so that their case matches the case of letters that you most recently typed. For example, if you first specify a variable as myvalue (all lowercase) and then enter the variable as MyValue (mixed case), VBA changes all other occurrences of the variable to MyValue. An exception to this occurs if you declare the variable with Dim or a similar statement; in this case, the variable name always appears as it was declared.

• The VB Editor scans the instruction for syntax errors. If it finds an error, it changes the color of the line and may display a message describing the problem. You can set various options for the VB Editor in the Options dialog box (accessible by choosing Tools⇒Options).

Like Excel, the VB Editor has multiple levels of Undo and Redo. Therefore, if you mistakenly delete an instruction, you can click the Undo button (or press Ctrl+Z) repeatedly until the instruction returns. After undoing the action, you can choose Edit⇒Redo Delete (or click the Redo Delete toolbar button) to redo previously undone changes.

Using the macro recorder

Another way to get code into a VBA module is to record your actions by using the Excel macro recorder. No matter how hard you try, you cannot record a Function procedure (the type of procedure that is used for a custom worksheet function). All recorded macros are Sub procedures. Using the macro recorder can help you to identify various properties that you can use in your custom functions. For example, turn on the macro recorder to record your actions while you change the username. Follow these steps in Excel:

1. Choose Developer⇒Code⇒Record Macro.

2. In the Record Macro dialog box, accept the default settings and click OK to begin recording.

The Record Macro button's caption toggles to Stop Recording.

3. Choose File⇒Options and click the General tab.

4. Under the Personalize Your Copy of Microsoft Office heading, change the name in the User Name box.

5. Click OK to close the Excel Options dialog box.

6. Choose Developer⇒Code⇒Stop Recording.

7. Press Alt+F11 to activate the VB Editor.

8. In the Project window, select the project that corresponds to your workbook.

9. Double-click the VBA module that contains your recorded code.

Generally, this will be the module with the highest number (for example, Module3).

You'll find a VBA procedure that looks something like this:

Sub Macro1()

‘

‘ Macro1 Macro

‘

‘

    Application.UserName = “Robert Smith”

End Sub

Note that this is a Sub procedure, not a Function procedure. In other words, you can't use this procedure in a worksheet formula. If you examine the code, however, you'll see a reference to the UserName property. You can use this information when creating a Function procedure. For example, the following Function procedure uses the UserName property. This function, when used in a worksheet formula, returns the name of the user.

Function USER()

    USER = Application.UserName

End Function

You can consult the VBA Help system to identify various properties, but using the macro recorder is often more efficient if you don't know exactly what you're looking for. After you identify what you need, you can check the Help system for details.

You can use the Excel Options dialog box to change the UserName property back to what it was. Or, you can make the change by using VBA. Just edit the code in the recorded macro (replace the name quotes with the original username). Then, move the cursor anywhere within the Macro1 procedure and choose Run⇒Run Sub/UserForm (or press F5) to execute the macro. Executing the macro changes the UserName property.

Copying VBA code

This section has covered entering code directly and recording your actions to generate VBA code. The final method of getting code into a VBA module is to copy it from another module. For example, you may have written a custom function for one project that would also be useful in your current project. Rather than reenter the code, you can open the workbook, activate the module, and use the normal Clipboard copy-and-paste procedures to copy it into your current VBA module.

You also can copy VBA code from other sources. For example, you may find a listing on a web page or in a newsgroup. In such a case, you can select the text in your browser (or newsreader), copy it to the Clipboard, and then paste it into a module.

Saving your project

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.

When you save your project, you actually save your Excel workbook. By the same token, if you save your workbook in Excel, you also save the changes made in the workbook's VB project.

The VB Editor does not have a Save As command. If you save a workbook for the first time from the VB 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.