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) :think:

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) :think:

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:

18739
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