VBA Programming Series...8

Everyone is familiar with the word object. Well, folks, forget the definition you think you know. In the world of programming, the word object has a different meaning. You often see it used as part of the expression object-oriented programming, or OOP for short. OOP is based on the idea that soft-ware consists of distinct objects that have attributes (or properties) and can be manipulated. These objects are not material things. Rather, they exist in the form of bits and bytes.

In this article, I introduce you to the Excel object model, which is a hierar-chy of objects contained in Excel. By the time you finish this article, you’ll have a reasonably good understanding of what OOP is all about — and why you need to understand this concept to become a VBA programmer. After all, Excel programming really boils down to manipulating Excel objects. It’s as simple as that.

The material in this article may be a bit overwhelming. But please take my advice and plow through it, even if you don’t fully grasp it at first. 

Excel Is an Object?

You’ve used Excel for quite a while, but you probably never thought of it as an object. The more you work with VBA, the more you view Excel in those terms. You’ll understand that Excel is an object and that it contains other objects. Those objects, in turn, contain still more objects. In other words, VBA programming involves working with an object hierarchy.

At the top of this hierarchy is the Application object — in this case, Excel itself (the mother of all objects).

Climbing the Object Hierarchy

The Application object contains other objects. Following is a list of some of the more useful objects contained in the Excel Application:

✓ Addin ✓ Window ✓ Workbook

✓ WorksheetFunction

Each object contained in the Application object can contain other objects. For example, the following is a list of objects that can be contained in a Workbook object:

✓ Chart ✓ Name

✓ VBProject ✓ Window ✓ Worksheet

In turn, each of these objects can contain still other objects. Consider a Worksheet object, which is contained in a Workbook object, which is con-tained in the Application object. Some of the objects that can be contained in a Worksheet object are:

✓ Comment ✓ Hyperlink ✓ Name

✓ PageSetup

✓ PivotTable ✓ Range

Put another way, if you want to do something with a range on a particular worksheet, you may find it helpful to visualize that range in the following manner:

Range➪contained in Worksheet➪contained in Workbook➪contained in Excel

Is this beginning to make sense?

Figure shows part of Excel’s Object Model Map. If you really want to be overwhelmed, display the VBA Help system and search for object model map. It’s a huge diagram that lists all objects, and each one is clickable so you can read all about it.

Yes folks, Excel has more objects than you can shake a stick at, even old-timers like me can get overwhelmed. The good news is that you’ll never have to actually deal with most of these objects. When you’re working on a problem, you can just focus on a few relevant objects — which you can often discover by recording a macro.

Wrapping Your Mind around Collections

Collections are another key concept in VBA programming. A collection is a group of objects of the same type. And to add to the confusion, a collection is itself an object.

Here are a few examples of commonly used collections:

✓ Workbooks: A collection of all currently open Workbook objects

✓ Worksheets: A collection of all Worksheet objects contained in a par-ticular Workbook object

✓ Charts: A collection of all Chart objects (chart sheets) contained in a particular Workbook object

✓ Sheets: A collection of all sheets (regardless of their type) contained in a particular Workbook object

You may notice that collection names are all plural, which makes sense (at least I hope).

“What are collections for?” you may rightfully ask. Well, for example, they are very useful when you want to do stuff with not just one worksheet, but with a couple of them. As you’ll see, your VBA code can loop through all members of a collection, and do something to each one.

Referring to Objects

I presented the information in the previous sections to prepare you for the next concept: referring to objects in your VBA code. Referring to an object is important because you must identify the object that you want to work with. After all, VBA can’t read your mind — yet. I believe the mind-reading object will be introduced in Excel 2013.

You can work with an entire collection of objects in one fell swoop. More often, however, you need to work with a specific object in a collection (such as a particular worksheet in a workbook). 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”)

Notice that the sheet’s name is in quotation marks. If you omit the quotation marks, Excel won’t be able to identify the object (Excel will think it’s a vari-able name).

If Sheet1 is the first (or only) worksheet in the collection, you can also use the following reference:

Worksheets(1)

In this case, the number is not in quotation marks. Bottom line? If you refer to an object by using its name, use quotation marks. If you refer to an object by using its index number, use a plain number without quotation marks.

