Catégorie : Excel VBA Course

The Collection object allows you to dynamically group a family of objects, identified by index.

The Collection object has only one property:

• Count — returns the number of elements in the collection.

It also has three methods, described in Table.

TableMethods of the Collection Object

Method	Description
Add	Adds a new element to the collection.
Add item [, key, before, after]	Here:

• item — required parameter, specifies the element to be added.
• key — optional parameter, identifies the element (can be used instead of its index).
• before — optional parameter, specifies the element before which the new element is inserted.
• after — optional parameter, specifies the element after which the new element is inserted. |
  | Item | Returns the specified element of the collection. |
  | Item(index) | index is the element’s index in the collection or its identifier defined by the key parameter in the Add method. Numbering in a Collection begins with 1. |
  | Remove | Removes an element from the collection. |
  | Remove index | index is the element’s index in the collection or its identifier defined by the key parameter in the Add method. |

Up to this point, the objects we created neither received messages from other objects nor sent them, which may suggest that in VBA programs are simply a sequence of procedures executed one after another. This is not the case.

Often, the moment when a procedure is executed in a program is determined by an external factor — an event. For example, clicking a button, selecting a radio option, minimizing a form, etc.

Thus, an event serves as a signal in an application, informing that something important has happened in the system and requires additional attention.

An event is declared within a class using the keyword Event. The declaration must include the event identifier and its list of parameters.

For example, the following event — MoneyWithdrawn — which may be generated when money is withdrawn from an account, has two parameters: Money and Cancel. The first returns the withdrawn amount, while the second determines whether the operation should be canceled:

Public Event MoneyWithdrawn(ByVal Money As Long, ByRef Cancel As Boolean)

Events themselves cannot return data. Data is returned through the event’s parameters.

Declaring an event only indicates that it can be generated. The actual generation is performed by the RaiseEvent statement. For example, the following statement generates the MoneyWithdrawn event when the code reaches it:

RaiseEvent MoneyWithdrawn(Money, Cancel)

When declaring a variable that will reference an instance of the class capable of generating events, you must additionally include the keyword WithEvents.

For example:

Private WithEvents sc As SumListener

To respond to an event, it must be associated with a special procedure called an event handler.

This association is made using the following template:

Sub Object_Event(argumentList)
    ...
End Sub

In the example below, the handler procedure is:

Sub sc_SumDone(ByVal s As Double)
    Debug.Print CStr("Sum done " & s)
End Sub

Example: Class with an Event

Consider a simple example.

The class SumListener has two public fields a and b, and one private field s.

• The method Sum() adds the values of a and b and places the result in s. After the sum is computed, it generates the event SumDone(ByVal s As Double), whose parameter returns the result of the addition.
• The method GetRes() returns the computed sum.

Thus, in the procedure DemoEvent, the result is displayed in the Immediate window first via the SumDone event handler triggered by the Sum() method, and then again explicitly via the GetRes() method.

Class with an event. Class module SumListener

Public a As Double
Public b As Double
Private s As Double

Public Event SumDone(ByVal s As Double)
Public Sub Sum()
    s = a + b
    RaiseEvent SumDone(s)
End Sub

Public Function GetRes() As Double
    GetRes = s
End Function

Class with an event. Worksheet or ThisWorkbook module

Private WithEvents sc As SumListener

Sub DemoEvent()
    Set sc = New SumListener
    sc.a = 1 : sc.b = 3
    sc.Sum
    Debug.Print sc.GetRes()
End Sub

Sub sc_SumDone(ByVal s As Double)
    Debug.Print CStr("Sum done " & s)
End Sub

VBA allows you to create both read-only properties (i.e., those that can only retrieve data) and write-only properties (i.e., those that can only assign values).

When declaring a read-only property, there is no need to define a Property Let or Property Set procedure. When declaring a write-only property, the Property Get block is omitted.

In the following example, a class Point is created.

• The properties GetX and GetY are read-only properties that allow reading the values of the fields.
• The properties SetX and SetY are write-only properties intended for assigning values to the fields.

Additionally, the class defines a ToString() method for outputting general information about the class instance as a string.

« Read-only » and « Write-only » properties. Class module Point

Private x As Integer
Private y As Integer

Public Property Get GetX() As String
    GetX = x
End Property

Public Property Let SetX(ByRef valX As String)
    x = valX
End Property

Public Property Get GetY() As String
    GetY = y
End Property

Public Property Let SetY(ByRef valY As String)
    y = valY
End Property

Public Function ToString() As String
    ToString = "(" & x & "," & y & ")"
