This would be a perfect application of custom classes.
You would want two custom objects:
clsPerson has the properties of Name, Age, Gender, MaritalStatus and Race
clsPeople has a collection of clsPerson's, called Persons; a property Count, and a function Add
in a class module (named clsPerson) put
'in class module clsPerson
Public Index As Long
Public Collection As Collection
Dim pName As String
Dim pGender As Boolean
Dim pAge As Double
Dim pRace As String
Dim pMarital As String
Property Get Name() As String
Name = pName
End Property
Property Let Name(strName As String)
pName = StrConv(strName, vbProperCase)
If Index = 0 Then
pName = strName
Else
Collection.Remove Index
If Collection.Count < Index Then
Collection.Add Item:=Me, Key:=pName
Else
Collection.Add Item:=Me, Key:=pName, before:=Index
End If
End If
End Property
Property Get Gender() As String
Gender = "Female"
If pGender Then
Gender = "Male"
End If
End Property
Property Let Gender(strGender As String)
pGender = (LCase(strGender) = "male")
End Property
Property Get Age() As Double
Age = pAge
End Property
Property Let Age(dblAge As Double)
If (0 <= dblAge) Then pAge = dblAge
End Property
Property Get Race() As String
Race = pRace
End Property
Property Let Race(strRace As String)
pRace = StrConv(strRace, vbProperCase)
End Property
Property Get MaritalStatus() As String
MaritalStatus = pMarital
End Property
Property Let MaritalStatus(strMarital As String)
pMarital = StrConv(strMarital, vbProperCase)
End Property
Private Sub Class_Initialize()
pAge = -1
End Sub
Private Sub Class_Terminate()
Set Collection = Nothing
End Sub
in the class module clsPeople:
' in class module clsPeople
Public Persons As Collection
Function Add(Name As String, _
Optional Gender As String, _
Optional Age As Double = -1, _
Optional Race As String = "unknown", _
Optional MaritalStatus As String = "unknown") As clsPerson
Dim aPerson As New clsPerson
With aPerson
.Name = Name
If 0 <= Age Then .Age = Age
.Gender = Gender
.Race = Race
.MaritalStatus = MaritalStatus
.Index = Persons.Count + 1
Set .Collection = Persons
End With
Persons.Add Item:=aPerson, Key:=aPerson.Name
Set Add = aPerson
Set aPerson = Nothing
End Function
Property Get Count() As Long
Count = Persons.Count
End Property
Private Sub Class_Initialize()
Set Persons = New Collection
End Sub
Private Sub Class_Terminate()
Dim onePerson As clsPerson
For Each onePerson In Persons
Set onePerson = Nothing
Next onePerson
Set Persons = Nothing
End Sub
And it could be used with code like this (in a normal module)
Sub Test()
Dim Folks As New clsPeople
Dim aPerson As clsPerson
Dim i As Long
Folks.Add "bob", "male", 44, "Single", "white"
Folks.Add "sally", "female", 56, "married", "latina"
Folks.Add "George", "male", MaritalStatus:="bigamist"
Folks.Add "Sandy", Gender:="female", Age:=25
MsgBox "Bob's info is" & vbCr & vbCr & PersonSummary(Folks.Persons("Bob"))
For i = 1 To Folks.Count
MsgBox PersonSummary(Folks.Persons(i))
Next i
End Sub
Function PersonSummary(aPerson As clsPerson) As String
With aPerson
PersonSummary = .Name
PersonSummary = PersonSummary & vbCr & .Age & " years old"
PersonSummary = PersonSummary & vbCr & .Gender
PersonSummary = PersonSummary & vbCr & .MaritalStatus
PersonSummary = PersonSummary & vbCr & .Race
End With
End Function
Bookmarks