Basics of working with class modules

Macros are complete programs, they just run on top of another application. Therefore, all programming principles are followed. And all programming languages ​​are divided into two categories: procedural and object-oriented.

Macros are developed on the basis of the VBA language, which is more of a procedural language with the ability to access individual objects. The disadvantage of this approach is that one procedure is not closely related to another. 

The object-oriented approach has some fundamental differences from the procedural one. Now this type of programming is the most popular. Therefore, it needs to be considered in more detail in relation to Excel macros.

General information and definitions

All subroutines that are in object-oriented programming are closely related. The main concept that you need to know at this stage is the class.

A class is a data type that describes the structure of an object. All programs written in object-oriented languages ​​are based on objects. In the case of Excel, these are forms, worksheets, books, charts, and others. A class can combine a whole bunch of objects, each of which is a kind of copy of it with some modifications.

In simpler terms, a class is a template from which an object is generated.

Class structure:

1. Field. This is a class element that directly contains data.
2. Property. This is an integral part of the class, which also stores data that can be further processed.
3. Method. Essentially the same procedure or function, just within a class. Methods are one of the main concepts of any object-oriented programming language. 
4. Event. This is the situation in which the method is activated. This could be changing the contents of the cells, or executing another method. 

VBA is not a complete object-oriented programming language because it has only two principles:

1. Abstraction. With this method, you can create an object that most resembles a non-computer one. For example, you can create a “dog” object, which will have properties such as “number of ears”, “length of hair” and others. 
2. Encapsulation. It makes it possible to hide a class from other procedures that do not use it in their work. But this principle allows the use of individual properties of the class by other procedures, if necessary. 

How to create a class in VBA

Let’s move on to the practical aspects of using classes in the VBA programming language. To create a class, in the editor you need to click on the “Class Module” item, which can be found in the “Insert” menu. Next, you need to give the class a specific name. This can be done in only one way – to change the name generated automatically.

VBA provides the ability to hide a class from another workbook if it refers to this one. The Instancing property is responsible for this feature. If its value is set to Private, then this class will only be discovered by the workbook in which the macro is opened. If such a macro is opened on several documents, then the class will be available only to the book to which it is linked. If you set the PublicNotCreateable parameter, then the class will be discovered if it is created in the book, but still will not be available to other books. 

Ways to instantiate a class

So, a class is a description of an object, but not the object itself. To create it based on a class, you must use one of the available methods. 

Method # 1

Here is a line of code that demonstrates the first way to create an object from an existing class.

Private Sub TestClass()  

Dim cl As ExampleClass   

Set cl = New ExampleClass End Sub 

Method # 2

Dim cl As ExampleClass   

Private Sub TestClass()   

Set cl = New ExampleClass End Sub 

In this case, the class declaration is carried out outside the procedure, so it is possible to work with it from all procedures inside the module. If you replace the Dim statement with Public, then the object will be available to any procedure that runs within the project, provided that the declaration is carried out outside the object module.

Method # 3

Dim cl WithEvents As ExampleClass   

Private Sub TestClass()   

Set cl = New ExampleClass End Sub

In this example, the object is created with events, and only in connection with them do they become available. This method has a limited scope, it can only be used within an object module (for example, a sheet or a book).

Method # 4

Private Sub TestClass()   

Dim cl As New ExampleClass   

End Sub

This method is not the most popular, and many do not know about it at all. In this case, the object is created through the cl variable. This method is somewhat worse, because it is better to allocate memory for a certain variable first, and only then create an object that will be embedded in this variable.

How to delete an instance of a class?

It’s a good habit to clear the allocated memory if the object is no longer needed. This will greatly increase the performance of the macro. This is especially important if it will be used on a weak computer. 

To do this, you need to use the following line of code.

Set cl = Nothing

Of course, modern versions of office suites (as well as computers) are designed in such a way that if you ignore this recommendation and do not delete the class, then nothing bad will happen. However, it is strongly recommended to do this in case you have to run a macro on a weak computer (especially now that a class of devices called smartbooks has begun to gain popularity).

How to create class fields

The class we created earlier does not contain anything, neither fields, nor properties, nor other elements described above. Therefore, it is of no use. So let’s start by creating the class fields. These are variables that are used to store data.

