Using Misc in VBA

Let's find out! We'll examine:

• What a Class Is
• What the Different Elements of a Class Are
• How to Create a Class Definition
• How to Instantiate a Class Object
• How to Create a Class Method
• How to Use a Class Method
• How to Create a Class Constructor and a Class Destructor
• When the Constructor and Destructor Get Called
• Places to Go From Here

If you already know something about Objects, and the use of the New and Nothing keywords, you'll have a head start.

What Is a Class, Anyway?

Classes are Objects which allow you to group a set of related functionality into one group. That's it. Classes are the foundation of Object Oriented Programming. You define a Class's form, such as Dog or Person or SalesOrder, and then you instantiate an instance of a class in order to use it. To instantiate a class object, you must use the New keyword and assign the result to a variable that is a reference to the class.

What Can I Do with a Class?

A Class can contain:

• Subs and Functions – generally called Class Methods
• Properties – you can Get and Let these (or Set them if they are Objects)
• Events – can be fired
• A Constructor and a Destructor – called Class_Initialize and Class_Terminate
• Public Member Variables – can be accessed from outside the class – generally considered bad Object Oriented Design – use Properties instead
• Private Member Variables – can only be accessed inside the class – very handy!

You may take advantage of some of these features at one time or another, depending on the application. You won't necessarily use all of them all the time.

How Do I Create a Class Definition in VBA?

To create a Class definition in VBA, you will select Insert / Class Module from the VBA menu (while in the correct project, of course).

## Figure 1 – Using the Insert / Class Module command in VBA

This creates a blank Class for you to use. To name the class, change the name of the class from "Class1" to the desired name in the Properties Pane. You can name the class "Dog", for example. Some people choose names like "clsDog" so it's clear in code that this is the name of a class and not an actual class instance itself. It's up to you.

## Figure 2 – Changing the name of your class in the Properties pane

How Do I Instantiate a Class Object

I would have called this section "How Do I Instantiate a Class Instance" but I thought that would sound too weird. But that's what you do – you instantiate (call into existence) a class instance (a particular instance of a class). You can instantiate any number of class instances during the operation of a program – limited mostly by computer memory.

Let's say you do have a Class called Dog. To use the Dog class, you can write the two lines of code below in another Module (never in the Class Module itself):

Dim Minnie As Dog  
Set Minnie = New Dog

The first line declares a variable named Minnie of type "reference to Dog". You can't do much with it until you Set it to the result of something – either the result of a Function call, or the result of a New expression. (I hope you know about Objects and using the Set keyword – if not, please brush up on that first – it will make this all much more clear.) The second line actually instantiates the Dog object. (And calls the Constructor.)

There is another way to instantiate a Class instance using a single line of code:

Dim Minnie As New Dog

This method is shortly, and may be perfectly suitable in many applications. There is one difference between this single-line technique and the two-line technique above: In this single-line technique, the Dog object is not actually instantiated until the first call to a class method.

Most VBA programmers use either method interchangeably – the difference can be important in some applications and irrelevant in others.

How Do I Create a Class Method?

What can your dog do? And what can you do to your dog? Well, maybe you can walk it. So you create a Method called Walk in the Class Module. You can write

Public Sub Walk()  
' Walk the dog...  
End Sub

Maybe you want to specify how long you will walk the dog. In that case, write:

Public Sub Walk(ByVal Duration As Integer)  
' Walk the dog for Duration minutes.  
End Sub

Maybe the dog needs to be able to have a litter with a random number of puppies, up to a certain maximum (and with 1 as the minimum). The HaveLitter Function will return the number of puppies in the litter:

Public Function HaveLitter(ByVal MaxPuppies As Integer)  
HaveLitter = Int(Rnd * MaxPuppies) + 1  
End Function

Here's what our Dog class looks like:

## Figure 3 – The complete code listing of Class Dog at this point

How Do I Use a Class Method?

OK, now how do you use these Walk and HaveLitter Methods? Let's say you already have a Dog object instantiated:

Dim Fido As New Dog

Then, you can walk Fido by writing:

Fido.Walk 10

or

Call Fido.Walk(10)

Minnie is about to have puppies, so let's use the HaveLitter Function:

Dim Minnie As Dog ' This is the two-line technique to  
Set Minnie = New Dog ' instantiate a New Dog object.  
MsgBog "Minnie just had " & Minnie.HaveLitter(10) & " puppies!"

## Figure 4 – Playing with Fido and Minnie in Code Module Module1

This will display a message box showing how many puppies Minnie had (between 1 and 10).

## Figure 5 – Minnie had her puppies!

How Do I Create a Class Constructor and Destructor?

A Class can have a single Constructor and a single Destructor. The Constructor must be named Class_Initialize and the Destructor must be named Class_Terminate. They are called automatically when a Class object is instantiated and destroyed, respectively. You don't need to call these Methods; they are called automatically by VBA.

Let's take an example. Let's say that you want to get notified when an object is instantiated – as a matter of fact, you can use this as a test to see when the object is actually instantiated and how it varies between the two techniques. You write:

Private Sub Class_Initialize()  
MsgBox "Initialized!"  
End Sub

## Figure 6 – Class Dog with the Class_Initialize event handler

And then write this in another Code Module:

Dim Minnie As Dog  
Set Minnie = New Dog ' Gets Initialized here.  
MsgBox "About to Walk..."  
Minnie.Walk

You'll see "Initialized!", then "About to Walk..."

Compare the results with:

Dim Minnie As New Dog  
MsgBox "About to Walk..."  
Minnie.Walk ' Gets Initialized here, on first use.

In this case, you'll see "About to Walk..." THEN "Initialized!", because Minnie doesn't actually get instantiated until the first use, when Walk is invoked.

## Figure 7 – Two different ways to instantiate Minnie

You can try a similar examples to see when a class gets destroyed using the Class_Terminate method. You'll be entering the strange world of VBA garbage collection!

Where to Go From Here

In this article, you've learned:

• What a Class Is
• What the Different Elements of a Class Are
• How to Create a Class Definition
• How to Instantiate a Class Objetc
• How to Create a Class Method
• How to Use a Class Method
• How to Create a Class Constructor and a Class Destructor
• When the Constructor and Destructor Get Called

From here, you might want to study the use of Class Properties, Member Variables (Public and Private), and Events. We'll see if we can help out in a future article.

Now that you know what a Class is, it might take a while for it to sink in and to discover how to use them. I expect over time you'll start to get ideas of how to use them. It can be very nice to group a bunch of related functions into a class instead of having all your Subs and Functions in one module. It helps you get into the world of Object Oriented Programming, which makes your programs easier to read and easier to debug. Hopefully, soon you'll be able to add Object Oriented Programming with the use of Classes to your programming repertoire and maybe even impress your friends. Geek out!