Exploring Excel Objects

Problem

You've heard of Excel objects (in fact I've already mentioned them several times) and want to know what they are and how they'll benefit you.

Solution

Excel exposes its features and functionality through objects. Excel's object model, as it's called, includes nearly 200 objects that represent everything from cell ranges to charts to worksheets, workbooks, and the Excel application. Each object has properties that allow access and control over its attributes, as well as methods that expose the object's functionality. When working with VBA, you access Excel and the spreadsheets included in your project through Excel objects. There are far too many objects to discuss here. Further, not all of them are directly relevant to the sort of calculations in which we're interested in this book. The purpose of this recipe is to introduce key objects to you now, so we can use them in later recipes. The discussion to follow gives an overview of these key objects.

Discussion

Before looking at specific objects, I want to discuss the Object Browser available in the VBA development environment. The Object Browser is a handy tool that allows you to browse through available objects, inspecting their properties and methods.

The Object Browser

As I mentioned earlier, there are nearly 200 Excel objects that you can access using VBA. Each object also contains numerous properties and methods. Thus, it's very difficult to keep track of all the objects, their properties, and methods without the aid of a good reference book, the VBA help guide, or the Object Browser. The Object Browser is a window that you can open within the VBA IDE by pressing the F2 key or selecting View images/U2192.jpg border=0> Object Browser from the main menu bar. Figure 2-6 shows the Object Browser opened in the VBA IDE.

Figure 2-6. Object Browser window

The Object Browser displays a list of objects. The upper-left drop-down listbox allows you to select the scope of the objects to view. For example, in Figure 2-6 I've selected Excel; therefore, the Object Browser is displaying only those objects belonging to Excel. Other options include selecting only those objects in your project or only objects in the Microsoft Office Suite object model; there are also many others. We're going to focus on objects in Excel.

The list labeled Classes is the list of objects. You can select any object in this list to display more information on the object. For example, the very bottom panel of the Object Browser window is telling us that the selected Application object is a member of the Excel object model. Also, the list to the right of the Classes list displays all members of the selected object. Members include both properties and methods. You can select a member in this list and the bottom panel will display relevant information, such as whether it is a property or a method. If you press the button at the top of the Object Browser window with the question mark icon, a new window will open that contains context-sensitive help specific to the selected object or member. Thus the Object Browser is a convenient way to navigate the object reference guide, too.

The second drop-down listbox at the top of the browser window allows you to enter a term (e.g., a property name), and search for it in the object list. The search results panel will display a list of findings for your search term.

I encourage you to play around with the Object Browser and explore the myriad objects, properties, and methods included in the Excel object model. That said, for the most part we'll use only a small subset of these for our purposes.

Application object

The Application object represents the Excel program. It's a top-level object that gives access to top-level features in Excel such as setting options, among others. Some of the most common uses I have for the Application object involve accessing an object that represents the currently active worksheet, accessing an object that represents the currently active cell, and controlling Excel's window updating while VBA procedures are running.

Example 2-17 shows how to access the currently active worksheet in Excel by using the ActiveSheet property of the Application object. You can type the statement shown in Example 2-17 directly in a VBA procedure.

Example 2-17. ActiveSheet property

You access members of objects by placing a period after the object name, followed by the property or method you want to access. In Example 2-17 I'm accessing the Cells property of the ActiveSheet, which itself is a property of the Application object. Note that properties can be objects with members of their own. This sort of cell access is useful when writing calculation output directly into cells in a worksheet, in this case the currently active worksheet. Note the cell reference style used here. Cells(1, 2) represents the cell in row 1 column 2.

Example 2-18 shows how to access the ScreenUpdating property of the Application object.

Example 2-18. ScreenUpdating property

The ScreenUpdating property stores a Boolean value, indicating whether the Excel window should be updated while VBA code is running. In most cases I leave ScreenUpdating set to TRue so I can follow my VBA code as it runs. However, for some intensive calculations, especially with a lot of looping, keeping ScreenUpdating set to true can really slow things down. In those cases, it may be desirable to turn ScreenUpdating off (i.e., set it to false). You can set it back to true after the intensive block of code.

Workbook object

The Workbook object represents an Excel workbook. You can control many aspects of workbooks, such as gaining access to the active sheet in a workbook through its ActiveSheet property, obtaining the name and path to the workbook, and saving the workbook.

I often use the Workbook object's Save method to save the workbook. For example, if I write a particular VBA procedure that makes changes to a workbook by writing calculation output to cells in a worksheet, then I'll have the VBA procedure also save the workbook. Example 2-19 shows how to call the Save method.

Example 2-19. Save method

In this example, I'm gaining access to the currently active workbook through the Application object's ActiveWorkbook property.

Worksheet object

The Worksheet object represents an Excel worksheet. It provides access to sheet-level features and functionality such as copying and pasting, printing the sheet, saving the sheet, and showing or hiding the sheet. I use it primarily to access cells, as shown earlier in Example 2-17 (in that example, ActiveSheet is a Worksheet object that refers to the currently active worksheet).

WorksheetFunction object

As discussed in Recipes 2.5 and 2.12, the WorksheetFunction object allows you to access Excel functions from within VBA. Figure 2-7 shows a screenshot of the Object Browser with the WorksheetFunction object selected.

Figure 2-7. WorksheetFunction object in the Object Browser

In the figure, you can see a partial list of functions to which you have access through the WorksheetFunction object. Example 2-20 shows a few ways to call Excel functions using the WorksheetFunction object.

Example 2-20. Calling functions through the WorksheetFunction object

The first line uses the worksheet function Pi in an expression for calculating the area of a circle given its radius (stored in the variable r).

The second line calculates the arccosine of the variable b and returns the result in the variable a.

The last pair of lines sets a range consisting of the first five cells in column A of Sheet1 in the open workbook and then uses Excel's Sum function to get the sum of the values stored in the range.

This last pair of lines uses the Worksheets collection, which is simply a collection of all the open Worksheet objects in a workbook. In this case, we access a specific sheet in the collection using the sheet's name. We then access the Range property of the selected sheet and set it to include cells A1 through A5.

I use the Set statement to set the Range object to a temporary variable called TestRange. This saves a bit of typing when using the range in multiple statements. Finally, TestRange is passed in as an argument to the Sum function, and the result is returned in the TestSum variable.

Range objects

Range objects represent cell ranges. Earlier I showed you how to access cells using the Cells property of the ActiveSheet object. You can use Range objects as another means of accessing cells, as shown previously in Example 2-20.

Example 2-21 shows a few more ways to use the Range object to access cells.

Example 2-21. Using Ranges

This example includes the last two statements from the earlier example, with the addition of two new statements. The second to last statement takes the sum calculated in the previous statement and puts it in cell A7. As a comparison, the final statement adds a cell formula to cell B7, which also calculates the sum of values in cells A1 to A5. When these statements are executed, both cells A7 and B7 should show the same value.

These are just a few common tasks that involve working with Range objects. Another very useful member of the Range object is the GoalSeek function, which we can use for iterative calculations (see Chapter 9 for some examples).

See Also

The summary presented in this recipe only scratches the surface of Excel's object model. Granted, we don't really need much in the way of Excel objects for our purposes; however, it's good to be familiar with the other objects in case you decide to expand your use of VBA and Excel; for example, if you decide you want to write and distribute your own add-ins.

For more information on Excel objects, you can always consult the VBA help guide. Look for the "Objects" topic under Microsoft Excel Visual Basic Reference in the table of contents. There you'll find an alphabetically organized object reference.