There are two types of fields: open and closed. The main characteristic of the latter is that they are inaccessible to other procedures that are not inside this module. Any module can have access to open ones, and in this aspect they do not differ fundamentally from properties.

But they are different – it is impossible to specify a value that will be displayed in the field by default, and it is also impossible to prohibit editing it using VBA. The information in the field is stored until it is overwritten or until the corresponding variable exists. 

To create a field, you need to execute the following code.

Dim sBody As String 

You can also use this variant of the code, which produces the same result.

Private sBody As String 

Both described cases allow you to create a field of a closed type. To create an open field, you need to write the following line.

Public Head As String   

After these manipulations, it becomes possible to use the Head property of the object. You can perform various operations with it, including writing:

cl.Head = «FHead»   

and reading

Debug.Print cl.Head  

The data type for a field is strictly defined. Therefore, you cannot change it to a numeric or floating point number format. 

The process of defining class properties

A property also means an element through which you can write and read object information, but the difference is that these are functions, not variables. 

The Property Get function is used to read data, and the Property Let function is used to write data. Of course, the language also provides for a third construction – Property Set, but in practice it is rarely used due to the fact that a reference to another object (namely, this function is needed) can also be written using the Property Let procedure.

Let’s describe in more detail the syntax of all the described functions. So here is a screenshot describing the syntax of the Property Get function.

And here is a table that describes in detail the elements of the properties of the class.

When reading a class property, you need to consider the following nuances:

1. The Exit Property element exits the method. It is allowed to use several similar elements at once within one method. This can be useful, for example, if loops and conditional statements are used. 
2. Although the Property Get function itself can contain an unlimited number of procedures inside, it cannot itself be a component of other functions.

Now let’s take a closer look at the syntax of the Property Let function. It is visible in this screenshot.

This function is affected by all the remarks discussed above.

And finally, what the syntax of the Property Set function looks like. Despite the fact that it is considered useless, you need to know how to work with it, if only because the other person can use it. And you need to be able to read someone else’s code. The syntax itself is shown in this screenshot.

An example of creating a property in a class module

Now let’s take a closer look at some practical examples. We will give code fragments with brief explanations.

In this case, we can use the Height property to both read and edit the information that is listed there. In simple words, we actually have an open field, because it has all its characteristics. It is simply formed by a slightly different method – through a function, and not a variable with a procedure. 

Now let’s take a look at another piece of code.

In this case, we can’t make any changes to the property because it’s read-only. By default, the idea is taken that the value of the sHeight variable is already specified in another procedure that exists in this module.

In this example, the property cannot be edited and contains a constant value (constant). It can only be opened from the module that is in this object. If we try to use the construction Public Const sHeight As Single = 2, then we can’t do anything.

There are many more examples, but they are already at a deeper level and are not suitable for beginners. Therefore, let’s now consider the features of creating methods and class events. 

Class Methods

Within a class, you can create both procedures and functions. All of them are collectively called methods, but only under one condition. For a function to be considered a method, it must be public to other classes. If such rights are not required for subprograms, then it is necessary to make them private. This is a mandatory rule that follows the principle of encapsulation. 

In general, the procedure for creating methods is no different from how procedures and functions are created. They can contain any arguments, both those that must be specified, and those that make no sense. However, the challenge is different. Take a closer look at the following example.

Class events

Each class already contains several events:

1. Class_Initialize. An event that is fired when the class is created. Here you can easily set the values ​​of variables and properties, which will always be used, unless otherwise specified. 
2. Class_Terminate. This event occurs when an object is deleted. This happens in two cases. The first is object deinitialization. The second is the termination of the procedure in which the class was declared.

As a rule, there are no problems in order to add new events. However, there is one requirement. It is important to make sure that the object to which the event is bound is only declared within the same module at the same level. 

The event itself is specified within the module itself. 

The syntax of the construction with which the event is declared is as follows.

Conclusions

Of course, this is not an easy topic to understand. But these are just two principles of object-oriented programming. In the case of other languages ​​it is even more difficult. VBA is generally considered a very easy language. Therefore, if you apply due perseverance, everything should work out. Good luck.