VBA Class: Understanding a Vital Part of Programming

Harnessing the full power of Visual Basic for Applications (VBA) requires you to understand a great deal about programming. With VBA at your disposal, you can create everything from more dynamic spreadsheets to video games. It only requires understand the language and having a great deal of imagination.

Classes can be a very powerful tool for VBA programmers, but they’re used mostly for intermediate and advanced VBA programming, so if you’re having a little trouble with it don’t worry. Our course Visual Basic for Excel has over 8 hours of content to take you from being a beginner to an expert in understanding the VBA programming language in no time.

What are Classes?

The concept of a class may seem confusing if you’re new to programming, but it can be easy to understand, but they are an essential component to programming, especially Object Oriented Programming.

Object Oriented Programming is the most modern type of programming language we use today. Understanding it can be easy – check out the Object Oriented Programming Udemy course that teaches you about it without forcing you to write a single line of code.

In VBA specifically, a class, defined in a class module, serves as the basic template for your object. An object can be whatever you want. For example, say you were creating a game that had lots of weapons in it. How would you make that weapon in the game? How would you make each weapon different? A class is the answer to these questions.

A class isn’t necessarily an object, but it is instead a blueprint that the other objects follow. Once you create a class, you can create an instance of that class with a new keyword, this process is called instantiating, and the class suddenly becomes an object with memory and the ability to perform tasks and do actions.

Classes have several components to them, which include properties that give the class its attributes, methods, which carry out specific actions of the object.

A fun way to think about classes is in terms of English. A class is the noun. The property is the adjective that describes the noun. A method is kind of like a verb that carries out the action of the noun.

For instance, say you created a class called Dog and it held in certain attributes about different dog species. You could instantiate the class by writing code that looks like this:

Dim D As Dog
Set D = New Dog

What the above two lines of code just did was create an instance of the class Dog and name it D. Then it set D as a new instance of the original Dog class so that it can be changed and manipulated on its own as a new type of the original dog class.

Consider the analogy from before. We had the basic sentence structure of noun, adjective to describe the noun, and a very to give the noun an action. The new instance of the Dog class D would be an example of setting up a noun for our sentence. Now we just need to fill in the adjective and the very and we will have made full use of the class.

You may come across quite a few terms you may not understand in VBA, but don’t worry about it. Read the Udemy blog Excel VBA Programming Basics in Plain Straight Forward English, which teaches you all about the weird programming terminology you will see and how you can understand it easily.

Creating a Program and Understanding How It Works

The best way to understand programming is to actually create a program. What you’re going to do is create a class, input data, and see how the class works and how you will instantiate it.

First of all, you will need to create the class. Go to the insert menu on your VBA editor and insert a class module into your VBProject, you can name the class anything you want, but for now just name it Workers.

In this class we will first create three properties that will be private variables. You always want to make your variables private so that they cannot be accessed or changed when you’re programming outside of the class the variables are located.

Private name As String
Private address As String
Private earnings As Double

Above we have declared three properties in the program, notice how the first two properties are strings, because they include text, and how the final property is a double because it includes more than just whole numbers. Remember that data types are always important in programming, and choosing the right one can help make things easier for you in the long run.

Now that you have created your properties though, you have to write code to make it so these properties can be written to and read from. You do this with the property, get, let, and set functions.

The name property

Public Property Get Name() As String
 Name = name
 End Property
 Public Property Let Name(Value As String)
 name = Value
 End Property

The address property

Public Property Get Address() As String
 Address = address
 End Property
 Public Property Let Address (Value As String)
 address = Value
 End Property

The earnings property

Public Property Get Salary() As Double
 Earnings = earnings
 End Property
 Public Property Let Salary(Value As Double)
 earnings = Value
 End Property

Note the two functions in the program above. The Get function will allow us to return a value outside of the class. On the other hand, the Let function allows you to put a function into the class.

This is the adjective of programming, but we still need the verb. In programming a method is a block of code that you can write to do anything. For example, since we are dealing with employees here we will use the method Paycheck. In order to create the Paycheck method type the following lines of code.

Public Sub Paycheck()

End Sub

Between Public Sub Paycheck() and End Sub you can write any code you like that would print out the paychecks for the workers

For now we are going to leave this method blank though, and worry about instantiating the class instead so you can see how it works.

Creating Objects Based On Your Class

Now that you’ve defined your class you can create objects based on the parameters you set for it. Simply, create a variable and set its data type as your class in the standard code module. It should look something like this:

Dim employee As Workers

Now that you’ve set your variable, in this case I’ve set mine as employee; you can create specific parameters to the employee based on your class. The code will look like this:

Set employee = New Workers
employee.Name = “John”
employee.Address = “145 Maple Road”
employee.Earnings = 600000

Why Do We Use Classes?

The reason we use classes is because it provides us with versatility, accuracy, and it cuts down on the amount of code we have to write. Instead of creating a new class for Workers, I can create a new instance of the class I just created, call it employee2 and input the same code as before.

So, congratulations on creating your first class! This is one of the best aspects of Object Oriented Programming. If you really want to see what you can do with classes, the Excel VBA 24-Hour Trainer and VBA – Introduction to VBA Macros courses will be perfect for you.