VBA for Office Class Module (Object) Factory

Question

I have a relatively complex application for Access 2007 written in VBA (4 enumerations, 7 modules, 38 class-modules, 86 forms, and a whole slew of tables and queries) . I've found a situation where it would be beneficial to use an Object Factory design, but so far I am unable to find a clean way to implement this type of functionality without the standard abstract/inheritance that is easily accomplished in VB or C#.

Has anyone had any experience implementing a factory design in VBA, and is it even possible? ... Or is there a neat "trick" that can help me obtain the same overall goal?

My experience on factory design is limited to C#, and i've never done it in VB, so maybe there is something in VBA that is common to VB that I am missing.

Example

I will be receiving a specific date. based on that date i will need to calculate anywhere between 2 and 5 other dates. the rules for calculating these dates changes based on the "type" of date being entered.

So if I have a date of 07/15/2009, and this is a type 1 date it would return

07/15/2010 for date 1, 07/15/2011 for date 2, 07/15/2012 for date 3, 06/10/2012 for date 4 and 07/10/2012 for date 5

if i put the same date but put it in as a date type 2 i would get null for date 1, null for date 2, null for date 3, 06/10/2011 for date 4 and 07/10/2011 for date 5

so for each set of rules there will be a minimum of 3 possible max of 6 (for now this could always expand at any time) i will basically be entering a starting date... the rule... and returning an object that will contain all of the date properties.

I hope that helps a little bit.

Wow ! Access 2008 ! Where did you find THAT ? A see sharp special ? — iDevlop, Jan 27 '11 at 19:59
/facepalm my mistake. 2007. At least in my defense the 7 and 8 are right beside each other on the keyboard > — Patrick, Jan 27 '11 at 20:01
These aren't really "factory"-specific answers, but you might find them helpful re: inheritance and object creation in VBA. In short, it's a pain. See: http://stackoverflow.com/questions/3669270/vba-inheritance-analog-of-super/3671434#3671434 and http://stackoverflow.com/questions/1731052/is-there-a-way-to-overload-the-constructor-initialize-procedure-for-a-class-in/1744818#1744818 — jtolle, Jan 27 '11 at 22:23
I think you'd likely get better answers with a less abstract question. We know VBA can't do exactly what you're asking for in the general case, but if you'd describe a specific task where you'd like to apply the "factory" approach, perhaps old Access hands can give you advice on how to code it up more efficiently. — David-W-Fenton, Jan 29 '11 at 01:11

awrigley · Accepted Answer · 2011-01-28T08:22:28.890

I've probably missed the point of the question, but why not have a "factory" method / "constructor" in a standard module:

'default constructor
    Public Function MyClassFactory() As MyClass
        Set MyClassFactory = New MyClass
    End Function

Or, if you need a "constructor" with parameters:

'Constructor with parameters
    Public Function MyClassFactory(Param1 As ParamObject1, Param2 As ParamObject2) As MyClass
        Dim MyThing As MyClass
        Set MyThing = New MyClass

    'MyObjectInitializer is a Sub that does what a constructor should do
        MyThing.MyObjectInitializer Param1, Param2
        Set MyClassFactory = MyThing
    End Function

Etc, etc.

If you ALWAYS create MyObject instances using this, then this "Factory Pattern" replaces the constructor.

You can modify this code to only create singletons, etc. Sometimes, VBA's downsides (eg, standard modules having global scope) can be turned into something useful.

To call it you just do:

Dim Thing As MyClass
Set Thing = MyClassFactory(Param1, Param2)

With this kind of thing you are pretty close to having a constructor... or a Factory...

I must be missing something. My understanding of the Factory pattern is probably too simplistic, but then you probably don't want to get too complex in VBA. If you find you need to, there is probably a design issue.

That's how I do it. I IS Painful not having a real constructor . . . — XIVSolutions, Jan 28 '11 at 02:48
Sorry, I've just now gotten the time to get back to this project... yes, i see where you are coming from and it does make sense... but the factory will have to be able to actually return different objects... like i would have a base "abstract" class, and 4 - 6 classes that inherit that class. the factory would decide which of the inheriting classes to return based on the information given to it... — Patrick, Feb 01 '11 at 18:42

bravesparrow · Answer 2 · 2017-02-20T03:37:08.390

Here is a way to implement a Factory Pattern in VBA that is described very well on the Rubberduck website https://rubberduckvba.wordpress.com/2016/07/05/oop-vba-pt-2-factories-and-cheap-hotels/. Here is my attempt to explain it. I know there is probably a more concise way to do this but I am trying to demonstrate two things: the use of a factory pattern and dependency injection to construct objects without having to New them up; and the ability to use polymorphism in VBA, so that there can be multiple different implementations of an abstract interface class. Open to feedback. Here goes:

