Chapter 5: Introducing Visual Basic for Applications

In This Chapter

• Introducing VBA — the programming language built into Excel

• Discovering how VBA differs from traditional spreadsheet macro languages and the Visual Basic language

• Using Visual Basic Editor (VBE)

• Working in the code windows in VBE and customizing the VBE environment

• Using Excel's macro recorder

• Getting an overview of objects, collections, properties, and methods

• Reviewing a case study of the Comment object

• Looking at specific information and examples of working with Range objects

• Accessing a lot of information about Excel objects, properties, and methods

Getting Some BASIC Background

Many hard-core programmers scoff at the idea of programming in BASIC. The name itself (an acronym for Beginner's All-purpose Symbolic Instruction Code) suggests that BASIC isn't a professional language. In fact, BASIC was first developed in the early 1960s as a way to teach programming techniques to college students. BASIC caught on quickly and is available in hundreds of dialects for many types of computers.

BASIC has evolved and improved over the years. For example, in many early implementations, BASIC was an interpreted language. Each line was interpreted before it was executed, causing slow performance. Most modern dialects of BASIC allow the code to be compiled — converted to machine code — which results in faster and more efficient execution.

BASIC gained quite a bit of respectability in 1991 when Microsoft released Visual Basic for Windows. This product made it easy for the masses to develop stand-alone applications for Windows. Visual Basic has very little in common with early versions of BASIC, but Visual Basic is the foundation on which VBA was built.

Delving into VBA

Excel 5, released in 1994, was the first application on the market to feature Visual Basic for Applications (VBA). VBA is best thought of as Microsoft's common application scripting language, and it's included with most Office 2013 applications and even in applications from other vendors. Therefore, if you master VBA by using Excel, you'll be able to jump right in and write macros for other Microsoft (and some non-Microsoft) products. Even better, you'll be able to create complete solutions that use features across various applications.

Object models

The secret to using VBA with other applications lies in understanding the object model for each application. VBA, after all, simply manipulates objects, and each product (Excel, Word, Access, PowerPoint, and so on) has its own unique object model. You can program an application by using the objects that the application exposes.

Excel's object model, for example, exposes several powerful data analysis objects, such as worksheets, charts, pivot tables, and numerous mathematical, financial, engineering, and general business functions. With VBA, you can work with these objects and develop automated procedures. While you work with VBA in Excel, you gradually build an understanding of the object model. Warning: The object model will be confusing at first. Eventually, however, the pieces come together — and all of a sudden, you realize that you've mastered it!

VBA versus XLM

Before version 5, Excel used a powerful (but cryptic) macro language called XLM. Later versions of Excel (including Excel 2013) still execute XLM macros, but the capability to record macros in XLM was removed beginning with Excel 97. As a developer, you should be aware of XLM (in case you ever encounter macros written in that system), but you should use VBA for your development work.

Don't confuse the XLM macro language with eXtensible Markup Language (XML). Although these terms share the same letters, they have nothing in common. XML is a storage format for structured data. The Office 2013 applications use XML as their default file format.

Covering the Basics of VBA

I suggest that you read the material in this section to get a broad overview of the topics I cover in the remainder of this chapter.

Following is a quick-and-dirty summary of what VBA is all about:

• Code: You perform actions in VBA by executing VBA code. You write (or record) VBA code, which is stored in a VBA module.

• Module: VBA modules are stored in an Excel workbook file, but you view or edit a module by using Visual Basic Editor (VBE). A VBA module consists of procedures.

• Procedures: A procedure is basically a unit of computer code that performs some action. VBA supports two types of procedures: Sub procedures and Function procedures.

• Sub: A Sub procedure consists of a series of statements and can be executed in a number of ways. Here's an example of a simple Sub procedure called Test: This procedure calculates a simple sum and then displays the result in a message box.

Sub Test()

    Sum = 1 + 1

    MsgBox “The answer is “ & Sum

End Sub

• Function: A Function procedure returns a single value (or possibly an array). A Function can be called from another VBA procedure or used in a worksheet formula. Here's an example of a Function named AddTwo:

Function AddTwo(arg1, arg2)

    AddTwo = arg1 + arg2

End Function

• Objects: VBA manipulates objects contained in its host application. (In this case, Excel is the host application.) Excel provides you with more than 100 classes of objects to manipulate. Examples of objects include a workbook, a worksheet, a range on a worksheet, a chart, and a shape. Many more objects are at your disposal, and you can use VBA code to manipulate them. Object classes are arranged in a hierarchy.

Objects also can act as containers for other objects. For example, Excel is an object called Application, and it contains other objects, such as Workbook objects. The Workbook object contains other objects, such as Worksheet objects and Chart objects. A Worksheet object contains objects such as Range objects, PivotTable objects, and so on. The arrangement of these objects is referred to as Excel's object model.

• Collections: Like objects form a collection. For example, the Worksheets collection consists of all the worksheets in a particular workbook. Collections are objects in themselves.

• Object hierarchy: When you refer to an object, you specify its position in the object hierarchy by using a period (also known as a dot) as a separator between the container and the member. For example, you can refer to a workbook named Book1.xlsx as

Application.Workbooks(“Book1.xlsx”)

This code refers to the Book1.xlsx workbook in the Workbooks collection. The Workbooks collection is contained in the Excel Application object. Extending this type of referencing to another level, you can refer to Sheet1 in Book1 as

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”)

• Active objects: If you omit a specific reference to an object, Excel uses the active objects. If Book1 is the active workbook, the preceding reference can be simplified as

Worksheets(“Sheet1”).Range(“A1”)

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

Range(“A1”)

• Objects properties: 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 to determine object properties and also to change them. Some properties are read-only properties and can't be changed by using VBA.

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

Worksheets(“Sheet1”).Range(“A1”).Value

• VBA variables: You can assign values to VBA variables. Think of a variable as a name that you can use to store a particular value. 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

• Object methods: 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 cell A1 on the active worksheet, use

Range(“A1”).ClearContents

• Standard programming constructs: VBA also includes many constructs found in modern programming languages, including arrays, conditional statements, and loops.

• Events: Some objects recognize specific events, and you can write VBA code that is executed when the event occurs. For example, opening a workbook triggers a Workbook_Open event. Changing a cell in a worksheet triggers a Worksheet_Change event.

Believe it or not, the preceding section pretty much summarizes what VBA is all about and how it works with Excel. Now you just need to learn the details.

Introducing Visual Basic Editor

You do all your VBA work in Visual Basic Editor (VBE). VBE is a separate application that works seamlessly with Excel. By seamlessly, I mean that Excel takes care of the details of opening VBE when you need it. You can't run VBE separately; Excel must be running for VBE to run.

VBA modules are stored in workbook files. However, the VBA modules aren't visible unless you activate VBE.

Displaying Excel's Developer tab

The Excel Ribbon doesn't display the Developer tab by default. If you're going to be working with VBA, it's essential that you turn on the Developer tab:

1. Right-click anywhere on the Ribbon and choose Customize the Ribbon.

Excel displays 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.

After you perform these steps, Excel displays a new tab, as shown in Figure 5-1.

Figure 5-1: By default, the Developer tab is not displayed.

Activating VBE

When you're working in Excel, you can switch to VBE by using either of the following techniques:

• Press Alt+F11.

• Choose Developer⇒Code⇒Visual Basic.

Figure 5-2 shows the VBE window, but your window probably will look different. This window is highly customizable — you can hide windows, change their sizes, dock them, rearrange them, and more.

Figure 5-2: The Visual Basic Editor window.

VBE windows

VBE has a number of parts. I briefly describe some of the key components in the following list:

• VBE menu bar: Although Excel uses a fancy new Ribbon interface, VBE is still stuck in the menu bar and toolbar world. The VBE menu bar works like every other menu bar that you've encountered. It contains commands that you use to work with the various components in VBE. Also, you'll find that shortcut keys are associated with many menu commands. For example, the View⇒Immediate Window command has a shortcut key of Ctrl+G.

VBE also features shortcut menus. As you'll discover, you can right-click virtually anything in a VBE window to get a shortcut menu of common commands.

• VBE toolbars: The Standard toolbar, which is directly under the menu bar by default, is one of six VBE toolbars. You can customize toolbars, move them around, display other toolbars, and so on. Choose View⇒Toolbars to display or hide VBE toolbars.

• Project Explorer window: The Project Explorer window displays a tree diagram that consists of every workbook that is currently open in Excel (including add-ins and hidden workbooks). Each workbook is known as a project. I discuss the Project Explorer window in more detail in the next section (“Working with Project Explorer”).

If the Project Explorer window isn't visible, press Ctrl+R. To hide the Project Explorer window, click the Close button in its title bar or right-click anywhere in the Project Explorer window and select Hide from the shortcut menu.

