Type or Class?

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:

Locate workbooks to be processed, add them to a collection
For each workbook in the collection:
	Open it
	Read the data in the workbook into memory
	Close the workbook

Open consolidation workbook
For each block of data collected:
	write into the appropriate place in the workbook

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:

Public Type uCheckSheetData
    sLocation As String
    dtMonth As Date
    vErrors As Variant
    lNumCases As Long
    sComment As String
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:

Option Explicit
' Replaces public type as class can be added to 
' collections/dictionaries

Private sLocation As String
Private dtMonth As Date
Private vErrors As Variant
Private lNumCases As Long
Private sComment As String

Public Property Get Location() As String
    Location = sLocation
End Property

Public Property Let Location(ByVal strIn As String)
    sLocation = strIn
End Property

Public Property Get Month() As Date
    Month = dtMonth
End Property

Public Property Let Month(ByVal dtIn As Date)
    dtMonth = dtIn
End Property

Public Property Get Errors() As Variant
    Errors = vErrors
End Property

Public Property Let Errors(ByVal vArr As Variant)
    vErrors = vArr
End Property

Public Property Get NumCases() As Long
    NumCases = lNumCases
End Property

Public Property Let NumCases(ByVal lIn As Long)
    lNumCases = lIn
End Property

Public Property Get Comment() As String
    Comment = sComment
End Property

Public Property Let Comment(ByVal strIn As String)
    sComment = strIn
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.


12 thoughts on “Type or Class?

  1. jonpeltier says:

    I almost never use UDTs. I learned about UDTs and classes at about the same time, and the enhanced abilities of classes (to perform actions in addition to just holding information) is almost always needed when I have a choice.

    It’s almost as easy to set up a class as a UDT, and it’s easier to just set up the class at first, than to realize later that a class should have been used and this UDT has to be converted.

    • geoffness says:

      Agreed. The main argument I have seen people make for using UDTs has been performance – i.e. using less memory. Another way around the restriction above (coercing items added to a collection to Variant) would have been to set up an array of UDTs rather than a collection, and access them using a custom function that would mimic the ‘key’ used to access items in a collection. IMO rather convoluted and not worth the effort – unless memory is a real concern.

  2. John Davies says:

    A year late, but I’m trying to work out how to avoid autoinstanciating an array of controls that will be handled by a class module. Walkenbach’s code is typical of the examples I’ve seen and code I’ve written (Excel 2002 Power programming with VBA, p. 460)

    Dim Buttons() As New BtnClass

    Private Sub UserForm_Initialize()
    Dim ButtonCount As Integer
    Dim ctl As Control

    ' Create the Button objects
    ButtonCount = 0
    For Each ctl In UserForm1.Controls
    If TypeName(ctl) = "CommandButton" Then
    If ctl.Name "OKButton" Then 'Skip the OKButton
    ButtonCount = ButtonCount + 1
    ReDim Preserve Buttons(1 To ButtonCount)
    Set Buttons(ButtonCount).ButtonGroup = ctl
    End If
    End If
    Next ctl
    End Sub

    I have tried Dim … Set, but having an array seems to mean that I can’t Set to it and have to autoinstanciate. Maybe I haven’t googled properly, but I can’t find any other way. Bright ideas would be appreciated.


    John Davies

    • geoffness says:

      Hi John

      I haven’t tested this out, but rather than creating an array of BtnClass objects, maybe try creating another class to act as a parent to all of them? It’s more code but I see it as a better solution than using an array. The parent class could hold a collection property which would hold all the controls.

      Chapter 7 in Professional Excel Development (http://www.oaltd.co.uk/ProExcelDev/Default.htm) gives an example of code which does something similar to the above – with cells on a worksheet rather than controls on a form, but the principle seems the same, and it seems to me the code could work as a template for what you’re trying to do.

      Hope that helps 🙂

  3. andrewhharmonandrew says:

    Great article, and I am actually trying to do a very similar thing. I defined my classes, but when I try to consolidate them across workbooks I run into issues. I have a “Consolidator” workbook that opens each file in a folder. I need to populate and instance of my class from the data in each file and add that instance to a collection. I can’t figure out how to “share” my custom class between the files. Can you elaborate a little further on the process you used to build your collection? Great post and thanks for the help.

    • geoffness says:

      Thanks for reading, and for your kind words 🙂

      The code that instanciates your class would loop over the files in the folder, and for each one, open it and assign it to a new workbook object, create a new instance of the class and then set properties of that instance based on data in the workbook. This instance gets added to the collection, the workbook is closed and then the next iteration of the loop repeats the process with a new instance of the class and a new workbook object.

      The way I’ve done this in the past, the process requires you to declare filesystemobject, folder and file objects to set up the loop over files in the folder, a workbook object and a custom class object, as well as a collection object (which might or might not be another class, depending on the need). The fact that at each stage of the process you’re setting the custom class object to a new instance means that you never really have to ‘share’ the class in the way you suggest (unless I’ve misunderstood the question).

      I’d be happy to take a look at your code and suggest something if that’s not working the way you expect, feel free to send it through to me (address at https://excelicious.wordpress.com/about/).

  4. vbanewbie says:

    Could you please post an example of a task which doesn’t use class modules and then the same task with it, so I can appreciate its usefulness and how to go about writing the syntax.

    • geoffness says:

      So you’re looking for an example where I’ve written code which performs a task with UDTs and then re-written it using classes? The only time I’ve ever done that is the above, and sorry I don’t have that code any longer, it was a bespoke application written for a job I’m no longer in. There are plenty of good examples of VBA applications using classes on the interwebs, for instance the page I linked to in the post above would be a good place to start: http://www.cpearson.com/Excel/Classes.aspx

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s