Creating an Instance of a Class with Excel VBA

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.