Using the Object Browser

If you want to move easily through the myriad of VBA elements and features, examine the capabilities of the Object Browser. This special built-in tool is available in the Visual Basic Editor window.

To access the Object Browser, use any of the following methods:

■ Choose View | Object Browser

The Object Browser allows you to browse through the objects that are available to your VBA procedures, as well as view their properties, methods, and events. With the aid of the Object Browser, you can move quickly between procedures in your own VBA projects, as well as search for objects and methods across type libraries.

The Object Browser window is divided into three sections (see Figure 2-16). The top of the window displays the Project/Library drop-down list box with the names of all libraries and projects that are available to the currently active VBA project. A library is a special file that contains information about the objects in an application. New libraries can be added via the References dialog box (Tools | References). The entry for <All Libraries> lists the objects of all libraries that are installed on your computer. When you select the library called Excel, you will only see the names of the objects that are exclusive to Microsoft Excel. As opposed to the Excel library, the VBA library lists the names of objects that are exclusive to Visual Basic for Applications.

Figure 2-16: The Object Browser window allows you to browse through all the objects, properties, and methods available to the current VBA project.

Below the Project/Library drop-down list box, there is a Search text box that allows you to quickly find information in a particular library. This field remembers the last four items for which you searched. To find only whole words, right-click anywhere in the Object Browser window and choose Find Whole Word Only from the shortcut menu. The Search Results section of the Object Browser (Figures 2-16 and 2-17) displays the Library, Class, and Member element that met the criteria entered in the Search text box. When you type the search text and click the Search button Visual Basic expands the Object Browser dialog box to show the Search Results. You can hide or show the Search Results by clicking the button located to the right of the binoculars.

Figure 2-17: Searching for answers in the Object Browser

The Classes list box displays the available object classes in the selected library. If you select a VBA project, this list shows objects in the project. In Figure 2-16 the CommandBarComboBox object class is selected. When you highlight a class, the list on the right-hand side (Members) shows the properties, methods, and events available for that class. Figure 2-16 shows some of the members of the CommandBarComboBox class. By default, members are listed alphabetically. You can, however, organize the members list by group type (properties, methods, or events) using the Group Members command from the Object Browser shortcut menu.

If you select a VBA project in the Project/Library list box, the Members list box will list all the procedures available in this project. To examine the code of a procedure, simply double-click its name. If you select a VBA library, you will see a listing of Visual Basic built-in functions and constants. If you need more information on the selected class or member, click the question mark button at the top of the Object Browser window.

The bottom of the Object Browser window displays a Code template area with the definition of the selected member. If you click the green hyperlink text in the Code template, you can quickly jump to the selected member's class or library in the Object Browser window. Text displayed in the Code template area can be copied to the Windows clipboard and then pasted to the Code window. If the Code window is visible while the Object Browser window is open, you can save time by dragging the highlighted code template and dropping it into the Code window.

You can easily adjust the size of the various sections of the Object Browser window by dragging the dividing horizontal and vertical lines.

Now that you've discovered the Object Browser, you may wonder how you can put it to use in VBA programming. Let's assume that you placed a text box in the middle of your worksheet. How can you make Excel move this text box so that it is positioned at the top left-hand corner of the sheet?

1. Open a new workbook.

2. Choose View | Toolbars and click Drawing.

3. Click Text box on the Drawing toolbar. Draw a box in the middle of the sheet and enter any text.

^ Microsoft Eitce! Chap02.xls

_|nt

x|

SQ File Edit

View Insert Format

Jciols- Data Win do'A

Help

. i?

X

Arial

. 10

• !

h m

g m

E - il|

m

» -a»

- A -

» »

□ Sir y 1

