Creating Class properties with sub levels

Question

I've been reading this topic on how to use class modules.

My goal is to improve my code performance and readability so I think I'm in the right path.

But I have some questions about the limitations.

In my head i want to do this:

Is it possible to achieve such a structure?

The topic I've read has very few examples and this is not handled. I'm assuming this would be possible with collections of collections, but I not sure how to look for this.

My data comes from 2 tables, one has all the items but the department and the other one has the ID's alongisde the departments. Both tables have the dates of the current month as headers and their Schedule/Department depending on the table.

I'd know how to achieve this for one day, but not for a whole month.

This is how I wrote the basics for my class:

Option Explicit
Private DirNeg As String
Private Agrup As String
Private DNI As String
Private Centro As String
Private Servicio As String
Private Nombre As String
Property Get Business() As String
    Business = DirNeg
End Property
Property Let Business(ByVal sBusiness As String)
    DirNeg = sBusiness
End Property
Property Get Group() As String
    Group = Agrup
End Property
Property Let Group(ByVal sGroup As String)
    Agrup = sGroup
End Property
Property Get ID() As String
    ID = DNI
End Property
Property Let ID(ByVal sID As String)
    DNI = sID
End Property
Property Get Location() As String
    Location = Centro
End Property
Property Let Location(ByVal sLocation As String)
    Centro = sLocation
End Property
Property Get Service() As String
    Service = Servicio
End Property
Property Let Service(ByVal sService As String)
    Servicio = sService
End Property
Property Get Name() As String
    Name = Nombre
End Property
Property Let Name(ByVal sName As String)
    Nombre = sName
End Property

On the other hand, is it correct to fill the whole class on the Class_Initializeevent? My data will always be the same so I don't need to loop in a normal module to fill the class, it could be done everytime the class is created.

EDIT/UPDATE:

This is how my data looks like:

Schedules alongside Agent's info

Departments alongside Agent's ID

clAgent Class Module:

Option Explicit
Private DirNeg As String
Private Agrup As String
Private DNI As String
Private Centro As String
Private Servicio As String
Private Nombre As String
Private Fechas As Object
Property Get Business() As String
    Business = DirNeg
End Property
Property Let Business(ByVal sBusiness As String)
    DirNeg = sBusiness
End Property
Property Get Group() As String
    Group = Agrup
End Property
Property Let Group(ByVal sGroup As String)
    Agrup = sGroup
End Property
Property Get ID() As String
    ID = DNI
End Property
Property Let ID(ByVal sID As String)
    DNI = sID
End Property
Property Get Location() As String
    Location = Centro
End Property
Property Let Location(ByVal sLocation As String)
    Centro = sLocation
End Property
Property Get Service() As String
    Service = Servicio
End Property
Property Let Service(ByVal sService As String)
    Servicio = sService
End Property
Property Get Name() As String
    Name = Nombre
End Property
Property Let Name(ByVal sName As String)
    Nombre = sName
End Property
Property Get clFechas(ByVal StringKey As String) As clFechas
    With Fechas
        If Not .Exists(StringKey) Then
            Dim objFechas As New clFechas
            .Add StringKey, objFechas
        End If
    End With

End Property
Private Sub Class_Initialize()

    Set Fechas = CreateObject("Scripting.Dictionary")

End Sub

clFechas Class Module:

Option Explicit
Private Modos As Object
Private Horarios As Object
'Aqiço creamos la propiedad Modo para la clase Fecha
Public Property Get Modo(ByVal StringKey As String) As String
    Modo = Modos(StringKey)
End Property
Public Property Let Modo(ByVal StringKey As String, ByVal StringValue As String)
    Modos(StringKey) = StringValue
End Property
Public Property Get Keys() As Variant
    Keys = Modos.Keys
End Property
'Aquí creamos la propiedad Horario para la clase Fecha
Public Property Get Horario(ByVal StringKey As String) As String
    Modo = Horarios(StringKey)
End Property
Public Property Let Horario(ByVal StringKey As String, ByVal StringValue As String)
    Horarios(StringKey) = StringValue
End Property
Public Property Get Keys() As Variant
    Keys = Horarios.Keys
End Property
'Iniciamos la clase
Private Sub Class_Initialize()
    Set Modos = CreateObject("Scripting.Dictionary")
    Set Horarios = CreateObject("Scripting.Dictionary")
End Sub
Private Sub Class_Terminate()
    Set Modos = Nothing
    Set Horarios = Nothing
End Sub