End Function

« Read-only » and « Write-only » properties. Standard module

Sub DemoOnlyProperties()
    Dim p As New Point
    p.SetX = 1 : p.SetY = 4
    Debug.Print p.ToString
    Debug.Print p.GetX & vbTab & p.GetY
End Sub

Many classes have public fields, which programmers can access directly. However, in most cases, this approach is not very convenient. A more suitable approach is to use private fields, i.e., fields that are inaccessible outside the class.

The use of private fields allows data to be protected from external interference. Therefore, the process of getting and setting values for the current state of an object is preferably implemented through properties or special methods.

To describe a property, you use special procedures:

• Property Let — declares property names whose values are non-object data types.
• Property Set — declares property names whose values are objects.
• Property Get — provides the ability to read property values.

In the following code, a class Employee is created, encapsulating information about a company employee.

This class has three private fields: fname, lname, and pos, which describe the first name, last name, and position of the employee.

To set and retrieve values for these fields, three properties are defined: FirstName, LastName, and Position.

The class also defines the ToDebug() method, which outputs combined information about the class instance into the Immediate window.

Properties. Class module Employee

Private fname As String
Private lname As String
Private pos As String

Property Get FirstName() As String
    FirstName = fname
End Property

Property Let FirstName(ByRef newfname As String)
    fname = newfname
End Property

Property Get LastName() As String
    LastName = lname
End Property

Property Let LastName(ByRef newlname As String)
    lname = newlname
End Property

Property Get Position() As String
    Position = pos
End Property

Property Let Position(ByRef newpos As String)
    pos = newpos
End Property

Public Sub ToDebug()
    Debug.Print "First Name: " & fname & vbCr & _
                "Last Name: " & lname & vbCr & _
                "Position: " & pos
End Sub

Properties. Standard module

Sub DemoEmployee()
    Dim emp1 As New Employee
    emp1.FirstName = "James"
    emp1.LastName = "Bond"
    emp1.Position = "Secret agent 007"
    emp1.ToDebug
    Dim emp2 As New Employee
    emp2.FirstName = "Jack"
    emp2.LastName = "Sparrow"
    emp2.Position = "Pirate"
    emp2.ToDebug
End Sub

Class methods usually contain code that analyzes the state of an object and modifies it. For example, classes often have certain functions that are not limited to simply reading or assigning values to some parameters, but require specific computations.

In such cases, methods come into play. Essentially, methods are procedures. If they return or assign values, it is often more convenient to implement them as functions.

A method call is an operation performed on an object by referencing it, followed by a dot, then the method name, and finally a list of actual parameters in parentheses:

Object.Method(parameterList)

In the code , the functionality of the Point class is extended by adding three methods:

• Move() moves the point in the direction specified by another point.
• Length() returns the vector length, i.e., the distance from the origin to the point.
• ToString() accumulates information about the class instance into a string.

The first of these methods is implemented as a Sub procedure, while the second and third are implemented as functions.

Methods. Class module Point

Public x As Integer
Public y As Integer

Public Sub Move(ByVal pt As Point)
    x = x + pt.x
    y = y + pt.y
End Sub

Public Function Length() As Double
    Length = Sqr(x ^ 2 + y ^ 2)
End Function

Public Function ToString() As String
    ToString = "(" & x & "," & y & ")"
End Function

Methods. Standard module

Sub TestPointWithMethods()
    Dim p1 As New Point
    p1.x = 1 : p1.y = 1
    Debug.Print p1.ToString
    Debug.Print p1.Length
    Dim p2 As New Point
    p2.x = 2 : p2.y = 2
    Debug.Print p2.ToString
    p1.Move p2
    Debug.Print p1.ToString
    Debug.Print p1.Length
End Sub

To use a created class, you need to be able to obtain an instance of this class. An instance is an object of that class type.

For any created class, you can obtain instances in the same way as for any other data type. However, when declaring an object, you must specify the keyword New.

After creating an instance of a class, you can read or set the values of its fields and properties, and apply its methods.

For example, in the following code, an instance of the Point class is created, and values for its fields x and y are set.

In addition, a variable can first be declared as an object (in this case of type Point), and then assigned a value using the Set statement.

In the Immediate window, you can see the created instance of the Point class, as well as the value of the object variable.

Creating an instance of a class. Standard module

Sub TestPoint()
    Dim p1 As New Point
    p1.x = 2 : p1.y = 3
    Dim p2 As Point
    Set p2 = p1
    Debug.Print p1.x & vbTab & p1.y
    Debug.Print p2.x & vbTab & p2.y
