Microsoft Excel 2016 BIBLE (2016)

If you've ever wanted to automate routine operations so that you don't always have to perform boring, repetitious tasks manually, this part is for you. This part is also aimed at those Excel users who want to develop Excel-based applications for other users. Visual Basic for Applications (VBA) is the powerful programming language that you can use for these tasks, as well as for more esoteric purposes, such as developing that specialized worksheet function that you simply can't find in Excel.

In This Part

1. Chapter 39
Introducing Visual Basic for Applications

2. Chapter 40
Creating Custom Worksheet Functions

3. Chapter 41
Creating UserForms

4. Chapter 42
Using UserForm Controls in a Worksheet

5. Chapter 43
Working with Excel Events

6. Chapter 44
VBA Examples

7. Chapter 45
Creating Custom Excel Add-Ins

IN THIS CHAPTER

1. Introducing VBA macros

2. Creating VBA macros

3. Recording VBA macros

4. Writing VBA code

5. Learning more about VBA

This chapter is an introduction to the Visual Basic for Applications (VBA) macro language — a key component for users who want to customize and automate Excel. This chapter teaches you how to record macros and create simple macro procedures. Subsequent chapters expand upon the topics in this chapter.

Introducing VBA Macros

A macro is a sequence of instructions that automates some aspect of Excel so that you can work more efficiently and with fewer errors. You may create a macro, for example, to format and print your month-end sales report. After the macro is developed, you can then execute it to perform many time-consuming procedures automatically.

You don't have to be a power user to create and use simple VBA macros. Once they understand a few basics, even casual users can simply turn on Excel's macro recorder, which records your actions and converts them into a VBA macro. When you execute the recorded macro, Excel performs the actions again. More advanced users, though, can write code that tells Excel to perform tasks that can't be recorded. For example, you can write procedures that display custom dialog boxes or process data in a series of workbooks and even create special-purpose add-ins.

What You Can Do with VBA

VBA is an extremely rich programming language with thousands of uses. The following list contains just a few things that you can do with VBA macros. (Not all of these tasks are covered in this book.)

· Insert boilerplate text. If you need to enter standard text into a range of cells, you can create a macro to do the typing for you.

· Automate procedures that you perform frequently. For example, you may need to prepare a month-end summary. If the task is straightforward, you can develop a macro to do it for you.

· Automate repetitive operations. If you need to perform the same action in 12 different workbooks, you can record a macro while you perform the task once — and then let the macro repeat your action in the other workbooks.

· Create custom commands. For example, you can combine several Excel commands so that they're executed from a single keystroke or from a single mouse click.

· Create a simplified “front end” for users who don't know much about Excel. For example, you can set up a foolproof data-entry template.

· Develop new worksheet functions. Although Excel includes a wide assortment of built-in functions, you can create custom functions that greatly simplify your formulas.

· Create complete macro-driven applications. Excel macros can display custom dialog boxes and respond to new commands added to the Ribbon.

· Create custom add-ins for Excel. Most add-ins shipped with Excel were created with Excel macros. I used VBA exclusively to create my Power Utility Pak.

Displaying the Developer Tab

If you plan to work with VBA macros, make sure that the Developer tab is present on the Excel Ribbon. The Developer tab, which does not appear by default, contains useful commands for VBA users (see Figure 39.1). To display this tab, follow these steps:

Figure 39.1 The Developer tab.

1. Right-click any Ribbon control and select Customize the Ribbon from the shortcut menu. The Customize Ribbon tab of the Excel Options dialog box appears.

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

3. Click OK to return to Excel.

About Macro Security

Macros have the potential to cause serious damage to your computer, such as erasing files or installing malware. Consequently, Microsoft has added macro-security features to help prevent macro-related problems.

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

Figure 39.2 The Macro Settings section of the Trust Center dialog box.

By default, Excel uses the Disable All Macros with Notification option. With this setting in effect, if you open a workbook that contains macros (and the file is not digitally “signed”), the macros will be disabled, and Excel will display a security warning above the Formula bar (see Figure 39.3). If you're certain that the workbook comes from a trusted source, click the Enable Content button in the security warning area, and the macros will be enabled. Excel remembers your decision; if you enable the macros, you won't see the security warning the next time you open that file.

Figure 39.3 Excel displays a security warning if a workbook contains macros.

Note

If the Visual Basic (VB) Editor window is open when you open a workbook that contains macros, Excel does not display the security warning above the Formula bar. Instead, it displays a dialog box with two buttons: Enable Macros and Disable Macros.

Rather than deal with individual workbooks, you may prefer to designate one or more folders as “trusted locations.” All the 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 an XLSM extension.

The first time you save a workbook that contains macros (or even an empty VBA module), the file format defaults to XLSX — and this format can't contain macros. Unless you change the file format to XLSM, Excel displays the warning shown in Figure 39.4. You need to click No and then choose Excel Macro-Enabled Workbook (*.xlsm) from the Save As Type drop-down list in the Save As dialog box.

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

