Microsoft VBA Excel Course

Excel VBA Intermediate

Unit 3 Understanding Objects

An object is an element of an application that can be accessed and manipulated using Visual Basic.  Examples of objects in Excel are worksheets, charts and ranges.

Defining Objects

Objects are defined by lists of Properties, and Methods.  Many also allow for custom sub-procedures to be executed in response to Events.

The term Class refers to the general structure of an object.  The class is a template that defines the elements that all objects within that class share.

Properties

Properties are the characteristics of an object.  The data values assigned to properties describe a specific instance of an object.

A new workbook in Excel is an instance of a Workbook object, created by you, based on the Workbook class.  Properties that define an instance of a Workbook object would include its name, path, password, etc.

Methods

Methods represent procedures that perform actions.

Printing a worksheet, saving a workbook selecting a range are all examples of actions that can be executed using a method.

Events

Many objects can recognize and respond to events.  For each event the object recognizes you can write a sub procedure that will execute when the specific event occurs.

A workbook recognizes the Open event.  Code inserted into the Open event procedure of the workbook will run whenever the workbook is opened.

Events may be initiated by users, other objects, or code statements.  Many objects are designed to respond to multiple events.

Examining the Excel Object Hierarchy

The Excel Object Module is a set of objects that Excel exposes to the development environment.  Many objects are contained within other objects.  This indicates a hierarchy or parent-child relationship between the objects.

The Application object represents the application itself.  All other objects are below it and accessible through it.  It is by referencing these objects, in code, that we are able to control Excel.

Objects, their properties and methods are referred to in code using the "dot" operator.

Application.ActiveWorkbook.SaveAs "Employees.xls"

Some objects in Excel are considered global. This means they are on top of the hierarchy and can be referenced directly.  The Workbook object is a child object of the Excel Application object.  But since the Workbook object is global you don’t need to specify the Application object when referring to it.

Therefore the following statements are equal:

Application.ActiveWorkbook.SaveAs "Employees.xls

ActiveWorkbook.SaveAs "Employees.xls"

Some objects in the Excel Object model represent a Collection of objects.  A collection is a set of objects of the same type.

The Workbooks collection in Excel represents a set of all open workbooks.  An item in the collection can be referenced using an index number or its name.

To view the entire Excel Object model:

• Open the Help window
• Select the Contents tab
• Expand Programming Information
• Expand Microsoft Excel Visual basic Reference
• Select Microsoft Excel Object Model.

The following illustration shows a portion of the Excel object hierarchy.  Most projects will only use a fraction of the available objects.

Defining Collections

A collection is a set of similar objects such as all open workbooks, all worksheets in a workbook or all charts in a workbook.

Many Excel collections have the following properties:

Some collections provide methods similar to the following:

Referencing Objects in a Collection

A large part of programming is referencing the desired object, and then manipulating the object by changing its properties or using its methods.  To reference an object you need to identify the collection in which it’s contained.

The following syntax references an object in a collection by using its position.  Since the Item property is the default property of a collection there is no need to include it in the syntax.

CollectionName(Object Index Number)

Workbooks.Item(1)

Workbooks(1)

Charts(IntCount)

The following syntax refers to an object by using the object name.  Again the Item property is not necessary:

CollectionName(ObjectName)

Workbooks("Employees")

Worksheets("Purchases By Month")

Sheets("Total Sales")

Charts("Profits 2006")

Using the Object Browser

The Object Browser is used to examine the hierarchy and contents of the various classes and modules.

The Object Browser is often the best tool to use when you are searching for information about an object such as:

• Does an object have a certain property, method or event
• What arguments are required by a given method
• Where does an object fit in the hierarchy

To access the Object Browser:

In the Visual Basic Editor, do one of the following:

• Open the View menu
• Select Object Browser            OR
•  Press F2              OR
•  Click  the Object Browser icon.

The Object Browser dialog box appears.

The following icons and terms are used in the Object Browser:

To search for an object in the Object Bowser:

• Type in the search criteria in the Search Text box
• Click

To close the Search pane:

• Click

Working with Properties

Most objects in Excel have an associated set of properties.  During execution, code can read property values and in some cases, change them as well.

The syntax to read an object’s property is as follows:

ObjectReference.PropertyName

ActiveWorkbook.Name

The syntax to change an object’s property is as follows:

ObjectReference.PropertyName = expression

ActiveWorkbook.Name = "Quarterly Sales 2006"

The With Statement

The With statement can be used to work with several properties or methods belonging to a single object without having to type the object reference on each line.

The With statement helps optimize the code because too many "dots" in the code slows down execution.

The syntax for the With statement is as follows:

With ObjectName

End With

With ActiveWorkbook
.PrintOut
.Save
.Close
End With

You can nest With statements if needed.

Make sure that the code does not jump out of the With block before the End With statement executes.  This can lead to unexpected results.

Working With Methods

Many Excel objects provide public Sub and Function procedures that are callable from outside the object using references in your VB code.  These procedures are called methods, a term that describes actions an object can perform.

Some methods require arguments that must be supplied when using the method.

The syntax to invoke an object method is as follows:

ObjectReference.method [argument]

Workbooks.Open "Sales 2006"

Range("A1:B20").Select

Selection.Clear

hen calling procedures or methods that have arguments you have two choices of how to list the argument values to be sent.

Values can be passed by listing them in the same order as the argument list.  This is known as a Positional Argument.

Alternatively you can pass values by naming each argument together with the value to pass.  This is known as a Named Argument.  When using this method it is not necessary to match the argument order or insert commas as placeholders in the list of optional arguments

The syntax for using named arguments is as follows:

Argumentname:= value

The example shows the PrintOut method and its syntax:

Sub PrintOut([From],[To],[Copies],[Preview],[ActivePrinter],[PrintToFile],[Collate],[PrToFilename])

The statements below show both ways of passing values when calling the PrintOut method.  The first passes by Position, the second by Naming:

Workbooks("Quarterly Sales 2006").PrintOut (1,2,2,  ,  ,  ,True)

Workbooks("Quarterly Sales 2006").PrintOut From:=1, To:=2, Copies:=2, Collate:=True

Event Procedures

An event procedure is a sub procedure created to run in response to an event associated with an object.  For example run a procedure when a workbook opens.

Event procedure names are created automatically.  They consist of the object, followed by an underscore and the event name.  These names cannot be changed.  Event procedures are stored in the class module associated with the object for which they are written.

The syntax of the Activate Event procedure is as follows:

Private Sub Worksheet_Activate()

Creating An Event Procedure

To create an Event Procedure:

• Display the code window for the appropriate class module
• Select the Object from the Object drop-down list
• Select the event from the Procedure drop-down list
• Enter the desired code in the Event Procedure