4.3 Objects


4.3 Objects

Working with 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), PageSetup (page setup for printing), Workbook (Excel workbook), Window (an Excel window), Worksheet (Excel worksheet), Range (a range of cells in a worksheet), Chart (Excel chart), 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 about 150 objects altogether defined in Excel (to this are added still more objects from other libraries; but more on that later). These objects are organized hierarchically. At the highest level is the Application object. For the properties of this object one can access loaded workbooks ( Workbooks ), and for their properties the individual sheets ( Chart and Worksheet ) of the workbook. Chapter 15 contains a reference to all objects 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 ' changes 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 VIEWFULL SCREEN. Excel thus reacts immediately to changes in properties.

Methods

While properties are most nearly analogous to variables, methods are closer to procedures. With methods you can execute instructions, for example save the current workbook under a new name, delete a chart, or create a new toolbar. Methods make possible the accessing of other objects. For example, Sheets(n) or Sheets("sheetname") returns a particular sheet of the workbook.

There exist two types of methods: those that correspond to a subprogram and do not have a return value ( Select , Activate , Delete , etc.), and those that correspond to functions and return a concrete result. Many methods can be used either with or without a return value, such as the Add method.

Enumeration Methods and Objects

A special role is played by the so-called enumeration methods, which end in the plural "s" (such as Sheets , 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 Window with respect to Application ). 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 ( Sheets("name") ) or to the n th 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("name") .

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 are some properties and methods that have default objects associated with them. If these properties or methods are used without giving the object, then they automatically assume that the default object is meant. The property ActiveSheet refers automatically to the default object Application . This property can also refer to a window or to a workbook. In that case the object must be given.

Note  

Application is the default object in all normal modules, but not in class modules! There the object described by the module is the default object. In the class modules such as "ThisWorkbook" or "Table1" the default objects are thus Workbook and, respectively, Worksheets( ) .

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 the above example Value is the default property of Application . (In this case Value returns a character string with 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 between methods and properties we have the following rule of thumb: Keyword with parameter means method; keyword without parameter means property. Enumeration objects such as Sheets represent an exception, since they can be used both with and without 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 sheet to the current workbook. This example demonstrates several aspects of dealing with objects: the interplay of objects, methods, and properties; the use of named parameters; the use of predefined constants:

 Application.ActiveWorkbook.Sheets.Add Type:=xlChart 

Application gives the root object (the application program Excel). ActiveWorkbook is a property of the object Application and refers to a Workbook object. Sheets is a method (even if it looks like a property) that refers to a Sheets object. Add is again a method of Sheets and makes possible the addition of new worksheets. Add recognizes four named parameters, all of which are optional. When no parameter is given, the method generates a new worksheet, which is appended in front of the currently active sheet. By means of the four parameters one can determine the insertion point, the type of sheet (such as chart, table, module, macro template), and the number of sheets to be added. In the example above only the type was specified, and in that case with the predefined constant xlChart .

The property ActiveSheet and the method Sheets 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.ActiveWorkbook . In the three following instructions this input simplification will be taken into account.

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 property.

 Sheets.Add Type:=xlChart ActiveSheet.Name = "My Chart" 

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, but as a function. For this reason the parameter now appears in parentheses. The result of the method (namely a reference to the new Chart object) will be further modified with the property Name .

Example 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 Chapter 10, which is devoted to charts.)

When you work with such complex methods as ChartWizard , 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 Select 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" Sub  CreateChart  ()   Sheets("Tab1").Activate   ActiveSheet.ChartObjects.Add(184.5, 110.25, 187.5, 69.75).Select   ActiveChart.ChartWizard Source:=Range("B3:B7"), _     Gallery:=xlColumn, Format:=6, PlotBy:=xlColumns, _     CategoryLabels:=0, SeriesLabels:=0, _     HasLegend:=1 End Sub 

Access to Active 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 current sheet of the active 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 . This property refers to the workbook in which the VBA code that is currently being executed 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 refer to the related object. In a worksheet module Me refers to a Worksheet object, in a form module to a UserForm object, and so on.

PROPERTIES FOR ACCESSING ACTIVE OBJECTS

ActiveCell

active cell in a worksheet

ActiveChart

active chart in a worksheet/window/workbook/Excel

ActiveMenuBar

active (currently visible) menu bar in Excel

ActivePane

active pane of a window

ActivePrinter

selected printer in Excel

ActiveSheet

active sheet in a window/workbook/Excel

ActiveWorkbook