Note

Alternatively, you can save the workbook in the old Excel 97–2003 format (which uses an .xls extension) or the newer Excel binary format (which uses an XLSB extension). Both of these file formats can contain macros.

Two Types of VBA Macros

Before getting into the details of creating macros, you need to understand a key distinction. A VBA macro (also known as a procedure) can be one of two types: a Sub or a Function. The next two sections discuss the difference.

What's New in the Visual Basic Editor?

In a word, nothing. Beginning with Excel 2007, Microsoft made many changes to Excel's user interface. However, the VB Editor has remained untouched and seems like outdated software. The VBA language has been updated to accommodate most of the new Excel features, but the VB Editor has no new features, and the toolbars and menus work as they always have.

VBA Sub procedures

You can think of a Sub procedure as a new command that either the user or another macro can execute. You can have any number of Sub procedures in an Excel workbook. Figure 39.5 shows a simple VBA Sub procedure. When this code is executed, VBA inserts the current date into the active cell, applies a number format, makes the cell bold, sets the text color to white, sets the background color to black, and adjusts the column width.

Figure 39.5 A simple VBA procedure.

A workbook that contains this macro is available on this book's website at www.wiley.com/go/excel2016bible. It also includes a button that makes it easy to execute the macro. The file is named current date.xlsm.

Sub procedures always start with the keyword Sub, the macro's name (every macro must have a unique name), and then a pair of parentheses. (The parentheses are required; they're empty unless the procedure uses one or more arguments.) The End Sub statement signals the end of the procedure. The lines in between make up the procedure's code.

The CurrentDate macro also includes a comment. Comments are simply notes to yourself, and VBA ignores them. A comment line begins with an apostrophe. You can also put a comment in the same line as a statement. In other words, when VBA encounters an apostrophe, it ignores the rest of the text in the line.

You execute a VBA Sub procedure in any of the following ways:

· Choose Developer Code Macros (or press Alt+F8) to display the Macro dialog box. Select the procedure name from the list, and then click Run.

· Assign the macro to a control in the Quick Access Toolbar or to a control in the Ribbon.

· Press the procedure's shortcut key combination (if it has one).

· Click a button or other shape that has a macro assigned to it.

· If the VB Editor is active, move the cursor anywhere within the code and press F5.

· Execute the procedure by calling it from another VBA procedure.

VBA functions

The second type of VBA procedure is a function. A function returns a single value (just as a worksheet function always returns a single value). A VBA function can be executed by other VBA procedures or used in worksheet formulas, just as you would use Excel's built-in worksheet functions.

Figure 39.6 shows a custom worksheet function. This function is named CubeRoot, and it requires a single argument. CubeRoot calculates the cube root of its argument and returns the result. A Function procedure looks much like a Sub procedure. Notice, however, that function procedures begin with the keyword Function and end with an End Function statement.

Figure 39.6 This VBA function returns the cube root of its argument.

A workbook that contains this function is available on this book's website at www.wiley.com/go/excel2016bible. The file is named cube root.xlsm.

Creating VBA functions that you use in worksheet formulas can simplify your formulas and enable you to perform calculations that otherwise may be impossible. Chapter 40, “Creating Custom Worksheet Functions,” discusses VBA functions in greater detail.

Some Definitions

If you're new to VBA, you may be overwhelmed by the terminology. I've put together some key definitions to help you keep the terms straight. These terms cover VBA and UserForms (custom dialog boxes) — two important elements that are used to customize Excel:

· Code: VBA instructions that are produced in a module sheet when you record a macro. You can also enter VBA code manually.

· Controls: Objects on a UserForm (or in a worksheet) that you manipulate. Examples include buttons, check boxes, and list boxes.

· Function: One of two types of VBA macros that you can create. (The other is a Sub procedure.) A function returns a single value. You can use VBA functions in other VBA macros or in your worksheets.

· Macro: A set of VBA instructions performed automatically.

· Method: An action taken on an object. For example, applying the Clear method to a Range object erases the contents and formatting of the cells.

· Module: A container for VBA code.

· Object: An element that you manipulate with VBA. Examples include ranges, charts, and drawing objects.

· Procedure: Another name for a macro. A VBA procedure can be a Sub procedure or a Function procedure.

· Property: A particular aspect of an object. For example, a Range object has properties, such as Height, Style, and Name.

· Subprocedure: One of two types of Visual Basic macros that you can create. The other is a Function.

· UserForm: A container that holds controls for a custom dialog box and holds VBA code to manipulate the controls.

· VBA: Visual Basic for Applications. The macro language that is available in Excel as well as in the other Microsoft Office applications.

· VB Editor: The window (separate from Excel) that you use to create VBA macros and UserForms. Press Alt+F11 to toggle between Excel and the VB Editor.

Chapter 41, “Creating UserForms,” and Chapter 42, “Using UserForm Controls in a Worksheet,” explain UserForms in depth.

Creating VBA Macros

