VBA Classes. Calling Methods with multiple parameters and static functions

Question

The following is an example class in a class module

Private mKey As String
Private mValue As String

'CREATE INSTANCE
Public Static Function CreateInstance(Key As String, Value As String) As ExampleClass
    Dim obj As New ExampleClass
    obj.InitialiseKey (Key)
    obj.InitialiseValue (Value)
    Set CreateInstance = obj
End Function

'INITIALISE
Public Sub InitialiseKey(Key As String)
    mKey = Key
End Sub

Public Sub InitialiseValue(Value As String)
    mValue = Value
End Sub

Public Sub InitialiseKVP(Key As String, Value As String)
    mKey = Key
    mValue = Value
End Sub

'GETTER
Public Property Get Value() As String
    Value = mValue
End Property

Public Property Get Key() As String
    Key = mKey
End Property

And the following code is in a regular module

Sub Example_1()
    Dim A As New ExampleClass
    A.InitialiseKey ("Key 1")
    A.InitialiseValue ("Value 1")
    Debug.Print A.Key
    Debug.Print A.Value
End Sub

Sub Example_2()
    Dim A As New ExampleClass
    'A.InitialiseKVP ("Key 2", "Value 2") 'DOES NOT COMPILE, EXPECTS '=' FOR SOME REASON
    Debug.Print A.Key
    Debug.Print A.Value
End Sub

Sub Example_3()
    Dim A As ExampleClass
    Set A = ExampleClass.CreateInstance("Key 3", "Value 3") 'RUNTIME ERROR 424, no object
    Debug.Print A.Key
    Debug.Print A.Value
End Sub

'Example_1' works, 'Example_3' is what i want to write. 'Example_2' was meant to be intermediate but does not even compile.

There is an answer on stackoverflow (Class (Static) Methods in VBA) essentially stating that methods can not be called. This seems odd to me since 'Example_1' calls a method and 'Example_3' compiles without error.

How do i get 'Example_2' to compile? How do i get 'Example_3' to work?

in example 2 just do `A.InitialiseKVP "Key 2", "Value 2"` and in example 3 do this `Dim A As New ExampleClass` and then `Set A = A.CreateInstance("Key 3", "Value 3")`. You **always** need an initialized instance of a class in VBA to call any of the members. There is no such concept as static classes in VBA. — , Jul 29 '14 at 12:06
Also, you may be interested in [THIS ANSWER](http://stackoverflow.com/questions/15224113/pass-arguments-to-constructor-in-vba) — , Jul 29 '14 at 12:11

z̫͋ · Accepted Answer · 2014-07-29T12:20:30.893

Example2 should be:

Sub Example_2()
    Dim A As New ExampleClass
    Call A.InitialiseKVP("Key 2", "Value 2")
    Debug.Print A.Key
    Debug.Print A.Value
End Sub

with the Call keyword, or alternatively A.InitialiseKVP "Key 2", "Value 2"

And as @mehow pointed out, you need to instanciate the class first, you cannot access a class like an object or a regular module:

Sub Example_3()
    Dim A As New ExampleClass
    Set A = A.CreateInstance("Key 3", "Value 3")
    Debug.Print A.Key
    Debug.Print A.Value
End Sub

But that doesn't make much sense, you should use a regular function for the instanciation:

Sub Example_3()
    Dim A As ExampleClass
    Set A = CreateInstance("Key 3", "Value 3") 'RUNTIME ERROR 424, no object
    Debug.Print A.Key
    Debug.Print A.Value
End Sub


Public Function CreateInstance(Key As String, Value As String) As ExampleClass
    Dim obj As New ExampleClass
    obj.InitialiseKey (Key)
    obj.InitialiseValue (Value)
    Set CreateInstance = obj
End Function

VBA Classes. Calling Methods with multiple parameters and static functions

1 Answers1