VBA Class Modules and How to Use Them - Excel Tip

What is a Class in VBA?

A class is a blueprint for an object to be created. A class itself does nothing but using classes you can create multiple identical objects that can perform operations or can be used as a data resource.

In real life, the model (design and functionalities) of a car is a class and the car itself is an object of that class. In the class, we define what the car has in it and what it can do. For example, a car has 4 wheels, 5 gears, steering wheel, etc. These are attributes/properties. We also define what a car can do, like moving forward, backward, turn, etc. These are functions of the car class. A car object created using car class, will have all these properties. If you define a car that has 5 wheels than a car created using this class will have 5 wheels. You get the point.

Enough of theory, now let's see how you can use a class module in VBA.

Using Excel VBA class module

While working in VBA, you must have used Range("A1").select. The Range is pre-defined class in VBA. The select is one of the functions of Range class that select the specified range. Similarly, Debug is class in VBA and print and assert are it's methods. Worksheets,  Workbooks, Range, etc. all are VBA classes that we use in our subs.

Create your own class

First, we need to add a class  module in VBA

Press ALT+F11 key combination to open excel VBA editor.

How does it work?

In sub Test, we have created is an object "wc" of Welcome class. An object is created in VBA in two methods. we run the code, Test sub creates an object wc of the Welcome class. This object has all the properties of the Welcome class. We use the sayHiTo method of the Welcome class to say hi to the user.

Object Creation in Excel VBA

In Instant creation, we create an object while declaring the object with the "new" key. In our example above, we have used instant creation.

2. Delayed creation In delayed creation, we first declare the object only. We don't use the "new" keyword. In order to use the object, we need to initialize it with the "new" keyword.

Accessing Variables of a Class

In the above examples, we have used public variables for class but it is wrong to practice. We should avoid using public variables in a class. Now the question is how would we access variables of the class. Earlier, we used subroutine to access name but VBA classes provide properties that are used to systematically update and retrieve private variable values of the class. The properties are more elegant than sub or function for updating and accessing private variables. Let's see how.

Syntax of class property

Let's use them in a module.

When you will run this test sub, you will get two names printed for two objects of the "Welcome" class.

How properties are different from sub and functions

In the above example, notice that we have used MyName property as a variable. We initialized the value of "Name" variable just by writing wc.MyName="assdf". This line of command called the property called Property Get MyName() As String. We didn't pass any value in parenthesis as we did in the beginning.

Similarly, to print the values of "Name" variable we used command Debug.Print wc.MyName. Isn't it as simple as normal variable initialization? The only difference is that you can do a whole lot in the property segment. You put data validation, calculation, communication, etc. and user will only see the result.

Another difference is that we can use same name of the property to let and get part. This makes it easier and less confusing.

So yeah guys, this was a simple example of a class module in Excel VBA. This is just the tip of the iceberg, there's a lot of juice in this topic that we will explore in later articles. We will explore each of them one by one in the easiest way possible. I hope I was explanatory enough to make you understand this. If you have any doubts regarding this topic or any other excel VBA topic, mention it in the comments section below.

Related Articles:

Import a module from a file using VBA in Microsoft Excel | Learn how to import entire module from another file using VBA.

Create a new module using VBA in Microsoft Excel | You can use a module to create another model in VBA. This can help you minimize the additional overhead work.

Add a procedure to a module using VBA in Microsoft Excel | To add procedures to modules automatically use this VBA code.

Popular Articles:

50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.

The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets. 

COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don't need filter your data to count specific value. Countif function is essential to prepare your dashboard.

How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.

Your email address will not be published. Required fields are marked *

Name *

How to Use Structures in VBA? The User Define...

Delete module content using VBA in Microsoft ...

Create a new module using VBA in Microsoft Ex...

Delete all macros in a workbook/document usin...

How to Create Dependent Combobox in VBA Excel...

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Get latest updates from exceltip in your mail.

Google serves cookies to analyse traffic to this site. Information about your use of our site is shared with Google for that purpose

This website uses cookies so that we can provide you with the best user experience possible. Cookie information is stored in your browser and performs functions such as recognising you when you return to our website and helping our team to understand which sections of the website you find most interesting and useful.

You can adjust all of your cookie settings by navigating the tabs on the left hand side.

Strictly Necessary Cookie should be enabled at all times so that we can save your preferences for cookie settings.

If you disable this cookie, we will not be able to save your preferences. This means that every time you visit this website you will need to enable or disable cookies again.