Objects and events

2.7. Objects and events

All modern programming languages (in particular, Visual Basic and VBA) are object- and event-oriented.

The object orientation is based on partitioning the subject area (for which we are developing a program) and clustering the parts.

The program’s part, which corresponds to the cluster, is named object.

The object’s characteristics are named properties.

Actions performed over the object are named methods.

Examples of the subject area are problems of modeling financial risks, semi-conductor devices and evolution of stars (p. 47), as well as Visual Basic and VBA themselves.

Examples of the object are the Visual Basic objects, which were considered in Section 1.23: UserForm, Label, TextBox, CommandButton and CheckBox.

The event orientation is based on the following concepts:

“event” — the object’s qualitative change, which follows from work of the user or computer;

“handler for event” — the command set, executed by the computer when the event occurs.

An example of the event is the click on the Account button in the user-defined form, for example, depicted in Fig. 1.28. Because of handling this event, the ComButCalc_Click program execution is started.

The VBA programming language is intended for creating programs in the Microsoft Office applications, such as word processor Word, tabular processor Excel, technical editor Visio, database management system Access, etc. VBA differs from Visual Basic in the presence of specific objects of Microsoft Office and of its applications.

We will be interested in the so-called Excel objects intended for Excel VBA Programming. Examples of such objects are Workbook, Worksheet, Range and ActiveCell.

Operators intended for work with an object make the following:

setting object properties;

returning object properties into the program;

applying object methods.

The syntax of setting object properties follows:

object.property = expression

where expression is an arithmetic or logical expression or string. The com-puter executes this operator as the normal assignment operator:

1) calculates the value of expression;

2) assigns this value to property object.property.

The syntax of returning object properties follows:

object.property

Property object.property may be a part of operators, in particular, it may be in the right-hand side of the assignment operator, i.e., the property is similar to built-in functions of Visual Basic. Often, object.property itself is an object.

Note that not all object properties can be returned and set. There are proper-ties, which can be only returned or set. To study possibilities of this or that object property, we must use the reference systems, which are started by pressing the F1 and F2 keys when the VB window is active (Sections 1.6 and 2.12).

The operator of applying object methods has the following form:

object.method

The operator of applying the Add method is the exception. It creates a new object, subobject, and adds it to object. The syntax of this operator follows:

[Set variable = ]object.Add

In this syntax, Set is the keyword, variable is a variable of the same data type as subobject.

Applying the Add method is similar to calling the MsgBox procedure of Visual Basic, which is both a subroutine and function.

An object hierarchy exists. The highest in the hierarchy of the application is the Application object, i.e., all other objects “are included” in it. The Application object reminds the Russian nested doll, but (unlike the nested doll) several objects may be included in each object.

The full object name is a sequence of the object names separated by a point, at that, this sequence begins with Application. For example,

Application.Workbooks("Archive").Worksheets("Cod"). _ Range("A1")

2.7. Objects and events

in VBA for Excel is the full name of the Range("A1") object or the reference to the A1 cell on the Cod worksheet of the Archive workbook.

The use of the full object name is not necessary. Often, we can use the incomplete name, i.e., without names of the objects activated at present. For example, if the Archive workbook is active, then the full name of the Range("A1") object may be shortened as follows:

Worksheets("Cod").Range("A1")

As before, this is the reference to the A1 cell on the Cod worksheet of the Archive workbook.

In the above VBA notations, strings are in the parentheses. These strings may be compound.

An example of using compound strings is the following operator:

Range("G" & CStr(CInt(Now - #1 Jan 2000#))).Select Because of its execution, the following cell is selected on the active Excel work-sheet: the intersection of the G column and the row whose number is equal to the number of days from the century beginning.

We advise the reader to do the following:

1) type the last operator above line EndSub of program Century_20 (p. 25);

2) execute the obtained program;

3) pay attention to the position of the Excel cell activated.

We see that the object construct is similar to the record (Section 1.18). There-fore, as the first approximation, the object can be considered as the built-in record whose creation operator (the Type operator) is hidden from the program developer.

In addition to properties and methods, some objects of VBA are also charac-terized by events. However, it does not relate to the main Excel objects, which will be considered below.