Is VBA Object Oriented?

This article will discuss the question – is VBA Object Orientated?

Microsoft Office is, by design, made up of Objects. In Excel, we start with the Application object, and then move to the Workbook, Worksheet and Range Objects. These objects can all communicate with each other by means of Properties and Methods. The Range object for example, can use the method “AddComment” to add a comment to a cell in Excel.

When we use VBA to write our macros, we use all these built in objects, properties and methods to control the functionality of Excel. We can however, also create our own objects in Excel by the way of class modules.

Built-in Object Orientated Coding

When you create code in VBA, you refer to the objects such as the Worksheet object, the Workbook object or the Range object.  You then use the properties and methods of each of these objects to control what you want to happen  to the object, or what you want the object to do.

If we run the code above, the following will take place in our worksheet.

Custom Object Orientated Coding

You can use Excel to create what is known as a Class Module. This differs from a normal module in Excel as you are able to create Properties and Methods in your module,

To add a class module to Excel, in VBE, make sure the relevant VBA project that are working in is selected and then, select Insert > Class Module.

OR

Right-click on the VBA Project in the Project Explorer and then select Insert > Class Module.

 

You can then rename your class module with a relevant name by clicking on the module and then, in the Properties, amending the name property of the module.

 

Now you can create properties for this class module.

 

To use this class module, and populate a collection using the class module and it’s properties, we can create a standard module.

 

First we (1), create a new object (a clsClient – an instance of the class module).  Then (2) we populate the properties of that new object in our code.  The properties of the class will pop up in intellisense just like properties do for built in Excel objects.

 

 

Once we have populated the class object, we add that object to a collection.   We can then use that collection object in our code as required.

As VBA uses Objects and is completely orientated around objects, I would classify VBA as Object Orientated. However, it is definitely a ‘much smaller sibling’ to the fully OOP languages such as Java and C#.