• Code window: A code window (sometimes known as a module window) contains VBA code. Every item in a project's tree has an associated code window. To view a code window for an object, double-click the object in the Project Explorer window. For example, to view the code window for the Sheet1 object, double-click Sheet1 in the Project Explorer window. Unless you've added some VBA code, the code window is empty.

Another way to view the code window for an object is to select the object in the Project Explorer window and then click the View Code button in the toolbar at the top of the Project Explorer window.

I discuss code windows later in this chapter (see the section “Working with Code Windows”).

• 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 isn't visible, press Ctrl+G. 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).

Working with Project Explorer

When you're working in VBE, each Excel workbook and add-in that's currently open is considered a project. You can think of a project as a collection of objects arranged as an expandable tree. You can expand a project by clicking the plus sign (+) at the left of the project's name in the Project Explorer window. You contract a project by clicking the minus sign (–) to the left of a project's name. If you try to expand a project that's protected with a password, you're prompted to enter the password.

The top of the Project Explorer window contains three icons. The third icon, named Toggle Folders, controls whether the objects in a project are displayed in a hierarchy or are shown in a single nonhierarchical list.

Figure 5-3 shows a Project Explorer window with four projects listed (two XLAM add-ins and two workbooks).

Figure 5-3: A Project Explorer window with four projects listed.

When you activate VBE, you can't assume that the code module that's displayed corresponds to the highlighted object in the Project Explorer window. To make sure that you're working in the correct code module, always double-click the object in the Project Explorer window.

If you have many workbooks and add-ins loaded, the Project Explorer window can be a bit overwhelming. Unfortunately, you can't hide projects in the Project Explorer window. However, you probably want to keep the project outlines contracted if you're not working on them.

When viewing the Project Explorer in Folder view, 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 another object called ThisWorkbook (which represents the Workbook object). If the project has any VBA modules, the project listing also shows a Modules node, and the modules are listed there. A project can also contain a node called Forms that contains UserForm objects (also known as custom dialog boxes). If your project has any class modules, it displays another node called Class Modules. Similarly, if your project has any references, you see another node called References. The References node is a bit misleading because references can't contain any VBA code.

Adding a new VBA module

To add a new VBA module to a project, select the project's name in the Project Explorer window and choose Insert⇒Module. Or you can just right-click the project's name and choose Insert⇒Module from the shortcut menu.

When you record a macro, Excel automatically inserts a VBA module to hold the recorded code.

Removing a VBA module

If you need to remove a VBA module, a class module, or a UserForm from a project, select the module's name in the Project Explorer window and choose File⇒Remove xxx (where xxx is the name of the module). Or you can right-click the module's name and choose Remove xxx from the shortcut menu. You're asked whether you want to export the module before removing it. (See the next section for details.)

You can't remove code modules associated with the workbook (the ThisWorkbook code module) or with a sheet (for example, the Sheet1 code module).

Exporting and importing objects

Except for those objects listed under the References node, you can save every object in a project to a separate file. Saving an individual object in a project is called exporting. It stands to reason that you can also import objects into a project. Exporting and importing objects might be useful if you want to use a particular object (such as a VBA module or a UserForm) in a different project or share some code with a colleague.

To export an object, select it in the Project Explorer window and choose File⇒Export File. You get a dialog box that asks for a filename. Note that the object remains in the project. (Only a copy of it is exported.) If you export a UserForm object, any code associated with the UserForm is also exported.

To import a file into a project, select the project's name in the Project Explorer window and choose File⇒Import File. You get a dialog box that asks for a file. You can import only a file that has been exported by choosing the File⇒Export File command.

The exported files have an extension that corresponds to the type of object. Exporting a UserForm generates two files.

If you want to copy a module or UserForm to another project, you don't need to export and then import the object. Make sure that both projects are open; then simply activate Project Explorer and drag the object from one project to the other. The original module or UserForm remains, and a copy is added to the other project.

Working with Code Windows

When you become proficient with VBA, you'll be spending lots of time working in code windows. Each object in a project has an associated code window. To summarize, these objects can be

• The workbook itself (ThisWorkbook in the Project Explorer window)

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

• A VBA module

• A class module (a special type of module that lets you create new object classes)

• A UserForm

Minimizing and maximizing windows

Depending on how many workbooks and add-ins are open, VBE can have many code windows, and things can get a bit confusing. Most people find it most efficient to maximize the code window that they're working in. Doing so enables you to see more code and keeps you from getting distracted. To maximize a code window, click the maximize button in its title bar or just double-click its title bar. To restore a code window (make it nonmaximized), click the Restore button (below the Application title bar).

Sometimes, you may want to have two or more code windows visible. For example, you might want to compare the code in two modules or perhaps copy code from one module to another. To view two or more code windows at once, make sure that the active code window isn't maximized. Then drag and resize the windows that you want to view.

Minimizing a code window gets it out of the way. You can also 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 Explorer window.

You can save a workbook from VBE. Select the workbook in the Project window and chose File⇒Save, press Ctrl+S, or click the Save icon in the Standard toolbar.

VBE doesn't have a menu command to close a workbook. You must reactivate Excel and close it from there. You can, however, use the Immediate window to close a workbook or an add-in. Just activate the Immediate window (press Ctrl+G if it's not visible), type a VBA statement like the one that follows, and press Enter:

Workbooks(“myaddin.xlam”).Close

As you'll see, this statement executes the Close method of the Workbook object, which closes a workbook. In this case, the workbook happens to be an add-in.

Storing VBA code

In general, a code window can hold four types of code:

• Sub procedures: A procedure is a set of instructions that performs some action.

• Function procedures: A function is a set of instructions that returns a single value or an array (similar in concept to a worksheet function, such as SUM).

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

• Property procedures: These are special procedures used in class modules.

A single VBA module can store any number of Sub procedures, Function procedures, and declarations. How you organize a VBA module is 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.

Although you have lots of flexibility regarding where to store your VBA code, there are some restrictions. Event-handler procedures must be located in the code window for the object that responds to the event. For example, if you write a procedure that executes when the workbook is opened, that procedure must be located in the code window for the ThisWorkbook object, and the procedure must have a special name. This concept will become clearer when I discuss events (Chapter 17) and UserForms (Part III).

Entering VBA code

Before you can do anything meaningful, you must have some VBA code in a code window. This VBA code must be within a procedure. A procedure consists of VBA statements. For now, I focus on one type of code window: a VBA module.

You can add code to a VBA module in three ways:

• Enter the code manually. Use your keyboard to type your code.

• Copy and paste. Copy the code from another module (or from a website) and paste it into the module that you're working in.

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

Entering code manually

Sometimes, the most direct route is the best one. Entering code directly involves . . . well, entering the code directly. In other words, you type the code by using your keyboard. You can use the Tab key to indent the lines that logically belong together — for example, the conditional statements between the If and End If statements. Indenting isn't necessary, but it makes the code easier to read, so it's a good habit to acquire.

Entering and editing text in a VBA module works just as you would expect. You can select text, copy it or cut it, and then paste it to another location.

A single instruction in VBA can be as long as you need it to be. For readability's sake, 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. The following code, for example, is a single VBA statement split over four lines:

MsgBox “Can't find “ & UCase(SHORTCUTMENUFILE) _

  & vbCrLf & vbCrLf & “The file should be located in “ _

  & ThisWorkbook.Path & vbCrLf & vbCrLf _

  & “You may need to reinstall BudgetMan”, vbCritical, APPNAME

Notice that I indented the last three lines of this statement. Doing so is optional, but it helps clarify the fact that these four lines are, in fact, a single statement.

Like Excel, VBE has multiple levels of undo and redo. Therefore, if you find that you deleted an instruction that you shouldn't have, you can click the Undo button (or press Ctrl+Z) repeatedly until the instruction comes back. After undoing, you can click the Redo button (or press Ctrl+Y) to redo changes that were previously undone. This feature can be a lifesaver, so I recommend that you play around with it until you understand how it works.

To get a feel for entering a VBA procedure, try this: Insert a VBA module into a project and then enter the following procedure in the code window of the module:

Sub SayHello()

    Msg = “Is your name “ & Application.UserName & “?”

    Ans = MsgBox(Msg, vbYesNo)

    If Ans = vbNo Then

        MsgBox “Oh, never mind.”

    Else

        MsgBox “I must be clairvoyant!”

    End If

End Sub

Figure 5-4 shows how this code looks in a VBA module.

Figure 5-4: Your first VBA procedure.

While you enter the code, note that VBE makes some adjustments to your text. For example, if you omit the space before or after an equal sign (=), VBE inserts the space for you. Also, the color of some of the text is changed. These adjustments are all perfectly normal, and you'll appreciate them later.

To execute the SayHello procedure, make sure that the cursor is located anywhere within the text that you typed. Then do any of the following:

• Press F5.

• Choose Run⇒Run Sub/UserForm.

• Click the Run Sub/UserForm button on the Standard toolbar.