active workbook in Excel

SelectedSheets

selected sheets of a window

Selection

selected objects in a sheet/window/workbook/Excel; the property can refer to quite different objects depending on the selection; most frequently, Selection is used to access the selected cells of a worksheet

ThisWorkbook

workbook whose code is currently being executed

Me

the object belonging to the module (for example, Worksheet , UserForm )

Properties/Methods Whose Name Is Given as a Character String

With the VBA method CallByName (available since Excel 2000) you can call methods and properties whose name is taken from a character string. In rare cases this offers additional flexibility in programming, since the name can be determined at runtime. If more than one parameter must be passed, then a Variant field can be used. CallByName cannot be used for normal procedures. (For those, you can use the method Application.Run .)

The following lines show the code for a custom class class1 . (Detailed information on the definition of custom classes can be found in the following section.)

 ' Class Module class1 Public Function  testmethod  (x As Variant) As Variant   testmethod = 2 * x End Function 

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

 ' Module module1 Sub  testCallByName  ()   Dim result As Variant   Dim obj As New class1   result = CallByName(obj, "testmethod", VbMethod, 3)   MsgBox result End Sub 

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 VIEWOBJECT BROWSER or, more conveniently, with F2 . This form has already been described in Chapter 3.

click to expand
Figure 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 instructions and functions that belong to the VBA programming language except for those that concern special features of Excel. Among other things, in the VBA library you will find commands for working with character strings, for managing files, and for working with dates and times. The VBA library is, unfortunately , incomplete. For instance, all the keywords for procedural programming are lacking. (This restriction is perhaps determined by the system; that is, it may be that this type of keyword cannot be integrated into the browser. It would, of course, be nice if the documentation gave some indication of this situation, but that, alas, is not the case.) Also, there are none of the normal functions that are easy to forget (such as 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 Excel are included the 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\)

  • Excel9.olb Excel object library (directory 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 TOOLSREFERENCES if you wish to employ database functions in VBA code.

Pointer  

The VBA and Excel libraries are so extensive that their description has been distributed throughout the entire book. Thus most of the objects of the Scripting library are described in Chapter 5, those of the MS Forms library in Chapter 7, those of the Office library in Chapter 8, and those of the ADO library in Chapter 12.

Important Object Libraries in 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 Common Files\Microsoft Shared\ VBA\VBA6\ )

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

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

  • 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)

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

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 Analysis\Atpvbaen.xla , Solver\Solver.xla , and MSQuery\Xlquery.xla . You will have to search out the particular file with TOOLSREFERENCESBROWSE. (That is, it is not sufficient to activate the appropriate add-in with the add-in manager, TOOLSADD-INS.) In the past, the use of these add-in libraries led to numerous problems, which occurred particularly when an application was given to another user. It is not surprising, then, that these add-ins are not included in Excel 2002 and are available only as an optional download via the Internet.

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 References to Object Libraries

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

click to expand
Figure 4.2: The References form

If the name of the required object library is not yet displayed in the REFERENCES form, you can select and add the file using the BROWSE button. Only those files that have been registered (that is, a key has been inserted into the Windows Registration database) are shown automatically. The files that are considered active are those marked with a check. It is not sufficient that the file merely be shown in the REFERENCES form.

The information as to which references are active is stored separately for each workbook. Even if an object library has been activated in one workbook, this library is considered inactive in another workbook until it is activated there.

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 Workbooks

Just as you can set up references to object libraries, you can also set up references to other workbooks. For this purpose, in the REFERENCES form are listed the file names of all active object libraries. (The name of the currently active workbook is not listed, since this workbook does not require a reference 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 the currently active workbook and are saved with it. When the workbook is again opened, Excel is then in a position, since it has these references, to activate the referenced 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 combination 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 first variant the individual instructions are so long that they must be split over two lines. In the second example the object (namely, the first tool on the toolbar "new toolbar," is fixed by means of With . Note that within the keywords With and 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). _   OnAction = "Menu_Quit" ' with the keyword "With" With CommandBars("New Toolbar").Controls("File").Controls(1)   .Caption = "End"   .OnAction = "Menu_Quit" End With 

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

 With Toolbars("New Toolbar")   With .ToolbarButtons(1)     .PasteFace     .OnAction = "Button1_Click"   End With   With .ToolbarButtons(2)     .PasteFace     .OnAction = "Button2_Click"   End With End With 

Within With constructs "normal" VBA instructions are also allowed, those that do not refer to the current object (and therefore do not begin with a period). In the following example new images are copied from the clipboard to all icons on the toolbar. Moreover, 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 ToolbarButtons(i)       .PasteFace       .OnAction = "Button" & i & "_Click"     End With    Next i End 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

Normally, numbers and character strings are stored in variables. However, variables can also refer to objects. The definition of object variables is not accomplished with the definition operator "=" but by means of the keyword Set .

The following example demonstrates how one works with object variables. The variable w of object type Window is defined in objvar1 . A reference to the active window is stored in this variable, and then the subprogram objvar2 is called. There the title of the window of the object variable x is changed. The example shows that object variables can be passed as parameters to subprograms and functions.

 ' example file vba-concepts.xls, Module "Objects" Sub  objvar1  ()   Dim w As Window   Set w = Application.ActiveWindow   objvar2 w End Sub Sub  objvar2  (w As Window)   w.Caption = "new window title" End Sub 

Many objects can also be declared with Dim x 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).

