VBA Class

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.

VBA-Class.png

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?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.

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 ExplicitOption ExplicitVBA 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.

vba class Example 1-1

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

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

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.

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. For this, we need to add a Module by going there under the Insert menu as shown menu.

vba class Example 1-5

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.

vba class Example 1-6

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 codeVBA CodeVBA 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.

vba class Example 1-7

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.

vba class Example 1-8

Similarly, do the same for the rest of the mobile phone brand, 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. For this, use the Set function and assign it to a New Mobile, as shown below.

vba class Example 1-10

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

Example 1-12

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.

Example 1-14

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.

Example 1-15

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.

Message of mobile brand

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.

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 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.

Recommended Articles

This has been a guide to VBA Class. Here we discuss how to create custom VBA class & objects along with examples and a downloadable excel template.  Below are some useful articles related to Excel VBA

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>