Using the Object Browser

The Object Browser is a useful tool for looking at the properties, methods , and constants of an object ‚ in this case, the Excel Application object. To access the Object Browser, select View Object Browser from the VBE menu or press F2 . Use the pull-down that says <All Libraries> to find the Object Library, and click it. This will show all the classes of the Excel object and the properties and methods. It will also show the relationships and hierarchy of the objects themselves . Figure 12-3 shows how it looks onscreen.

Figure 12-3: The Excel object model in the Object Browser

You can search on specific strings by entering your search string into the box underneath the pull-down showing Excel and then clicking the binoculars symbol or pressing Enter . For example, you may want to know which parts of the Excel object model deal with charts . Simply type Chart into the Search box, click the binoculars symbol, and you will see all references containing the word Chart . This is far easier than investigating each hierarchical structure, which could take some time.

You can click a class (which is an object) to see all the properties, methods, and collections underneath it. If you have searched on the word Chart, click Class Charts (which is the actual collection of all charts) and examine the methods and properties available in the Members box to the right of the class. Methods have a green icon, and properties have a gray icon with a hand holding it.

Clicking a property will show whether a property is read-only or if you can write to it. The window at the bottom of the Object Browser will show the property type and if it is read-only. Clicking a method displays the syntax for parameters and which ones are optional and mandatory. The window at the bottom of the Object Browser displays the full syntax of the method, with optional properties shown in square brackets ([ ‚  ]).

Communicating with the Spreadsheet

One of the main uses of VBA in Excel is to communicate with spreadsheets and to manipulate values within cells . To do this you must use the Range object. The Range object is something of a hybrid between a singular object and a collection in that it can be used to reference one cell or a collection of cells. For example, to reference a single cell , your code would look something like this:

 Workbooks("book1").Worksheets("sheet1").Range("a1").Value = 10 

Place this code into a module and then run it by clicking the cursor on the procedure. Press F5 and check the value of cell A1 on sheet1 in book1. This will show the value of 10.

You can also reference a range (or collection) of cells:

 Workbooks("book1").Worksheets("sheet1").Range("a1.a10").Value = 5 

This fills the cells from A1 to A10 with the value 5. Notice that the dot is used between a1 and a10 to separate the cell references.

This is not the only way of doing this, as Microsoft very kindly gave us a number of choices. You can also use a colon (:), a comma (,), or a double dot (..). You can also go the full way and give individual cell references for start and finish:

 Workbooks("book1").Worksheets("sheet1").Range("a1","a10").Value = 5 

Personally, I do not make life hard for myself , and I use the dot because it is the least number of keypresses. (Also, because you use it in other parts of the programming language, it falls to hand quite easily.)

Conversely, you can also read the value of A1 back into your code. You may be writing an application that takes values out of a worksheet, processes them in some way, and then puts them back onto the same worksheet, or even into another workbook.

 MsgBox Workbooks("book1").Worksheets("sheet1").Range("a1").Value 

This will show the value 5. However, if you try reading the value of a range of cells, you will get a Type Mismatch error. A Type Mismatch error occurs when you are trying to put data into a variable that does not accept this type of data. For example, this might occur if you try to put a string of text into a variable that has already been dimensioned as an integer (numeric). It is a bit like trying to bang a square peg into a round hole, and VBA does not like it.

When reading a range of cells, the Value property of the Range object can return only one cell at a time. If you force it to read a range of cells, it will try to bring too much information back. When reading the Value property, a mismatch error occurs because it is not designed to hold an array of information.

You have probably noticed by now how much code references the ‚“tree ‚½ of objects. For example, you can start off with the Application object, reference a workbook in the Workbooks collection underneath it, then reference a worksheet within the Worksheets collection for that workbook, and finally reference a range and a value. The Application object is the root, the Workbook and Worksheet objects are the branches, and the Range object is the leaves . This can become somewhat laborious if you're working with many lines of code and you have to keep writing out this enormous reference to identify a particular cell. As a shortcut, you can refer to the worksheet name , for example:

 MsgBox Worksheets("sheet1").Range("a1").Value 

This will work, but suppose you have more than one workbook loaded and they both have a sheet1 in the workbook? Excel will choose the sheet in the active workbook. Confusion reigns supreme. Fortunately, there is a way to cut down the amount of referencing and keep the integrity of the code by using the Dim statement to create a Workbook object in memory.

Creating a Workbook Object in Memory

When you create a Workbook object in memory, you define a variable to represent that workbook by dimensioning a variable with the Dim statement. You can call your variable anything you want as long as it has not already been used in your code and is not a reserved word (see Chapter 2).

The advantage of creating a Workbook object is that it can be set to represent a particular workbook with a Set statement. After that, you can use that variable to reference that workbook, and the automatic list boxes showing the underlying properties, methods, and collections will still work with it. You can work without the Set statement, but it means working without the automatic list boxes and providing a full hierarchy in every line of code:

 Dim w As Workbook, s As Worksheet 
 
 Set w = Workbooks("book1") 
 
 Set s = w.Worksheets("sheet1") 
 
 MsgBox s.Range("a1").Value 

The Dim statement creates two variables : w as a workbook and s as a worksheet. The first Set statement sets w to point at book1 in the Workbooks collection. The second Set statement sets s to point at sheet1 within the Worksheet , collection of w that is already set to book1 .

Now you can use s as the worksheet object for sheet1. This has the added advantage that all the list boxes of properties and methods will automatically appear as you write your code to show the options available for that particular object. Type s and then a dot in the procedure, and the list box will appear next to your code. You need only click the item required in the list box to complete your code.