Excel VBA Class
VBA Class allows us to create our own Object function in which we can add any kind of features, details of the command line, type of function. This helps us in building applications like which are already there in VBA and Excel. When we create Class in VBA, they act like totally an independent object function but they all are connected together. For example, the pedaling cycle wheel rotates. Pedals and Wheels both are the parts of Cycle, but both works independently to give the output as moving cycle.
Example of Excel VBA Class
Let’s consider an example of 3 different companies’ mobile phones which are Apple, Samsung, and Nokia. Considering the popular and newly launched mobile phones of these companies which are iPhone X, Samsung S8, and Nokia 7+.
We will compare some of the important features for these mobile phones such as Brand, Model, Screen Size, Camera Type and Charger Type. These are basically important features on that basis we mostly compare any mobile phone. Below we have mapped the above-discussed parameters in a chart.
Now to create a Class in VBA first go to VBA. And from 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 variables must be declared before they are used. If we don’t declare any variable and use it, then the system will throw an error. Now in Class, define all the discussed parameters of measurement of mobile phones with Public. This will make those defined parameters open to using anytime, anywhere without any limitations.
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 a different process of operating, features and functions of a mobile phone. Such as starting of a phone, Switching off the phone, playing music, charging the battery, etc. with Subcategory for each function as shown below. And add a message box in each Sub-category loop so that we will be able to see which methods are currently running.
'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
This completes the creation of Class in excel VBA. Before we move further, it is advised to change the name of the Class Module. 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. For this, we need to add a Module by going there under the Insert menu as shown menu.
We will get a new module opened with having Option Explicit enabled in this. As we are working and creating a VBA Class. Now start writing Subcategory in the name of performed functions as shown below.
We can change the name of the Module as also as we did for Class. This helps us keeping the co-related identity of the created VBA code. This 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’s define 3 variables in any name (Preferably in the name of mobile phone brand) and assign it with Mobile. Let’s start with iPhone first as shown below.
Similarly, do the same for the rest of the mobile phone brand as shown below.
By this, we assign the created VBA Class to each dimensional variable of Mobile brand. Now for each brand do the same process of assigning created Class.
Now we need to assign all the features of Mobile phone and its value. For this, use Set function and assign it a New Mobile as shown below.
Now open With-End loop for iPhone. This is where we will define each attribute of 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 brand as well.
Now we will use Debug.Print to print the information on immediate window. It is useful where we want to see certain variable in a line of code. We will select different features for each Mobile brand as shown below.
Now assign the MobileStarts and MobileOff functional operations which we defined in Class to each of mobile brand in the same module along with Message box. You may skip message box here.
This completes the assigning of Class to Module. Now compile the code and run using the F5 key. We will message box of each mobile brand as shown below.
Now, if you want to know what variables and functions have what kind of values in it. For that open the Local window from view tab which will help us getting the details when we compile the code as shown below.
Below code is for your Reference.
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 of Excel VBA Class
- We can build our own application with a lot of features.
- Once Class is created, we can update any feature, anytime whenever we require.
- If we update the code then also it won’t cause any problem in other parts of the Class.
- We can test the individual part of the application as per our need.
Cons of Excel VBA Class
- Initially, it takes a lot of time to create a Class in VBA.
- People who are new to VBA will find Class very difficult to apply.
Things to Remember
- As the code can be huge and big, so it’s better to compile the code step-by-step. By doing this we would avoid N number of errors which will be difficult for us to debug and resolve.
- We can ignore the use of Message Box if you are taking and testing above written code.
- For testing, you can use lesser techniques or functions in creating Class. This can be modified later when we want to add more product functions and techniques.
- Always consider similar or same features when we finish Class and assigning it to a Module. This will help us in comparing the features of different products.
This has been a guide to VBA Class. Here we discuss how to Create Custom Class & Objects in VBA along with examples and downloadable excel templates. Below are some useful articles related to Excel VBA –
- Replace String Function in VBA
- VBA CLng Function | Examples
- Find Variable Data Type using VARTYPE
- VBA Resize
- Use DateSerial in VBA
- Named Range in Excel VBA
- ME Keyword in Excel VBA
- VBA Const Statement