The Object Browser

The VBA IDE includes a convenient and very useful tool for browsing through all available objects for a project and viewing their properties, methods, and events. It is called the Object Browser, and you'll use it to view Excel's object model and learn about what objects are available for you to use in your programs. You can also view all procedures and constants from your current project.

To open the Object Browser, select View, Object Browser, as shown in Figure 5.3, or simply hit F2. Figure 5.4 shows the Object Browser.

Selecting the Object Browser from the VBA IDE.

Selecting the Object Browser from the VBA IDE.

Object libraries

Object definitions (Classes)

Object members (properties, methods, and events)

The Object Browser.

Object libraries

Object definitions (Classes)

Object members (properties, methods, and events)

The Object Browser.

To use the object browser, first select the library from which you need to view the desired object, or select All Libraries (see Figure 5.5).

Selecting an object library.

w Objcct Browser

QŒM

¡■a3tLibraries» jr| !_j ^jjfj f j

Excel

1 Office

embers of"«globais>"

6 VRAProjKt

p Abs

a

J AciiweCell

«S Adeline

it? AelivsChart

35 Adjustments A'lowEditRange AilowEditRanges

© AftSweiWlzard

e£? AcllvePrinter esp AcliveSheet 1É? AdiveWindow if? AcliveWorktoook

¡SR AnswerwizardFiies

es" Ad dins

Application © Areas

Ap pAdruate Ef? Application

© Assistant

d» ASD

¿S AuloComect

A3 OB

iSJ AuloFilter

-fv AscW

© AuloRecm-er

id? Assistant

85 AHBS

Atn

Beep

¡SSI

An object library is a collection of objects provided by a specific application. You may notice libraries for Excel, Office, VBA, and VBAProject. You may see others as well, but it is these specific libraries that are of the most interest to you now. As you might have guessed, the Excel library contains objects specific to Excel and the Office library contains objects common to all MS Office applications (Word, PowerPoint, Excel, etc.). The VBA library adds a few objects specific to the VBA programming language, and the VBAProject library represents objects in the project currently open in Excel (that is, a workbook). In this chapter, it is the Excel library that is of the most interest to you because it's the library that contains specific objects that will allow you to interact with and extend Excel's capabilities.

After selecting the Excel library you'll see a list of all available objects within Excel in the bottom left window of the Object Browser (see Figure 5.4 or 5.5). The window is labeled Classes but don't let that confuse you. A class is just an object definition. A class definition is used to create an instance of the object it defines. This is all just technical jargon that you don't need to worry about right now—just remember that when you see the word class, you should immediately think "object." Also, remember that the class/object list represents all objects available for you to use in your program. After selecting an object from the list, the available properties, methods, and events of the selected object will be displayed in the window on the bottom right side of the Object Browser (refer to Figure 5.4). This window is labeled Members, because these items belong to, or are members of the selected object. When you select an item in the members list, information about that member—the member type, required syntax, and data type— will be displayed at the very bottom of the Object Browser. Once you become more familiar with the Object Browser, and VBA in general, you should find this information more helpful.

To Learn more about a specific object or one of its members, simply select an item in the Object Browser and press F1. The Help window will appear, displaying the result for the selected item in much more detail than what you see in the Object Browser.

If you prefer a more graphical representation of the Excel object model, look for the Object Model chart in the Help window under Microsoft Excel Objects. The chart, shown in Figure 5.6, displays the object hierarchy and provides links to documentation on the entire Excel Object Model.

The Excel Object Model.

The Excel Object Model.

Excel Vba Object Hierarchy 2007

Whatever tool you prefer to use (the Object Browser or Object Model chart), keep in mind that there is a hierarchy of objects that must be followed. You should think of the object hierarchy as a path to the object of interest much like a file path in a computer's operating system. It is a good idea to use these tools to set a specific object property or invoke an object's method when you're having difficulty navigating through the object hierarchy.

Consider a simple example. How do we insert the string "VBA is fun!" into cell A4 of Sheet2 in Book2 from the project shown in Figure 5.2? From examples in previous chapters, you know that you can use the Range property of the Application object.

Range("A4").Value = "VBA is fun!"

