Table Of Contents
Excel VBA Class Module
VBA class allows us to create our Object function in which we can add any features, details of the command line, and type of function. So, when we create a class in VBA, they act like independent object functions, but they are all connected.
It helps us build applications already there in VBA and Excel. For example, the pedaling cycle wheel rotates. Pedals and wheels are the cycle parts, but both work independently to give the output as a moving cycle.
How to Create Custom Class & Objects in VBA?
Let us consider an example of three different companies’ mobile phones: Apple, Samsung, and Nokia —considering the popular and newly launched mobile phones of these companies, which are the iPhone X, Samsung S8, and Nokia 7+.
We will compare some of the important features of these mobile phones, such as brand, model, screen size, camera type, and charger type. These are important features on that basis. However, we mostly compare any mobile phone. Below we have mapped the above-discussed parameters in a chart.
In the VBA window from the Insert menu, select the Class Module, as shown below.
We will get the class module window starting with Option Explicit, as shown below.
Option Explicit ensures that it must declare variables before using. If we do not declare any variable and use it, the system will throw an error. Now, in class, define all the discussed parameters of measurement of mobile phones with the Public. Then, it will make those defined parameters open to use anytime, anywhere, without limitations.
Code:
Option Explicit 'List of properties Public Brand As String Public Model As String Public ScreenSize As String Public CameraType As String Public ChargerType As String
Now, we will add different operating, features, and functions of a mobile phone, such as starting a phone, Switching off the phone, playing music, charging the battery, etc., with a subcategory for each function as shown below. And add a message box in each subcategory loop so we can see which methods are currently running.
Code:
'Possible Techniques Sub MobileStarts() MsgBox "Mobile is Turning On" End Sub
Sub MobileOff() MsgBox "Mobile is Turning Off" End Sub
Sub PlayMusic() MsgBox "Audio system is currently working" End Sub
Sub BatteryCharge() MsgBox "Charger is currently plugged-in" End Sub
It completes the creation of the class. However, changing the class module's name is advisable before we move further. Choose any name as per your requirement as we change it to Mobile.
Now, we will write a code in a module where we will see and compare the features and functions of each mobile phone brand. As shown, first, we need to add a Module under the "Insert" menu.
We will open a new module with Option Explicit enabled as we work and create a class. Then, write a subcategory in the name of performed functions, as shown below.
We can also change the module's name as we did for class. It helps us keep the co-related identity of the created VBA code. It can be done from properties windows, as shown below.
We have already defined various features, functions, and methods of all brand mobile phones. Now, let us define three variables in any name (preferably in the name of the mobile phone brand) and assign them to Mobile. Let us start with the iPhone first, as shown below.
Similarly, do the same for the other mobile phone brands, as shown below.
By this, we assign the created Class to each dimensional variable of the Mobile brand. Now for each brand, do the same process of assigning the Class.
Now, we need to assign all the features of the Mobile phone and its value. First, use the Set function and assign it to a New Mobile, as shown below.
Now, open the "With-End" loop for iPhone. It is where we will define each attribute of the mobile.
As we can see, we have assigned all the defined features of the iPhone brand from class with specific values as String.
Do the same thing for Samsung and Nokia brands as well.
Now we will use DebugPrint to print the information on the prompt window. It is useful where we want to see a certain variable in a line of code. First, we will select different features for each Mobile brand, as shown below.
Now, assign the MobileStarts and MobileOff functional operations, which we defined in the class, to each mobile brand in the same module along with the message box. You may skip the message box here.
It completes the assigning of classes to the module. Now, compile the code and run using the F5 key. Finally, we will see the message of each mobile brand, as shown below.
Now, if you want to know what variables and functions have what kind of values in them, we must open the Locals window from the “View” tab, which will help us get the details when we compile the code, as shown below.
The below code is for your Reference.
Code:
Sub VBA_Class() Dim iPhone As Mobile Dim Samsung As Mobile Dim Nokia As Mobile Set iPhone = New Mobile With iPhone .Brand = "iPhone" .Model = "iPhone X" .ScreenSize = "6.5 Inches" .CameraType = "12 MegaPixel" .ChargerType = "Regular" End With Set Samsung = New Mobile With Samsung .Brand = "Samsung" .Model = "Samsung S8" .ScreenSize = "5.8 Inches" .CameraType = "12 MegaPixel" .ChargerType = "Power" End With Set Nokia = New Mobile With Nokia .Brand = "Nokia" .Model = "Nokia 7+" .ScreenSize = "6 Inches" .CameraType = "12 MegaPixel" .ChargerType = "Power" End With Debug.Print "Phone Screen Size is: " & iPhone.ScreenSize Debug.Print "Camera of Samsung is: " & Samsung.CameraType Debug.Print "Charger Type is: " & Nokia.ChargerType iPhone.MobileStarts iPhone.MobileOff MsgBox iPhone.Model Samsung.MobileStarts Samsung.MobileOff MsgBox Samsung.Model Nokia.MobileStarts Nokia.MobileOff MsgBox Nokia.Model End Sub
Pros
- We can build our application with a lot of features.
- Once we create the class, we can update any feature whenever we require it.
- If we update the code, it won’t cause any problems in other parts of the class.
- We can test the individual part of the application as per our needs.
Cons
- Initially, it takes a lot of time to create a class in VBA.
- New to VBA may find the class very difficult to apply.
Things to Remember
- As the code can be huge, it is better to compile it step-by-step. By doing this, we would avoid the number of errors, which will be difficult for us to debug and resolve.
- We can ignore the use of the message box if you are taking and testing the above-written code.
- You can use lesser techniques or functions to create a class for testing. Then, it can be modified later when we want to add more product functions and techniques.
- Consider similar or same features when we finish class and assign it to a module. Then, it will help us in comparing the features of different products.
Recommended Articles
This article is a guide to VBA Class. We discuss creating custom VBA classes and objects, examples, and a downloadable Excel template here. Below are some useful articles related to Excel VBA: -