http://excelicious.wordpress.com/2009/03/20/type-or-class/#comment-355
http://www.cpearson.com/Excel/Classes.aspx
Type or Class?
March 20, 2009 by geoffness
I’ve been kept quite busy recently building an application for work. It’s a fairly standard task – take a number of regularly (monthly) populated workbooks from offices around the country and consolidate the data in them at a regional and national level. Aside from a little fluffing around with validation at the beginning and end of the process, the flow of the consolidation procedure runs like this:
01 |
Locate workbooks to be processed, add them to a collection |
02 |
For each workbook in the collection: |
03 |
Open it |
04 |
Read the data in the workbook into memory |
05 |
Close the workbook |
06 |
07 |
Open consolidation workbook |
08 |
For each block of data collected: |
09 |
write into the appropriate place in the workbook |
10 |
11 |
Save and close |
Now that’s all fairly straightforward. The issue I found myself faced with a couple of days ago relates to keeping the data from each of the workbooks in memory. There’s a fair variety of data in them, so I elected to use a user-defined type (UDT) to hold it:
1 |
Public Type uCheckSheetData |
2 |
sLocation As String |
3 |
dtMonth As Date |
4 |
vErrors As Variant |
5 |
lNumCases As Long |
6 |
sComment As String |
7 |
End Type |
I then formed a collection which would hold the UDTs – or so I thought. For those of you who’ve tried this before, you’ve probably also seen the rather obscure error message:
Compile error:
Only user-defined types defined in public object modules can be coerced to or from a variant or passed to late-bound functions
After a fair amount of head-scratching and wtf?ing I finally decided that Google is my friend and found a solution: UDTs can’t be added to collections – use a class. Classes in VBA may not be all that they are in other languages, but they do represent a more object-oriented approach, and can accomplish all and more that a UDT can. They just represent a bit more work to code. For example, here’s how I replaced the UDT uCheckSheetData with the class CCheckSheet:
01 |
Option Explicit |
02 |
' Replaces public type as class can be added to |
03 |
' collections/dictionaries |
04 |
05 |
Private sLocation As String |
06 |
Private dtMonth As Date |
07 |
Private vErrors As Variant |
08 |
Private lNumCases As Long |
09 |
Private sComment As String |
10 |
11 |
Public Property Get Location() As String |
12 |
Location = sLocation |
13 |
End Property |
14 |
15 |
Public Property Let Location(ByVal strIn As String) |
16 |
sLocation = strIn |
17 |
End Property |
18 |
19 |
Public Property Get Month() As Date |
20 |
Month = dtMonth |
21 |
End Property |
22 |
23 |
Public Property Let Month(ByVal dtIn As Date) |
24 |
dtMonth = dtIn |
25 |
End Property |
26 |
27 |
Public Property Get Errors() As Variant |
28 |
Errors = vErrors |
29 |
End Property |
30 |
31 |
Public Property Let Errors(ByVal vArr As Variant) |
32 |
vErrors = vArr |
33 |
End Property |
34 |
35 |
Public Property Get NumCases() As Long |
36 |
NumCases = lNumCases |
37 |
End Property |
38 |
39 |
Public Property Let NumCases(ByVal lIn As Long) |
40 |
lNumCases = lIn |
41 |
End Property |
42 |
43 |
Public Property Get Comment() As String |
44 |
Comment = sComment |
45 |
End Property |
46 |
47 |
Public Property Let Comment(ByVal strIn As String) |
48 |
sComment = strIn |
49 |
End Property |
Now it might be argued that a lot of that code is not really necessary. I could have defined a set of public variables in the class and just exposed them that way, rather than exposing public properties with Get and Let. However, one benefit of writing these procedures is that a lot of other code can be included in them, for instance to validate the data passed in to the class members – something that has to be done outside of a UDT. There’s also the possibility to include class methods and trap events and a whole lot of other stuff, which in this case is not really required. Chip Pearson has an excellent introductory discussion of classes in VBA at Classes in VBA. Which, by the way, includes a prominent warning not to make classes auto-instancing (using Dim as New syntax when declaring a class variable, otherwise known as a really bad idea). There’s also a great chapter in the book Professional Excel Development covering classes, which also warns against auto-instancing (did I mention that was a really bad idea?).
Finally, there’s a rather interesting discussion here about why VB won’t let you add a UDT to a collection.
—————————————–
—————————————
Introduction
Classes are a powerful tool in intermediate to advanced level VBA programming. This page is an introduction to what a class and an object are and will hopefully get you started working with classes. This is by no means a comprehensive guide.
In VBA, a class is defined in class module and serves as a template for an object. The term object is deliberately vague. An object can be defined to represent whatever you want. Anything that you can describe conceptually can be represented by a class. The difference between a class and an object is that a class does nothing and consumes no memory. When you have a variable of that class type and create instance of that class with the New keyword, a process called instantiating, it becomes an object and consumes memory and can carry out actions. A class is defined by its properties, which describe attributes of the class, and its methods (sub and function procedures), which carry out actions in the object. If a class is analogous to a noun, a property is like an adjective — it describes the object. A method is like a verb — it carries out an action.
You must instantiate a class into an object in order to do anything with it. There is nothing you can do with a class module beyond creating an object from it. An example of instantiation is shown below:
Dim C As Class1
Set C = New Class1
where Class1 is the name of the class module. Unlike other languages, VB/VBA allows for only one class in a class module, and the name of the class is the name of the module.
You can now work with the properties and methods defined in Class1 in the C object variable.
Dim C As New Class1
This is called an auto-instancing variable. When the variable C is first encountered in code, a new instance is created. In general, you should avoid auto-instancing variables for two reasons:
- First, it adds overhead to the code because the variable must be tested for Nothing every time it is encountered in code.
- Second, you have no way to test whether a auto-instancing variable is Nothing becaue the very act of using the variable name in an If Obj Is Nothing Then statement will automatically create an instance of the variable.
Before getting in to classes and objects, it will prove useful to examine briefly a class’s logical ancestor, the Type declaration. A Type is made up of other basic variable types. You may be familiar with Types from other programming languages, in which they are called a struct, structure, or record. For example, we could declare a Type that describes an employee:
Type Employee
Name As String
Address As String
Salary As Double
End Type
This defines a single type named Employee which has three elements: Name, Address, and Salary. You can then create variables of the Employee type and give values to the elements. For example,
Dim Manager As Employee
Manager.Name = “Joe Smith”
Manager.Address = “123 Main Street”
Manager.Salary = 40000
Types are quite useful, but have three shortcomings. First, you can’t declare new instances of a Type. You must declare all the variables you’ll need at design time or you need a dynamic array that is resized with Redim Preserve, an awkward and expensive operation. The second shortcoming of a Type is that you have no control over what values are assigned to the elements of a Type. For example, there is nothing to prevent the assignment of a negative value to the Salary element. Finally, a Type can’t do anything. It cannot carry out actions; it is simply a static data structure.
While Types have their place (they are used extensively in Windows API functions), a class module is often a better solution. New instances of a class may be created with the New keyword and stored in a Collection or Dictionary object. Next, the properties of a class can be set or retrieved with Property Let and Property Get procedures, which can contain executable code. Thus, code could be written to raise an error or take other appropriate action if an invalid value is used to set a property value, such as a negative value for a Salary. Finally, classes have methods (sub and function procedures) which can carry out actions. In the example of an employee, there might be a method to print a paycheck for the employee.
Class Basics
For illustration, let’s adapt the Employee Type described above into a class. First, insert a class module into your VBProject (from the Insert menu in the VBA editor). Name the class CEmployee (it is common practice to use a ‘C’ as the first letter of a class). There are three properties to create: Name, Address, and Salary. These values will be stored in private variables within the class. Since they are declared Private, they cannot be accessed outside the class module.
Private pName As String
Private pAddress As String
Private pSalary As Double
Next, we need to declare Property procedures to allow these variables to be read from and written to. This is done with Property Get and Property Let functions (or Property Set for object type variables).
”””””””””””
‘ Name property
”””””””””””
Public Property Get Name() As String
Name = pName
End Property
Public Property Let Name(Value As String)
pName = Value
End Property
”””””””””””
‘ Address property
”””””””””””
Public Property Get Address() As String
Address = pAddress
End Property
Public Property Let Address(Value As String)
pAddress = Value
End Property
”””””””””””
‘ Salary property
”””””””””””
Public Property Get Salary() As Double
Salary = pSalary
End Property
Public Property Let Salary(Value As Double)
pSalary = Value
End Property
The Get procedure is used to return a value out of the class, and the Let procedure is to put a value into the class. Note that the return data type of the Get property procedure must be the same data type as the (last) parameter to the Let property procedure. Otherwise, you’ll get a compiler error.
Because Property procedures can contain any code you like, the Let Salary procedure can be written to exclude non-positive values.
Public Property Let Salary(Value As Double)
If Value > 0 Then
pSalary = Value
Else
‘ appropriate error code here
End If
End Property
A property can be made read-only simply by omitting the Let procedure. For example, a read-only property might be withholding tax, which is calculated when it is called. E.g.,
Property Get WithholdingTax() As Double
WithholdingTax = calculated value
End Property
Finally, the class can contain methods, such as a PrintPaycheck procedure.
Public Sub PrintPaycheck()
‘ actual code to print check
End Sub
Now that we have defined the class, we can create objects based on the class. In a standard code module, declare a variable of type CEmployee.
Dim Emp As CEmployee
Then, Set that variable to a new instance of the class and assign some property values.
Set Emp = New CEmployee
Emp.Name = “Joe Smith”
Emp.Address = “123 Main Street”
Emp.Salary = 40000
Storing Multiple Objects In A Collection
If you need to store multiple instances of a class, such as for a group of employees, you can create mutliple objects from the class and store them in a Collection or Dictionary object, as shown below.
Dim Employees As Collection
Dim Emp As CEmployee
Set Employees = New Collection
For Each Item In SomeList
Set Emp = New CEmployee
‘ set properties for Emp
Employees.Add Emp
Next Item
Now, you can use a simple For Each loop to loop through the collection and iterate through the collection and access each instance of CEmployee sequentailly:
Dim Emp As CEmployee
For Each Emp In Employees
Debug.Print Emp.Name
Next Emp
The Instancing Property Of A Class
The Instancing property of a class controls where that class may be used. The default value is Private, which means that the class can be used only in the project in which the class is defined. You can set the instancing property to PublicNotCreatable, which allows a variable to be declared as that class type in projects that have a reference to the project containing the class. The second class may declare a variable of the class type, but cannot create an instance of the class with the New keyword. See the next section for more details.
Self-Referencing An Instance Of A Class
Code within a Property or method of a class can refer to its own instance by using the Me reference. For example,
Private pName As String
Property Let Name(S As String)
pName = S
End Property
Public Sub SomeMethod()
''''''''''''''
' some code
''''''''''''''
Me.Name = "ABCD"
End Sub
This refers to the Name property of the instance of the class from which it is executed.
Using Classes In Multiple Projects
If the Instancing property of the class is PublicNotCreatable a variable of that class type may be declared in other projects, but cannot be created in that project. You can use a function in the project containing the class to return a new instance to the caller. First, change the name of the project containing the class from the default value of VBProject to something meaningful like projSourceProject. Then, in the class that will use the class, set a reference to projSourceProject. Back in the project containing the class, create a procedure that will create and return a new instance of the class:
Public Function GetClass() As CEmployee
Set GetClass = New CEmployee
End Function
Then call this function in the project that will use the class:
Dim NewEmp As projSourceProject.CEmployee
Set NewEmp = projSourceProject.GetClass()
Setting The Default Value Of A Class
You can specify a property to be the default property of a class. When you do this, you can omit that property name and the compiler will use the default property. For example if you made Name the default property, the following lines of code are functionally equivalent:
Emp.Name = “Joe Smith”
Emp = “Joe Smith”
See Default Property Of A Class for information and examples of creating a default property.
This page just scatches the surface of what you can do with classes. Consult a good book on VBA or VB6 programming for a more in depth treatment of classes.
This page last updated: 20-July-2007
Ever Note 
Leave a comment