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.
Public name As String Public var1 As Integer Public var2 As Integer
Sub sayHiTo(user As String) name = user MsgBox ("Hi! " & name) End Sub
Sub test() Dim wc As New Welcome 'Declared and initialized Welcome object wc.sayHiTo ("Jack") 'used sayHiTo method of Welcome Object. End Sub
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.
Dim wc As New Welcome
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.
Sub test() Dim wc As Welcome 'wc.sayHiTo ("Jack") 'generates error since wc is not initialised yet 'initialising object Set wc = New Welcome wc.sayHiTo ("Cory") 'this will work. End Sub
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
Private name As String Private var1 As Integer Private var2 As Integer Property Let MyName(nm As String) name = nm End Property Property Get MyName() As String MyName = name End Property
Let's use them in a module.
Sub test() 'creating class object Dim wc As New Welcome Dim wc1 As New Welcome 'using properties wc.MyName = "Exceltip.com" wc1.MyName = "ExcelForum.com" Debug.Print wc.MyName Debug.Print wc1.MyName End Sub
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.
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.
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.
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.