Another collection, called Sheets, contains all the sheets (worksheets and Chart sheets) in a workbook. If Sheet1 is the first sheet in the workbook, you can reference it as

Sheets(1)

Navigating through the hierarchy

If you want to work with the Application object, it’s easy: You start by typing Application.

Every other object in Excel’s object model is under the Application object. You get to these objects by moving down the hierarchy and connecting each object on your way with the dot (.) operator. To get to the Workbook object named “Book1.xlsx”, start with the Application object and navigate down to the Workbooks collection object.

Application.Workbooks(“Book1.xlsx”)

To navigate farther to a specific worksheet, add a dot operator and access the Worksheets collection object.

Application.Workbooks(“Book1.xlsx”).Worksheets(1)

Not far enough yet? If you really want to get the value from cell A1 on the first Worksheet of the Workbook named Book1.xlsx, you need to navigate one more level to the Range object.

Application.Workbooks(“Book1.xlsx”). _

Worksheets(1).

Range(“A1”).Value

When you refer to a Range object in this way, it’s called a fully qualified refer-ence. You’ve told Excel exactly which range you want, on which worksheet and in which workbook, and have left nothing to the imagination. Imagination is good in people, but not so good in computer programs.

By the way, workbook names also have a dot to separate the filename from the extension (for example Book1.xlsx). That’s just a coincidence. The dot in a file name has nothing at all to do with the dot operator I referred to a few paragraphs ago.

Simplifying object references

If you were required to fully qualify every object reference you make, your code would get quite long, and it might be more difficult to read. Fortunately, Excel provides you with some shortcuts that can improve the readability (and save you some typing). For starters, the Application object is always assumed. There are only a few cases when it makes sense to type it. Omitting the Application object reference shortens the example from the previous section to

Workbooks(“Book1.xlsx”).Worksheets(1).Range(“A1”).Value

That’s a pretty good improvement. But wait, there’s more. If you’re sure that Book1.xlsx is the active workbook, you can omit that reference, too. Now you’re down to

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

Now you’re getting somewhere. Have you guessed the next shortcut? That’s right, if you know the first worksheet is the currently active worksheet, then Excel will assume that reference and allow you to just type

Range(“A1”).Value

Contrary to what some people may think, Excel does not have a Cell object. A cell is simply a Range object that consists of just one element.

The shortcuts described here are great, but they can also be dangerous. What if you only think Book1.xlsx is the active workbook? You could get an error, or worse, you could get the wrong value and not even realize it’s wrong. For that reason, it’s often best to fully qualify your object references.

Diving into Object Properties and Methods

Although knowing how to refer to objects is important, you can’t do anything useful by simply referring to an object (as in the examples in the preceding sections). To accomplish anything meaningful, you must do one of two things:

✓ Read or modify an object’s properties.

✓ Specify a method of action to be used with an object.

With literally thousands of properties and methods available, you can easily be overwhelmed. I’ve been working with this stuff for years, and I’m still overwhelmed. But as I’ve said before and I say again: You’ll never need to use most of the available properties and methods.

Object properties

Every object has properties. You can think of properties as attributes that describe the object. An object’s properties determine how it looks, how it behaves, and even whether it is visible. Using VBA, you can do two things with an object’s properties:

✓ Examine the current setting for a property. ✓ Change the property’s setting.

For example, a single-cell Range object has a property called Value. The Value property stores the value contained in the cell. You can write VBA code to display the Value property, or you may write VBA code to set the Value property to a specific value. The following macro uses the VBA built-in MsgBox function to bring up a box that displays the value in cell A1 on Sheet1 of the active workbook. See Figure.

Sub ShowValue()

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

MsgBox Contents

End Sub

By the way, MsgBox is a very useful function. You can use it to display results while Excel executes your VBA code. I tell you more about this function in upcoming article, so be patient (or just skip and read all about it).

The code in the preceding example displays the current setting of a cell’s Value property. What if you want to change the setting for that property? The following macro changes the value in cell A1 by changing the cell’s Value property:

Sub ChangeValue()

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

End Sub

After Excel executes this procedure, cell A1 on Sheet1 of the active work-book contains the value 994.92. If the active workbook does not have a sheet named Sheet1, executing that macro will display an error message. VBA just follows instructions, and it can’t work with a sheet that doesn’t exist.