Excel provides two ways to create macros:

· Turn on the macro recorder and record your actions.

· Enter the code directly into a VBA module.

The following sections describe these methods.

Recording VBA macros

In this section, I describe the basic steps that you take to record a VBA macro. In most cases, you can record your actions as a macro and then simply replay the macro; you don't need to look at the code that's automatically generated. If simply recording and playing back macros is as far as you go with VBA, you don't need to be concerned with the language itself (although a basic understanding of how things work doesn't do any harm).

Recording your actions to create VBA code: The basics

The Excel macro recorder translates your actions into VBA code. To start the macro recorder, choose Developer Code Record Macro (or click the Record Macro icon on the left side of the status bar). The Record Macro dialog box, shown in Figure 39.7, appears.

Figure 39.7 The Record Macro dialog box.

The Record Macro dialog box presents several options:

· Macro Name: The name of the macro. Excel proposes generic names, such as Macro1, Macro2, and so on.

· Shortcut Key: You can specify a key combination that executes the macro. The key combination always uses the Ctrl key. You can also press Shift when you enter a letter. For example, pressing Shift while you enter the letter H makes the shortcut key combination Ctrl+Shift+H.

Caution

Shortcut keys assigned to macros take precedence over built-in shortcut keys. For example, if you assign Ctrl+S to a macro, you can't use the key combination to save your workbook when that macro is available.

· Store Macro In: The location for the macro. Your choices are the current workbook, your Personal Macro Workbook (see “Storing macros in your Personal Macro Workbook,” later in this chapter), or a new workbook.

· Description: A description of the macro (optional).

To begin recording your actions, click OK; your actions within Excel are converted to VBA code. When you finish recording the macro, choose Developer Code Stop Recording. Or you can click the Stop Recording button on the status bar. This button replaces the Start Recording button while your macro is being recorded.

Note

Recording your actions always results in a new Sub procedure. You can't create a Function procedure by using the macro recorder. You must create function procedures manually.

Recording a macro: A simple example

This example demonstrates how to record a simple macro that inserts your name into the active cell.

To create the macro, start with a new workbook and follow these steps:

1. Activate an empty cell.

Note

Select the cell to be formatted before you start recording your macro. This step is important. If you select a cell while the macro recorder is turned on, the actual cell that you select will be recorded into the macro. In such a case, the macro would always format that particular cell, and it would not be a general-purpose macro.

2. Choose Developer Code Record Macro. The Record Macro dialog box appears. (Refer to Figure 39.7.)

3. Enter a new single-word name for the macro, to replace the defaultMacro1name. For example, type MyName as the name.

4. Assign this macro to the shortcut key Ctrl+Shift+N by entering an uppercase N in the Shortcut Key field.

5. Make sure This Workbook is selected in the Store Macro In field.

6. Click OK to close the Record Macro dialog box and begin recording your actions.

7. Type your name into the selected cell and then press Enter.

8. Choose Developer Code Stop Recording (or click the Stop Recording button on the status bar).

Examining the macro

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:

· Press Alt+F11.

· Choose Developer Code Visual Basic.

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.

Figure 39.8 shows the recorded macro, as displayed in the Code window.

Figure 39.8 The MyName procedure was generated by the Excel macro recorder.

The macro should look something like this (with your name substituted for mine, of course):

Sub MyName()

'

' MyName Macro

'

' Keyboard Shortcut: Ctrl+Shift+N

'

ActiveCell.FormulaR1C1 = "John Walkenbach"

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 = "John Walkenbach"

This single statement causes the name you typed while recording the macro to be inserted into the active cell. The FormulaR1C1 part is a property of the Range object, but I'm getting ahead of myself.

Testing the macro

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:

· Press Alt+F11.

· Click the View Microsoft Excel button on the VB Editor toolbar.

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.

Editing the macro