See this. You may wanna change the dictionaries to collections, depending on your requirements. https://stackoverflow.com/questions/44965938/vba-creating-nested-class/44968814#44968814 — Kostas K., Sep 04 '19 at 10:05
Thank you @KostasK. gonna read it, and about the second question? Is it a bad practice to fill the whole class upon `Class_Init`? — Damian, Sep 04 '19 at 10:08
That depends. It's not inherently good or bad to do so in the `Initialize` event. However - I do prefer to determine the time of populating my object's properties with data myself, so I most of the time write my own (Public) `Init` method. Which then can be called either in the `Initialize` class event or independent from other parts of the application. Writing your own `Init` function also has the advantage of being able to return a result back to the caller, even it is just a boolean `True/False`. — Hel O'Ween, Sep 04 '19 at 11:51
I agree with @Hel O'Ween. In general, avoid code that could throw errors in the object initializer. Let the object be created and call a public method to fill with data. You can even pass parameters to the method and handle exceptions. You should instantiate other objects in the initializer though. — Kostas K., Sep 04 '19 at 13:54
@KostasK. I'm trying to implement your answer to my needs but i'm having troubles with this: `Public Property Get Department(ByVal StringKey As String) As Department` where is this `As Department` coming from? My structure is... Agents have ID, Location, Service.. Also Days, and Days have Deparments and Schedules for that agent. Is your example valid for my purpose? — Damian, Sep 04 '19 at 13:58
Let me update my question with screens for the data so its clearer, also will update the code with what I did following your answer. — Damian, Sep 04 '19 at 14:07
@KostasK. Updated my question with the screenshots and the code I have so far with your answer. — Damian, Sep 04 '19 at 14:14
[YT video](https://www.youtube.com/watch?v=eAu9ePz1tgM) discussing collection vs. dictionary, — John Alexiou, Sep 04 '19 at 15:35
I recommend [About Class Modules](https://rubberduckvba.wordpress.com/2019/07/08/about-class-modules/) and related articles.. Also look at [Matthieu](https://codereview.stackexchange.com/users/23788/mathieu-guindon)'s posts on Codereview on vba . — ComputerVersteher, Sep 04 '19 at 16:14
BTW, @Damian, if you can snatch a used copy of [Debora Kurata's Doing Objects...](https://www.amazon.com/Doing-Objects-Visual-Basic-6/dp/1562765779) book, it might be worth the expense. It helped me a lot back in the day. It's a VB6 book, but as VBA/VB6 is basically identical, the concepts in there apply to VBA and most of the code shown also works "as is". — Hel O'Ween, Sep 05 '19 at 08:38

Kostas K. · Accepted Answer · 2019-09-05T08:40:31.980

You don’t seem to have any issues with regular properties so let’s focus on the complex ones; Schedule and Department. Both are the same, so same rules apply to both.

The property is basically list, the date is the index and the item is an object. I personally prefer to work with dictionaries as I can look if a key exist etc.

So, your Agent class could look something like this:

Option Explicit
Private m_schedules As Object

Public Property Get Schedule(ByVal Key As Date) As Schedules
    With m_schedules
        If Not .Exists(Key) Then .Add Key, New Schedules
    End With
    Set Schedule = m_schedules(Key)
End Property

'For testing purposes - can be ommited.
Public Property Get Keys() As Variant
    Keys = m_schedules.Keys
End Property

'For testing purposes - can be ommited.
Public Property Get Count() As Long
    Count = m_schedules.Count
End Property

Private Sub Class_Initialize()
    Set m_schedules = CreateObject("Scripting.Dictionary")
End Sub

Private Sub Class_Terminate()
    Set m_schedules = Nothing
End Sub

The Schedules class:

Option Explicit
Private m_schedule As String

Public Property Get Schedule() As String
    Schedule = m_schedule
End Property
Public Property Let Schedule(ByVal param As String)
    m_schedule = param
End Property

Now, let's test it:

Sub Test()

    Dim obj As Agent
    Set obj = New Agent

    obj.Schedule(#1/9/2019#).Schedule = "Schedule 1"
    obj.Schedule(#2/9/2019#).Schedule = "Schedule 2"
    obj.Schedule(#3/9/2019#).Schedule = "Schedule 3"

    PrintToDebug obj

    'Lets make a change
    obj.Schedule(#2/9/2019#).Schedule = "Schedule 2222"

    PrintToDebug obj

End Sub


Private Sub PrintToDebug(ByVal obj As Agent)

    Debug.Print ""

    Dim m As Variant
    With obj
        For Each m In .Keys
            Debug.Print "Key: " & m & String(3, " ") & "Value: " & .Schedule(m).Schedule
        Next m
    End With

    Debug.Print "Total Items: " & obj.Count
End Sub

Output:

'Key: 09/01/2019   Value: Schedule 1
'Key: 09/02/2019   Value: Schedule 2
'Key: 09/03/2019   Value: Schedule 3
'Total Items: 3

'Key: 09/01/2019   Value: Schedule 1
'Key: 09/02/2019   Value: Schedule 2222
'Key: 09/03/2019   Value: Schedule 3
'Total Items: 3

Additional information regarding the Dictionary object can be found here: Dictionary object

Also keep this in mind. It's quite important:

If key is not found when changing an item, a new key is created with the specified newitem. If key is not found when attempting to return an existing item, a new key is created and its corresponding item is left empty.

If the dictionary item is not a simple string, let me know to update the answer. Sorry, I couldnt read the data in the screenshots. :)

Thank you a lot! I'm gonna try to implement this.. And regarding the dictionary don't worry, I'm used to them and understand how they work! — Damian, Sep 04 '19 at 15:49
Now I've read the code... This is part of my need but I want to be able to do this: `obj.Schedule(#1/9/2019#).Schedule = "Schedule1"` as well as `obj.Schedule(#1/9/2019#).Department= "Department1"` Is it possible to do so? — Damian, Sep 04 '19 at 15:58
See updated answer. Instead of a string, you just need to return a Schedule object. Note, no setter property here. You will do the same with Department. — Kostas K., Sep 04 '19 at 16:14
Genius!!!!!! Appreciate this a lot! Now I can go forward learning with class modules! — Damian, Sep 04 '19 at 16:35

Creating Class properties with sub levels

1 Answers1

Linked