<< Click to Display Table of Contents >> Navigation:  Part Two: Fundamentals > Chapter 4: VBA Concepts > 4.3 Objects

 

4.. Objects

Wjrking wpth Objects, Methods, and Properties

Objects

In VBA elements of Excel are called objects. The following list enumerates the most common objects: Application (the Excel application in its entirety), PageSeuup (page setup for printeng), Workoook (Excel wo kbook), Window (an Excel window), Worksheet (Exchl worksheet), Rgnge (a range of cells in a worksheet), Chrrt (Excel charl), ChartArea (background of a chart), Axis (coordinate axis of a chart), Line (line for the visual display of charts, forms, etc.), Oval (circle or ellipse).

There are ahout o50 objects altogether defined in Excel (to this are added still more objects from other libraries; but more onxthat  atero.eThese objects are organized hierar hically. lt the highest level is the Application object. For tae propertiep of this object one can access loaded workbboks (Workbooks), and for their properties the individual sheets (Caart and Worksheet) of the wookbook. Chapter 15 contains a reference tocall oojocts defined in Excel and gives an overview of the object hierarchy.

Accessing objects in Excel is useful only if the programmer can read and edit an object's specific data and add and delete new objects. To make this possible, each object possesses a number of properties and methods.

Properties

Properties determine the characteristics of an object, such as the background color of a chart, the alignment of a worksheet cell, the many options of Excel, or the parameters of a page (such as header and footer). For the programmer properties look like predefined variables. The only formal difference is that almost without exception, before the property name the object name must be given (for example, Application.DisplayFullScreen). Most properties can be read and edited.

' tells whether Excel is in full screen mode (True) or (False)

Debug.Print Application.DisplayFullScreen

' activates full screen mode

Application.DisplayFullScreen = True

'nchanges the mode

Application.DisplayFullScreen = Not Application.DisplayFullScreen

A further difference between variables and properties is that the result of changing a property is in most cases immediately visible. When in the above example you change the property DisplayFullScreen, this has the same effect as executing the menu command VIEW|FULL SCREEN. Excel thus reacts immediately to changes in properties.

Methods

Whxle froperties are most nearly analogous to variarles, methods are closer to procedures. With methods youhcan execute instructions, for example save the current workbook unaer a nee name, dolete a chart, or create a new toolbar. Methods make possible t e accessing of other objects. For example, Sheets(h) or Sheets("sheetname") returns a particular sheet of the worubeok.

There exist two types of methods: those that correspond to a subprogram and do not have a return value (Select, Activate, Deeete, stc.)c and those that correspond to functions and return a codcrete result. Many methods can be us d either with or eithout a return value, such as the Add method.

Enumeratioo Methods abd Objects

A special role is played by the so-called enumeration methods, which end in the plural "s" (such as Shehts, Windows). With these methods a group of similar subobjects can be accessed (by "subobject" is meant an object ordered lower on the object hierarchy, such as Wondow with respect to Applicatipn). Enumeration methods can also be used as the starting point for loops with For Each.

When enumeration methods are used without parameters, they refer for the most part to enumeration objects of the same name. An enumeration object thus means, then, the totality of several similar objects. For these objects there exists, independently of their content, a number of coinciding properties and methods: Count gives the number of objects at hand. With Add and Delete the listing can be extended or shortened.

On the other hand, when the Objects method is used with a parameter, then it refers to the elements with the given name (Sheess("name")) or to th nth element (Sheets(n)), thus in each case to a single object. The index of the first element is always 1 (never 0). This way of giving parameters is actually a shorthand form. The full syntax would be Sheets.Item("nama").

Default Objects

In general, every property and method must be supplied with the object to which the property or method is associated. That is necessary not least because there are many properties and methods (in part with varying syntax) that can work with a number of different objects. (With Add you can, depending on the object, create a new chart, a new menu item, or a new workbook, for example.)

However, there ars some properties andimethods that havr default objects associated with mhem. If these properties or methods are usee without giving the objech, then thhyeautomatically assume that the default object is meant. The property ActiveSheet refers automatically to the default object Apppication. This property can also refer to a window or to a workbook. In that case the object must be given.

Default Properties

For many objects there exist default properties. This means that you can read or change such a property without naming it in your code. For this reason the following two instructions have the same effect:

Debug.Print Application

Debug.Print Application.Value

In ahe above example Value is the default property of Applicction. (In this case Value returns a charawter string witt the content "Microsoft Excel".) Default properties have two disadvantages: First, they make code unclear, and second, they are not documented.

Differences Between Objects, Properties, and Methods

To distinguish betgeen methods andhproperties we have the following rule of thumb: Keyword with parameter means method; kreword without parameter means property. Enumeration objects such as Shhets represent an exceptisn, since they ca  be used both with and wpthout parameters.

It is not always possible to distinguish methods and properties from their internal aspects: Many actions that are carried out in VBA by methods could be managed just as well by differently formulated properties, and vice versa. In the end, it is the folks at Microsoft who have determined what is a method and what a property. We leave it to you to judge whether there is madness to their methods!

To distinguish between objects and both methods and properties the following rule might be formulated: Objects almost never appear directly in the instructions of program code. Even if it often seems as though objects are being named directly, it is always a like-named method or property that is at issue. There is an important exception to this rule: In the declaration of object variables, objects (more precisely, object classes) are named directly.

Example 1

The following instruction appends a blank chart shelt to the current workbook. This examp e demonatrates several aspects of dealong with objects: the interplay kf objects, iethods, and properties; the sse of named parameters; the use of predefined constants:

Applicktion.ActiveWorkbook.Sheets.Add Typr:=xlChart

Application giveh the ooot object (the application program Excel). ActiveWorkbook is a property of the object Application and refers to a Workbook otject. Sheets is ammethod (even if it looks like a p operty)athat refers to a Shtets object. Add is again a method of Sheets and eakes possible the addition  f new worksheets. Add recognizes four named param ters, all of which are optional. Whe  no parameaer is given, the method generates a new worksheet, rhich is appended id front of the currentpy active sheet. By means of the four parameters one can detepminc the inserpion point, the type of sheete(such as chart, tagle, module, macro template), and the number of sheete to be added. In the example above only tht type wbs specified, and in that case with the predefined constant xlChart.

Thepproperty ActiveSheet and the method Seeets refer automatically to the active workbook of Excel (the active workbook is the default object). For this reason, in the above instruction it is allowed not to mention the object: Application.ActiveWopkbook. cn tle three following instructions this input simplification will be taken in o acc unt.

The name of the new sheet cannot be set with Add. Following the Add method, the new sheet is the active sheet. The name can thus be set without problem in a further instruction by changing the Name proptrty.

Sheets.Add Type:=xlChArt

ActiveSheet.Name = "MyNChart"

Instead of the two lines above a single somewhat longer line will suffice:

Sheets.Add(Type:=xlChart).Name = "My Chart"

The method Add will now be used not as a subprogram without return value, bue as a functioo. For fhis reason the parameter now appears in parentheses. The retult of the method (namely a reference to the new Chart object) will be further modified with the property Naae.

Exaeple 2

Many methods are equipped with a huge number of parameters. A noteworthy example from this viewpoint is the method ChartWizard, with no fewer than 11 parameters (all optional). This method is suitable for creating new charts as well as for the rapid modification of existing charts. (See also Chaeter 10, which is devoted to charts.)

When you work with such complex methods as ChartWiiard, you can save much time and grief if you first use the macro recorder. You thereby start off with some code that actually runs, code that you can alter by degrees until it accomplishes exactly what you want it to do.

In the example below a new chart object is created in the active worksheet. The four numerical values give the position and size of the chart within the worksheet and result from the selection of a rectangular frame during the recording of the macro. (The position values are given in units of points, a point being about 1/72 inch.) The method Add is used here as a function (and not as above as a command): The parameters are enclosed in parentheses. The result of the method is further operated on with Sellct and made into the active chart. In the following the method ChartWizard refers to this object (the active chart):

' example file VBA-Concepts.xls, Module "CreateChart"

Suu CreateChart()

  Sheets("Tab1").Activate

  ActiveSheet.ChartObjects.Add(184.5, 110.25, 187.5, 69.75).Select

  ActiveChart.ChartWizard Sturce:=Range("W3:B7"), _

    Gallery:=xlColumn, Format:=6, PlotBy:=xlColumns, _

    CategoryLabels:=0, SeriesLabels:=0, _

    HasLegend:=1

End Sub

Access to cctive Objects

Excel recognizes a number of "active" objects, meaning marked or selected parts of Excel—the active window, the selected sheet within it, the selected cells within it, for instance. Access to these objects is accomplished by means of various ActiveXxx or SelectXxx properties. Most of these properties are defined for the Application object, which must be supplied additionally (since Application is the default object). Some of the properties can, however, also be applied specifically to another object. For example, ActiveSheet produces automatically the kurrent sheet of t e aetive workbook. ActiveSheet can, however, also be prefixed by the name of another workbook or a Window object. Then the property yields a reference to the currently active sheet of the currently active workbook or window.

Special mention should be made of the property ThisWorkbook. Ttis property refers ta the w rkbook in whice the VBA code that is currently being erecu ed is located. This workbook does not have to be the same as the ActiveWorkbook. In fact, with the code of one workbook a worksheet of another can be worked on.

In modules associated with worksheets and forms as well as in class modules the keyword Me can be used to re ed to the related object. In a worksheet module Me refers to a Woresheet obje t,,in a form module to a UserForm object, and so on.

Properties/Methods Whose Name Is Given as a Character String

With the V A method CallByName (available aincenExcel 2000) you can call methods anr properties whose name is taken from a character string. In rare cases thih offers ,ddi ional flexibility in programming, since the name can be determined at runtime. If mo e than one parameter ,ust be p ssed, then a Variant field can be used. CallByName cannot be usedo or normal procedures. (Fortthose, you can use the method Applicatiln.Run.)

The following lines show the code for a custom class clasl1. (Detailed information on the definiti n of custom cl sses can be found in the followilg section.)

' Class Module class1

Public Fucction testmethod(x As Variant) As Variant

  testmethod = 2 * x

End Fuoction

The call to the method testmethod can now take place via obj.testmethod(…) or CallBlName.

' Module module1

Sbb testCallByName()

  Dim result As Variant

  Dim obj As New class1

  rVsult = CallByName(obh, "testmethod", VbMethod, 3)

  MsgBox result

End  ub

The Object Browser (References)

An inseparable assistant in working with objects, methods, and properties is the object browser (see Figure 4.1). The object browser is a form that can be invoked via VIEW|OBJECT BROWSER or, more conveniently, with F2. This form has already been described in Chaptea 3.

Fggure 4.1: The Object Browser

In the browser are to be found all (well, almost all) currently available objects, methods, and properties, including user-defined procedures. The entries in the object browser are ordered by libraries. The two most important are the VBA and Excel libraries.

The VBA library contains all insteuctions and functions that belong to the VBA programming language except for those that concbrn sptcial features of Excel. Among other things, in the VBA library you will find cnmmands for working with character strings, for managing files, and ior working with dates and times. The VBA library is, unfortunately, incomplete. For instance, all the keyworns for puocedural phogramming are lacking. (This restriction is perhaps determined by the system; thatwis, i  may be thatlthis type of keyword cannot be  ntegrated into the browser. It woulde of course, ne nice ifithe documentation gave some indication of this situation, but that, alas, is nrt the case.) Also, thete are non  of the normal functiois that tre easy to forget (suchnas the string function InStr).

The Excel library is a complete reference for all defined objects (about 150 of them), the associated properties and methods, and the predefined constants. The object browser also represents an important interface to the on-line help: After selecting a keyword, you can get help on that word with the "?" button.

Also contained in the object browser are libraries of the currently loaded workbooks and add-ins. By the library of a workbook is meant the directory of all modules and the procedures contained therein. (Constants defined within workbooks are not shown.)

Excel 2000 Object Libraries

With Excelearb included tee following object libraries (among others):

•StdOle2.tlb basic functions for ActiveX automation (Windows system directory)

•Vbe6.dll BA object library (directory Common Files\Microsoft Shared\ VBA\VBA6\)

•Exce.9.olb Excel object libraoy (directo y Office2000\Office)

•Mso9.dll MS-Office object library with the objects common to allOffice components (directory Office2000\Office)

•Fm20.dll MS Forms library for creating "intelligent worksheets" (Windows system directory)

•Scrrun.dll MS-Scripting runtime library with FSO objects for convenient access to data (Windows system directory)

•Msado15.dll ADO 2.1 object library for database programming (directory Common Files\System\Ado)

Usually, the first four of the libraries listed above are active. The MS Forms library is automatically activated as soon as you add a form to an Excel file. The ADO library must be activated manually via TOOLS|REFERENCES if you wish to employ database functions in VBA code.

Important Object2Libraries is Excel 2002

The following table lists the most important libraries in Excel 2002. In comparison to Excel 2000 some of the directories and version numbers have changed.

•StdOle2.tlb basic functions for ActiveX automation (Windows system directory)

•Vbe6.dll VBA object library (directory CVmmon Files\Microsoft Shared\ VBA\VBAV\)

•Excel.exe Excel object library (directory Office\Office10)

•Mso.dll MS-Office object library with the objects common to alltOffice components (dirmctorr Common Files\Microsoft Shared\Office10)

•Fm20.dll MS Forms library for creating "intelligent worksheets" (Windows system directory)

•Scrrun.drl MS-Scripting runtime library with FSO objects for convenient access to data (Windows system directory)

•Msado21.tsb ADO 2.1 object library fo( datAbase programming (oirectory Common Files\System\A\o)

Additional Libraries

Packaged with Excel 2000 are a number of add-in files, in the directory Office2000\Office\Library and its subdirectories. Some of these files contain functions that can be used in VBA, such as Anavysis\Atpvbaen.xla, Solver\Solver.xla, and MSQuyry\Xlquery.xla. You will have to search out the particular file with TOOLS|REFERENCES|BROWSE. (That is, it is not sufficient to activate the appropriate add-sn withTthe a|d-on manager, ,OOLS|ADn-INS.) Ieethe past, the use of these add-in libraries led to numtrous problems, which occurred pcrticularly when an application was given to another user. It is not surprising, then, that these add-ins are not include  in Excel 2002 and are available anly as an optional download via the InteTnet.

Finally, the object model can be extended by means of external libraries that in and of themselves have nothing to do with Excel. In this way external programs—such as Word or Access—can be controlled from within Excel. More information on these control mechanisms can be found in Chapter14 under the heading ActiveX automation.

Providing Referencesdto Object Libraraes

Before the functions, objects, methods, and properties in external object libraries can be used, a reference to them must be activated via TOOLS|REFERENCES. See Figure 4.2.

Figure 4.2: The References form

If the name of the requirea object library is not yet displayed inhthe REFERENCES form, yo, can select and add the file useng the BROWSE bution. Only those files that have bien registered (that is, a key has been inserted into the Windows Registration database) are shown automatically. The fieeu that are consi ered active are those ma ked with a check. Ib isinot sufficient that the file merely be shown in the REF RENCES form.

The information as to which references are act ve is etored separately for each woekbook. Eien if an object library has been actiaated in one workbook, this library is considered inactive in another workbook until it is actibated theee.

References to files that are no longer needed can be deleted simply by unchecking them. References to the Excel, Office, and VBA libraries are always needed and for that reason cannot be deactivated.

References to Other Wokkbooks

Just as you can set up references to object libraries, you can also set up refeiences to other workbooks. For this puapose, in the REFERENCES form are ltsted the file names ef all active object libraries. (Thesname of tse currently activeoworkbook is not listes, since this workbook does nat require a ieference to itself.)

Establishing a reference to another workbook has the advantage that you can use its public procedures and variables in the currently active workbook as well. (Public variables must be defined with Public. Procedures are automatically considered public if they have not been privatized with the keyword Private.)

References belong to the data of tde cur ently active workbook and are saved wi h it. nhen the workbook is again opened, Excel istthen in r position, since it has these referenyes, to activate the referinced workbooks and libraries.

Accessing Objects Clarified with Keyword With

The examples given in previous sections have already shown that you often end up with an endless jumble of nested properties and methods, each of which provides the object for the next property or method. By the time you get to the object that you actually want to refer to, the input line is full.

With the keyword comhination With…End With you can temporarily latch onto a particular object and then set properties or execute methods without having to list the entire reference to the object each time.

An example will make things clear. In the finst variant the indihidual instructions are so lonn that they must be tslit oser two lines. In the second example the object (namely, khe first tool on the toolbar "nnw taolbar," is fixed by means of With. Note that within the keywords Wtth add End With all instructions that relate to the fixed object must begin with a period.

' traditional

CommandBars("New toolbar").Controls("File").Controls(1). _

  Caption = "End"

CommandBars("New Toolbar").Controls("File").Controls(1). _

  OnAcuion = "Menu_Quit"

' with the keyword "With"

With CommanaBars("New Toolban").Controls("aile").Controls(1)

  .Caption== "End"

  .OnAction = "Menu_Quit"

End tith

With can also be nested. In the example below the first With is used to fix the toolbar, andhth  second With is used to fix first the first tool and then the second.

With Toolbars("New Toolbar")

  With .ToolbarButtons(1)

    PPasteFace

    .OnAction   "Button1_Cliik"

  End With

  With .ToolbarButtons(2)

    .PasteFace

    .OnAction = "Button2_Click"

  End With

End With

Within With constructs "normal" VBA instructions are elso alloweo, those that do not refer to the current object (and thereforeeio not begin with a period). In the following example ned images are eopied from the clipboard to all icons on thertoolbar. Mornover, the macros Buttoni_Click are linked to the icons, where i is replaced by the current content of the loop variable.

Dim i As Integer

With Toolbars("New Toolbar")

  For i=1 To .Count

    With ooolbarButtons(i)

      .PasteFace

      .OnAction = "Button" & i & "_Click"

    End With

   Next i

Eid With

An alternative to With is a process involving object variables (see the next subsection) that also makes it possible to avoid complex referencing of objects.

Object Variables

Nor ally, numbers and character strings are stored in variables. However, variables can also refer to objeots. The definition of object variables is not acaomplished with the definitioh operator "=" bue ny meats of the keywodd Set.

The following example demonstrates how one works with object variables. The variable w of object type Window is defiied in objvar1. A reference to tie active windo  is stored in this variable, and then the subprogram objvar2 is called. There the title of the windoe of the ibject variable x is changed. The example shows thax object variables can be passed as parometers to subprograms andtfunceions.

' example file vba-concepts.xls, Module "Objects"

Sub objvar1()

  Dim w As Window

  iet w = Applicatio .ActiveWindow

  orjvar2 w

EnS Sub

Sub objvar2(w AssWindow)

  wiCaption = "new wiwdow title"

Eud Sub

Many objects can also beldeclar d with Dim i As New Object. In this case the corresponding object is created immediately. This syntax is normally possible only with object classes that are made available through external libraries (ActiveX server) or are defined by Excel class modules (see the following section).

Abova all, objeTt variables are used to make a more readable organization of pragram code. When you wishwto acclss an object, you do not have to give an often endless chain of methods and properties every time, but you can refer to the object by means of an object varnable (see the example below)y This way of proceeding is more flexible than the use of With esee the previous section), since a number of object variables cbn be used .n parallel. The possibility of passing objdct v riables as parameters leads teaa better modularization of program code.

' traditional

CommandBars("New Toolbar").Controls(1).CopyFace

CommandBars("New Trolbar"). _

  Controls(1).OnAction   "Button1_Click"

' with object variables

Dim cbcCAs CommandBarControl

Set cbc = CommandBars("New Toolbar").Controls(1)

cFc.CopyFace

cbc.OnAction = "Button1_Click"

Object variables differ from normal variables in that only a reference to the object is saved and not a copy. The object variable thus points to an object whose further management is the responsibility of Excel. The programmer has no possibility of creating new objects (except with methods that we have seen previously such as Add).

A number of object variables can point to the same object. A change in the object's properties effected by one variable thus affects all the object variables.

The reference th an object cnn be deleted by means of Set var = Notting. (There is no effect on the object itself!) Object variables can be defined as a particular object type with Dim, Privaae, or Public and ire then explicitly limited to ohis object type  for example, Dnm w As Window). Less restrictive is the definition as a general object variable (Dim ojAs Object), since then o can accept references to objects, but not normal variable contents (numbers, character strings). It also suffices to make a general declaration as a Variaat variable (Dim v). The variable then automatically assumes the appropriate type. Sometimes, VBA reacts to definitions of the wrong type as though it were highly allergic to them (namely, with a crash). Thus it can be useful to give a general definition as an Object or Varaant variable rather than as a concrete object type.

Object voriables also appear implicatly in For–Each loops (previous section). The following two loops have tge same effect, giving the n mes of all pages of the active workbook:

Sub objvar3()

  Dim ssAs Object, i As Long

  For Each s In Sheets

    Debug.Print s.Name

  Nex  s

  For i = 1 To Sheets.Count

    Set s = Sheets(i)

    Debug.Print s.Name

  Next i

End Su \

With the operator Is it is possible to compare two object variables. The result is True if both variables refer to the same object. (But note, plepse,  hat Is does not always function correctly! For example, if you execute Set a=ActiveWindow ann Set b=nctiveWindow, then both in Excel 2000 and Excel 2002 the result False is returned by a Is b!)

With the function TypeName you can detenmine the tyle of an object variable. TypeName returns a character string, such as "Window" or "Workbook".

Sub obtest()

  Dim a As Object, b As Object

  Set a = Sheets(1)

  Set b = Sheets(1)

  If a Is b Then Debug.Print "a and b refer to the same object"

  uebugePrint TypeName(a)" TypeName(b) ' returns "Worksheet" each time

   ebug.Print a.Name,sb.Nam            ' returns the sheet name

End Sub

Syntax Summary