Excel vba - class constructor method not working, debug error

Question

i have this class and i am trying to make a class constructor or factory method (not sure how's the right name in VBA). When i try to run it i get a dialog with written debug error, and ig highlights the set row of the test module. What's wrong? What is the right way to instantiate the collection in the constructor? is it better to use the keyword this when using let/get ?

Class Address

Private pStreet As String
Private pZip As Integer

Public Property Let Street(val As String)
   pStreet = val
End Property
Public Property Get Street() As String
   Street = pStreet
End Property
Public Property Let Zip(val As Integer)
   pZip = val
End Property
Public Property Get Zip() As Integer
   Zip = pZip
End Property

Class Person

Private pName As String
Private pSurname As String
Private pAddresses As New Collection

Public Property Let Name(val As String)
    pName = val
End Property
Public Property Get Name() As String
    Name = pName
End Property

Public Property Let Surname(val As String)
    pSurname = val
End Property
Public Property Get Surname() As String
    Surame = pSurname
End Property
Private Sub Class_Initialize()
    Set pAddresses = New Collection
End Sub
Private Sub Class_Terminate()
    Set pAddresses = Nothing
End Sub
Public Sub addAddress(ByVal val As Address)
    pAddresses.Add val
End Sub
Public Property Get Addresses() As Collection
    Set Addresses = pAddresses
End Property
Public Property Get Address(ByVal Index As Long) As Address
    Set Address = pAddresses(Index)
End Property

Public Function CreatePerson(ByVal Name As String, ByVal Surname As String) As Person
    With New Person
        .pName = Name
        .pSurname = Surname
        Set CreatePerson = .Self
        instance
    End With
End Function

test Module

sub test()
Dim x as Person
Set x = Person.CreatePerson("Mike","Jordan")
end sub

You need to create a new Person instance before you can use it, unless you have edited the class outside of Excel to make it auto-instantiating. — Rory, Apr 05 '22 at 10:38
isn't it what i do in the test module? i'm struggling to learn the syntax. I tried to read the official microsoft docs, and if i paste their code on excel i get errors... where else should i read the right syntax???? — antonioa7, Apr 05 '22 at 10:50
No, you declared it but never created an instance of it. You need `Set x = New Person` but then I don't see the point of the next line. In all honesty, most of that doesn't make sense to me - what is `GiantComp` for example? — Rory, Apr 05 '22 at 10:55
Note that: `Private pZip as Int` but then you `Let` and `Get` `as String`... — Robin Mackenzie, Apr 05 '22 at 11:12
Then, there is confusion because you declare `Private pAddresses as New Address` but have `pAddress.Add val` in `addAddress`. Perhaps you want `pAddresses` to be a `Collection` or something, but then you don't refer to it in `addAddress` (possible typo). — Robin Mackenzie, Apr 05 '22 at 11:16
Does this answer your question? [Class (Static) Methods in VBA](https://stackoverflow.com/questions/396117/class-static-methods-in-vba) — Robin Mackenzie, Apr 05 '22 at 11:19
You have no self function e.g. Friend Function Self() as Person — freeflow, Apr 05 '22 at 11:32
i'm sorry i've been doing few tests and i pasted the wrong code, now i fixed the typos... should i always do set x = new person even if i'm using the constructor? — antonioa7, Apr 05 '22 at 11:55

Brian M Stafford · Accepted Answer · 2022-04-05T15:40:09.983

2

Another option for creating a factory method is to use another class:

PersonFactory Class

Option Explicit

Public Function Create(ByVal Name As String, ByVal Surname As String, ByVal Street As String, ByVal Zip As Integer) As Person
   Dim a As Address
   
   Set Create = New Person
   Create.Name = Name
   Create.Surname = Surname
   
   Set a = New Address
   a.Street = Street
   a.Zip = Zip
   Create.Addresses.Add a
End Function

Test Module

Private Sub Test()
   Dim pf As PersonFactory
   Dim p As Person
   
   Set pf = New PersonFactory
   Set p = pf.Create("Mike", "Jordan", "my street", 11111)
End Sub

edited Apr 05 '22 at 15:40

answered Apr 05 '22 at 13:12

Brian M Stafford

8,483
2
16
25

it works! Thanks! how to add also the collection inside the create? – antonioa7 Apr 05 '22 at 15:26
@antonioa7 I modified my code to show one way of adding the address. You could also pass an Address object if you prefer. – Brian M Stafford Apr 05 '22 at 15:42

score 1 · Answer 2 · answered Apr 05 '22 at 12:01

You have several errors

Your anonymous new is for GiantCorp and Not Person
You have no self method to return the Me Instance created by the With New Person 3 No idea what 'instance' is doing.
Your address class does not manage a collection of addresses, nor does you person class

Here is updated code for your person class. Don't feel too bad, Factory classes in VBA are actuall a tricky subject when you first encounter them.

Option Explicit
'@PredecalredId
'@exposed

Private Type Properties

    Name                As String
    Surname             As String
    Address             As Address
    
End Type

Private p               As Properties


Public Property Let Name(ipName As String)
    p.Name = ipName
End Property
Public Property Get Name() As String
    Name = p.Name
End Property

Public Property Let Surname(ipSurname As String)
    p.Surname = ipSurname
End Property
Public Property Get Surname() As String
    Surame = p.Surname
End Property

' This property will fail as the Address class is not a collection
Public Sub addAddress(ipAddress As Address)
   Set p.Address = ipAddress
End Sub

Public Function CreatePerson(ByVal ipName As String, ByVal ipSurname As String) As Person
    With New Person 'GiantComp  no idea what this GiantComp' class is doing here
        ' Private fields cannot be accessed here, you need to forward them to the self function
        '.pName = ipName
        '.pSurname = ipSurname
        Set CreatePerson = .Self(ipName, ipSurname)
    End With
End Function

Public Function Self(ByVal ipName As String, ByVal ipSurname As String) As Person
' You are now inside the anonymous Person class you created with 'With nEw Person' so you can now access private fields
    p.Name = ipName
    p.Surname = ipSurname
    Set Self = Me
End Function

You will also need to set the PredeclaredId attribute. This involves either exporting you class, editing the relevant attribute and reimporting, or, much more conveniently, using the attribute annotation '@PredecaredId provided by the free and fantastic Rubberduck add in for VBA.

Good luck in creating an addresses collection class to manage you addresses. Lots of examples are available of how to wrap a collection to produce a collection class.

thank you so much!!!! i edited my post with few typos corrections. I also tried to run your code on excel but i still get the same debug error. Should i change the sub test() ? I used classes in tons of other languages but in vba its a true mess mostly due to the fact that i cannot find a documentation with a syntax. I went to microsoft docs and the syntax that i found it gets error when pasted on excel... how to solve? where can i read something? i found tons of completely different examples online with totally different syntax... — antonioa7, Apr 05 '22 at 12:53
The person and address classes need to be PublicNotCreatable, please check to make sure that the PredeclaredId is set to true. If you are using Rubberduck you need to refresh and correct the relevant code inspection. When this is done your test code works on my PC. — freeflow, Apr 05 '22 at 13:44
i have no idea what rubberduck is :D it's my first use of vba :D can you suggest me a place with a decent documentation or something to learn more of it? — antonioa7, Apr 05 '22 at 14:05

Excel vba - class constructor method not working, debug error

2 Answers2