Above all, object variables are used to make a more readable organization of program code. When you wish to access 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 variable (see the example below). This way of proceeding is more flexible than the use of With (see the previous section), since a number of object variables can be used in parallel. The possibility of passing object variables as parameters leads to a better modularization of program code.

 ' traditional CommandBars("New Toolbar").Controls(1).CopyFace CommandBars("New Toolbar"). _   Controls(1).OnAction = "Button1_Click" ' with object variables Dim cbc As CommandBarControl Set cbc = CommandBars("New Toolbar").Controls(1) cbc.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 to an object can be deleted by means of Set var = Nothing . (There is no effect on the object itself!) Object variables can be defined as a particular object type with Dim , Private , or Public and are then explicitly limited to this object type (for example, Dim w As Window ). Less restrictive is the definition as a general object variable ( Dim o As 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 Variant 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 Variant variable rather than as a concrete object type.

Object variables also appear implicitly in For “Each loops (previous section). The following two loops have the same effect, giving the names of all pages of the active workbook:

 Sub  objvar3  ()   Dim s As Object, i As Long   For Each s In Sheets     Debug.Print s.Name   Next s   For i = 1 To Sheets.Count     Set s = Sheets(i)     Debug.Print s.Name   Next i End Sub\ 
Note  

There is no Sheet object in the Excel library. The above enumeration of Sheets can refer equally well to objects of type WorkSheet and Chart . Thus in the example, s must be defined with the generally valid Object .

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, please , that Is does not always function correctly! For example, if you execute Set a=ActiveWindow and Set b=ActiveWindow , then both in Excel 2000 and Excel 2002 the result False is returned by a Is b !)

With the function TypeName you can determine the type 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"   Debug.Print TypeName(a), TypeName(b) ' returns "Worksheet" each time   Debug.Print a.Name, b.Name           ' returns the sheet name End Sub 

Syntax Summary

METHODS AND PROPERTIES

 

result = object.property

read property

object. property =

change property

object.Method para1, para2

method without return value

erg = object.Method(para1, para2)

method with return value

obj.Method(para1, para2).Method

immediate further processing with another method

ACCESSING ACTIVE OBJECTS

 

ActiveCell

active cell in a worksheet

ActiveChart

active chart

ActiveMenuBar

active (viewable) menu bar

ActivePane

active pane of a window

ActivePrinter

selected printer

ActiveSheet

active sheet in window/workbook/Excel

ActiveWorkbook

active workbook

SelectedSheets

selected sheets of a window

Selection

selected objects in sheet/window/workbook

ThisWorkbook

workbook whose code is currently being executed

ENUMERATION METHODS AND OBJECTS

 

Objects

the plural refers to an enumeration; for example, Axes , Sheets , Windows

Objects(n)

refers to the n th object

Objects("name")

refers to the object named

Objects.Count

gives the number of objects

Objects.Add obj

adds a new object to the list

obj.Delete

deletes the object from the list

ACCESSING OBJECTS VIA WITH

 

With object

fixes the object

   .property =

the period refers to the fixed object

   .methods para1, para2

 

End With

OBJECT VARIABLES

 

Dim var As objecttype

placeholder for objects

Dim var As New objecttyp

generate an object immediately

Set var = object

var refers to the given object

Set var = Nothing

deletes the reference (not the object)

name = TypeName(var)

determines the name of the object




Definitive Guide to Excel VBA
Linux Application Development For The Enterprise (Charles River Media Programming)
ISBN: 1584502533
EAN: 2147483647
Year: 2002
Pages: 134

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net