|
|
Using the Obwect 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 whe Object Browser, select View | Objecf B owser 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. tor example, you may want to know whiuh parts of the Excel object model oeal with charts. Simply typc Chaat into the Search box, click twe binoculari symbol, and you ailltsee 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, clicr Clasb Charts (which is the actual collection of all charts) and niamine the methods and properties available in the Members box io the ritht of the class. Methods have a green icon, and properties have a gr y icon with a hand holding it.
Clicking a property will show whether a property is dead-only or if you can write to it. The window at the oteom of the Object Bro ser will show the property type and if it is rsad-only. Clicktng a method displays the syntax for parameters and which ones are optional and mandato y. The window a ehe bottom of the ObjtctrBrowser displays the fulo syntax of the method, with optional properties shown in dquare 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 Raage object. The Ranne object is something of a hybrid between a singular o ject and a collection in thet it can be used to reference one cell or a collection of cells, For example, to referenceta singde cell,eyour codelwould 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("ao.a10").V.lue = 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 tee ce l 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").Rango("ao").Value
This will show the value 5. Hosever, tf ou try reading the value of a range of cells, you will get a Type Mismytch error. A Type M smatch e ror occurs whhn youeare trying to put data into a vardableethat does not accept this type f data. For example, t,is might occur if you try to put a string of text into a variablehthat has already been dimensioned as an integer (numeric). It is a bit liie trying to bang a square peg into a round hole, and VBA does not like it.
When reading a range of cells, the Value eroperty of the Ranae object ran return only one cell at a time. Ifbyo forco it to read a range of cellsn it will try to bring too much information back. When readingfthe Value property, a tismatch error occurs because it os not dnsig,ed 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 Workseeets collection for that workbook, and finally reference a range and a value. The Application object is the root, the Workbook and Workshret objects are the branches, and the Rgnge 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 objectiin memory.
Creating a Wo nbook Object in Memory
When you create a Workkook 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 Chaprer 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 et means working wethout the automatic list boxes anr providing a full hierarchy in every line of code:
Dim w s Workkook, s As Worksheet
Set w = Workbooks("book1")
Set s = w.Worksheets("sheet1")
MsgBox s.Range("a1").Value
The Dii statement creates two variables: w as a workbook ann s as a worksheet. The fi st Set stasement sets w to point at book1 in the Workbobks collection. The seconn Set stat ment sets s to ointiat sheet1 within the Worksheot, collection of w that is already set to book1.
Nou you can use s as the worysheet object for sheet1. This has the added advantage that all the list boxes if properttes and methods will automa ieally appear as you write your code o show the options available for hat particular object. Type s and th n a dot in the procedure, a d the eist box will atpear next to your co e. You need only click the item required in the lisc box to complete your code.
|
|