I frequently find myself wanting to use class abstraction in VB/VBA code, and
frankly, with the tacked-on class support in VB/VBA, there are some problems
with trying to do that and have any type-safety as well. I thought I would
share some of what I've come to think about this after dealing with it several
times of late.
First, an example. Let's say I have several classes, each with a string
property called Name, and I have several cases in which I want to make VBA
collections of one or the other of these classes using each item's Name
property value its key in the collection.
For type safety, I could:
A) Make one function for each of these cases that takes an explicit object
type to add, but that's a ludicrous code duplication.
B) Create a common interface for these classes called, say INamed, but the way
VBA uses interfaces, for this to be reliable, I need 2 implementations of the
Name property, one for the class when referenced as its own type (Name), and
one for when referenced as an INamed type (INamed_Name), with one
implementaiton wrapping the other. That's a real mess, and it's a bigger mess
if more than one interface is involved.
Forgetting type safety:
C) Make one function to add any "named" class instance to a collection, but
have it use Object as the Item parameter type. This is late bound, so the
Name property must be looked up in the class definition for each call, and the
compiler can't tell you if you're trying to pass a non-named type.
In spite of the lack of type safety and the potential (but rarely significant)
performance issue, I've found that "C" is the most reasonable answer,
particularly if more than about 2 cases are involved.
Note that the use of automated testing can be a good substitute for type
safety in this case (much as double-entry accounting can be a good substitute
for or supplement to having Excel), but I realize that not many people will
ever do automated testing in VB/VBA. See http://timestream.net/learningxp/ to
follow the progress of some of us mavericks who do.