Using Objects and Classes in VBA development

by Philipp Stiefel, originally published August 7th, 2016

Photo by Wokandapix used here under CC0 licensing

If you wrote a couple of VBA procedures already, you’ll most certainly have used objects in your VBA code. But are you really aware of the concept behind objects and classes?

Unless you have previous knowledge in another programming language or are really advanced in VBA, probably not. – And this is something you should change to advance your programming skills. Start today by reading on…

What are objects and classes?

Objects and classes are very closely linked to each other. First, I will explain some of the inner workings of objects you have probably used already. Then it will be easier to grasp the purposes of the classes behind the objects.

Objects

There are lots of objects readily available for use in VBA via the build in Object Libraries of Access, DAO and/or ADO and to a lesser extend the VBA base library itself. Heavily used examples are the Access.Form or Report object or the DAO. or ADO.Recordset object.

Very important about Objects is the fact that they are holding state. Other than intrinsic plain functions and procedures that are (almost) always starting from scratch whenever they are called. When a procedure or function completes, all state they might have internally managed during execution is reset.

Objects do keep their state during their lifetime. If you set the property of any Object the Object will usually keep the value of that property for as long as the object instance stays in memory. The same applies to procedures (methods) of objects. When you call them, they might change the state of the object and one or more properties with it.

An examples of this is the caption of a Form. Once you set it to a specific text, this caption will remain until you close the form. - Or until you set it to a different value, of course.

Another example is the Recordset. It contains data from your database. If you call the MoveNext, MovePrevious or MoveLast method, the whole state of the Recordset will change, because the pointer to the current record (AbsolutPosition) is moved to the next (or previous or last) record in the collection. Depending on the new position the EOF or BOF properties might change as well.

So after considering these very common examples of objects you should understand already how useful and important Objects are.

Classes

Now for classes. The above examples dealt with predefined objects from some of the built-in Object Libraries.

Wouldn’t it be useful if you could define your own objects? – Of course it would. And you can.

Here Classes come into play. Classes, or Class Modules in VBA terms, are the blueprints for objects. So by writing a class module in VBA you are essentially defining your own object.

How to write classes in VBA

If you are familiar with “normal” VBA modules, you already got the basic knowledge required to write a class.

You start by creating a new Class Module, using either the Insert menu or the toolbar button to insert a new Class Module into your VBA Project.

Properties and Methods

As with “normal” modules, the important part of a class module are Functions and (Sub) Procedures (Methods) and Property Procedures.

The significant difference is the scope of variables. Within “normal” modules you would usually declare all your variables local only (inside) to the procedure using the Dim keyword. These variables will lose their values as soon as the procedure completes.

Within the methods of Class Modules, you will still declare most of the variables that way. But almost any class module will also contain some variables declared in the scope of the class. These are used to keep the state of an object created from your class.

These variables are declared at the top of the Class Module, before the first procedure declaration. Usually you will use the Private modifier keyword instead of Dim to declare a class scope variable.

Simple, right?

Now these variables are valid inside all methods of the class and you can change their values from each procedure of the class, but they will not be visible from code outside of the class. If you want to be able to change these values from code outside of the class, you should write a special type of procedure for that purpose. – A Property Procedure.

Property procedures come in three different variations. Get, Let and Set Property procedures.

The purpose of Get Property procedures is guessable by their name. They are usually used to return the value of an internal variable to the outside world.

Now Set and Let property procedures both mainly have the purpose to setting the values of class internal variables from outside of the class. The only difference between them is that Set procedures are used to set the value to object type variables while the Let procedures are used for simple, built-in variable types.

I quite intentionally used the adjectives mainly and usually in the above paragraphs about the property procedures. – Property procedures do not limit you in any way regarding what you can do in their code. But the above purposes are what is done in these procedure by convention. Unless you have a good reason to do otherwise, you should limit yourself to things directly related to setting and reading variable values.

The Me keyword

You probably used the Me keyword already in VBA code inside of form or report class modules. Me is used inside of a Class Module to refer to the current object instance of the class. So this always points to the object it is used in.

Defining your own Events

Other than in normal modules you can define your own events in class modules as well. Events are a powerful concept in their own right. Describing Events in the depth they deserve would go far beyond the scope of this article. In fact, they deserve a whole series, but I published only the first introductory article on events yet.

How to use your own objects on code

As I am pretty sure you used objects in your VBA coding already, I’ll keep this rather short.

The part you might not be familiar with is instantiating objects. For most of the predefined objects in the built-in Object Libraries there are factory methods available the will create and initialize the object for you. Examples of these are the CurrentDb-Method to get a DAO.Database object or the DB.OpenRecordset-Method to open and return a Recordset from a SQL query.

Now, while you can of course write similar factory methods for your own objects, you will usually instantiate you objects with the New operator. The full syntax is:

After that you can set further properties of the object you instantiated from your class or can call any of it’s methods.

The really useful thing is that your class will keep its state between all calls to any of its methods. So this makes it much easier to implement complex, multistep processes.

Finally, you should remember what we learned earlier on the scope of variables. If you just declared yourObjectVariable inside a procedure, the object will be destroyed automatically the moment your procedure finishes and its variables go out of scope.

But if you declare your variable in the top section of a form or module, the object will live on after the procedure creating it completes. You might (re)use the object in other procedures. – But always keep in mind that the object will keep its state. This can be extremely useful, and it is what enables very advanced concepts like observing events from lots of event sources.

But with object variables on module (or even global) scope comes a certain risk of hard-to-detect errors in your code, if the object of a variable you are using is not in the state you are expecting. So be very careful when using object variables (any variables) with a scope beyond a single procedure. Only do this, if it is really necessary.

Conclusion

Writing your own classes and using the resulting Objects in your VBA code is a very powerful concept. Some proven programming patterns cannot be used in VBA (or most other languages) without applying this knowledge about classes and objects.

You should master the concepts of classes and object orientation to move your programming skills to a new level.

Share this article: