Table Of Contents

arrow

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.

VBA-Class.png

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.

Mobile Phone Chart

In the VBA window from the Insert menu, select the Class Module, as shown below.

vba class Example 1

We will get the class module window starting with Option Explicit, as shown below.

vba class Example 1-1

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
vba class Example 1-2

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
vba class Example 1-3

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.

vba class Example 1-4

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.

vba class Example 1-5

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.

vba class Example 1-6

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.

vba class Example 1-7

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.

vba class Example 1-8

Similarly, do the same for the other mobile phone brands, as shown below.

vba class Example 1-9

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.

vba class Example 1-10

Now, open the "With-End" loop for iPhone. It is where we will define each attribute of the mobile.

Example 1-11

As we can see, we have assigned all the defined features of the iPhone brand from class with specific values as String.

Example 1-13

Do the same thing for Samsung and Nokia brands as well.

Example 1-12

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.

Example 1-14

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.

Example 1-15

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.

Message of mobile brand

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.

Local window

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.