Faking inheritance in VBA to remove code duplication

[Steve Jorgensen]

A while back, I started boning up on Software Engineering best practices and
learning about Agile programming. In the process, I've become much more
committed to removing duplication in code at a much finer level. As such,
it's very frustrating to be working in VBA which lacks inheritance, one of the
more powerful tools for eliminating duplication at the level I'm talking
about.

I've recently come up with a technique to emulate one special case of
inheritance that's handy in an awful lot of cases.

First off, yes, it's true that you can just simulate inheritance using
encapsulation, but then you have to add members to every single encapsulating
class to wrap every single public member of the encapsulated class. Each time
you add a member to the "base class" you have to meticulously update all the
"inheriting " classes as well.

So, here's my alternative for one common use case of inheritance. Note that
this is made more useful since other cases of inheritance can be force-fit
into this model. If this is too much to swallow cold, try looking at the code
example at the end, then come back to the text. The code is simpler than the
explanation.

First, the case I'm talking about is where the base class provides functions
and interfaces that are mutable primarily in terms of the metadata they use to
do their jobs. It is not enough, however, to simply initialize an instance
with a data structure, though, because not all the metadata is constant, or
one would not want to count on that being a permanent situation. In a typical
OOP language, this would be done by having private virtual members in the base
class that would be overridden by the inheriting class to supply data to the
base class.

Now, the model. I call this model the "Setup Class" model. It's sort of a
cross between a factory model and a decorator class. Instead of having the
"base class" contained in the "inheriting class", it's the other way around,
but the "inheriting class" (the Setup class) is what the external code creates
first, then uses that as sort of a factory for its base class which is then
stored by the external code, and now encapsulates the Setup class which now
provides the metadata for the Base class.
A simplified code example follows. This is air code, but I have similar code
in production. In my production system, I have one setup object for the
production database, and one for a testing database. The pre-setup for the
testing database makes a new copy from a template, so it is always in a known
state before the download begins. The production database has no pre-setup
action (like this example)...
- Usage example
Option Explicit

Public Sub TestDataReaderA bc()
Dim objDataReader As clsDataReader

' With block creates temporary reference to a new
' clsDataReaderSe tupAbc object, then that is used to
' create and return a new clsDataReader object that
' now encapsulates the clsDataReaderSe tupAbc object,
' and uses it for metadata, etc.
With New clsDataReaderSe tupAbc
Set objDataReader = .GetReader()
End With

With objDataReader
While Not .EndOfData
Debug.Print .DataItem
.NextItem
Wend
End With

End Sub
- clsDataReader
Option Explicit

Private mobjSetup As IDataReaderSetu p
Private mdbs As DAO.Database
Private mrst As DAO.Recordset

Public Sub Setup(objSetup As IDataReaderSetu p)
Dim strSql As String
Set mobjSetup = objSetup
With mobjSetup
.PreSetup
Set mdbs = CurrentDb
strSql = "SELECT " & .FieldName & _
" FROM " & .TableName & _
" ORDER BY " & .FieldName
End With
Set mrst = mdbs.OpenRecord set("",strSql)
End Sub

Public Property Get DataItem() As Variant
DataItem = mrst.Fields(mob jSetup.FieldNam e).Value
End Property

Public Sub NextItem()
mrst.MoveNext
End Property

Public Property Get EndOfData() As Boolean
EndOfData = mrst.EOF
End Property

Private Sub Class_Terminate ()
If Not (mrst Is Nothing) Then
mrst.Close: Set mrst = nothing
End If
Set mdbs = Nothing
End Sub
- IDataReaderSetu p
Option Explicit

Public Sub PreSetup() As clsDataReader
End Function

Public Function GetDataReader() As clsDataReader
End Function

Public Property Get TableName() As String
End Property

Public Property Get FieldName() As String
End Property
- clsDataReaderSe tupAbc (There could have many other Setup classes!)
Option Explicit

Implements IDataReaderSetu p

Public Sub IDataReaderSetu p_PreSetup() As clsDataReader
' No pre-setup actions required for this Setup class
End Function

Private Function IDataReaderSetu p_GetDataReader () As clsDataReader
Dim objResult As New clsDataReader
objResult.Setup Me
Set GetDataReader = objResult
End Function

