HOMEへ

クラスシート======================================================

Option Explicit

Enum eFieldSheet1
    eId = 1
    eName
    eGender
    eBirthday
    eActive
End Enum

Public Persons As Collection
Public MaxId As Long

'**
' テーブルのデータをPersonsコレクションとして格納
'
' @param myRange{Range}
'
Public Sub LoadData()
    Set Persons = New Collection

    With ListObjects(1)

        Dim myRow As ListRow
        For Each myRow In .ListRows
            Dim p As Person: Set p = New Person
            p.Initialize myRow.Range
            Persons.Add p, CStr(p.Id)
        Next myRow
    
        MaxId = .ListRows.Count
    
    End With

End Sub

'**
' Personsコレクションのデータをテーブルに展開
'
'
Public Sub ApplyData()

    With ListObjects(1)
        If .ListRows.Count > 0 Then
            .DataBodyRange.EntireRow.Delete
        End If
        
        Dim p As Person
        For Each p In Persons
            .ListRows.Add.Range = Array(p.Id, p.Name, p.Gender, p.Birthday, p.Active)
        Next p
    
        MaxId = .ListRows.Count
    
    End With

End Sub

'**
' PersonsコレクションのPersonオブジェクトを更新
'
' @param p{Person} 更新するPersonオブジェクト

Public Sub UpdatePerson(p As Person)

    With Persons(p.Id)
        .Id = p.Id
        .Name = p.Name
        .Gender = p.Gender
        .Birthday = p.Birthday
        .Active = p.Active
    End With

    Call ApplyData

End Sub

'**
' PersonsコレクションのPersonオブジェクトを追加
'
' @param p{Person} 追加するPersonオブジェクト

Public Sub AddPerson(p As Person)

    Persons.Add p, CStr(p.Id)
    Call ApplyData

End Sub

'**
' Studentクラス
'

Private mID As String
Private mName As String
Private mAge As Long
Private mMobile As String

Public Property Let ID(ByVal vID As String)

    mID = vID

End Property

Public Property Get ID() As String

    ID = mID

End Property

Public Property Let Name(ByVal vName As String)

    mName = vName

End Property

Public Property Get Name() As String

    Name = mName

End Property

Public Property Let Age(ByVal vAge As Long)

    mAge = vAge

End Property

Public Property Get Age() As Long

    Age = mAge

End Property

Public Property Let Mobile(ByVal vMobile As String)

    mMobile = vMobile

End Property

Public Property Get Mobile() As String

    Mobile = mMobile

End Property

'**
' Studentsクラス
'
Option Explicit

Private mItems As Collection
Private mItemDictionary As Object

Private Sub Class_Initialize()
    Set mItems = New Collection
    Set mItemDictionary = CreateObject("Scripting.Dictionary")
End Sub

Private Sub Class_Terminate()
    Set mItems = Nothing
    Set mItemDictionary = Nothing
End Sub

Public Sub Add(ByVal vID As String, ByVal vName As String, ByVal vAge As Long, ByVal vMobile As String)

    Dim vStudent As Student

    mItemDictionary.Add Key:=vID, Item:=mItems.Count + 1

    Set vStudent = New Student
'    mItems.Add vStudent
    With vStudent
        .ID = vID
        .Name = vName
        .Age = vAge
        .Mobile = vMobile
    End With
    mItems.Add vStudent

    Set vStudent = Nothing

End Sub

Public Function SearchItemIndex(ByVal vID As String) As Variant
    
    SearchItemIndex = False
    If mItemDictionary.Exists(vID) Then
        SearchItemIndex = mItemDictionary.Item(vID)
    End If

End Function

Public Property Get Item(ByVal Index As Long) As Student
    Set Item = mItems.Item(Index)
End Property