Create a simple Interface class called IExampleClass and set the following members on it:

Option Explicit

Public Property Get Firstname() As String
End Property

Public Property Get Lastname() As String
End Property

Public Function ToString() As String
End Function

Create an implementation class, ExampleClass. Note this has a Create method. This is your factory method. Also notice the Self getter, which allows the Create method to use a really neat syntax:

Option Explicit     
Private Type TExample
    Firstname As String
    Lastname As String
End Type

Private this As TExample

Implements IExampleClass

Public Property Get Firstname() As String
    FirstName = this.Firstname
End Property

Public Property Let Firstname(Value As String)
    this.Firstname = Value
End Property

Public Property Get Lastname() As String
    Lastname = this.Lastname
End Property

Public Property Let Lastname(Value As String)
    this.Lastname = Value
End Property

Public Property Get Self() As IExampleClass
    Set Self = Me
End Property

Public Function Create(ByVal First As String, ByVal Last As String)
    With New ExampleClass
        this.Firstname = First
        this.Lastname = Last
        Set Create = Self
    End With
End Function

Private Property Get IExampleClass_Firstname() As String
    IExampleClass_Firstname = this.Firstname
End Property

Private Property Get IExampleClass_Lastname() As String
    IExampleClass_Lastname = this.Lastname
End Property

Private Function IExampleClass_ToString() As String
    IExampleClass_ToString = this.Firstname & " " & this.Lastname
End Function

Note how in this class the implementation of each of the members in the Interface has a private signature, so code that consumes this ExampleClass can only access the ToString method from the IExampleClass interface (abstract) object.

Now lets Create another class which implements the IExampleClass interface, calling it BackwardsExampleClass:

   Option Explicit
   Private Type TExample
       Firstname As String
       Lastname As String
   End Type

   Private this As TExample

   Implements IExampleClass

   Public Property Get Firstname() As String
       Firstname = this.Firstname
   End Property

   Public Property Let Firstname(Value As String)
       this.Firstname = Value
   End Property

   Public Property Get Lastname() As String
       Lastname = this.Lastname
   End Property

   Public Property Let Lastname(Value As String)
       this.Lastname = Value
   End Property

   Public Property Get Self() As IExampleClass
       Set Self = Me
   End Property

   Public Function Create(ByVal First As String, ByVal Last As String)
       With New ExampleClass
           this.Firstname = First
           this.Lastname = Last
           Set Create = Self
       End With
   End Function

   Private Property Get IBackwardsExampleClass_Firstname() As String
       IExampleClass_Firstname = this.Firstname
   End Property

   Private Property Get IBackwardsExampleClass_Lastname() As String
       IExampleClass_Lastname = this.Lastname
   End Property

   Private Function IBackwardsExampleClass_ToString() As String
      IExampleClass_ToString = this.Lastname & ", " & this.Firstname
   End Function

Here is the trick to make this Factory Class work, so that yo do not need to use the New Keyword to use the factory, and so you can use Dependency Injection. This is the trick which allows you to set the Factory as a Singleton. Now... you need to Remove ExampleClass and BackwardsExampleClass from your project, export it into a folder, open each .cls file in a text editor, set the Predeclared Attribute to "True", save each .cls file, and re-import both class files into your project. What this does is create a default instance of both of these "Factory" classes that implement the IExampleClass interface.
Now Type into the immediate pane:
```
   Debug.print ExampleClass.Create("John","Smith").ToString
```
and it will return the output "John Smith"
Next Type into the immediate pane:
```
   Debug.print BackwardsExampleClass.Create("John","Smith").ToString
```
and it will return the output "Smith, John"

LOL thanks. I don't even work for the company that I had that project with anymore, much less do anything in VBA. Hopefully if someone in the future wonders across this question, your answer might add perspective to what they are looking to do. — Patrick, Feb 21 '17 at 12:51
Pleasure, this is my first attempt to answer a question on SE. Hope it makes sense to someone in the future. Cheers. — bravesparrow, Mar 09 '17 at 00:11
Your code is not really initializing new instances of ExampleClass and BackwardsExampleClass correctly, it simply reuses the predeclared instances. If you check the article you are referencing, the Create method needs to set properties of New ExampleClass inside the With block, while you are setting properties of the existing predeclared instance. — Kirill Tkachenko, Apr 08 '21 at 09:56

VBA for Office Class Module (Object) Factory

2 Answers2