However, the line of code above will insert the string into the current or active worksheet, and this may not be your target worksheet. To ensure the string finds the correct target, first select the desired workbook.

Workbooks("Book2").Activate

To find the next object in the desired path to cell A4 of Sheet2 of Book2, look at the Object Browser. Since the above line of code gets you to the Workbook object, start by selecting the Excel object library and Workbook from the list of objects. Immediately, the members of the Workbook object are displayed on the right. If you scroll through this list you will eventually come to a property called Worksheets, as shown in Figure 5.7.

Viewing the Worksheets property of the Workbook object.

Viewing the Worksheets property of the Workbook object.

To select Sheet2, use the following code.

Workbooks("Book2").Worksheets("Sheet2").Activate

The second part of this statement (Worksheets("Sheet2")) is really the same code as written for selecting the Workbook object from the Workbooks collection object. The Worksheet object Sheet2 is selected from the Worksheets collection object. This code uses the Worksheets property of the Workbook object to return a Worksheet object from the Worksheets collection object. Since the Worksheet object is lower in the object hierarchy than the Workbook object, it follows it in the line of code above. Finally, the Activate() method of the Worksheet object selects Sheet2 within the workbook Book2. That was a mouthful, but if you work through the hierarchy slowly, and view each of these components through the Object Browser, it will make sense.

To add the string "VBA is fun!" to cell A4, use the following code:

Workbooks("Book2").Sheets("Sheet2").Range("A4").Value = "VBA is fun!"

The Range property is found in the list of members for the Worksheet object, as shown in Figure 5.8. Note that the Cells property could have also been used.

Workbooks("Book2").Sheets("Sheet2").Cells(4, "A").Value = "VBA is fun!"

ta' Object Browser

Viewing the Range property of the Worksheet object.

ta' Object Browser

j'All Libraries- jrj * _J ffoJjjJ _fj

jd äi£j

Classes

Members of Worksheet'

¡0 Windows A

Ph/otTableVVizard

¡£j Workbook

1§f Previous

Workbooks

^ PrlrHO ut

®iwörjräheet j

PrimPreview

Worksheetf unclion

Protect

Worksheets

tflf ProtectContenls

xJAppdcallon internat

& ProteotDravrinaObjects

¿P WAptWamesOrder

tí? Protection

dp tfArabrcModes

tSf ProtectionMode

¿f XfArrangeStyle

lí¡? Protects cenarlos

GÈP XfArrowHeadLenath

if? QuerVTaDles

d? WArrowHeadStyie

tíHsMIM

dfi XÏArrowHeadWidlK

ResetAll P ageBre aks

# XIAutoFillType

Rows

¿P XWutoFllterOperator

saveAs

¿P KJAxisCrosses

Ä Scenarios

JM

-a* 0,,;,+-

Property RanyeíCefl'í, [CefJ5]) As n.mne

ieskl-oflty

Mt IT be r fir fxtH VVûik^li^^t ieskl-oflty

The Range property returns a Range object that represents one or more cells in a continuous block on a worksheet. In this case, the Range property returns the Range object that represents cell A4. Next, the Value property of the Range object is used to set the contents of cell A4 to the desired string "VBA is fun!", as shown in Figure 5.9.

Inserting a string in a worksheet ceLL.

Vba Object Hierarchy

You may be wondering if you really need to work your way through the entire object hierarchy to set one property? The answer is yes, but only if each object referenced in the code needs to be identified out of a collection of objects. For example, if there is only one Workbook object open, then

Sheets("Sheet2").Range("A4").Value = "VBA is fun!"

works just as well as the previous code. Actually, this code will execute regardless of how many Workbook objects are open, but it will put the string in the currently selected or active workbook. Likewise,

Range("A4").Value = "VBA is fun!"

executes, but it will put the string in the active worksheet; thus, each object qualifier is necessary only as long as it is needed to identify one specific object out of several possibilities.

Biorhythm Awareness

Biorhythm Awareness

Who else wants to take advantage of biorhythm awareness to avoid premature death, escape life threatening diseases, eliminate most of your life altering mistakes and banish catastrophic events from your life.

Get My Free Ebook


Responses

Post a comment