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:
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.