Excelシート上のデータ構造のまま、クラスとCollectionでVBA上のデータとして扱う

Excelのシート上にあるデータ構造のまま、VBA上で扱いたい場合、クラスとCollectionでそれを実現する方法です。

データ構造例

データ構造は次のような従業員データとします。

クラスを作成

クラスの作成方法は右クリックで、挿入→クラス モジュール の選択です。

今回は EmployeeClassという名前にします。
そこにシート同じ項目の変数(プロパティ)を Public で作成します。

EmployeeClass.cls

Public Id As String             ' ID
Public Name As String           ' 名前
Public Department  As String    ' 部署

※定義した変数しか使用しないため、Option Explicit を付けるかどうかは任意とします。

モジュールの追加

新しく 標準モジュールを挿入します。

今回はEmployeeModule という名前にします。

EmployeeModule.bas

Option Explicit

Private Const EMPLOYEE_SHEET_NAME As String = "従業員データ"    ' シート名
Private Const EMPLOYEE_ID_COLUMN_NUM As Long = 1                ' IDの行番号
Private Const EMPLOYEE_NAME_COLUMN_NUM As Long = 2              ' 名前の行番号
Private Const EMPLOYEE_DEPARTMENT_COLUMN_NUM As Long = 3        ' 部署の行番号

' 全従業員データを取得する
Public Function GetAllEmployeeData() As Collection
    Dim employeeData As EmployeeClass
    Dim resultList As Collection    ' 結果を格納する Collection型
    Set resultList = New Collection ' その初期化
    Dim rowNum As Long
    rowNum = 2  ' データは二行目から始まるため
    
    ' シート名で以下のセルを指定
    With ThisWorkbook.Worksheets(EMPLOYEE_SHEET_NAME)
        ' ID行は必ず存在する値であるから、そこが空になるまで繰り返す
        Do While IsEmpty(.Cells(rowNum, EMPLOYEE_ID_COLUMN_NUM)) = False
            Set employeeData = GetEmployeeData(rowNum)  ' 指定行の従業員データを取得する
            Call resultList.Add(employeeData)   ' 結果の変数に格納する
            rowNum = rowNum + 1 ' 次の行へ
            DoEvents    ' 繰り返しが多すぎると制御できなくなるのでOS側に制御を渡してあげる
        Loop
    End With
    Set GetAllEmployeeData = resultList
End Function


' 指定行の従業員データを取得する
Private Function GetEmployeeData(rowNum As Long) As EmployeeClass
    Dim employeeData As EmployeeClass
    With ThisWorkbook.Worksheets(EMPLOYEE_SHEET_NAME)
        Set employeeData = New EmployeeClass
        ' ここで各セルから値を変数にセット
        employeeData.Id = .Cells(rowNum, EMPLOYEE_ID_COLUMN_NUM)
        employeeData.Name = .Cells(rowNum, EMPLOYEE_NAME_COLUMN_NUM)
        employeeData.Department = .Cells(rowNum, EMPLOYEE_DEPARTMENT_COLUMN_NUM)
    End With
    
    Set GetEmployeeData = employeeData
End Function

GetAllEmployeeData() と GetEmployeeData() に分けているのは、データの項目数が多くなった時に、この方が修正がしやすくなるからです。
また GetAllEmployeeData() を Public にすることで、外のモジュールからでも呼び出せるようにしてあります。

実行例

試しに実行してみます。適当なモジュールで次のようなSubプロシージャを作成します。

Module1.bas

Sub Start()
    Dim employeeList As Collection
    Dim employeeData As EmployeeClass
    Set employeeList = GetAllEmployeeData
    
    Debug.Print "従業員数は" & employeeList.Count & "人です。"
    
    For Each employeeData In employeeList
        Debug.Print employeeData.Name & "(" & employeeData.Id & ")さんの部署は" & employeeData.Department & "です。"
    Next
End Sub

すると、従業員の全情報が、employeeList に格納され、その中身はEmployeeClassとして扱えることが分かります。

実行画面

以上がExcelシートのデータ構造のままVBAで扱う方法になります。

参考資料

VBAでクラスをつくる
VBAでCollectionとdictionaryを使う
Excel VBAコーディング ガイドライン案
VBA コーディングガイドライン