If you entered the code correctly, the procedure executes, and you can respond to a simple dialog box (see Figure 5-5) that displays the username, as listed in the Excel Options dialog box. Notice that Excel is activated when the macro executes. At this point, it's not important that you understand how the code works; that becomes clear later in this chapter and in subsequent chapters.

Figure 5-5: The result of running the procedure in Figure 5-4.

Most of the time, you'll be executing your macros from Excel. However, it's often more efficient to test your macro by running it directly from VBE.

What you did in this exercise was write a VBA Sub procedure (also known as a macro). When you issued the command to execute the macro, VBE quickly compiled the code and executed it. In other words, each instruction was evaluated, and Excel simply did what it was told to do. You can execute this macro any number of times (although it tends to lose its appeal after a while).

For the record, this simple procedure uses the following concepts (all of which I cover later in the book):

• Declaring a procedure (the first line)

• Assigning a value to variables (Msg and Ans)

• Concatenating strings (using the & operator)

• Using a built-in VBA function (MsgBox)

• Using built-in VBA constants (vbYesNo and vbNo)

• Using an If-Then-Else construct

• Ending a procedure (the last line)

Not bad for a first effort, eh?

Copying VBA code

Another method of getting code into a VBA module is to copy it from another module. For example, you may have written a procedure for one project that would also be useful in your current project. Rather than reenter the code, you can simply open the workbook, activate the module, and use the Clipboard copy-and-paste procedures to copy the code into your current VBA module. After you've finished pasting, you can modify the code as necessary.

As I note previously in this chapter, you can also import an entire module that has been exported.

And don't forget about the Internet. You'll find thousands of VBA code examples at websites, forums, and blogs. Copying code from a browser and pasting it into a VBA module is simple.

Sometimes, the code copied from a web page includes smart quotes rather than quotation mark characters. Smart quotes will cause an error, so you must replace them with quotation mark characters.

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, there is absolutely no way to record the SayHello procedure shown in the previous section. As you'll see, recording macros is useful but has limitations. In fact, when you record a macro, you almost always need to make adjustments or enter some code manually.

This next example shows how to record a macro that simply changes the page setup to landscape orientation. If you want to try any of these examples, start with a blank workbook:

1. Activate a worksheet in the workbook (any worksheet will do).

2. Choose Developer⇒Code⇒Record Macro.

Excel displays its Record Macro dialog box.

3. Click OK to accept the default setting for the macro.

Excel automatically inserts a new VBA module into the workbook's VBA project. From this point on, Excel converts your actions into VBA code. Note that Excel's status bar displays a gray square. You can click that control to stop recording.

4. Choose Page Layout⇒Page Setup⇒Orientation⇒Landscape.

5. Select Developer⇒Code⇒Stop Recording (or click the gray square in the status bar).

Excel stops recording your actions.

To view the macro, activate VBE (pressing Alt+F11 is the easiest way) and locate the project in the Project Explorer window. Double-click the Modules node to expand it. Then double-click the Module1 item to display the code window. (If the project already had a Module1, the new macro will be in Module2.) The code generated by this single Excel command is shown in Figure 5-6.

Figure 5-6: An excessive amount of code generated by Excel's macro recorder.

You may be surprised by the amount of code generated by this single command. (I know I was the first time I tried something like this.) Although you changed only one simple setting in the Page Setup tab, Excel generates more than 50 lines of code that affects dozens of print settings.

This code listing illustrates an important concept. The Excel macro recorder is not the most efficient way to generate VBA code. Often, the code produced when you record a macro is overkill. Consider the recorded macro that switches to landscape mode. Practically every statement in that macro is extraneous. You can simplify this macro considerably by deleting the extraneous code, which makes the macro easier to read and faster to run. In fact, you can simplify this recorded macro to the following:

Sub Macro1()

    With ActiveSheet.PageSetup

        .Orientation = xlLandscape

    End With

End Sub

I deleted all the code except for the line that sets the Orientation property. Actually, you can simplify this macro even more because the With-End With construct isn't necessary when you're changing only one property:

Sub Macro1()

    ActiveSheet.PageSetup.Orientation = xlLandscape

End Sub

In this example, the macro changes the Orientation property of the PageSetup object on the active sheet. By the way, xlLandscape is a built-in constant that's provided to make things easier for you. The variable xlLandscape has a value of 2, and xlPortrait has a value of 1. The following macro works the same as the preceding Macro1:

Sub Macro1a()

    ActiveSheet.PageSetup.Orientation = 2

End Sub

Most would agree that it's easier to remember the name of the constant than the arbitrary numbers. You can use the Help system to learn the relevant constants for a particular command.

You could have entered this procedure directly into a VBA module. To do so, you would have to know which objects, properties, and methods to use. Obviously, recording the macro is much faster, and this example has a built-in bonus: You also learned that the PageSetup object has an Orientation property.

A point that I make clear throughout this book is that recording your actions is perhaps the best way to learn VBA. When in doubt, try recording. Although the result may not be exactly what you want, chances are that it will steer you in the right direction. You can use the Help system to check out the objects, properties, and methods that appear in the recorded code.

I discuss the macro recorder in more detail later in this chapter. See the section “The Macro Recorder.”

Customizing the VBE Environment

If you're serious about becoming an Excel programmer, you'll be spending a lot of time with the VBE window. To help make things as comfortable as possible, VBE provides quite a few customization options.

When VBE is active, choose Tools⇒Options. You see a dialog box with four tabs: Editor, Editor Format, General, and Docking. I discuss some of the most useful options on these tabs in the sections that follow. By the way, don't confuse this Options dialog box with the Excel Options dialog box, which you bring up by choosing File⇒Options in Excel.

Using the Editor tab

Figure 5-7 shows the options that you access by clicking the Editor tab of the Options dialog box.

Figure 5-7: The Editor tab of the Options dialog box.

Auto Syntax Check option

The Auto Syntax Check setting determines whether VBE pops up a dialog box if it discovers a syntax error while you're entering your VBA code. The dialog box tells you 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.

I keep this setting turned off because I find the dialog boxes annoying, and I can usually figure out what's wrong with an instruction. But if you're new to VBA, you might find the Auto Syntax Check assistance helpful.

Require Variable Declaration option

If the Require Variable Declaration option is set, VBE inserts the following statement at the beginning of each new VBA module that you insert:

Option Explicit

If this statement appears in your module, you must explicitly define each variable that you use. Variable declaration is an excellent habit to get into, although it does require additional effort on your part. If you don't declare your variables, they will all be of the Variant data type, which is flexible but not efficient in terms of storage or speed. I discuss variable declaration in more depth in Chapter 6.

Changing the Require Variable Declaration option affects only new modules, not existing modules.

Auto List Members option

If the Auto List Members option is set, VBE provides help when you're entering your VBA code by displaying a list of member items for an object. These items include methods and properties for the object that you typed.

This option is helpful, and I always keep it turned on. Figure 5-8 shows an example of Auto List Members (which will make a lot more sense when you actually start writing VBA code). In this example, VBE is displaying a list of members for the Application object. The list changes as you type additional characters, showing only the members that begin with the characters you type. You can just select an item from the list and press Tab (or double-click the item), thus avoiding typing it. Using the Auto List Members list also ensures that the item is spelled correctly.

Auto Quick Info option

If the Auto Quick Info option is set, VBE displays information about the arguments available for functions, properties, and methods while you type. This information can be helpful, and I always leave this setting on. Figure 5-9 shows this feature in action, displaying the syntax for the Cells property.

Figure 5-8: An example of Auto List Members.

Figure 5-9: An example of Auto Quick Info offering help about the Cells property.

Auto Data Tips option

If the Auto Data Tips option is set, you can hover your mouse pointer over a variable, and VBE displays the value of the variable. This technique works only when the procedure is paused while debugging. When you enter the wonderful world of debugging, you'll definitely appreciate this option. I always keep this option turned on.

Auto Indent option

The Auto Indent setting determines whether VBE automatically indents each new line of code by the same amount as the previous line. I'm a big fan of using indentations in my code, so I keep this option on. You can also specify the number of characters to indent; the default is four.

Use the Tab key, not the spacebar, to indent your code. Using the Tab key results in more consistent spacing. In addition, you can use Shift+Tab to unindent a line of code. These keys also work if you select more than one line of code.

Drag-and-Drop Text Editing option

The Drag-and-Drop Text Editing option, when enabled, lets you copy and move text by dragging and dropping. I keep this option turned on, but I never use drag-and-drop editing. I prefer to use keyboard shortcuts for copying and pasting.

Default to Full Module View option

The Default to Full Module View option specifies how procedures are viewed. If this option is set, procedures in the code window appear as a single scrollable window. If this option is turned off, you can see only one procedure at a time. I keep this setting turned on.

Procedure Separator option

