How To Create A Class In Vba

Excel VBA Class Module

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. When we create Class in VBA, they act like totally an independent object function but they all are connected together.

This helps us in building applications like which are already there in VBA and Excel. For example, the pedaling cycle wheel rotates. Pedals and Wheels both are the parts of Cycle, but both work independently to give the output as a moving cycle.

You are free to use this image on your website, templates etc, Please provide us with an attribution link Article Link to be Hyperlinked
For eg:
Source: VBA Class (wallstreetmojo.com)

How to Create Custom Class & Objects in VBA?

You can download this VBA Class Excel Template here – VBA Class Excel Template

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.

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 VBA option explicitly makes a user mandatory to declare all the variables before using them; any undefined variable will throw an error while coding execution. We can enable it for all codes from options to require variable declaration. read more , 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 the Public. This will make those defined parameters open to using anytime, anywhere, without any 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 a different process of operating, features, and functions of a mobile phone, such as starting 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.

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

This completes the creation of Class. 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 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 VBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task. read more . 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 to Mobile. Let's start with the 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 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. For this, use the Set function and assign it to a New Mobile, as shown below.

Now open With-End loop for iPhone. This 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 brand as well.

Now we will use DebugPrint to print the information on the immediate window. It is useful where we want to see a 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 the mobile brands in the same module along with the Message box. You may skip the message box here.

This completes the assigning of Class to Module. Now compile the code and run using the F5 key. 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 it, for that open the Local window from the view tab, which will help us getting 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 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 needs.

Cons

Initially, it takes a lot of time to create a class in VBA.
People who are new to VBA will find the 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 the 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 the 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.