How do I use class modules to set up parent and child variables?

Question

First of all, excuse my explanation of my problem if I use incorrect terminology, I'm not experienced and self-taught.

I am learning to use class modules to set up "Objects" to easier reference variables and to run common functions. The issue I'm having is that I can't find information on how to set up a class module which can act as a collection to make use of the add function inherent in a collection.

For Example, I have my parent class module named clsSchool

in this class module, I have defined an object so that I can set a "child" class, clsTeacher

In my class module for clsTeacher, I have set a string variable Name. This is how my 2 class modules look.

clsSchool

Public Student As Object

clsTeacher

Public Name as String

In my module I have

modSchool

Set mySchool = New clsSchool
Set mySchool.Teacher = New clsTeacher

mySchool.Teacher.Name = "Jim"

At this point, my code is exactly what I want it is very easy to use the variable mySchool.Teacher.Name to recall "Jim", I can also use a while loop with mySchool.Teacher to recall various variables I have defined in my clsTeacher

I problem is that I want to add multiple teachers without having to set multiple classes at the top of my code as the number of teachers can vary. I.e. the following does work but has limitations.

modSchool

Set mySchool = New clsSchool
Set mySchool.Teacher1 = New clsTeacher
Set mySchool.Teacher2 = New clsTeacher
Set mySchool.Teacher3 = New clsTeacher

mySchool.Teacher1.Name = "Jim"
mySchool.Teacher2.Name = "Jack"
mySchool.Teacher3.Name = "John"

What I would like is something similar to how collections work. I.e. I want to find the total number of teachers (which varies) and create a for loop to create a new clsTeacher for each unique teacher. What I want is something like the following, but I'm stuck and I can't find any resources which help explain how to set up the following.

modSchool

Set mySchool.Teacher = New clsTeacher

n_teachers = 6

for i=1 to n_teachers
    mySchool.Teacher(i).Name = Range("A1").Offset(i,0)
Next i

This way I can easily recall the name of teacher 1 or teacher 2 and use while/for loops to do so.

Have a look at this: https://stackoverflow.com/questions/44965938/vba-creating-nested-class/44968814#44968814 — Kostas K., Jan 24 '18 at 07:14

score 1 · Answer 1 · answered Jan 24 '18 at 05:59

You can create a class (clsTeachers) which hides a collection of clsTeachers. Below is a sample of some of the things that can be done - effectively adding to what a Collection can do.

Private pTeachers as New Collection ' Debate: New in the declaration, or New in Class_Initialise?

Property Get Count() as Long
  Count = pTeachers
End Property

Sub AddTeacher(NewTeacher as clsTeacher)  ' enforces type. 
    pTeachers.Add(NewTeacher)
End Sub

Function SortTeachers1() as clsTeachers
Dim tNewTColl as clsTeachers
    'some sort routine using the existing collection and adding in order to a new class
    Set SortTeachers =  tNewColl
End Function

Sub SortTeachers2()
Dim tNewColl as New Collection
        'some sort routine using the existing collection and the new one
    Set pTeachers = tNewColl
End Sub

Sub PrintTeachers()
Dim tTchr as clsTeacher
    For each tTchr in pTeachers
        'valid print command or add to string for later printing
    Next 'tTchr
End Sub

The methods are only limited by your imagination. The one drawback I have found is no easy way to use this in a 'For Each' loop. The standard For I = 1 to MyTeachers.Count is a useful fallback - just not as neat. In the class you can have:

Property Get Teacher(Index as Long) as clsTeacher
    'error check here to ensure Index exists
    if tIndexValid then
        Teacher = pTeachers(Index)
    else
        Teacher = Nothing 'or however you want to handle this
    end if
End Property

I have found this way of doing collections useful because you can do validation and tailored outputs hidden from the main code view. As you can see by the signatures, you can also enforce types (i.e. your chosen collection cannot have anything but Teachers in it).

** some references for doing a for-each with custom collections:

https://www.mrexcel.com/forum/excel-questions/466141-use-custom-class-each-loop.html
How to implement custom iterable class in VBA which includes other links.

Thanks for the quick response, I'll try to work through what you have shown me and understand it better. But it sounds like this is exactly what I am after. Thanks! — Soggy, Jan 26 '18 at 00:42
Turns out I'm very confused. Is the main code you have provided within the class clsTeachers? Are you able to explain to me the following lines of code? I'm struggling to understand what is happening. Private pTeachers as New Collection ' Debate: New in the declaration, or New in Class_Initialise? Sub AddTeacher(NewTeacher as clsTeacher) ' enforces type. pTeachers.Add(NewTeacher) End Sub — Soggy, Jan 26 '18 at 01:09
I think, if I'm not mistaken the Addteacher sub should require a variable input like a string? I.e. AddTeacher(NewTeacher as String). This works well, it requires me to make a collection for every property. Is it possible to set a collection item as a class? — Soggy, Jan 26 '18 at 02:00
@Soggy Yes: main code is illustrative of what can go in `clsTeachers`. `AddTeacher` in my example takes a object of `clsTeacher` (your code) - in other words you create a teacher and then add it to the collection. There are ways of getting `clsTeachers` to add a `clsTeacher` given a string input but I left that out in this simple example mainly because as classes get more complex, nested creation also gets more complex. Again, my code above was illustrative and you can create whatever methods you need in your class. — AJD, Jan 26 '18 at 04:07

How do I use class modules to set up parent and child variables?

1 Answers1