VBA Classes

[RickA]

Hi

I have quiet a bit of experience in VBA, but I am trying to learn classes.

Below is my example code. I am taking a set of data for a group of students and trying to perform some analysis on that data. So I have set up a student class (clsStudent) as follows:

Private vID As String
Private vName As String
Private vDoB As Date
Private vAge As Double
Private vSex As String
Private vPlaceofBirth As String
Private vExamScore() As Double

Public Property Get ID() As String
    ID = vID
End Property

Public Property Let ID(value As String)
    vID = value
End Property

Public Property Get Name() As String
    Name = vName
End Property

Public Property Let Name(value As String)
    vName = value
End Property

Public Property Get DoB() As Date
    DoB = vDoB
End Property

Public Property Let DoB(value As Date)
    vDoB = value
End Property

Public Property Get Age() As Double
    Age = vAge
End Property

Public Property Let Age(value As Double)
    vAge = value
End Property

Public Property Get Sex() As String
    Sex = vSex
End Property

Public Property Let Sex(value As String)
    vSex = value
End Property

Public Property Get PlaceofBirth() As String
    PlaceofBirth = vPlaceofBirth
End Property

Public Property Let PlaceofBirth(value As String)
    vPlaceofBirth = PlaceofBirth
End Property

Public Property Get ExamScore(Index As Integer) As Double
    ExamScore = vExamScore(Index)
End Property

Public Property Let ExamScore(Index As Integer, value As Double)
    vExamScore(Index) = value
End Property

Private Sub Class_Initialize()
    ReDim vExamScore(1 To noExams)
End Sub

Public Sub LoadExam(ByVal MyArray As Variant)  'MyArray() fails?
    Dim Index As Integer
     
    For Index = LBound(MyArray) To UBound(MyArray)
        vExamScore(Index) = MyArray(Index)
    Next
End Sub

I then iterate though the students as follows:

Option Explicit

Public StudentCollection As New Collection
Public noExams As Double
Public noStudents

Sub Main()

Dim tempStudent As clsStudent
Dim i As Long
Dim j As Long
Dim k As Integer

noExams = 5
noStudents = 3

For i = 1 To noStudents

    Set tempStudent = New clsStudent
    j = i + 1
    
    tempStudent.ID = "Student" & i
    tempStudent.Name = StudentSheet.Range("A" & j).value
    tempStudent.DoB = StudentSheet.Range("B" & j).value
    tempStudent.Age = StudentSheet.Range("C" & j).value
    tempStudent.Sex = StudentSheet.Range("D" & j).value
    tempStudent.PlaceofBirth = StudentSheet.Range("E" & j).value
  
    tempStudent.LoadExam Application.Transpose(Application.Transpose(StudentSheet.Range(Range("F" & j), Range("F" & j).Offset(0, noExams - 1)).value))

    StudentCollection.Add tempStudent, tempStudent.ID
Next i


For i = 1 To noStudents
    Debug.Print StudentCollection(i).Name
    For k = 1 To noExams
        Debug.Print StudentCollection(i).ExamScore(k)
    Next k
Next i

End Sub

This all works perfectly fine, but is it the best way? For example, I have seen a number of examples where the reading in of the student data would be done within a method in the clsStudent class module, or even done in a separate class module (say clsReadStudent).

Also, I want to do some analysis on each student (predict grades for example). Where is it best to include this method? Should I have an additional class module (say clsCalculateStudent) that performs these calculations and writes the data back into StudentCollection, or should it be done in clsStudent, or should it just be a normal sub within a normal module?

Basically I get the idea of using class modules for something like student data, but I don't know where I should be switching back to just using "normal" functions / subs?

And finally, watching the locals window shows that each Student in StudentCollection also gets a copy of vName, vDob, etc, as well as the expected Name, DoB, etc. Why am I doubling up on variables? And for the ExamScore array, whilst vExamScore exists for each Student in StudentCollection, the actual ExamScore does not, although I have no problem reading it with Debug.Print StudentCollection(i).ExamScore(k)

Thanks
Rick

[Aflatoon]

watching the locals window shows that each Student in StudentCollection also gets a copy of vName, vDob, etc, as well as the expected Name, DoB, etc. Why am I doubling up on variables? And for the ExamScore array, whilst vExamScore exists for each Student in StudentCollection, the actual ExamScore does not, although I have no problem reading it with Debug.Print StudentCollection(i).ExamScore(k)

Name, DoB etc are properties that read the variables vName, vDoB etc. Those variables must exist for each class instance or the properties would have no values to read.

You can't see ExamScore in Locals because it requires an argument to read it, unlike the other properties.

Regarding your more general questions, I would say that anything that only relates to a specific student should be part of the clsStudent class. If you need routines that access multiple students, you may want to look at creating a collection class of Students.

[RickA]

Hi

Thanks for your reply

I see what you are saying with the variables. I think the thing that was confusing me was that given vName, vDoB are private within the class, I was surprised they end up being in StudentCollection. However, Debug.Print StudentCollection(i),vName doesn't work (as expected), as only the property is public.

Let's say I want to add a variable AvgExamScore to each Student. Presumably I would have something like in clsStudent:

Public Property Get AvgExamScore() As Double
    AvgExamScore = vAvgExamScore
End Property

Public Sub CalcAvgExamScore()
    Dim ExamTotal As Double
    Dim Score As Variant
    ExamTotal = 0
     
    For Each Score In vExamScore
        ExamTotal = ExamTotal + Score
    Next
    vAvgExamScore = ExamTotal / noExams
End Sub

And then in Main()

tempStudent.CalcAvgExamScore

Again, this works, but it feels like I should be using me.ExamScore in the for loop rather than vExamScore, but that fails?

Thanks
Rick

[Aflatoon]

I think the thing that was confusing me was that given vName, vDoB are private within the class, I was surprised they end up being in StudentCollection.

I don't know what you mean by that - they are not in the collection.

Re the average score, I would calculate that directly in the property routine. You can't use your ExamScore property because it only returns one value and you have to pass an index. There would be no real benefit to that anyway since all it does is read a value from the private variable.

[RickA]

Screen grab of locals window showing vName, vDoB, etc as variables within StudentCollection:

Grab.jpg
All clear on your second point. Thanks.

Rick

[Aflatoon]

They're variables within an item of StudentCollection - i.e. within a Student - rather than within StudentCollection itself.

[RickA]

Ok. Understood. Thank you for all your help

Rick

[Jan Karel Pieterse]

Just a quick note on performance. If your data will have many students then you should probably reconsider the way you are adding data to the class instances as reading everything one cell at the time is very, very inefficient.

[RickA]

Just a quick note on performance. If your data will have many students then you should probably reconsider the way you are adding data to the class instances as reading everything one cell at the time is very, very inefficient.

OK, thanks. Any pointers? Dump the row into an array and extract the data from that, or dump the entire range?!

[Jan Karel Pieterse]

Best is to get the entire range. Even better is to use Excel formulas and functionality to solve the problem :-)

[Paul_Hossler]

Good write up at Chip's site


http://www.cpearson.com/Excel/Classes.aspx