When the Procedure Separator option is turned on, VBE displays separator bars between procedures in a code window (assuming that the Default to Full Module View option is also selected). I like the visual cues that show where my procedures end, so I keep this option turned on.

Using the Editor Format tab

Figure 5-10 shows the Editor Format tab of the Options dialog box. The options on this tab control the appearance of VBE itself.

Figure 5-10: The Editor Format tab of the Options dialog box.

• Code Colors option: The Code Colors option lets you set the text color (foreground and background) and the indicator color displayed for various elements of VBA code. Choosing these colors is largely a matter of individual preference. I find the default colors to be just fine. But for a change of scenery, I occasionally play around with these settings.

• Font option: The Font option lets you select the font that's used in your VBA modules. For best results, stick with a fixed-width font (monofont) such as Courier New. In a fixed-width font, all characters are exactly the same width. Using fixed-width characters makes your code much more readable because the characters are nicely aligned vertically and you can easily distinguish multiple spaces.

• Size setting: The Size setting specifies the size of the font in the VBA modules. This setting is a matter of personal preference determined by your video display resolution and your eyesight. The default size of 10 (points) works for me.

• Margin Indicator Bar option: The Margin Indicator Bar option controls the display of the vertical margin indicator bar in your modules. You should keep this turned on; otherwise, you won't be able to see the helpful graphical indicators when you're debugging your code.

Using the General tab

Figure 5-11 shows the following options available under the General tab in the Options dialog box:

Figure 5-11: The General tab of the Options dialog box.

• Form Grid Settings: The options in this section let you specify a grid to help align controls on a UserForm (custom dialog box). When you have some experience designing UserForms, you can determine whether a grid display is helpful or not.

• Show ToolTips check box: This check box refers to toolbar buttons. There's no reason to turn off the tooltips display.

• Collapse Proj. Hides Windows option: If checked, this setting closes windows automatically when you collapse a project in the project window. I keep this setting turned on.

• Edit and Continue section: This area contains one option, which may be useful for debugging. When checked, VBA displays a message if your variables are about to lose their values because of a problem.

• Error Trapping settings: These settings determine what happens when an error is encountered. If you write any error-handling code, make sure that the Break on Unhandled Errors option is set. If the Break on All Errors option is set, error-handling code is ignored (which is hardly ever what you want). I discuss error-handling techniques in Chapter 7.

• Compile settings: The two Compile settings deal with compiling your code. I keep both of these options turned on. Compiling code is virtually instantaneous unless the project is extremely large.

Using the Docking tab

Figure 5-12 shows the Docking tab of the Options dialog box. These options determine how the various windows in VBE behave. When a window is docked, it's fixed in place along one of the edges of the VBE window. Docking windows makes it much easier to identify and locate a particular window. If you turn off all docking, you have a confusing mess of windows. Generally, you'll find that the default settings work fine.

Figure 5-12: The Docking tab of the Options dialog box.

To dock a window, just drag it to the desired location. For example, you might want to dock the Project Explorer window to the left side of the screen. Just drag its title bar to the left, and you see an outline that shows it docked. Release the mouse, and the window is docked.

Docking windows in VBE has always been a bit problematic. Often, you find that some windows simply refuse to be docked. I've found that if you persist long enough, the procedure will eventually work. Unfortunately, I don't have any secret window-docking techniques.

The Macro Recorder

Earlier in this chapter, I discuss the macro recorder, which is a tool that converts your Excel actions into VBA code. This section covers the macro recorder in more detail.

Make sure that Excel displays the Developer tab on the Ribbon. If you don't see this tab, refer to the section “Displaying Excel's Developer tab,” earlier in this chapter.

The macro recorder is an extremely useful tool, but remember the following points:

• The macro recorder is appropriate only for simple macros or for recording a small part of a more complex macro.

• Not all the actions you make in Excel get recorded.

• The macro recorder can't generate code that performs looping (that is, repeating statements), assigns variables, executes statements conditionally, displays dialog boxes, and so on.

• The macro recorder always creates Sub procedures. You can't create a Function procedure by using the macro recorder.

• The code that is generated sometimes depends on certain settings that you specify.

• You'll often want to clean up the recorded code to remove extraneous commands.

What the macro recorder actually records

The Excel macro recorder translates your mouse and keyboard actions into VBA code. I could probably write several pages describing how this translation occurs, but the best way to show you is by example. Follow these steps:

1. Start with a blank workbook.

2. Make sure that the Excel window isn't maximized.

You don't want it to fill the entire screen.

3. Press Alt+F11 to activate the VBE window.

Note: Make sure that this window isn't maximized. Otherwise, you won't be able to see the VBE window and Excel's window at the same time.

4. Resize and arrange Excel's window and the VBE window so that both are visible. (For best results, minimize any other applications that are running.)

5. Activate Excel, choose Developer⇒Code⇒Record Macro, and then click OK to start the macro recorder.

6. Activate the VBE window.

7. In the Project Explorer window, double-click Module1 to display that module in the code window.

8. Close the Project Explorer window in VBE to maximize the view of the code window.

Your screen layout should look something like the example in Figure 5-13. The size of the windows depends on your video resolution. If you happen to have a dual-display system, just put the VBA window on one display and the Excel window on the other display.

Figure 5-13: A convenient window arrangement for watching the macro recorder do its thing.

Now move around in the worksheet and select various Excel commands. Watch while the code is generated in the window that displays the VBA module. Select cells, enter data, format cells, use the Ribbon commands, create a chart, manipulate graphic objects, and so on. I guarantee that you'll be enlightened while you watch the code being spit out before your very eyes.

Relative or absolute recording?

When recording your actions, Excel normally records absolute references to cells. In other words, when you select a cell, it will remember that exact cell (not the cell relative to the current active cell). To demonstrate how absolute references work, perform these steps and examine the code:

1. Activate a worksheet and start the macro recorder.

2. Activate cell B1.

3. Enter Jan into cell B1.

4. Move to cell C1 and enter Feb.

5. Continue this process until you've entered the first six months of the year in B1:G1.

6. Click cell B1 to activate it again.

7. Stop the macro recorder and examine the new code in VBE.

Excel generates the following code:

Sub Macro1()

    Range(“B1”).Select

    ActiveCell.FormulaR1C1 = “Jan”

    Range(“C1”).Select

    ActiveCell.FormulaR1C1 = “Feb”

    Range(“D1”).Select

    ActiveCell.FormulaR1C1 = “Mar”

    Range(“E1”).Select

    ActiveCell.FormulaR1C1 = “Apr”

    Range(“F1”).Select

    ActiveCell.FormulaR1C1 = “May”

    Range(“G1”).Select

    ActiveCell.FormulaR1C1 = “Jun”

    Range(“B1”).Select

End Sub

To execute this macro from Excel, choose Developer⇒Code⇒Macros (or press Alt+F8) and select Macro1 (or whatever the macro is named) and click the Run button.

The macro, when executed, re-creates the actions that you performed when you recorded it. These same actions occur regardless of which cell is active when you execute the macro. Recording a macro using absolute references always produces the same results.

In some cases, however, you want your recorded macro to work with cell locations in a relative manner. For example, you'd probably want such a macro to start entering the month names in the active cell. In such a case, you want to use relative recording to record the macro.

You control how references are recorded by using the Use Relative References button, in the Code group of the Developer tab. This button is a toggle. When the button appears in a different color, the macro recorder records relative references. When the button appears in the standard color, the macro recorder records absolute references. You can change the recording method at any time, even in the middle of recording.

To see how relative referencing is recorded, erase the cells in B1:G1 and then perform the following steps:

1. Activate cell B1.

2. Choose Developer⇒Code⇒Record Macro.

3. Click OK to begin recording.

4. Click the Use Relative References button to change the recording mode to relative.

After you click this button, it appears in a different color.

5. Enter the first six months' names in B1:G1, as in the previous example.

6. Select cell B1.

7. Stop the macro recorder.

With the recording mode set to relative, the code that Excel generates is quite different:

Sub Macro2()

    ActiveCell.FormulaR1C1 = “Jan”

    ActiveCell.Offset(0, 1).Range(“A1”).Select

    ActiveCell.FormulaR1C1 = “Feb”

    ActiveCell.Offset(0, 1).Range(“A1”).Select

    ActiveCell.FormulaR1C1 = “Mar”

    ActiveCell.Offset(0, 1).Range(“A1”).Select

    ActiveCell.FormulaR1C1 = “Apr”

    ActiveCell.Offset(0, 1).Range(“A1”).Select

    ActiveCell.FormulaR1C1 = “May”

    ActiveCell.Offset(0, 1).Range(“A1”).Select

    ActiveCell.FormulaR1C1 = “Jun”

    ActiveCell.Offset(0, -5).Range(“A1”).Select

End Sub

