I'm beginning to work with VBA class modules more in Excel and want to make sure that no properties can be used if the class hasn't been initialized. Below is the class and my attempt to at doing so.

Private Type helper
    Prop1 As Boolean
    Prop2 As Boolean
    Prop3 As String
    Prop4 As String
End Type

Private m_initialized As Boolean

Private this As helper

Public Sub Init(ByVal Prop1 As Boolean, ByVal Prop2 As Boolean, ByVal Prop3 As String, ByVal Prop4 As String)
    this.Prop1 = Prop1
    this.Prop2 = Prop2
    this.Prop3 = Prop3
    this.Prop4 = Prop4

    m_initialized = True
End Sub

Public Property Get Prop1() As Boolean
    If Not m_initialized Then ThrowNotInitializedError

    Prop1 = this.Prop1
End Property

Public Property Get Prop2() As Boolean
    If Not m_initialized Then ThrowNotInitializedError

    Prop2 = this.Prop2
End Property

Public Property Get Prop3() As String
    If Not m_initialized Then ThrowNotInitializedError

    Prop3 = this.Prop3
End Property

Public Property Get Prop4() As String
    If Not m_initialized Then ThrowNotInitializedError

    Prop4 = this.Prop4
End Property

Public Function Self() As Foo
    Set Self = Me
End Function

Private Sub ThrowNotInitializedError()
    Err.Raise 5, "Init() not run.", "Values weren't initially assigned. Make sure they are assigned before use"
End Sub

The repetition of If Not m_initialized Then ThrowNotInitializedError is the part that I don't like. I can already imagine myself forgetting to put it on a property. Are there any other options?

Testing in a standard module to make sure it worked as I expected

Sub TestingFooInitializeProtection()
    Dim properlyInitialized As Foo
    Set properlyInitialized = New Foo
    properlyInitialized.Init True, False, "Input3", "Input4"
    Debug.Print properlyInitialized.Prop1
    Debug.Print properlyInitialized.Prop2
    Debug.Print properlyInitialized.Prop3
    Debug.Print properlyInitialized.Prop4

    Dim improperlyInitialized As Foo
    Set improperlyInitialized = New Foo
    'improperlyInitialized.Init False, True, "input3", "input4"
    Debug.Print improperlyInitialized.Prop1 'If above line is left commented out error is thrown.
    Debug.Print improperlyInitialized.Prop2
    Debug.Print improperlyInitialized.Prop3
    Debug.Print improperlyInitialized.Prop4
End Sub
  • \$\begingroup\$ AIUI you're basically trying to create the notion of a Constructor? \$\endgroup\$
    – Vogel612
    Apr 11 '18 at 18:14
  • \$\begingroup\$ @Vogel612 I think so, basically because Visual Basic didn't has parametrized constructor (it does has empty constructors however). \$\endgroup\$
    – Alejandro
    Apr 11 '18 at 18:41
  • 2
    \$\begingroup\$ @Vogel612 Yes. As @Alejandro commented, VBA has Private Sub Class_Initialize() but it doesn't accept any parameters. I'm using Init as a poor mans option to act as a parameterized constructor. \$\endgroup\$
    – IvenBach
    Apr 11 '18 at 19:32
  • 1
    \$\begingroup\$ While a very interesting topic I'd be very happy to discuss, the code in this post isn't really up for review; we're looking at a hypothetical implementation of a theoretical scenario where Prop1...Prop4, Foo and helper (?) merely serve an illustrative purpose to better picture an idea, a concept, a design - which off-topic. Basically it's okay to solve a problem and wonder about the architecture; reviewers can tackle every aspect of it, including whether a creational pattern such as a factory could be put to good use. But we can't review a pattern by itself, out of its context. \$\endgroup\$ Apr 24 '18 at 2:06

A couple of quick points.

If you are setting up a class, don't encapsulate a Type inside of it - you are only repeating what a class does! I am not sure where this anti-pattern comes from.

Within VBA do not raise errors unless absolutely necessary. Error raising and trapping will lead to unintended consequences and you can never be confident that they will be handled properly. Always handle the error explicitly. For starters, they can disrupt global variables.

You can create a Function in a module (let's call it NewFoo) with the correct signature - which creates the Foo instance and runs the Init method. This will make it a bit easier in the rest of your code to ensure you have the right sequence.

In Excel, you could make use of the xlError values to return an error if not initialised. This means that all your properties would be Variants (which has its own issues), something along the line of:

Public Property Get Prop1() As Variant
    Prop1 = IIF(m_initialised, CBool(Prop1_var), CVErr(xlErrNA))
End Property

Also, explicitly set m_initialised in the Class_Initialize routine to False. In theory not needed, but I prefer to be explicit.


You're right, VBA does not allow overloading the init. However, the New keyword initializes the class. You can get around needing to overload the function like this -

Option Explicit

Private defaultFirstString As String
Private defaultSecondString As String
Private defaultThirdString As String
Private defaultFourthString As String

Private Sub Class_Initialize()
End Sub

Public Sub InitializeDefault()
     defaultFirstString = "default value"
     defaultSecondString = "default value"
     defaultThirdString = "default value"
     defaultFourthString = "default value"
End Sub

Public Sub InitializeWithValues(ByVal first As String, ByVal second As String, ByVal third As String, ByVal fourth As String)
     defaultFirstString = first
     defaultSecondString = second
     defaultThirdString = third
     defaultFourthString = fourth
End Sub

It's not great, but it's what we have. No reason to use the boolean as you can't create the object without initializing. Instead you can just test for default values, or make them vbNullString and test for that, but outside of the Class.

Now just use it like this -

Option Explicit

Public Sub main()
    Dim myObject As Class1
    Set myObject = New Class1

    Dim myFirstObject As New Class1

    Dim myOtherObject As New Class1
    myOtherObject.InitializeWithValues "one", "two", "three", "four"

End Sub

Not the answer you're looking for? Browse other questions tagged or ask your own question.