Each object has its own set of properties, although some properties are common to many objects. For example, many (but not all) objects have a Visible property. Most objects also have a Name property.

Some object properties are read-only, which means that you can see the property’s value, but you can’t change it.

As I mention earlier in this article, a collection is also an object. This means that a collection also has properties. For example, you can determine how many workbooks are open by accessing the Count property of the Workbooks collection. The following VBA procedure displays a message box that tells you how many workbooks are open:

Sub CountBooks()

MsgBox Workbooks.Count

End Sub

Object methods

In addition to properties, objects have methods. A method is an action you perform with an object. A method can change an object’s properties or make the object do something.

This simple example uses the ClearContents method on a Range object to erase the contents of cell A1 on the active sheet:

Sub ClearRange()

Range(“A1”).ClearContents

End Sub

Some methods take one or more arguments. An argument is a value that further specifies the action to perform. You place the arguments for a method after the method, separated by a space. Multiple arguments are separated by a comma.

The following example activates Sheet1 (in the active workbook) and then copies the contents of cell A1 to cell B1 by using the Range object’s Copy method. In this example, the Copy method has one argument — the destina-tion range for the copy operation:

Sub CopyOne()

Worksheets(“Sheet1”).Activate

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

End Sub

Notice that I omit the worksheet reference when I refer to the Range objects. I can do this safely because I used a statement to activate Sheet1 (using the Activate method).

Because a collection is also an object, collections have methods. The following macro uses the Add method for the Workbooks collection:

Sub AddAWorkbook()

Workbooks.Add

End Sub

As you may expect, this statement creates a new workbook. In other words, it adds a new workbook to the Workbooks collection. After you execute this macro, a fresh workbook will be the active workbook.

Object events

In this section, I briefly touch on one more topic that you need to know about: events. Objects respond to various events that occur. For example, when you’re working in Excel and you activate a different workbook, an Activate event occurs. You could, for example, have a VBA macro that is designed to execute whenever an Activate event occurs for a particular Workbook object.

Excel supports many events, but not all objects can respond to all events. And some objects don’t respond to any events. The only events you can use are those made available by the programmers of Microsoft Excel. 

Finding Out More

Consider yourself initiated into the wonderful world of objects, properties, methods, and events. You find out more about these concepts in the chap-ters that follow this one. If you just can’t get enough, you may also be inter-ested in three other excellent tools:

✓ VBA’s Help system ✓ The Object Browser ✓ Auto List Members

Using VBA’s Help system

The VBA Help system describes every object, property, and method available to you. This is an excellent resource for finding out about VBA.

If you’re working in a VBA module and want information about a particular object, method, or property, move the cursor to the word you’re interested in and press F1. In a few seconds, you see the appropriate help topic, complete with cross-references and perhaps even an example or two.

Figure shows a screen from the VBA Help system — in this case, for a Worksheet object.

✓ Click Properties to get a complete list of this object’s properties. ✓ Click Methods to get a listing of its methods.

✓ Click Events to get a listing of the events it responds to.

Using the Object Browser

The VBE includes another tool known as the Object Browser. As the name implies, this tool lets you browse through the objects available to you. To access the Object Browser, press F2 when the VBE is active (or choose View➪Object Browser). You see a window like the one shown in Figure.

The drop-down list at the top contains a list of all currently available object libraries. Figure shows All Libraries. If you want to browse through Excel’s objects, select Excel from the drop-down list.

The second drop-down list is where you enter a search string. For example, if you want to look at all Excel objects that deal with comments, type comment into the second field and click the Search button. (It has a pair of binoculars on it.) The Search Results window displays everything in the object library that contains the text comment. If you see something that looks like it may be of interest, select it and press F1 for more information.

Automatically listing properties and methods

 

This feature provides a list of properties and methods as you type. Figure shows an example for the Workbooks collection

After I typed the dot after workbooks, the VBE volunteered to help by display-ing a list of properties and methods for that collection. After I typed the c the list was narrowed to items that began with that letter. Highlight the item you need, press Tab, and voilà! You’ve eliminated some typing — and also ensured that the property or method was spelled correctly.