To test this macro, start by activating a cell other than cell B1. Then choose the Developer⇒Code⇒Macros command. Select the macro name and then click the Run button. The month names are entered beginning at the active cell.

Notice that I varied the recording procedure slightly in this example: I activated the beginning cell before I started recording. This step is important when you record macros that use the active cell as a base.

Although this macro looks complicated, it is fairly simple. The first statement enters Jan into the active cell. (The statement uses the active cell because it's not preceded by a statement that selects a cell.) The next statement uses the Select method (along with the Offset property) to move the selection one cell to the right. The next statement inserts more text, and so on. Finally, the original cell is selected by calculating a relative offset rather than an absolute cell. Unlike the preceding macro, this one always starts entering text in the active cell.

You'll notice that this macro generates code that appears to reference cell A1 — which may seem strange because cell A1 wasn't even involved in the macro. This code is simply a by-product of how the macro recorder works. (I discuss the Offset property later in this chapter.) At this point, all you need to know is that the macro works as it should.

The point here is that the recorder has two distinct modes, and you need to be aware of the mode in which you're recording. Otherwise, the result may not be what you expected.

By the way, the code generated by Excel is more complex than it needs to be, and it's not even the most efficient way to code the operation. The macro that follows, which I entered manually, is a simpler and faster way to perform this same operation. This example demonstrates that VBA doesn't have to select a cell before it puts information into it — an important concept that can speed things up considerably.

Sub Macro3()

    ActiveCell.Offset(0, 0) = “Jan”

    ActiveCell.Offset(0, 1) = “Feb”

    ActiveCell.Offset(0, 2) = “Mar”

    ActiveCell.Offset(0, 3) = “Apr”

    ActiveCell.Offset(0, 4) = “May”

    ActiveCell.Offset(0, 5) = “Jun”

End Sub

In fact, this macro can be made even more efficient by using the With-End With construct:

Sub Macro4()

    With ActiveCell

        .Offset(0, 0) = “Jan”

        .Offset(0, 1) = “Feb”

        .Offset(0, 2) = “Mar”

        .Offset(0, 3) = “Apr”

        .Offset(0, 4) = “May”

        .Offset(0, 5) = “Jun”

    End With

End Sub

Or, if you're a VBA guru, you can impress your colleagues by using a single statement:

Sub Macro5()

    ActiveCell.Resize(,6)=Array(“Jan”,”Feb”,”Mar”,”Apr”,”May”,”Jun”)  

End Sub

Recording options

When you record your actions to create VBA code, you have several options in the Record Macro dialog box. The following list describes your options:

• Macro name: You can enter a name for the procedure that you're recording. By default, Excel uses the names Macro1, Macro2, and so on for each macro that you record. I usually just accept the default name and change the name of the procedure later. You, however, might prefer to name the macro before you record it.

• Shortcut key: The Shortcut key option lets you execute the macro by pressing a shortcut key combination. For example, if you enter w (lowercase), you can execute the macro by pressing Ctrl+W. If you enter W (uppercase), the macro comes alive when you press Ctrl+Shift+W. Keep in mind that a shortcut key assigned to a macro overrides a built-in shortcut key (if one exists). For example, if you assign Ctrl+B to a macro, you won't be able to use the key combination to toggle the bold attribute in cells.

You can always add or change a shortcut key at any time, so you don't need to set this option while recording a macro.

• Store Macro In: The Store Macro In option tells Excel where to store the macro that it records. By default, Excel puts the recorded macro in a module in the active workbook. If you prefer, you can record it in a new workbook (Excel opens a blank workbook) or in your Personal Macro Workbook. (Read more about this in the sidebar, “The Personal Macro Workbook.”)

Excel remembers your choice, so the next time you record a macro, it defaults to the same location you used previously.

• Description: If you like, you can enter a description for your macro in the Description box. Text you enter here appears at the beginning of your macro as a comment.

Cleaning up recorded macros

Earlier in this chapter, you see how recording your actions while you issue a single command (the Page Layout⇒Page Setup⇒Orientation command) produces an enormous amount of VBA code. This example shows how, in many cases, the recorded code includes extraneous commands that you can delete.

The macro recorder doesn't always generate the most efficient code. If you examine the generated code, you see that Excel generally records what is selected (that is, an object) and then uses the Selection object in subsequent statements. For example, here's what is recorded if you select a range of cells and then use some buttons on the Home tab to change the numeric formatting and apply bold and italic:

Range(“A1:C5”).Select

Selection.Style = “Comma”

Selection.Font.Bold = True

Selection.Font.Italic = True

The recorded VBA code works, but it's just one way to perform these actions. You can also use the more efficient With-End With construct, as follows:

Range(“A1:C5”).Select

With Selection

     .Style = “Comma”     

     .Font.Bold = True

     .Font.Italic = True

End With

Or you can avoid the Select method altogether and write the code even more efficiently:

With Range(“A1:C5”)

     .Style = “Comma”

     .Font.Bold = True

     .Font.Italic = True

End With

If speed is essential in your application, you always want to examine any recorded VBA code closely to make sure that it's as efficient as possible.

You need to understand VBA thoroughly before you start cleaning up your recorded macros. But for now, just be aware that recorded VBA code isn't always the best, most efficient code.

About Objects and Collections

If you've worked through the first part of this chapter, you have an overview of VBA, and you know the basics of working with VBA modules in VBE. You've also seen some VBA code and were exposed to concepts such as objects and properties. This section gives you additional details about objects and collections of objects.

When you work with VBA, you must understand the concept of objects and Excel's object model. It helps to think of objects in terms of a hierarchy. At the top of this model is the Application object — in this case, Excel itself. But if you're programming in VBA with Microsoft Word, the Application object is Word.

The object hierarchy

The Application object (that is, Excel) contains other objects. Here are a few examples of objects contained in the Application object:

• Workbooks (a collection of all Workbook objects)

• Windows (a collection of all Window objects)

• AddIns (a collection of all AddIn objects)

Some objects can contain other objects. For example, the Workbooks collection consists of all open Workbook objects, and a Workbook object contains other objects, a few of which are as follows:

• Worksheets (a collection of Worksheet objects)

• Charts (a collection of Chart objects)

• Names (a collection of Name objects)

Each of these objects, in turn, can contain other objects. The Worksheets collection consists of all Worksheet objects in a Workbook. A Worksheet object contains many other objects, which include the following:

• ChartObjects (a collection of ChartObject objects)

• Range

• PageSetup

• PivotTables (a collection of PivotTable objects)

If this seems confusing, trust me, it will make sense, and you'll eventually realize that this object hierarchy setup is logical and well structured.

About collections

A key concept in VBA programming is collections. A collection is a group of objects of the same class, and a collection is itself an object. As I note earlier, Workbooks is a collection of all Workbook objects currently open. Worksheets is a collection of all Worksheet objects in a particular Workbook object. You can work with an entire collection of objects or with an individual object in a collection. To reference a single object from a collection, you put the object's name or index number in parentheses after the name of the collection, like this:

Worksheets(“Sheet1”)

If Sheet1 is the first worksheet in the collection, you could also use the following reference:

Worksheets(1)

You refer to the second worksheet in a Workbook as Worksheets(2), and so on.

There is also a collection called Sheets, which is made up of all sheets in a workbook, whether they're worksheets or chart sheets. If Sheet1 is the first sheet in the workbook, you can reference it as follows:

Sheets(1)

Referring to objects

When you refer to an object using VBA, you often must qualify the object by connecting object names with a period (also known as a dot operator). What if you had two workbooks open and they both had a worksheet named Sheet1? The solution is to qualify the reference by adding the object's container, like this:

Workbooks(“Book1”).Worksheets(“Sheet1”)

Without the workbook qualifier, VBA would look for Sheet1 in the active workbook.

To refer to a specific range (such as cell A1) on a worksheet named Sheet1 in a workbook named Book1, you can use the following expression:

Workbooks(“Book1”).Worksheets(“Sheet1”).Range(“A1”)

The fully qualified reference for the preceding example also includes the Application object, as follows:

Application.Workbooks(“Book1”).Worksheets(“Sheet1”).Range(“A1”)

Most of the time, however, you can omit the Application object in your references because it is assumed. If the Book1 object is the active workbook, you can even omit that object reference and use this:

Worksheets(“Sheet1”).Range(“A1”)

And — I think you know where I'm going with this — if Sheet1 is the active worksheet, you can use an even simpler expression:

Range(“A1”)

Contrary to what you might expect, Excel doesn't have an object that refers to an individual cell that is called Cell. A single cell is simply a Range object that happens to consist of just one element.

Simply referring to objects (as in these examples) doesn't do anything. To perform anything meaningful, you must read or modify an object's properties or specify a method to be used with an object.

Properties and Methods

It's easy to be overwhelmed with properties and methods; literally thousands are available. In this section, I describe how to access properties and methods of objects.

Object properties

Every object has properties. For example, a Range object has a property called Value. You can write VBA code to display the Value property or write VBA code to set the Value property to a specific value. Here's a procedure that uses the VBA MsgBox function to pop up a box that displays the value in cell A1 on Sheet1 of the active workbook:

Sub ShowValue()

    Msgbox Worksheets(“Sheet1”).Range(“A1”).Value

End Sub

The VBA MsgBox function provides an easy way to display results while your VBA code is executing. I use it extensively throughout this book.

The code in the preceding example displays the current setting of the Value property of a specific cell: cell A1 on a worksheet named Sheet1 in the active workbook. Note that if the active workbook doesn't have a sheet named Sheet1, the macro generates an error.

What if you want to change the Value property? The following procedure changes the value displayed in cell A1 by changing the cell's Value property:

Sub ChangeValue()

    Worksheets(“Sheet1”).Range(“A1”).Value = 123.45

End Sub

After executing this routine, cell A1 on Sheet1 has the value 123.45.

You may want to enter these procedures in a module and experiment with them.

Keep in mind that you can read the Value property only for a single-cell Range object. However, your code can write to the Value property for a multicell Range object. In the following statements, the first one is valid, and the second one is not:

Range(“A1:C12”).Value = 99

MsgBox Range(“A1:C12”).Value

Most objects have a default property. For a Range object, the default property is Value. Therefore, you can omit the .Value part from the preceding code, and it has the same effect. However, it's usually considered good programming practice to include the property in your code, even if it is the default property.

The statement that follows accesses the HasFormula and the Formula properties of a Range object:

If Range(“A1”).HasFormula Then MsgBox Range(“A1”).Formula

I use an If-Then construct to display a message box conditionally: If the cell has a formula, then display the formula by accessing the Formula property. If cell A1 doesn't have a formula, nothing happens.

The Formula property is a read-write property for only single-cell Range objects. For multicell Range objects, it's write-only. The following statement enters a formula into a range of cells:

Range(“A1:D12”).Formula = “=RAND()*100”

Object methods

In addition to properties, objects also have methods. A method is an action that you perform with an object. Here's a simple example that uses the Clear method on a Range object. After you execute this procedure, A1:C3 on Sheet1 is empty and all cell formatting is removed.

Sub ZapRange()

    Worksheets(“Sheet1”).Range(“A1:C3”).Clear

End Sub

If you'd like to delete the values in a range but keep the formatting, use the ClearContents method of the Range object.

Most methods also take arguments to define the action further. Here's an example that copies cell A1 to cell B1 on the active sheet by using the Copy method of the Range object. In this example, the Copy method has one argument (the destination of the copy).

Sub CopyOne()

     Range(“A1”).Copy Range(“B1”)

End Sub

The Comment Object: A Case Study

To help you better understand the properties and methods available for an object, I focus on a particular object: the Comment object. In Excel, you create a Comment object when you choose the Review⇒Comments⇒New Comment command to enter a cell comment. In the sections that follow, you get a feel for working with objects. I chose this object because it doesn't have an overwhelming number of properties and methods.

The point of this section is not so much to teach you about the Comment object but rather to demonstrate how to work with objects in general. Eventually, you will be able to adapt the concepts presented here when you work with other objects.

Viewing Help for the Comment object

One way to learn about a particular object is to look it up in the Help system. Figure 5-14 shows the Help topics for the Comment object. Note that the Help screen has links on the left so you can view the properties and methods for this object.

Figure 5-14: The main Help screen for the Comment object.

In Excel 2013, Help topics are displayed in your default web browser, and you must be connected to the Internet.

Properties of a Comment object

The Comment object has six properties. Table 5-1 contains a list of these properties, along with a brief description of each. If a property is read-only, your VBA code can read the property but can't change it.

Table 5-1: Properties of a Comment Object

Property	Read-Only	Description
Application	Yes	Returns an object that represents the application that created the comment (that is, Excel)
Author	Yes	Returns the name of the person who created the comment
Creator	Yes	Returns an integer that indicates the application in which the object was created
Parent	Yes	Returns the parent object, which is always a Range object, for the comment
Shape	Yes	Returns a Shape object that represents the shape attached to the comment
Visible	No	Is True if the comment is visible

Methods of a Comment object

Table 5-2 shows the methods that you can use with a Comment object. Again, these methods perform common operations that you may have performed manually with a comment at some point . . . but you probably never thought of these operations as methods.

Table 5-2: Methods of a Comment Object

Method	Description
Delete	Deletes a comment
Next	Returns a Comment object that represents the next comment in the worksheet
Previous	Returns a Comment object that represents the previous comment in the worksheet
Text	Returns or sets the text in a comment (takes three arguments)

You may be surprised to see that Text is a method rather than a property, which leads to an important point: The distinction between properties and methods isn't always clear-cut, and the object model isn't perfectly consistent. In fact, as long as you get the syntax correct, it doesn't matter whether a word in your code is a property or a method.

The Comments collection

Recall that a collection is a group of like objects. Every worksheet has a Comments collection, which consists of all Comment objects on the worksheet. If the worksheet has no comments, this collection is empty. Comments appear in the collection based on their position in the worksheet: left-to-right and then top-to-bottom.

For example, the following code refers to the first comment on Sheet1 of the active workbook:

Worksheets(“Sheet1”).Comments(1)

The following statement displays the text in the first comment on Sheet1:

MsgBox Worksheets(“Sheet1”).Comments(1).Text

Unlike most objects, a Comment object doesn't have a Name property. Therefore, to refer to a specific comment, you must either use an index number or (more frequently) use the Comment property of a Range object to return a specific comment.

The Comments collection is also an object and has its own set of properties and methods. For example, the Comments collection has a Count property that stores the number of items in the collection — which is the number of Comment objects in the active worksheet. The following statement displays the total number of comments on the active worksheet:

MsgBox ActiveSheet.Comments.Count

The next example shows the address of the cell that has the first comment:

MsgBox ActiveSheet.Comments(1).Parent.Address

Here, Comments(1) returns the first Comment object in the Comments collection. The Parent property of the Comment object returns its container, which is a Range object. The message box displays the Address property of the Range object. The net effect is that the statement displays the address of the cell that contains the first comment.

You can also loop through all the comments on a sheet by using the For Each-Next construct. (Looping is explained in Chapter 6.) Here's an example that displays a separate message box for each comment on the active worksheet:

For Each cmt in ActiveSheet.Comments

    MsgBox cmt.Text

Next cmt

If you'd rather not deal with a series of message boxes, use this procedure to print the comments to the Immediate window in VBE:

For Each cmt in ActiveSheet.Comments

    Debug.Print cmt.Text

Next cmt

About the Comment property

In this section, I've been discussing the Comment object. If you dig through the Help system, you'll find that a Range object has a property named Comment. If the cell contains a comment, the Comment property returns a Comment object. For example, the following code refers to the Comment object in cell A1:

Range(“A1”).Comment

If this comment were the first one on the sheet, you could refer to the same Comment object as follows:

ActiveSheet.Comments(1)

To display the comment in cell A1 in a message box, use a statement like this:

MsgBox Range(“A1”).Comment.Text

If cell A1 doesn't contain a comment, this statement generates an error.

The fact that a property can return an object is an important concept — a difficult one to grasp, perhaps, but critical to mastering VBA.

Objects contained in a Comment object

Working with properties is confusing at first because some properties actually return objects. Suppose that you want to determine the background color of a particular comment on Sheet1. If you look through the list of properties for a Comment object, you won't find anything that relates to color. Rather, you must do these steps:

1. Use the Comment object's Shape property to return the Shape object that's contained in the comment.

2. Use the Shape object's Fill property to return a FillFormat object.

3. Use the FillFormat object's ForeColor property to return a ColorFormat object.

4. Use the ColorFormat object's RGB property to get the color value.

Put another way, getting at the interior color for a Comment object involves accessing other objects contained in the Comment object. Here's a look at the object hierarchy that's involved:

Application (Excel)

Workbook object

Worksheet object

Comment object

Shape object

FillFormat object

ColorFormat object

I'll be the first to admit it: This process can get confusing! But, as an example of the elegance of VBA, you can write a single statement to change the color of a comment. The following statement changes the background color of the first comment on the active sheet:

ActiveSheet.Comments(1).Shape.Fill.ForeColor.RGB = RGB(0, 255, 0)

This type of referencing is not intuitive at first, but it will eventually make sense. Fortunately, recording your actions in Excel almost always yields some insights regarding the hierarchy of the objects involved.

By the way, to change the color of the text in a comment, you need to access the Comment object's TextFrame object, which contains the Characters object, which contains the Font object. Then you have access to the Font object's Color or ColorIndex properties. Here's an example that sets the ColorIndex property to 5:

ActiveSheet.Comments(1).Shape.TextFrame.Characters.Font.ColorIndex = 5

Refer to Chapter 28 for more information on colors.

Determining whether a cell has a comment

The following statement displays the comment in cell A1 of the active sheet:

MsgBox Range(“A1”).Comment.Text

If cell A1 doesn't have a comment, executing this statement generates a cryptic error message: Object variable or With block variable not set.

To determine whether a particular cell has a comment, you can write code to check whether the Comment object is Nothing. (Yes, Nothing is a valid keyword.) The following statement displays True if cell A1 doesn't have a comment:

MsgBox Range(“A1”).Comment Is Nothing

Note that I use the Is keyword and not an equal sign.

You can take this one step further and write a statement that displays the cell comment only if the cell actually has a comment (and does not generate an error if the cell lacks a comment). The statement that follows accomplishes this task:

If Not Range(“A1”).Comment Is Nothing Then MsgBox Range(“A1”).Comment.Text

Note that I used the Not keyword, which negates the True value that's returned if the cell has no comment. The statement, in essence, uses a double-negative to test a condition: If the comment isn't nothing, then display it. If this statement is confusing, think about it for a while, and it will make sense.

Adding a new Comment object

You may have noticed that the list of methods for the Comment object (or the Comments collection) doesn't include a method to add a new comment. This is because the AddComment method belongs to the Range object. The following statement adds a comment (an empty comment) to cell A1 on the active worksheet:

Range(“A1”).AddComment

If you consult the Help system, you discover that the AddComment method takes an argument that represents the text for the comment. Therefore, you can add a comment and then add text to the comment with a single statement:

Range(“A1”).AddComment “Formula developed by JW.”

The AddComment method generates an error if the cell already contains a comment. To avoid the error, your code can check whether the cell has a comment before adding one.

If you'd like to see these Comment object properties and methods in action, check out the example workbook on this book's website. This workbook, named comment object.xlsm, contains several examples that manipulate Comment objects with VBA code. You probably won't understand all the code at this point, but you will get a feel for how you can use VBA to manipulate an object.

Some Useful Application Properties

When you're working with Excel, only one workbook at a time can be active. In that workbook, only one sheet can be active. And if the sheet is a worksheet, one cell is the active cell (even if a multicell range is selected). VBA knows about active workbooks, worksheets, and cells and lets you refer to these active objects in a simplified manner.

This method of referring to objects is often useful because you won't always know the exact workbook, worksheet, or range on which you want to operate. VBA makes object referencing easy by providing properties of the Application object. For example, the Application object has an ActiveCell property that returns a reference to the active cell. The following instruction assigns the value 1 to the active cell:

ActiveCell.Value = 1

Note that in the preceding example, I omitted the reference to the Application object and to the active worksheet because both are assumed. This instruction will fail if the active sheet isn't a worksheet. For example, if VBA executes this statement when a chart sheet is active, the procedure halts, and you get an error message.

If a range is selected in a worksheet, the active cell is a cell within the selected range. In other words, the active cell is always a single cell (never a multicell range).

The Application object also has a Selection property that returns a reference to whatever is selected, which may be a single cell (the active cell), a range of cells, or an object such as ChartObject, TextBox, or Shape.

Table 5-3 lists the other Application properties that are useful when working with cells and ranges.

Table 5-3: Some Useful Properties of the Application Object

Property	Object Returned
ActiveCell	The active cell.
ActiveChart	The active chart sheet or chart contained in a ChartObject on a worksheet. This property is Nothing if a chart isn't active.
ActiveSheet	The active sheet (worksheet or chart sheet).
ActiveWindow	The active window.
ActiveWorkbook	The active workbook.
Selection	The object selected. It could be a Range object, Shape, ChartObject, and so on.
ThisWorkbook	The workbook that contains the VBA procedure being executed. This object may or may not be the same as the ActiveWorkbook object.

The advantage of using these properties to return an object is that you don't need to know which cell, worksheet, or workbook is active, and you don't need to provide a specific reference to it. This allows you to write VBA code that isn't specific to a particular workbook, sheet, or range. For example, the following instruction clears the contents of the active cell, even though the address of the active cell isn't known:

ActiveCell.ClearContents

The example that follows displays a message that tells you the name of the active sheet:

MsgBox ActiveSheet.Name

If you want to know the name and directory path of the active workbook, use a statement like this:

MsgBox ActiveWorkbook.FullName

If a range on a worksheet is selected, you can fill the entire range with a value by executing a single statement. In the following example, the Selection property of the Application object returns a Range object that corresponds to the selected cells. The instruction simply modifies the Value property of this Range object, and the result is a range filled with a single value:

Selection.Value = 12

Note that if something other than a range is selected (such as a ChartObject or a Shape), the preceding statement generates an error because ChartObject and Shape objects don't have a Value property.

The following statement, however, enters a value of 12 into the Range object that was selected before a non-Range object was selected. If you look up the RangeSelection property in the Help system, you find that this property applies only to a Window object.

ActiveWindow.RangeSelection.Value = 12

To find out how many cells are selected in the active window, access the Count property. Here's an example:

MsgBox ActiveWindow.RangeSelection.Count

Working with Range Objects

Much of the work that you will do in VBA involves cells and ranges in worksheets. The earlier discussion on relative versus absolute macro recording (see the section “Relative or absolute recording?”) exposes you to working with cells in VBA, but you need to know a lot more.

A Range object is contained in a Worksheet object and consists of a single cell or range of cells on a single worksheet. In the sections that follow, I discuss three ways of referring to Range objects in your VBA code:

• The Range property of a Worksheet or Range class object

• The Cells property of a Worksheet object

• The Offset property of a Range object

The Range property

The Range property returns a Range object. If you consult the Help system for the Range property, you learn that this property has two syntaxes:

object.Range(cell1)

object.Range(cell1, cell2)

The Range property applies to two types of objects: a Worksheet object or a Range object. Here, cell1 and cell2 refer to placeholders for terms that Excel recognizes as identifying the range (in the first instance) and delineating the range (in the second instance). Following are a few examples of using the Range property.

You've already seen examples like the following one earlier in the chapter. The instruction that follows simply enters a value into the specified cell. In this case, it puts the value 12.3 into cell A1 on Sheet1 of the active workbook:

Worksheets(“Sheet1”).Range(“A1”).Value = 12.3

The Range property also recognizes defined names in workbooks. Therefore, if a cell is named Input, you can use the following statement to enter a value into that named cell:

Worksheets(“Sheet1”).Range(“Input”).Value = 100

The example that follows enters the same value in a range of 20 cells on the active sheet. If the active sheet isn't a worksheet, the statement causes an error message:

ActiveSheet.Range(“A1:B10”).Value = 2

The next example produces exactly the same result as the preceding example:

Range(“A1”, “B10”) = 2

The sheet reference is omitted, however, so the active sheet is assumed. Also, the value property is omitted, so the default property (which is Value for a Range object) is assumed. This example also uses the second syntax of the Range property. With this syntax, the first argument is the cell at the top left of the range, and the second argument is the cell at the lower right of the range.

The following example uses the Excel range intersection operator (a space) to return the intersection of two ranges. In this case, the intersection is a single cell, C6. Therefore, this statement enters 3 in cell C6:

Range(“C1:C10 A6:E6”) = 3

And finally, this next example enters the value 4 in five cells: that is, a noncontiguous range. The comma serves as the union operator. Note that the commas are within the quote marks.

Range(“A1,A3,A5,A7,A9”) = 4

So far, all the examples have used the Range property on a Worksheet object. As I mentioned, you can also use the Range property on a Range object. This concept can be rather confusing, but bear with me.

Following is an example of using the Range property on a Range object. (In this case, the Range object is the active cell.) This example treats the Range object as if it were the upper-left cell in the worksheet, and then it enters a value of 5 in the cell that would be B2. In other words, the reference returned is relative to the upper-left corner of the Range object. Therefore, the statement that follows enters a value of 5 into the cell directly to the right and one row below the active cell:

ActiveCell.Range(“B2”) = 5

I said this is confusing. Fortunately, you can access a cell relative to a range in a much clearer way: the Offset property. I discuss this property after the next section.

The Cells property

Another way to reference a range is to use the Cells property. You can use the Cells property, like the Range property, on Worksheet objects and Range objects. Check the Help system, and you see that the Cells property has three syntaxes:

object.Cells(rowIndex, columnIndex)

object.Cells(rowIndex)

object.Cells

Some examples demonstrate how to use the Cells property. The first example enters the value 9 in cell A1 on Sheet1. In this case, I'm using the first syntax, which accepts the index number of the row (from 1 to 1048576) and the index number of the column (from 1 to 16384):

Worksheets(“Sheet1”).Cells(1, 1) = 9

Here's an example that enters the value 7 in cell D3 (that is, row 3, column 4) in the active worksheet:

ActiveSheet.Cells(3, 4) = 7

You can also use the Cells property on a Range object. When you do so, the Range object returned by the Cells property is relative to the upper-left cell of the referenced Range. Confusing? Probably. An example may help clear up any confusion. The following instruction enters the value 5 in the active cell. Remember, in this case, the active cell is treated as if it were cell A1 in the worksheet:

ActiveCell.Cells(1, 1) = 5

The real advantage of this type of cell referencing will be apparent when I discuss variables and looping (see Chapter 6). In most cases, you don't use actual values for the arguments; rather, you use variables.

To enter a value of 5 in the cell directly below the active cell, you can use the following instruction:

ActiveCell.Cells(2, 1) = 5

Think of the preceding example as though it said this: “Start with the active cell and consider this cell as cell A1. Place 5 in the cell in the second row and the first column.”

The second syntax of the Cells property uses a single argument that can range from 1 to 17,179,869,184. This number is equal to the number of cells in an Excel 2010 worksheet. The cells are numbered starting from A1 and continuing right and then down to the next row. The 16,384th cell is XFD1; the 16,385th is A2.

The next example enters the value 2 into cell SZ1 (which is the 520th cell in the worksheet) of the active worksheet:

ActiveSheet.Cells(520) = 2

To display the value in the last cell in a worksheet (XFD1048576), use this statement:

MsgBox ActiveSheet.Cells(17179869184)

You can also use this syntax with a Range object. In this case, the cell returned is relative to the Range object referenced. For example, if the Range object is A1:D10 (40 cells), the Cells property can have an argument from 1 to 40 and can return one of the cells in the Range object. In the following example, a value of 2000 is entered in cell A2 because A2 is the fifth cell (counting from the top, to the right, and then down) in the referenced range:

Range(“A1:D10”).Cells(5) = 2000

In the preceding example, the argument for the Cells property isn't limited to values between 1 and 40. If the argument exceeds the number of cells in the range, the counting continues as if the range were taller than it actually is. Therefore, a statement like the preceding one could change the value in a cell that's outside the range A1:D10. The statement that follows, for example, changes the value in cell A11:

Range(“A1:D10”).Cells(41) = 2000

The third syntax for the Cells property simply returns all cells on the referenced worksheet. Unlike the other two syntaxes, in this one, the return data isn't a single cell. This example uses the ClearContents method on the range returned by using the Cells property on the active worksheet. The result is that the content of every cell on the worksheet is cleared:

ActiveSheet.Cells.ClearContents

The Offset property

The Offset property, like the Range and Cells properties, also returns a Range object. But unlike the other two methods that I discussed, the Offset property applies only to a Range object and no other class. Its syntax is as follows:

object.Offset(rowOffset, columnOffset)

The Offset property takes two arguments that correspond to the relative position from the upper-left cell of the specified Range object. The arguments can be positive (down or to the right), negative (up or to the left), or 0. The example that follows enters a value of 12 into the cell directly below the active cell:

ActiveCell.Offset(1,0).Value = 12

The next example enters a value of 15 in the cell directly above the active cell:

ActiveCell.Offset(-1,0).Value = 15

If the active cell is in row 1, the Offset property in the preceding example generates an error because it can't return a Range object that doesn't exist.

The Offset property is useful, especially when you use variables in looping procedures. I discuss these topics in the next chapter.

When you record a macro using the relative reference mode, Excel uses the Offset property to reference cells relative to the starting position (that is, the active cell when macro recording begins). For example, I used the macro recorder to generate the following code. I started with the cell pointer in cell B1, entered values into B1:B3, and then returned to B1.

Sub Macro1()

    ActiveCell.FormulaR1C1 = “1”

    ActiveCell.Offset(1, 0).Range(“A1”).Select

    ActiveCell.FormulaR1C1 = “2”

    ActiveCell.Offset(1, 0).Range(“A1”).Select

    ActiveCell.FormulaR1C1 = “3”

    ActiveCell.Offset(-2, 0).Range(“A1”).Select

End Sub

Note that the macro recorder uses the FormulaR1C1 property. Normally, you want to use the Value property to enter a value in a cell. However, using FormulaR1C1 or even Formula produces the same result.

Also note that the generated code references cell A1 — a cell that wasn't even involved in the macro. This notation is a quirk in the macro recording procedure that makes the code more complex than necessary. You can delete all references to Range(“A1”), and the macro still works perfectly:

Sub Modified_Macro1()

    ActiveCell.FormulaR1C1 = “1”

    ActiveCell.Offset(1, 0).Select

    ActiveCell.FormulaR1C1 = “2”

    ActiveCell.Offset(1, 0).Select

    ActiveCell.FormulaR1C1 = “3”

    ActiveCell.Offset(-2, 0).Select

End Sub

In fact, here's a much more efficient version of the macro (which I wrote myself) that doesn't do any selecting:

Sub Macro1()

    ActiveCell = 1

    ActiveCell.Offset(1, 0) = 2

    ActiveCell.Offset(2, 0) = 3

End Sub

Things to Know about Objects

The preceding sections introduced you to objects (including collections), properties, and methods. But I've barely scratched the surface.

Essential concepts to remember

In this section, I note some additional essential concepts for would-be VBA gurus. These concepts will become clearer when you work with VBA and read subsequent chapters:

• Objects have unique properties and methods. Each object has its own set of properties and methods. Some objects, however, share some properties (for example, Name) and some methods (such as Delete).

• You can manipulate objects without selecting them. This idea may be contrary to how you normally think about manipulating objects in Excel. The fact is that it's usually more efficient to perform actions on objects without selecting them first. When you record a macro, Excel generally selects the object first, an unnecessary step that may make your macro run more slowly.

• It's important that you understand the concept of collections. Most of the time, you refer to an object indirectly by referring to the collection that it's in. For example, to access a Workbook object named Myfile, reference the Workbooks collection as follows:

Workbooks(“Myfile.xlsx”)

This reference returns an object, which is the workbook with which you're concerned.

• Properties can return a reference to another object. For example, in the following statement, the Font property returns a Font object contained in a Range object. Bold is a property of the Font object, not the Range object.

Range(“A1”).Font.Bold = True

• You can refer to the same object in many different ways. Assume that you have a workbook named Sales, and it's the only workbook open. Then assume that this workbook has one worksheet, named Summary. You can refer to the 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 usually determined by how much you know about the workspace. For example, if more than one workbook is open, the second and third methods aren'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.

Learning more about objects and properties

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 runtime error and your VBA code grinds to a screeching halt until you correct the problem.

Fortunately, you can learn about objects, properties, and methods in several ways.

Read the rest of the book

Don't forget, the name of this chapter is “Introducing Visual Basic for Applications.” The remainder of this book covers many additional details and provides many useful and informative examples.

Record your actions

The absolute best way to become familiar with VBA, without question, is to simply turn on the macro recorder and record some actions that you perform in Excel. This approach is a quick way to learn the relevant objects, properties, and methods for a task. It's even better if the VBA module in which the code is being recorded is visible while you're recording.

Use the Help system

The main source of detailed information about Excel's objects, methods, and procedures is the Help system. Many people forget about this resource.

Use Object Browser

Object Browser is a handy tool that lists every property and method available for every object. When VBE is active, you can bring up Object Browser in any of the following three ways:

• Press F2.

• Choose the View⇒Object Browser command from the menu.

• Click the Object Browser tool on the Standard toolbar.

Object Browser is shown in Figure 5-15.

Figure 5-15: Object Browser is a great reference source.

The drop-down list in the upper-left corner of Object Browser includes a list of all object libraries that you have access to:

• Excel itself

• MSForms (used to create custom dialog boxes)

• Office (objects common to all Microsoft Office applications)

• Stdole (OLE automation objects)

• VBA

• The current project (the project that's selected in Project Explorer) and any workbooks referenced by that project

Your selection in this upper-left drop-down list determines what is displayed in the Classes window, and your selection in the Classes window determines what is visible in the Members Of panel.

After you select a library, you can search for a particular text string to get a list of properties and methods that contain the text. You do so by entering the text in the second drop-down list and then clicking the binoculars (Search) icon. For example, assume that you're working on a project that manipulates cell comments:

1. Select the library of interest.

If you're not sure which object library is appropriate, you can select <All Libraries>.

2. Enter Comment in the drop-down list below the library list.

3. Click the binoculars icon to begin the text search.

The Search Results window displays the matching text. Select an object to display its classes in the Classes window. Select a class to display its members (properties, methods, and constants). Pay attention to the bottom pane, which shows more information about the object. You can press F1 to go directly to the appropriate help topic.

Object Browser may seem complex at first, but its usefulness to you will increase over time.

Experiment with the Immediate window

As I describe in the sidebar earlier in this chapter (“About the code examples”), the Immediate window of VBE is useful for testing statements and trying out various VBA expressions. I generally keep the Immediate window visible at all times, and I use it frequently to test various expressions and to help in debugging code.