End Sub

Initializing Field Values

The initial values of class instance fields can be set depending on the business logic of the project. To do this, simply assign the required values to the fields in the Initialize event procedure of the class module.

For example, the following modification of the Point class (Listing 2.37a and b) ensures that all created instances of this class are points (1, 1) instead of (0, 0), as before.

Class module Point

Public x As Integer
Public y As Integer
Private Sub Class_Initialize()
    x = 1 : y = 1
End Sub

Creating an instance of a class. Standard module

Sub TestInitPoint()
    Dim p As New Point
    Debug.Print p.x & vbTab & p.y
End Sub

The Me Keyword

The keyword Me returns the current instance of the class, through which you can access its fields.

Therefore, the code for initializing the field values of the Point class can be written in the following equivalent way (Listing 2.38).

Class Point using the Me keyword

Public x As Integer
Public y As Integer
Private Sub Class_Initialize()
    Me.x = 1
    Me.y = 1
End Sub

The Nothing Keyword and Removing an Object from Memory

The keyword Nothing is used to remove a reference from an object variable. For example:

Dim p As New Point
p.x = 1
Set p = Nothing

If no other variables reference the object, Windows deletes it from memory.

Classes are constructed in class modules, which are created by choosing the command Insert | Class Module in the VBA editor. When creating classes, you need to provide for its initialization, and describe the properties and methods with which the object will be endowed.

The process of creating a class can be described as the following sequence of steps:

1. Select the command Insert | Class Module. A new class module window will open.
2. Press the key, and in the Properties window set the Name property to the name of the class. The name of the class module must match the name of the class.
3. A class is initialized using the optional procedure Class_Initialize. In this procedure, you can specify the values assigned to fields when an instance of the class is constructed.
4. It is also possible to declare the Class_Terminate procedure to describe the process of removing the object from memory when work with it is completed.

As an example, let us create a class Point, which models a point in a plane.

In this class there are only two fields: x and y, which define the coordinates of the point. The Public access modifier specifies that the fields are available to all external code and can be read and modified by any of them.

Class module Point

Public x As Integer   ' Field x
Public y As Integer   ' Field y

In VBA, along with a large number of built-in objects (Application, Workbook, Worksheet, Range, etc.), there is also the possibility of creating user-defined objects.

The use of user-defined objects allows programs to be shorter, more transparent, and easier to understand. User-defined objects in VBA have properties, fields, methods, and can respond to events. Unfortunately, however, they do not support the inheritance mechanism typical of fully object-oriented languages.

Objects are instances (representatives) of classes. A method that an object executes in response to a message is determined by the class to which the receiving object belongs. All objects of the same class use the same methods in response to the same messages.

The members of a class are fields, properties, methods, and events. Let us consider them in more detail:

• Field — a variable that holds a certain value. Thus, a field provides information about the object.
  Example: if there is an object Car, it may have a field Cylinders that stores the number of cylinders in the car’s engine.
• Method — code that programs an action the object must perform.
  Example: the object Car could be repainted using the method Repaint.
• Property — serves the same role as a field (providing information about the object), but is created using special Property procedures that offer more flexibility in both assigning and retrieving values. A property allows data to be separated from the object, making them more robust.
• Event — allows objects to notify one another about a change in circumstances. Events are often used in graphical interfaces to notify, for example, when a user clicks a button. But they are also suitable for other kinds of notifications, such as receiving an email message.

Another interesting example of recursive procedures is the construction of fractals.

Fractals consist of a sequence of geometric objects, where the initial object is drawn on a large scale. Subsequently, smaller copies of this object are added to it. These copies may have the same orientation as the original object or a modified orientation.

Of course, in VBA (unlike in full Visual Basic), there are no direct tools for drawing graphics on a form. However, thanks to the Shapes object family, Excel provides abundant possibilities for creating graphics directly on a worksheet.

In the following example, we will place a geometric pattern on a worksheet using fractals.

This fractal has a simple structure:

• First, a large square is drawn.
• Then, smaller copies of this square are attached to its corners.
• This process continues until the added squares become small enough.

To view the entire picture on the worksheet, reduce the zoom level using the Zoom slider located in the lower-right corner of the Microsoft Excel window.

Let us give another example of fractal construction. In this case, the fractals are based on regular polygons. In particular, shows the result of a program that builds fractals from five-pointed stars.

This program has one notable feature: the use of a user-defined type for working with points on the plane. In this case, the user-defined type makes the code more transparent and easier to read.