Creating Your Own Objects in VBA | Getting Acquainted with Visual Basic for Applications

Problem

You know VBA is an object-oriented language and you'd like to create your own objects for use in other subroutines and functions.

Solution

Create a new Class Module in VBA and add your object properties and methods.

Discussion

Let me show you how to do this by way of example. First, for those of you not familiar with object-oriented lingo, an object is a code construct comprised of properties and methods. Properties are variables that describe the object in some way; they store attributes of the object. Methods are functions or subroutines that actually do something. They can manipulate properties, perform some action on given parameters, or do whatever other programming task you can devise. Objects provide a convenient way of packaging code that allows easy reuse.

In Recipe 7.11, I show you how you might add support for manipulating vectors using VBA. You might also approach the same problem by writing a vector object to encapsulate all the properties and functionality of a vector. This way you can define vector type variables and perform computations with these variables. I'll show you step by step how to get started.

Open Excel and the VBA editor. In the VBA editor, select Insert images/U2192.jpg border=0> Class Module from the main menu bar. In VBA (as in some other languages) an object is called a class. Under the VBA project browser panel, you should see a folder called Class Modules and under that a new module called something like class1. Now press F4 (or select View images/U2192.jpg border=0> Properties Window from the main menu bar) to open the properties panel. Click on your new class and you should see its properties displayed in the properties panel.

For the moment you won't see much. You should, however, see a Name property. Click the field to the right of Name and type in MyVector. This is the new name for your vector object, which should also be reflected in the VBA project browser window.

Find the module now named MyVector in the project browser and double-click it to open a code window. It will be blank at first, so you need to add the properties and methods for this new vector class.

First, let's add some properties. At the very least, for a 2D vector, you know you need an x and a y component. So add the code shown in Example 2-22.

Example 2-22. MyVector properties

'MyVector Object Properties:
Public x As Double
Public y As Double

These look similar to local variable declarations. Notice the Public keyword, however. You need to use this keyword to make your properties available to you outside of this module. You can add any number of properties in this manner, but for now these two will suffice.

When you use a custom object in other code, you have to create the object by allocating memory for it. I'll show you how to do that later, but you should be aware that when an object is created, VBA will automatically call an initializing subroutine to initialize the object for you, setting its properties to default values. If you want your object's properties set to some specific default values, then you should create a custom initialize method.

Look at the top of the code window in your class module. You should see a drop-down listbox with the word General displayed in it. Click this listbox and select Class from the drop-down list. Over to the right, you'll see another listbox. Click it and select "initialize" from the list. Now you should see that a new subroutine has been created in your code module. It should look like that shown in Example 2-23.

Example 2-23. Empty initialize method

Private Sub Class_Initialize( )

End Sub

You can add your custom initialization code inside this subroutine. For example, in Example 2-24 I added two lines to initialize this object's properties to 0.

Example 2-24. Empty initialize method

Private Sub Class_Initialize( )
 x = 0
 y = 0
End Sub

It isn't quite necessary in this simple case, but does illustrate how to initialize objects.

So far, this vector object only stores two values. To make it actually do something, you have to add methods. As I said earlier, methods are just subroutines and functions. Example 2-25 shows one I added to compute and return the magnitude of the vector.

Example 2-25. Magnitude method

Public Function Magnitude( ) As Double
 Magnitude = sqrt(x * x + y * y)
End Function

This method is simply a function. You add it just as you would a regular function, as shown earlier in this chapter. The Public keyword makes this function available outside of this module. As you can see, the vector magnitude is computed using the object's x and y properties. Since these properties have module-level scope, you can access them from within any method you write.

You can add any number of methods in a similar manner. For example, you could add a subroutine that normalizes the vector, or you could write a subroutine that adds another vector given as a parameter to the vector, and so on.

Now we have a real object that can do something. To use this object in other code, you must first declare a variable with your object name as the type. Then you have to call VBA's Set and New statements to actually create the memory for the object, which also initializes the object by calling its initialize method if one exists. Once the object is created, you can use it in code using the . syntax to access its methods and properties. Example 2-26 illustrates all these tasks.

Example 2-26. Using the MyVector class

Public Sub MySubroutine( )
 Dim v As MyVector
 Dim mag As Double

 Set v = New MyVector

 v.x = 2
 v.y = 4

 mag = v.Magnitude
End Sub

The line Dim v As MyVector declares a local variable of type MyVector. You can't use it just yet (unlike mag, which is a regular variable of type Double). The line Set v = New MyVector is required to actually create the MyVector object, assigning it to the variable v.

The lines v.x = 2 and v.y = 4 set the x and y properties of the vector, v, to 2 and 4, respectively. They'll stay that way until you change them. A method is called in a similar manner. The line mag = v.Magnitude calls the vector's Magnitude method, returning the vector magnitude and storing it in the local variable mag.