After you record a macro, you can make changes to it (although you must know what you're doing). For example, assume that you want your name to be bold. You could 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 = "John Walkenbach"

ActiveCell.Font.Bold = True

End Sub

Test this new macro, and you see that it performs as it should.

Another example

This example demonstrates how to record a time-stamp macro that inserts the current date and time into the active cell. To create the macro, follow these steps:

1. Activate an empty cell.

2. Choose Developer Code Record Macro. The Record Macro dialog box appears.

3. Enter a new, single-word name for the macro to replace the defaultMacro1name. A good name is TimeStamp.

4. Assign this macro to the shortcut key Ctrl+Shift+T by entering an uppercase T in the Shortcut Key field.

5. Make sure This Workbook is selected in the Store Macro In field.

6. Click OK to close the Record Macro dialog box.

7. Enter this formula into the selected cell:

=NOW()

8. With the date cell selected, click the Copy button (or press Ctrl+C) to copy the cell to the Clipboard.

9. Choose Home Clipboard Paste Values (V). This step replaces the formula with static text so that the date and time do not update when the worksheet is calculated.

10.Press Esc to cancel Copy mode.

11.Choose Developer Code Stop Recording (or click the Stop Recording button on the status bar).

Running the macro

Activate an empty cell and press Ctrl+Shift+T to execute the macro. There's a pretty good chance that the macro won't work!

The VBA code that is recorded in this macro depends on a setting on the Advanced tab of the Excel Options dialog box: namely, after Pressing Enter, Move Selection. If this setting is enabled (which is the default), the recorded macro won't work as intended because the active cell was changed when you pressed Enter. Even if you reactivated the date cell while recording (in step 7), the macro still fails.

Examining the macro

Activate the VB Editor and take a look at the recorded code. Figure 39.9 shows the recorded macro, as displayed in the Code window.

Figure 39.9 The TimeStamp procedure was generated by the Excel macro recorder.

The procedure has five statements. The first inserts the NOW() formula into the active cell. The second statement selects cell E13 — an action I performed because the cell pointer moved to the next cell after I entered the formula. The exact cell address depends on where the cell pointer was when the macro was recorded.

The third statement copies the cell. The fourth statement, which is displayed on two lines (the underscore character means that the statement continues on the next line), pastes the Clipboard contents (as a value) to the current selection. The fifth statement cancels the dashed border around the copied cell.

The problem is that the macro is hard-coded to select cell E13. If you execute the macro when a different cell is active, the code always selects cell E13 before it copies the cell. This is not what you intended, and it causes the macro to fail.

Note

You'll also notice that the macro recorded some actions that you didn't make. For example, it specified several options for the PasteSpecial operation. Recording actions that you don't specifically make is just a by-product of the method that Excel uses to translate actions into code.

Rerecording the macro

You can fix the macro in several ways. If you understand VBA, you can edit the code so it works properly. Or you can rerecord the macro using relative references.

Activate the VBA Editor, delete the existing TimeStamp procedure, and rerecord it. Before you start recording, click the Use Relative References command in the Code group of the Developer tab. This control is a toggle, and it's turned off by default.

Figure 39.10 shows the new macro, recorded with relative references in effect.

Figure 39.10 This TimeStamp macro works correctly.

Testing the macro

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+T. The macro immediately enters the current date and time into the cell. You may need to widen the column to see the date and time.

When the result of a macro requires additional manual intervention, it's a sign that the macro could be improved. To widen the column automatically, just add this statement to the end of the macro (before the End Sub statement):

ActiveCell.EntireColumn.AutoFit

More about recording VBA macros

If you followed along with the preceding examples, you should have a better feel for how to record macros — and a good feel for problems that might occur with even simple macros. If you find the VBA code confusing, don't worry. You don't really have to be concerned with it as long as the macro that you record works correctly. If the macro doesn't work properly, rerecording the macro rather than editing the code is often easier.

A good way to learn about what is recorded is to set up your screen so that you can see the code that is being generated in the VB Editor windows. To do so, make sure that the Excel window isn't maximized; then arrange the Excel window and the VB Editor window so both are visible. While you're recording your actions, make sure that the VB Editor window is displaying the module in which the code is being recorded. (You may have to double-click the module name in the Project window.)

Tip

If you do a lot of work with VBA, consider adding a second monitor to your system. Then you can display Excel on one monitor and the VB Editor on the other.

Absolute versus relative recording

If you're going to work with recorded macros, you need to understand the concept of relative versus absolute recording modes. In a previous example in this chapter, I showed how even a simple macro could fail because of an incorrect recording mode.

Normally, when you record a macro, Excel stores exact references to the cells that you select. (That is, it performs absolute recording.) If you select the range B1:B10 while you're recording a macro, for example, Excel records this selection as

Range("B1:B10").Select

This VBA statement means exactly what it says: “Select the cells in the range B1:B10.” When you invoke the macro that contains this statement, the same cells are always selected, regardless of where the active cell is located.

Look in the Developer Code group of the Ribbon for Use Relative References. When you click this control, Excel changes its recording mode from absolute (the default) to relative. When recording in relative mode, selecting a range of cells is translated differently, depending on where the active cell is located. For example, if you're recording in relative mode and cell A1 is active, selecting the range B1:B10 generates the following statement:

ActiveCell.Offset(0, 1).Range("A1:A10").Select

This statement can be translated as “From the active cell, move 0 rows down and 1 column right, and then treat this new cell as if it were cell A1. Now select what would be A1:A10.” In other words, a macro that is recorded in relative mode starts out by using the active cell as its base and then stores relative references to this cell. As a result, you get different results, depending on the location of the active cell. When you replay this macro, the cells that are selected depend on the active cell. This macro selects a range that is 10 rows by 1 column, offset from the active cell by 0 rows and 1 column.

When Excel is recording in relative mode, the Use Relative Reference control appears with a background color. To return to absolute recording, click the Use Relative Reference control again (and it displays its normal state, with no background color).

Storing macros in your Personal Macro Workbook

Most user-created macros are designed for use in 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.

Note

The Personal Macro Workbook normally is in a hidden window to keep it out of the way.

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 box.

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.

Assigning a macro to a shortcut key

When you begin recording a macro, the Record Macro dialog box gives you an opportunity to provide a shortcut key for the macro. Here's what to do if you'd like to change the shortcut key or provide a shortcut key for a macro that doesn't have one:

1. Choose Developer Code Macros (or press Alt+F8). The Macro dialog box appears.

2. Select the macro name from the list.

3. Click the Options button. The Macro Options dialog box, shown in Figure 39.11, appears.

Figure 39.11 Use the Macro Options dialog box to add or change a shortcut key for a macro.

4. Specify the shortcut key. Use a single letter (for a Ctrl+letter shortcut), or press Shift and enter an uppercase letter (for a Ctrl+Shift+letter shortcut).

5. Click OK to return to the Macro dialog box.

6. Click Cancel to close the Macro dialog box.

Assigning a macro to a button

After you record a macro and test it, you may want to assign the macro to a button placed in a worksheet. You can follow these steps to do so:

1. If the macro is a general-purpose macro that you plan to use in more than one workbook, make sure that the macro is stored in your Personal Macro Workbook.

2. Choose Developer Controls Insert, and then click the icon identified as Button (Form Control). Figure 39.12 shows the list of controls. Move your mouse pointer over the icons, and you see a ToolTip that describes the control.

Figure 39.12 Adding a button to a worksheet so that it can be used to execute a macro.

3. Click the worksheet and drag to draw the button. When you release the mouse button, the Assign Macro dialog box appears.

4. Select the macro from the list.

5. Click OK to close the Assign Macro dialog box.

6. (Optional) Change the text that appears on the button to make it descriptive; right-click the button, choose Edit Text from the shortcut menu, and make your changes.

After you perform these steps, clicking the button executes the assigned macro.

Adding a macro to your Quick Access toolbar

You can also assign a macro to a button on your Quick Access toolbar:

1. Right-click the Quick Access toolbar, and choose Customize Quick Access Toolbar from the shortcut menu. The Quick Access Toolbar tab of the Excel Options dialog box appears.

2. Select Macros from the drop-down list on the left.

3. At the top of the list on the right, choose For All Documents, or For xxx (where xxx is the active workbook's name). This step determines whether the macro will be available for all workbooks or just the workbook that contains the macro.

4. Select your macro and click the Add button.

5. To change the icon or displayed text, click the Modify button.

After performing these steps, your Quick Access toolbar will display a button that executes your macro.

Writing VBA code

As demonstrated in the preceding sections, the easiest way to create a simple macro is to record your actions. To develop more complex macros, however, you have to enter the VBA code manually — in other words, write a program. To save time (and assist in the learning process), you can often combine recording with manual code entry.

Before you can begin writing VBA code, you must have a good understanding of such topics as objects, properties, and methods. And it doesn't hurt to be familiar with common programming constructs, such as looping and If-Then statements.

This section is an introduction to VBA programming, which is essential if you want to write (rather than record) VBA macros. It isn't intended to be a complete instructional guide. My book Excel 2016 Power Programming with VBA (Wiley) covers all aspects of VBA and advanced spreadsheet application development.

The basics: Entering and editing code

Before you can enter code, you must insert a VBA module into the workbook. If the workbook already has a VBA module, you can use the existing module sheet for your new code.

Follow these steps to insert a new VBA module:

1. Press Alt+F11 to activate the VB Editor window. The Project window displays a list of all open workbooks and add-ins.

2. In the Project window, locate and select the workbook you're working in.

3. Choose Insert Module. VBA inserts a new (empty) module into the workbook and displays it in the Code window.

A VBA module, which is displayed in a separate window, works like a text editor. You can move through the sheet, select text, insert, copy, cut, paste, and so on.

VBA Coding Tips

When you enter code into a module sheet, you're free to use indenting and blank lines to make the code more readable. In fact, this is an excellent habit.

After you enter a line of code (by pressing Enter), it's evaluated for syntax errors. If none is found, the line of code is reformatted, and colors are added to keywords and identifiers. This automatic reformatting adds consistent spaces (before and after an equal sign, for example) and removes extra spaces that aren't needed. If a syntax error is found, you receive a pop-up message, and the line is displayed in a different color (red, by default). You need to correct your error before you can execute the macro.

A single statement can be as long as you need. However, you may want to break the statement into two or more lines. To do so, insert a space followed by an underscore (_). The following code, although written as two lines, is actually a single VBA statement:

Sheets("Sheet1").Range("B1").Value = _

Sheets("Sheet1").Range("A1").Value

You can insert comments freely into your VBA code. The comment indicator is an apostrophe single quote character ('). Any text that follows a single quote on that line is ignored. A comment can be a line by itself, or it can be inserted after a statement. The following examples show two comments:

' Assign the values to the variables

Rate = .085 'Rate as of November 16

How VBA works

VBA is one of the most complex features in Excel, and you can easily get overwhelmed. To set the stage for the details of VBA, here is a concise summary of how VBA works:

· You perform actions in VBA by writing (or recording) code in a VBA module and then executing the macro in any one of various ways. VBA modules are stored in an Excel workbook, and a workbook can hold any number of VBA modules. To view or edit a VBA module, you must activate the VB Editor window. (Press Alt+F11 to toggle between Excel and the VB Editor window.)

· A VBA module consists of procedures. A procedure is computer code that performs some action. The following is an example of a simple Sub procedure called ShowSum, which adds 1 + 1 and displays the result in a pop-up dialog box:

·Sub ShowSum()

· Sum = 1 + 1

· MsgBox "The answer is " & Sum

End Sub

· A VBA module also can store function procedures. A function procedure performs calculations and returns a single value. A function can be called from another VBA procedure or can even be used in a worksheet formula. Here's an example of a function named AddTwo. This function sums two values, which are supplied as arguments, and returns the result:

·Function AddTwo(arg1, arg2)

· AddTwo = arg1 + arg2

End Function

· VBA manipulates objects. Excel provides more than 100 classes of objects that you can manipulate. Examples of objects include a workbook, a worksheet, a range on a worksheet, a chart, and a rectangle shape.

· Objects are arranged in a hierarchy and can act as containers for other objects. For example, Excel itself is an object called Application, and it contains other objects, such as Workbook objects. The Workbook object can contain other objects, such asWorksheet objects and Chart objects. A Worksheet object can contain objects such as Range objects, PivotTable objects, and so on. The arrangement of all these objects is referred to as an object model.

· Objects that are alike form acollection. For example, the Worksheets collection consists of all worksheets in a particular workbook. The ChartObjects collection consists of all ChartObjects on a worksheet. Collections are objects in themselves.

· You refer to an object in your VBA code by specifying its position in the object hierarchy, using a period as a separator.

· For example, you can refer to a workbook namedBook1.xlsxas

Application.Workbooks("Book1.xlsx")

· This expression refers to theBook1.xlsxworkbook in theWorkbookscollection. The Workbooks collection is contained in the Application object (that is, Excel). Extending this to another level, you can refer to Sheet1 in Book1 as follows:

Application.Workbooks("Book1.xlsx").Worksheets("Sheet1")

· You can take it to still another level and refer to a specific cell as follows:

Application.Workbooks("Book1.xlsx").Worksheets("Sheet1").Range("A1")

· If you omit specific references, Excel uses theactiveobjects. If Book1.xlsx is the active workbook, the preceding reference can be simplified as follows:

Worksheets("Sheet1").Range("A1")

· If you know that Sheet1 is the active sheet, you can simplify the reference even more:

Range("A1")

· Objects have properties. A property can be thought of as a setting for an object. For example, a Range object has properties, such as Value and Address. A Chart object has properties such as HasTitle and Type. You can use VBA both to determine object properties and to change them.

· You refer to properties by combining the object with the property, separated by a period. For example, you can refer to the value in cell A1 on Sheet1 as follows:

Worksheets("Sheet1").Range("A1").Value

· You can assign values to variables. A variable is a VBA element that holds a value or text. To assign the value in cell A1 on Sheet1 to a variable called Interest, use the following VBA statement:

Interest = Worksheets("Sheet1").Range("A1").Value

· Objects have methods. A method is an action that is performed with the object. For example, one of the methods for a Range object is ClearContents. This method clears the contents of the range.

· You specify methods by combining the object with the method, separated by a period. For example, to clear the contents of range A1:C12, use the following statement:

Worksheets("Sheet1").Range("A1:C12").ClearContents

· VBA also includes all the constructs of modern programming languages, including typed variables, arrays, looping, and debugging aids.

The preceding describes VBA in a nutshell. Now you just have to learn the details, some of which are covered in the rest of this chapter.

Objects and collections

VBA is an object-oriented language, which means that it manipulates objects, such as Ranges, Charts, and Shapes. These objects are arranged in a hierarchy. The Application object (which is Excel) contains other objects. For example, the Application object contains a number of objects, including the following:

· AddIns (a collection of AddIn objects)

· Windows (a collection of Window objects)

· Workbooks (a collection of Workbook objects)

Most of these objects can contain other objects. For example, a Workbook object can contain the following objects:

· Charts (a collection of Chart sheet objects)

· Names (a collection of Name objects)

· Styles (a collection of Style objects)

· Windows (a collection of Window objects in the workbook)

· Worksheets (a collection of Worksheet objects)

Each of these objects, in turn, can contain other objects. A Worksheet object, for example, can contain the following objects:

· ChartObjects (a collection of all ChartObject objects)

· PageSetup (an object that stores printing information)

· PivotTables (a collection of all PivotTable objects)

A collection consists of all like objects. For example, the collection of all Workbook objects is known as the Workbooks collection. You can refer to an individual object in a collection by using an index number or a name. For example, if a workbook has three worksheets (named Sheet1, Sheet2, and Sheet3), you can refer to the first object in the Worksheets collection in either of these ways:

Worksheets(1)

Worksheets("Sheet1")

Properties

The objects that you work with have properties, which you can think of as attributes of the objects. For example, a Range object has properties, such as Column, Row, Width, and Value. A Chart object has properties, such as Legend, ChartTitle, and so on. ChartTitle is also an object, with properties such as Font, Orientation, and Text. Excel has many objects, and each has its own set of properties. You can write VBA code to do the following:

· Examine an object's current property setting and take some action based on it.

· Change an object's property setting.

You refer to a property in your VBA code by placing a period (a dot) and the property name after the object's name. For example, the following VBA statement sets the Value property of a range named frequency to 15. (That is, the statement causes the number 15 to appear in the range's cells.)

Range("frequency").Value = 15

Some properties are read-only, which means that you can examine the property, but you can't change the property. For a single-cell Range object, the Row and Column properties are read-only properties: you can determine where a cell is located (in which row and column), but you can't change the cell's location by changing these properties.

A Range object also has a Formula property, which is not read-only; that is, you can insert a formula into a cell by changing its Formula property. The following statement inserts a formula into cell A12 by changing the cell's Formula property:

Range("A12").Formula = "=SUM(A1:A10)"

Note

Contrary to what you may think, Excel doesn't have a Cell object. When you want to manipulate a single cell, you use the Range object (with only one cell in it).

At the top of the object hierarchy is the Application object, which is actually Excel, the program. The Application object has several useful properties:

· Application.ActiveWorkbook: Returns the active workbook (a Workbook object) in Excel.

· Application.ActiveSheet: Returns the active sheet (a Sheet object) of the active workbook.

· Application.ActiveCell: Returns the active cell (a Range object) object of the active window.

· Application.Selection: Returns the object that is currently selected in the active window of the Application object. This can be a Range, a Chart, a Shape, or some other selectable object.

You also should understand that properties can return objects. In fact, that's exactly what the preceding examples do. The result of Application.ActiveCell, for example, is a Range object. Therefore, you can access properties by using a statement such as the following:

Application.ActiveCell.Font.Size = 15

In this case, the ActiveCell property returns a Range object. The Font property returns a Font object, which is contained in the Range object. Size is a property of the Font object. The preceding statement sets the Size property to 15 — that is, it causes the font in the currently selected cell to have a size of 15 points (pt).

Tip

Because Application properties are so commonly used, you can omit the object qualifier (Application). For example, to get the row of the active cell, you can use a statement such as the following:
ActiveCell.Row

In many cases, you can refer to the same object in a number of different ways. Assume that you have a workbook named Sales.xlsx and it's the only workbook open. Furthermore, assume that this workbook has one worksheet, named Summary. Your VBA code can refer to the Summary sheet in any of the following ways:

Workbooks("Sales.xlsx").Worksheets("Summary")

Workbooks(1).Worksheets(1)

Workbooks(1).Sheets(1)

Application.ActiveWorkbook.ActiveSheet

ActiveWorkbook.ActiveSheet

ActiveSheet

The method that you use is determined by how much you know about the workspace. For example, if more than one workbook is open, the second or third method isn't reliable. If you want to work with the active sheet (whatever it may be), any of the last three methods would work. To be absolutely sure that you're referring to a specific sheet on a specific workbook, the first method is your best choice.

Methods

Objects also have methods. You can think of a method as an action taken with an object. For example, Range objects have a Clear method. The following VBA statement clears a Range, an action that is equivalent to selecting the Range and then choosing Home Editing Clear Clear All:

Range("A1:C12").Clear

In VBA code, methods look like properties because they're connected to the object with a “dot.” However, methods and properties are different concepts.

Variables

Like all programming languages, VBA enables you to work with variables. In VBA (unlike in some languages), you don't need to declare variables explicitly before you use them in your code (although doing so is definitely a good practice).

Note

If your VBA module contains an Option Explicit statement at the top of the module, you must declare all variables in the module. Undeclared variables will result in a compile error, and your procedures will not run.

In the following example, the value in cell A1 on Sheet1 is assigned to a variable named Rate:

Rate = Worksheets("Sheet1").Range("A1").Value

After the statement is executed, you can work with the variable Rate in other parts of your VBA code.

Controlling execution

VBA uses many constructs that are found in most other programming languages. These constructs are used to control the flow of execution. This section introduces a few of the more common programming constructs.

The If-Then construct

One of the most important control structures in VBA is the If-Then construct, which gives your applications decision-making capability. The basic syntax of the If-Then structure is

If condition Then statements [Else elsestatements]

In plain English, if a condition is true, then a group of statements will be executed. If you include the Else clause, then another group of statements will be executed if the condition is not true.

The following is an example (which doesn't use the optional Else clause). This procedure checks the active cell. If it contains a negative value, the cell's font color is changed to red. Otherwise, nothing happens:

Sub CheckCell()

If ActiveCell.Value < 0 Then ActiveCell.Font.Color = vbGreen

End Sub

Here's another multiline version of that procedure that uses an Else clause. Because it uses multiple lines, you must include an End If statement. This procedure colors the active cell text red if it's a negative value, and green otherwise:

Sub CheckCell()

If ActiveCell.Value < 0 Then

ActiveCell.Font.Color = vbRed

Else

ActiveCell.Font.Color = vbGreen

End If

End Sub

For-Next loops

You can use a For-Next loop to execute one or more statements a number of times. Here's an example of a For-Next loop:

Sub SumSquared()

Total = 0

For Num = 1 To 10

Total = Total + (Num ^ 2)

Next Num

MsgBox Total

End Sub

This example has one statement between the For statement and the Next statement. This single statement is executed ten times. The variable Num takes on successive values of 1, 2, 3, and so on, up to 10. The variable Total stores the sum of Num squared, added to the previous value of Total. The result is a value that represents the sum of the first ten integers squared. This result is displayed in a message box.

The With-End With construct

A construct that you sometimes encounter if you record macros is the With-End With construct. This is a shortcut way of dealing with several properties or methods of the same object. The following is an example:

Sub AlignCells()

With Selection

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlCenter

.WrapText = False

.Orientation = xlHorizontal

End With

End Sub

The following macro performs the same operations but doesn't use the With-End With construct:

Sub AlignCells()

Selection.HorizontalAlignment = xlCenter

Selection.VerticalAlignment = xlCenter

Selection.WrapText = False

Selection.Orientation = xlHorizontal

End Sub

The Select Case construct

The Select Case construct is useful for choosing among two or more options. The following example demonstrates the use of a Select Case construct. In this example, the active cell is checked. If its value is less than 0, it's colored red. If it's equal to 0, it's colored blue. If the value is greater than 0, it's colored black:

Sub CheckCell()

Select Case ActiveCell.Value

Case Is < 0

ActiveCell.Font.Color = vbRed

Case 0

ActiveCell.Font.Color = vbBlue

Case Is > 0

ActiveCell.Font.Color = vbBlack

End Select

End Sub

Any number of statements can go below each Case statement, and they all are executed if the case is true.

A macro that can't be recorded

The following is a VBA macro that can't be recorded because it uses programming concepts that must be entered manually. This macro creates a list of all formulas on the active sheet. The list is stored on a new worksheet:

Sub ListFormulas()

' Create a range object

Set InputRange = ActiveSheet.UsedRange

' Add a new sheet

Set OutputSheet = Worksheets.Add

' Variable for the output row

OutputRow = 1

' Loop through the range

For Each cell In InputRange

If cell.HasFormula Then

OutputSheet.Cells(OutputRow, 1) = "'" & cell.Address

OutputSheet.Cells(OutputRow, 2) = "'" & cell.Formula

OutputRow = OutputRow + 1

End If

Next cell

End Sub

A workbook that contains this example is available on this book's website at www.wiley.com/go/excel2016bible. The file is named list formulas.xlsm.

Although this macro may look complicated, it's fairly simple when you break it down. Here's how it works:

1. The macro creates an object variable named InputRange. This variable corresponds to the used range on the active sheet (avoiding the need to check every cell).

2. It then adds a new worksheet and assigns the worksheet to an object variable named OutputSheet. The OutputRow variable is set to 1. This variable is incremented later on.

3. The For-Next loop examines each cell in the InputRange. If the cell has a formula, the cell's address and formula are written to the OutputSheet. The OutputRow variable is also incremented.

Figure 39.13 shows the result of running this macro — a handy list of all formulas in the worksheet.

Figure 39.13 The ListFormulas macro creates a list of all formulas in a worksheet.

As macros go, this example is okay, but it's certainly not perfect. It's not very flexible, and it doesn't include error handling. For example, if the workbook structure is protected, trying to add a new sheet will cause an error.

Learning More

This chapter is a basic introduction to VBA. If this is your first exposure to VBA, you're probably a bit overwhelmed by objects, properties, and methods. I don't blame you. If you try to access a property that an object doesn't have, you get a run-time error, and your VBA code grinds to a screeching halt until you correct the problem. Fortunately, several good ways are available to learn about objects, properties, and methods.

· Read the rest of the book. Subsequent chapters in this section contain additional information and many more examples.

· Record your actions. The best way to become familiar with VBA is to turn on the macro recorder and record actions that you make in Excel. You can then examine the code to gain some insights regarding the objects, properties, and methods.

· Use the Help system. The main source of detailed information about Excel's objects, methods, and procedures is the VBA Help system. Help is thorough and easy to access. When you're in a VBA module, just move the cursor to a property or method and press F1. You get help that describes the word that is under the cursor. All VBA Help is online, so you must be connected to the Internet to use the Help system.

· Get another book. Several books are devoted exclusively to using VBA with Excel. My book, Excel 2016 Power Programming with VBA (Wiley), is one.