Private Property Get IDataReaderSetu p_TableName() As String
TableName = "tblTest"
End Property

Private Property Get IDataReaderSetu p_FieldName() As String
TableName = "ItemName"
End Property

[David W. Fenton]

Steve Jorgensen <no****@nospam. nospam> wrote in
news:mp******** *************** *********@4ax.c om:

[a lot that I'm not sure I understood]

Uh, what are you getting out of this that simply exposing your
recordset variable as a public member won't accomplish?

That is, aren't you just recreating properties of the recordset
class in your wrapper class?

We discussed this kind of thing long ago when I posted about my
recordset wrapper class (code posted below sig -- note this is a
specific implementation where I was navigating a large totals
recordset).

How is what you're doing better than what I was suggesting?

(indeed, yours doesn't even allow the use of dynamic SQL)

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
clRecordsetWrap per
------------------
Option Compare Database
Option Explicit

Private db As Database
Private rs As Recordset ' THIS COULD BE MADE PUBLIC
Private strSQL As String
Private strCriteria As String
Private strReturnField As String

Public Property Let SQL(pstrSQL As String)
On Error GoTo err_SQL

strSQL = pstrSQL
'Debug.Print strSQL
Set rs = db.OpenRecordse t(strSQL)

exit_SQL:
Exit Property

err_SQL:
MsgBox Err.Number & ": " & Err.Description , vbExclamation, _
"Error in clRecordSetWrap per.SQL Property Let"
Resume exit_SQL
End Property

Public Property Get SQL() As String
SQL = strSQL
End Property

Public Function GetValue(pstrCr iteria As String, _
pstrReturnField As String) As Variant
strCriteria = pstrCriteria
strReturnField = pstrReturnField
If rs.RecordCount <> 0 Then
rs.FindFirst strCriteria
If Not rs.NoMatch Then
GetValue = rs(strReturnFie ld)
End If
End If
End Function

Public Function GetNextValue(Op tional pstrReturnField As String) _
As Variant
If Len(strCriteria ) = 0 Then
MsgBox "You must call GetValue before you can call _
GetNextValue", vbExclamation, _
"Error in clRecordSetWrap per.GetNextValu e()"
Exit Function
End If
If Len(pstrReturnF ield) > 0 Then strReturnField = pstrReturnField
If rs.RecordCount <> 0 Then
rs.FindNext strCriteria
If Not rs.NoMatch Then
GetNextValue = rs(strReturnFie ld)
End If
End If
End Function

Private Sub Class_Initializ e()
Set db = CurrentDb()
End Sub

Private Sub Class_Terminate ()
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

Public Property Get GetTotal(pstrCr iteria As String, _
pstrReturnField As String) As Double
Dim dblTotal As Double

If rs.RecordCount <> 0 Then
rs.FindFirst pstrCriteria
Do Until rs.NoMatch
If Not IsNull(rs(pstrR eturnField)) Then
dblTotal = dblTotal + rs(pstrReturnFi eld)
End If
rs.FindNext pstrCriteria
If rs.EOF Then Exit Do
Loop
End If
GetTotal = dblTotal
End Property

[Steve Jorgensen]

On Wed, 07 Apr 2004 18:29:18 GMT, "David W. Fenton"
<dX********@bwa y.net.invalid> wrote:

Steve Jorgensen <no****@nospam. nospam> wrote in
news:mp******* *************** **********@4ax. com:

[a lot that I'm not sure I understood]

Uh, what are you getting out of this that simply exposing your
recordset variable as a public member won't accomplish?
Not much - I guess that's the problem with simplified examples. It makes more
sense when the reader is handling a business object model more complex than a
single recordset.
That is, aren't you just recreating properties of the recordset
class in your wrapper class?
It my real-world case, there are several recordsets involved, and the class
encapsulates the logic of reading and writing related values while providing a
clear, simple API to the external code.
We discussed this kind of thing long ago when I posted about my
recordset wrapper class (code posted below sig -- note this is a
specific implementation where I was navigating a large totals
recordset).
I remember having that discussion, though not the specifics - I'll review
that.
How is what you're doing better than what I was suggesting?

(indeed, yours doesn't even allow the use of dynamic SQL)

The purpose of this pattern is not to allow things like dynamic SQL, and its
advantages don't have to do with being general-purpose. This is intended to
be a design pattern applied individually to specific cases, and not a reusable
library routine itself.

The goal of this pattern is to remove duplication in a specific application,
so that the majority of the code in the Setup class has -only- to do with what
makes that case unique, and has almost nothing to do with what's common to all
cases. The common code goes in the "base" class and is kept in one place,
never duplicated, and not subject to errors that can arise later in
maintaining code that contains such duplication.

I'll review the earlier thread tomorrow, and possobly post another reply. It
could be that your suggestion id better than what I'm suggesting here, but I
perhaps didn't have the context to apply it right away at the time. It's also
likely that what we have are 2 different answers to 2 different issues.

[rkc]

"David W. Fenton" <dX********@bwa y.net.invalid> wrote in message
news:Xn******** *************** ***********@24. 168.128.86...

Steve Jorgensen <no****@nospam. nospam> wrote in
news:mp******** *************** *********@4ax.c om:

[a lot that I'm not sure I understood]

Uh, what are you getting out of this that simply exposing your
recordset variable as a public member won't accomplish?

That is, aren't you just recreating properties of the recordset
class in your wrapper class?

We discussed this kind of thing long ago when I posted about my
recordset wrapper class (code posted below sig -- note this is a
specific implementation where I was navigating a large totals
recordset).

How is what you're doing better than what I was suggesting?

His point wasn't about wrapping a recordset. It was about
using a base class that didn't change to do similar, but
different things via a setup class. The setup class is
implemented using an interface so that even though there
may be multiple setup classes each one is accessed using
the same methods and properties expected by the base class.

His example was too simple to illustrate any advantage(s)
there may be in going that far around the barn while using
VBA. Someone who develops applications alone is probably
less likely to go that route, or even explore it, than someone
who often finds themselves working with others.

That's my take. I could be full of crap.

[Steve Jorgensen]

On Fri, 09 Apr 2004 02:31:15 GMT, "rkc" <rk*@yabba.dabb a.do.rochester. rr.bomb>
wrote:

"David W. Fenton" <dX********@bwa y.net.invalid> wrote in message
news:Xn******* *************** ************@24 .168.128.86...

Steve Jorgensen <no****@nospam. nospam> wrote in
news:mp******** *************** *********@4ax.c om:

[a lot that I'm not sure I understood]

Uh, what are you getting out of this that simply exposing your
recordset variable as a public member won't accomplish?

That is, aren't you just recreating properties of the recordset
class in your wrapper class?

We discussed this kind of thing long ago when I posted about my
recordset wrapper class (code posted below sig -- note this is a
specific implementation where I was navigating a large totals
recordset).

How is what you're doing better than what I was suggesting?

His point wasn't about wrapping a recordset. It was about
using a base class that didn't change to do similar, but
different things via a setup class. The setup class is
implemented using an interface so that even though there
may be multiple setup classes each one is accessed using
the same methods and properties expected by the base class.

His example was too simple to illustrate any advantage(s)
there may be in going that far around the barn while using
VBA. Someone who develops applications alone is probably
less likely to go that route, or even explore it, than someone
who often finds themselves working with others.

That's my take. I could be full of crap.

You have correctly sussed my intent, though I must say, my rambling
explanation probably wasn't much help. I also realized later that since the
external code must explicitly refer to both the "Base" and "Setup" classes,
that it was not right to call a Get<Base> method of the "Setup" class that
does nothing more than pass itself to the Setup method of the "Base" class.
That just ends up requiring duplication of the setup code in every "Setup"
class for no good reason.

When I turn that around, and simply make the external code make a "Setup"
class, and pass it to the "Base" class, I end up not having a "Setup" pattern
at all, but the previously well-documented "Template" pattern. It just so
happens that this works out in VBA since it is a pattern that does not rely in
implementation inheritance - cool. The benefit is more obvious (and more so)
with this fix in place.

Here's the fixed-up example. I'm still not totally clear which part is
supposed to be the Template in the "Template" pattern, so forgive me (and
correct me if possible) if the naming is inside out...

- Usage example
Option Explicit

Public Sub TestDataReaderA bc()
Dim objDataReader As clsDataReader

Set objDataReader = New clsDataReader
objDataReader.S etup New clsDataReaderSe tupAbc

With objDataReader
While Not .EndOfData
Debug.Print .DataItem
.NextItem
Wend
End With

End Sub
- clsDataReader
Option Explicit

Private mobjSetup As IDataReaderTemp late
Private mdbs As DAO.Database
Private mrst As DAO.Recordset

Public Sub Setup(objSetup As IDataReaderTemp late)
Dim strSql As String
Set mobjSetup = objSetup
With mobjSetup
.PreSetup
Set mdbs = CurrentDb
strSql = "SELECT " & .FieldName & _
" FROM " & .TableName & _
" ORDER BY " & .FieldName
End With
Set mrst = mdbs.OpenRecord set("",strSql)
End Sub

Public Property Get DataItem() As Variant
DataItem = mrst.Fields(mob jSetup.FieldNam e).Value
End Property

Public Sub NextItem()
mrst.MoveNext
End Property

Public Property Get EndOfData() As Boolean
EndOfData = mrst.EOF
End Property

Private Sub Class_Terminate ()
If Not (mrst Is Nothing) Then
mrst.Close: Set mrst = nothing
End If
Set mdbs = Nothing
End Sub
- IDataReaderTemp late
Option Explicit

Public Sub PreSetup() As clsDataReader
End Function

Public Property Get TableName() As String
End Property

Public Property Get FieldName() As String
End Property
- clsDataReaderTe mplateAbc (One could have many other Template classes)
Option Explicit

Implements IDataReaderTemp late

Public Sub IDataReaderTemp late_PreSetup() As clsDataReader
' No pre-setup actions required for this Setup class
End Function

Private Property Get IDataReaderTemp late_TableName( ) As String
TableName = "tblTest"
End Property

Private Property Get IDataReaderTemp late_FieldName( ) As String
TableName = "ItemName"
End Property

[rkc]

"Steve Jorgensen" <no****@nospam. nospam> wrote in message
news:1r******** *************** *********@4ax.c om...

On Fri, 09 Apr 2004 02:31:15 GMT, "rkc" <rk*@yabba.dabb a.do.rochester. rr.bomb> wrote:

When I turn that around, and simply make the external code make a "Setup"
class, and pass it to the "Base" class, I end up not having a "Setup" pattern at all, but the previously well-documented "Template" pattern. It just so
happens that this works out in VBA since it is a pattern that does not rely in implementation inheritance - cool. The benefit is more obvious (and more so) with this fix in place.

Here's the fixed-up example. I'm still not totally clear which part is
supposed to be the Template in the "Template" pattern, so forgive me (and
correct me if possible) if the naming is inside out...

It's early, but all I see is a basic use of containment. I think that if
either class
were to be considered a template in this situation it would be the setup
class.

[Steve Jorgensen]

On Fri, 09 Apr 2004 11:32:03 GMT, "rkc" <rk*@yabba.dabb a.do.rochester. rr.bomb>
wrote:

"Steve Jorgensen" <no****@nospam. nospam> wrote in message
news:1r******* *************** **********@4ax. com...

On Fri, 09 Apr 2004 02:31:15 GMT, "rkc"

<rk*@yabba.dab ba.do.rochester .rr.bomb>

wrote:

When I turn that around, and simply make the external code make a "Setup"
class, and pass it to the "Base" class, I end up not having a "Setup"

pattern

at all, but the previously well-documented "Template" pattern. It just so
happens that this works out in VBA since it is a pattern that does not

rely in

implementation inheritance - cool. The benefit is more obvious (and more

so)

with this fix in place.

Here's the fixed-up example. I'm still not totally clear which part is
supposed to be the Template in the "Template" pattern, so forgive me (and
correct me if possible) if the naming is inside out...

It's early, but all I see is a basic use of containment. I think that if
either class
were to be considered a template in this situation it would be the setup
class.

A template is a simple example of containment, but it's what the containment
is used for and how it is used to supstitute for a kind of inheritance that
makes it so useful. Before I had this pattern, I was trying to have the
"Template" be the container, but then there's not enough benefit to make it
worthwhile, since you have to make a forwarding stub method for every method
in the base class.

[Steve Jorgensen]

On Fri, 09 Apr 2004 15:24:19 GMT, Steve Jorgensen <no****@nospam. nospam>
wrote:

On Fri, 09 Apr 2004 11:32:03 GMT, "rkc" <rk*@yabba.dabb a.do.rochester. rr.bomb>
wrote:

"Steve Jorgensen" <no****@nospam. nospam> wrote in message
news:1r****** *************** ***********@4ax .com...

On Fri, 09 Apr 2004 02:31:15 GMT, "rkc"

<rk*@yabba.da bba.do.rocheste r.rr.bomb>

wrote:

When I turn that around, and simply make the external code make a "Setup"
class, and pass it to the "Base" class, I end up not having a "Setup"

pattern

at all, but the previously well-documented "Template" pattern. It just so
happens that this works out in VBA since it is a pattern that does not

rely in

implementation inheritance - cool. The benefit is more obvious (and more

so)

with this fix in place.

Here's the fixed-up example. I'm still not totally clear which part is
supposed to be the Template in the "Template" pattern, so forgive me (and
correct me if possible) if the naming is inside out...

It's early, but all I see is a basic use of containment. I think that if
either class
were to be considered a template in this situation it would be the setup
class.

A template is a simple example of containment, but it's what the containment
is used for and how it is used to supstitute for a kind of inheritance that
makes it so useful. Before I had this pattern, I was trying to have the
"Template" be the container, but then there's not enough benefit to make it
worthwhile, since you have to make a forwarding stub method for every method
in the base class.

Well, dang. I just did some more research, and I don't think this is quite
the "Template" method either, nor quite the "Strategy" pattern, but it's kind
of like those. It seems like this must be a documented pattern, but I can't
figure out which one.

It turns out the Template pattern is about allowing the inheriting class to be
able to insert functionality at the beginning and/or end of an operation
implemented in the base class. A perfect example of this is the BeforeUpdate
and AfterUpdate event handlers in Access form modules.

If it were the Strategy pattern, the "Base" class would be called the Abstract
class, and the "Inheriting " classes would be called "Concrete" classes. In
that metaphor, though, the Template class would usually be very light, and
most of the implementation would belong to the Concrete classes (like using a
Socket API (abstract) for a communication protocol (concrete)). Otherwise,
that's close.

The "Entity" design pattern
(http://www.codeproject.com/gen/desig...ignpattern.asp) also looks
close, but also seems not quite right. I can see how what I'm starting with
might evolve to look the Entity design pattern as the application is fleshed
out.

I guess I'm still not sure what pattern I'm describing, but I do think it's a
very useful pattern in VBA and solves some problems one would normally think
of using inheritance to solve in a true OOP language.

[David W. Fenton]

Steve Jorgensen <no****@nospam. nospam> wrote in
news:3a******** *************** *********@4ax.c om:

I guess I'm still not sure what pattern I'm describing, but I do
think it's a very useful pattern in VBA and solves some problems
one would normally think of using inheritance to solve in a true
OOP language.

I simply can't separate the abstract part of your example from the
specific type of object you're encapsulating.

Can you do this with something other than a recordset?

For instance: I use dialog forms to collect criteria for filtering
reports and forms. What if one had a generic GetCriteria class that
was a wrapper around specific classes that encapsulated particular
dialog forms? Let's ignore for a moment that this is three levels of
classes (the form is itself a class), because your recordset example
also had that characteristic (which is perhaps why I was thrown
off).

My concept here is that you'd use a single GetCriteria class with a
fixed interface to utilize all the dialog forms for collecting
criteria.

I assume that's what you're trying to do, no?

My problem here is how you avoid duplication -- how do you enumerate
the properties and methods and members of the particular dialog
class via the generic class without setting up a bunch of interfaces
to custom private collections?

Or do I have the whole thing turned upside down here?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[rkc]

"Steve Jorgensen" <no****@nospam. nospam> wrote in message
news:3a******** *************** *********@4ax.c om...

I guess I'm still not sure what pattern I'm describing, but I do think it's a very useful pattern in VBA and solves some problems one would normally think of using inheritance to solve in a true OOP language.

That's the thing about design patterns. You really have to have a complete
understanding of the types of problems each one addresses before you
can recognize that the problem you face already has a solution.

I think you would need to explain more about the meta-data concept that
you mentioned before I can see what you have described as anything other
than using two of three methods available in VBA to simulate inheritance.