エクセルVBAでエクセル表のデータを反映するメソッドとクラスを使うメリット

みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。

「初心者向けエクセルVBAではじめてのクラスモジュール」についてシリーズでお伝えしています。

前回の記事はこちら。

エクセルVBAで自作コレクションの要素を削除するメソッドの作り方

「初心者でもわかるエクセルVBAのクラスモジュール」シリーズをお送りしています。今回は、エクセルVBAで自作コレクションの要素を削除するRemoveメソッドの作り方についてお伝えしていきます。

自作のコレクションから要素を削除するメソッドを追加しました。

さて、エクセル表を表すクラスを作ってそれをオブジェクトとして操作するのはいいのですが、操作した結果をエクセル表に反映させなくてはいけませんよね。

ということで、今回はシリーズ最終回。

エクセルVBAで自作コレクションのデータをエクセル表に反映させるメソッドの作り方をお伝えします。

では、行ってみましょう！

前回のおさらい
データをオブジェクトで操作した結果をエクセル表に反映する
1. Personsコレクションのデータをエクセル表に反映する
2. エクセル表への反映について動作確認
結局、クラスを使うと何がいいのか
まとめ
連載目次：初心者でもわかる！エクセルVBAでクラスを作ろう

前回のおさらい

では、前回のおさらいからです。

以下のようなエクセル表があります。

このエクセル表のデータを取り扱うクラスを作成しておりますが、まずは１行分のデータを表すクラスPersonがこちらです。

Private id_ As String
Public FirstName As String
Public Gender As String
Public Birthday As Date

Public Sub Greet()
    MsgBox Me.FirstName & "です、こんにちは！"
End Sub

Public Sub Initialize(ByVal values As Variant)

    Select Case TypeName(values)
        Case "Range"
            id_ = values(1).Value
            FirstName = values(2).Value
            Gender = values(3).Value
            Birthday = values(4).Value
        Case "Variant()"
            id_ = values(0)
            FirstName = values(1)
            Gender = values(2)
            Birthday = values(3)
    End Select

End Sub

Public Property Get IsMale() As Boolean
    IsMale = (Me.Gender = "male")
End Property

Public Property Get Id() As String
    Id = id_
End Property

Public Property Let Id(ByVal newId As String)
    If id_ <> "" Then
        Debug.Print "Idは上書きすることはできません"
    Else
        id_ = newId
    End If
End Property

フィールドを表すプロパティのほか、便利そうなメンバーをいくつか追加しております。

そして、それを集合として扱うために、自作コレクションを作りました。それが、以下のPersonsクラスになります。

Private items_ As Collection

Private Sub Class_Initialize()
    Set items_ = New Collection
    
    With Sheet1
        Dim i As Long: i = 2
        Do While .Cells(i, 1).Value <> ""
            Me.Add .Range(.Cells(i, 1), .Cells(i, 4))
            i = i + 1
        Loop
    End With

End Sub

Public Property Get Item(ByVal key As Variant) As Person
    Set Item = items_.Item(key)
End Property

Public Function Add(ByVal values As Variant) As Person

    Dim p As Person: Set p = New Person
    p.Initialize values
    items_.Add p, p.Id
    
    Set Add = p

End Function

Public Sub Remove(ByVal key As Variant)
    items_.Remove key
End Sub

このPersonsコレクションは、Personオブジェクトを集合として取り扱うものなので、結果としてPersonsコレクションがエクセル表のデータ全体を表します。

前回は、このPersonsコレクションから要素を削除するRemoveメソッドを作成しました。

それを検証するための標準モジュールがこちらです。

Sub MySub()

Dim myPersons As Persons: Set myPersons = New Persons

Dim p As Person
Set p = myPersons.Add(Array("a04", "Jay", "male", #7/7/1995#))

With myPersons
    .Remove 2
    .Remove "a01"
End With

Stop

End Sub

データをオブジェクトで操作した結果をエクセル表に反映する

これで、なんとなくエクセル表のデータをクラス化して取り扱う全体の機能ができた感じです。

手順としては、以下の通り。

エクセル表をもとに新規のPersonsコレクションを生成する
Personsコレクションの要素を追加したり、削除したり、変更したりする
Personsコレクションを再度エクセル表に反映する

こんな流れですね。

2の操作とかを、さらにユーザーフォームとかを使って操作できるようにしたら、アプリケーションぽい感じになります。

それで、お気づきの通り、3のPersonsコレクションを操作した結果をエクセル表に反映させるという処理がまだありませんので、今回はそれを作っていきます。

Personsコレクションのデータをエクセル表に反映する

Personsコレクションのデータをエクセル表に反映させるApplyToSheetメソッドを作成します。

戻り値などは必要ないので、Subプロシージャで作ります。

Public Sub ApplyToSheet()
    With Sheet1
        .Cells.Clear
        .Range(.Cells(1, 1), .Cells(1, 4)) = Array("Id", "FirstName", "Gender", "Birthday")
       
        Dim i As Long: i = 2
        Dim p As Person
        For Each p In items_
            .Range(.Cells(i, 1), .Cells(i, 4)) = Array(p.Id, p.FirstName, p.Gender, p.Birthday)
            i = i + 1
        Next p
    End With
End Sub

流れとしては以下のとおりですね。

Sheet1をいったんクリア
1行目に見出しを再度書き出す
Personsコレクションの要素の数だけ繰り返して
1. 各要素のデータをエクセル表に書き出す
2. 行を1つ追加

エクセル表への反映について動作確認

作成したPersonsコレクションのApplyToSheetメソッドの動作を確認します。

例えば、標準モジュールに以下のようなSubプロシージャを作りました。

Sub MySub()

Dim myPersons As Persons: Set myPersons = New Persons
With myPersons
    
    .Add (Array("a04", "Jay", "male", #7/7/1995#))
    .Remove 2
    With .Item("a01")
        .FirstName = "Bomb"
        .Birthday = #11/11/1993#
    End With
    
    .ApplyToSheet
End With

End Sub

確認をすると…