[ -1 ^ -1

. 10DS

K

Text Box 1

&

A I

B

M

.D

E

F

G

_

1

2

3

5

g_

6

1

My text tio'ji

l

7

S

y

-1

iz

10

a

r

II

12

j

fz

13

i

i

14

15

In < ► H \5heetl / SheeE / sheets /

«in

1

D[d'A'" AutuShdpe:::'

\

L 0 III I

l|

b - "g* gj

»

1 LlJ

Figure 2-18: Excel displays the name of the inserted object in the Name box above the worksheet.

4. Select any cell outside the text box area.

5. Press Alt+F11 to activate the Visual Basic Editor window, and select Personal (Personal.xls) in the Project Explorer window.

6. Choose Insert | Module to add a new module sheet to the Personal Macro Workbook.

7. In the Properties window, enter the new name for this module: Manipulations.

8. Choose View | Object Browser, or press F2.

9. In the Libraries/Projects list box, click the drop-down arrow and select the Excel type library.

10. Enter textbox as the search text in the Search box and click the Search button Make sure you don't enter a space in the search string.

Visual Basic searches the Excel library and displays the search results. It appears that the Shapes object is in control of our text box operations (Figure 2-19). Looking at the members list, you can quickly determine that the AddTextbox method is used for adding a new text box to a worksheet.

The Code template at the bottom of the Object Browser shows the correct syntax for using this method. If you select the AddTextbox method and press F1, you will see the Help window with more details on how to use this method (Figure 2-20).

When you examine the arguments of the AddTextbox method and their explanations in the Help window, you can quickly figure out that the Left and Top properties determine the position of the text box in a worksheet. All you have to do now is return to the Code window and write the procedure to move the text box to the upper left-hand corner.

V Object Browser

■Jnljä

1 Excel

fj

J textbox

MR

Search Results I

1 Library | Class

1 Member

1 1

[ri: v terrpft

es

1

Excel Constants 3 xUexfäox

Classes

I Members of'Shapes'

ShapeNode

.•a- AiJiLabel

M

¿5 ShapeNodes

AdJLiine

¿3 ShapeRange

s MdOLEOtjject

¿9[Shapes

AddPieture

¿if Sheets

_1

.fldiPolytlne

¿5 SmartTag

^ AddShape

1

& SmartTagActlon

â»!AdlITEïttl(H

¿3 SmartTagActions

<» AJiTeïlEflsrt

£9 SmarfTagOptions

& BulädFreeToriff

J

SmartTagRecognize ▼ |

Item

Function A<l<n"extl>ox{Or/enteiü/) 4s MsoText' >i n^totîôh, left

Single, Top 4s Single, Width 4s Single, Height A s Single) As Sli.n>e

Member of Ext el.Sh.ipeo

Using the Object Browser window, you can find the appropriate VBA instructions for writing your own procedures.

Figure 2-19:

Using the Object Browser window, you can find the appropriate VBA instructions for writing your own procedures.

11. Close the Object Browser window and open).

the Help window (if it is still

12. Double-click the Manipulations module, and enter the procedure MoveTextBox:

Sub MoveTextBox()

With ActiveSheet.Shapes("Text box 1") .Select .Left = 0 .Top = 0 End With End Sub

13. Choose Run | Run Sub/UserForm to try out this procedure.

When you return to the worksheet where you placed your text box, the box will be positioned at the top left-hand corner of the worksheet.

Notice that the MoveTextBox procedure selects Text box 1 in the collection of Shapes. Text box 1 is the default name of the first object placed in the worksheet. Each time you add a new object to your worksheet, Excel assigns a new number (index) to it. Instead of using the object name, you can refer to the member of a collection by its index.

For example, instead of:

With ActiveSheet.Shapes("Text box 1")

you can enter:

With ActiveSheet.Shapes(l).

Let's manipulate another object with Visual Basic. Try this on your own. Place a small circle in the same worksheet where you originally placed the text box. Use the Ellipse tool on the Drawing toolbar to draw the circle. Insert a new procedure in the Manipulations module and write the VBA code to place the circle inside the text box. Keep in mind that Excel numbers objects consecutively. The first object is assigned a number 1, the second one a number 2, and so on. The type of object, whether it is a text box, a circle, or a rectangle, does not matter.

The MoveCircle procedure shown below demonstrates how to move a circle to the top left-hand corner of the active worksheet:

Sub MoveCircle()

With ActiveSheet.Shapes(2) .Select .Left = 0 .Top = 0 End With End Sub

Moving a circle is similar to moving a text box or any other object placed in a worksheet.

Notice that instead of referring to the circle by its name, Oval 2, the procedure uses the object's index. When you run the MoveCircle procedure, it places the circle inside the text box object.

0 0

Post a comment