【エクセルVBA】クラスモジュールでシートをCollectionのように使う

慣れないうちは、エクセルVBAのクラスモジュールを、どう使えばいいか使い道が思い浮かばない人も多いと思います。

そこで、私が、最近よく使っているクラスモジュールの使い方を紹介しようと思います。

クラスモジュールをうまく使うことで、ソースコードがわかりやすくなることが実感できると思います。

 

なお、このページでは、クラスモジュールそのものの細かい解説はしません。

もし、クラスモジュールの詳細を知りたいというときには、このページの末尾にタカハシさんのクラスモジュールの解説記事へのリンクを掲載していますので、そちらを合わせてご覧ください。

合わせて読んで頂くと学習効果が増すのではないかと思います。


シートに入力した情報をVBAで読み込む

VBAを使うときには、動作に必要な情報をシートに入力して、それをVBA実行時に読み込むという処理を行います。

このようにしておくことで、VBAのソースコードを変えないでも、マクロの挙動を変えることができます。

たとえば、「読み込むファイルが状況に応じて変わる」というときには、読み込むファイルの一覧をワークシートに入力して、マクロ実行時に読み込めれば便利です。

このようなプログラムを、どう組めばいいか考えてみましょう。

直接Cellsを使って読む

まずは、Cellsを使って直接読み込んでみます。

Sub ReadDataByRange()
  With FilePathSheet    '「ファイルパス一覧」シートのオブジェクト名
    Dim Row As Long
    
'データが2行目から始まっているので、データ件数はusedrangeで得た行数から1を引いたものになる
    For Row = 1 To .UsedRange.Rows.Count - 1
      Debug.Print Row & "  " & .Cells(Row + 1, 2).Value & _
                        ", " & .Cells(Row + 1, 3).Value
    Next

  End With
End Sub

実行すると、イミディエイトウィンドウには、次のように表示されます。

これが普通の方法です。

ただし、この方法だと、ソースコードが若干読みにくくなります。

 

読みにくい理由は、3つあります。

  1. データが2行目から入力されているため、Forループの終了値に補正(-1)が必要
  2. 同様に、セルの値を読み込むときにも、行(変数Row)に対する補正(+1)が必要
  3. 列を直接数字で指定しているので、どの列を指定しているのかが直感的にわかりにくい

もちろん、2行目からデータを読みこむのですから、For~Nextループを2行目から始めれば、一見、ロジックはスッキリするように見えます。

Sub ReadDataByRange()
  With FilePathSheet    '「ファイルパス一覧」シートのオブジェクト名
    Dim Row As Long
    
    For Row = 2 To .UsedRange.Rows.Count
      Debug.Print Row - 1 & "  " & .Cells(Row, 2).Value & _
                            ", " & .Cells(Row, 3).Value
    Next

  End With
End Sub

でも、このロジックでは「1件目」のデータを読むときの「Rowの値が2」になってしまっています。

やはり、表をデータベースとして使う場合には、「Row=1」で「1件目のデータ」が取得できる仕組みのほうが、プログラムの意図が直感的に理解しやすくなるはずです

そこで、この記事では最終的に、クラスモジュールを使って「Row=1」のときに「1行目のデータを読み込める」ような直感的なわかりやすい仕組みを作っていきます。

その最終的な仕組みと比較がしやすいように、冒頭では、ループの開始値を「Row=1」にした例を載せています。

直接Cellsを使って読む+ConstやEnumを使う

先ほどのソースの読みにくさを解消するために、よく使われるものとして、定数定義(Const)・列挙型(Enum)があります。

Const MinRowOffset As Long = 1

Enum FilePathSheetColumns
  Col_ID = 1
  Col_FilePath = 2
  Col_Comment = 3
End Enum

Sub ReadDataByRange()
  With FilePathSheet
    Dim Row As Long
    
'データが2行目から始まっているので、データ件数はusedrangeで得た行数から1を引いたものになる
    For Row = 1 To .UsedRange.Rows.Count - MinRowOffset
      Debug.Print Row & "  " & .Cells(Row + MinRowOffset, Col_FilePath).Value & _
                        ", " & .Cells(Row + MinRowOffset, Col_Comment).Value
    Next

  End With
End Sub

少し、わかりやすくなったかもしれませんが、やはりゴチャゴチャしている印象です。

行を表すクラスモジュールを使う

そこで、このわかりにくさを「クラスモジュール」を使って解消してみようと思います。

具体的なソースコードの解説は後回しにして、まずは結果から見て見ましょう。

このプロジェクトウィンドウの画像のように、

  • クラスモジュール:FilePathRow
  • 標準モジュール:FilePathData

を準備します。

すると、本体部分のソースコードは、次のように書くことができます。

標準モジュールModule1のソースコード
Sub ReadDataByClass1()
  Dim Row As Long
  For Row = 1 To FilePathData.Count
    Debug.Print Row & "  " & FilePathData.Item(Row).FilePath & _
                      ", " & FilePathData.Item(Row).Comment
  Next
End Sub

ソースコードが、とてもすっきりしました。

さらに、裏側でクラスモジュールを使っているにもかかわらず、New文などのクラス特有の構文が出てきていないので、クラスモジュールに不慣れでも違和感なくプログラムが組めます。

下準備にはクラスモジュールの知識が必要ですが、下準備が済んでしまえばクラスモジュールを意識する必要がなくなるのです。

 

それでは、これから、その「下準備」の方法を紹介します。

完成版のソースコードは下記からダウンロードできます↓
クラスモジュールを使ったマクロ(その1)をダウンロードする

クラスモジュールFilePathRow

クラスモジュールを新規作成したら、オブジェクト名を「FilePathRow」に変更しましょう。
その後、下記ソースコードをコピペしてください。

クラスモジュールFilePathRowのソースコード
Private M_Worksheet As Worksheet
Private M_DataRow As Long

Public Sub Init(Worksheet As Worksheet, Row As Long)
  Set M_Worksheet = Worksheet
  M_DataRow = Row + 1           'データは2行目から始まるので+1の補正を入れる
End Sub

Public Property Get ID()        '「ID」という名前で1列目にアクセスする設定
  ID = M_Worksheet.Cells(M_DataRow, 1).Value
End Property

Public Property Get FilePath()  '「FilePath」という名前で2列目にアクセスする設定
  FilePath = M_Worksheet.Cells(M_DataRow, 2).Value
End Property

Public Property Get Comment()   '「Comment」という名前で3列目にアクセスする設定
  Comment = M_Worksheet.Cells(M_DataRow, 3).Value
End Property

このクラスを使って、指定したシート・指定した行の「セルの内容」を読み込みます。

このクラスを使うときは、最初にInitプロシージャを呼び出します。
Initプロシージャは、読み込み対象となるシート(M_Worksheet)・行(M_Row)を設定する役割を持っています。

そして、Propertyプロシージャ(ID、FilePath、Comment)で、それぞれに対応する列を読み込むことができます。

 

さて、これで、クラスモジュールは完成しました。

でも、このままの状態で、クラスモジュールを実際に使うのは、意外と面倒です。

というのは、クラスモジュールを使うためには、次のような手順を踏む必要があるからです。

  • Set XXX = New FilePathRow → クラスのインスタンス化
  • Call Ret.Init( ... ) → Initプロシージャの呼び出し

そこで、この準備をする処理を別途プロシージャ化します。

標準モジュールFilePathData

標準モジュールを新規作成し、オブジェクト名を「FilePathData」に変更します。
そして、下記ソースコードをコピペしてください。

標準モジュールFilePathDataのソースコード
Public Function Count() As Long
  Count = FilePathSheet.UsedRange.Rows.Count - 1  'データが2行目からなので最大値を補正
End Function


Public Function Item(Row As Long) As FilePathRow
  Dim Ret As FilePathRow
    
  Set Ret = New FilePathRow
  Call Ret.Init(FilePathSheet, Row) 'FilePathRowで読み込む「シート」と「行」の情報を設定

  Set Item = Ret
End Function

Itemプロシージャ

「Item」というFunctionプロシージャを作り、先ほどの「クラスを使うための面倒な処理」をまとめてしまいました。

このような工夫をすることで、本体プログラム側では、

FilePathData.Item

と入れるだけで、FilePathRowクラスのオブジェクトを取得でき、手軽に使えるようになりました。

Countプロシージャ

さらに、このエクセルシートのデータをCollection的に使いたいという場合、「何件データがあるかの情報」が取れると便利です。
そこで、この標準モジュールに「Count」というFunctionプロシージャを追加しています。

下記のように入力すれば「ファイルパス一覧」シートに入力されているデータの行数が取得できます。

FilePathData.Count

実際に入力する様子の動画

実際に、プログラムを入力すると次のようになります。

※下記を再生しても音は出ませんので、音が出せない環境でもご安心ください。

入力候補も出てきますし、かなり直感的に入力することができます。

なぜ、本体プログラムが簡単になったのか?

一言で言うと、面倒な処理を、クラスモジュールや(新たに作成した)標準モジュールに追い出してしまっているからです。

ソースコード中の「色を付けた部分」を見比べると、クラスモジュールや(新たに作成した)標準モジュールで面倒な処理を行っていることがわかります。
クラスモジュールFilePathRowのソースコード標準モジュールFilePathDataのソースコード

たとえば、データの件数を補正する処理(=シートの使用行数から1を引く処理)は、FilePathDataのCountプロシージャの中で行われています。

 

このように、クラスモジュールなどを使って、面倒な処理を外に追い出したおかげで、本体プログラムが簡単になっているのです。

いったん、このような仕組みを作りこんでしまえば、本体プログラムは見やすく書けるというのが、クラスモジュールを使う一番のメリットです。

行を表すクラスモジュールを使う その2

先ほどのプログラムを少し改良して、さらに、ある裏技(?)を使うと、本体プログラムをもっと簡単に書けるようになります。

標準モジュールModule1のソースコード
Sub ReadDataByClass2()
  Dim FP As New FilePathData
  
  Dim Row As Long
  For Row = 1 To FP.Count
    Debug.Print Row & "  " & FP(Row).FilePath & _
                      ", " & FP(Row).Comment
  Next
End Sub

先ほどのプログラムと比べると、2行目の「Dim FP As New FilePathData」という行が増えています。

その代わりに、6行目~7行目の表記がかなり簡単になっています。

たとえば、FilePathの情報を取得する部分は、次のように変化しています。

改良前:
FilePathData.Item(Row).FilePath

改良後:
FP(Row).FilePath

改良後のプログラムでは、「FP(Row)」というように、コレクションや配列のような表記をすることができ、さらに直感的にプログラムを作れるようになっています。

完成版のソースコードは下記からダウンロードできます↓
クラスモジュールを使ったマクロ(その2)をダウンロードする

ソースコード

実は、ソースコードそのものは、改良前のものとまったく同じです。

ただし、2点違いがあります。

FilePathDataのソースコードをクラスモジュールにする

改良前には、標準モジュールFilePathDataにソースコードを入力しました。
改良後バージョンでは、同じソースを、クラスモジュールFilePathDataに入力してください。

隠し属性を付与する

次に、下記ページを参考にして、FilePathDataクラスのItemファンクションを「デフォルトプロパティ」に指定します。

※ItemはFunctionですので、本来は、デフォルトプロシージャというほうが適切なのだと思いますが、このページでは、リンク先の表記に合わせて「デフォルトプロパティ」と表現しています。

 

この設定をすることで、「Item」というプロシージャ名を省略して表記することができるのです。

デフォルトプロパティを使用しない状態:
FP.Item(Row).FilePath

デフォルトプロパティ使用:
FP(Row).FilePath

実際に入力してみると、次のようになります。

※下記を再生しても音は出ませんので、音が出せない環境でもご安心ください。

ほとんどCollectionのように操作ができていることがわかります。

ダウンロード

今回のマクロは下記からダウンロードできますので、お試しください。

クラスモジュールを使ったマクロ(その1)をダウンロードする

クラスモジュールを使ったマクロ(その2)をダウンロードする

まとめ

最後に、もう一度、今日のソースコードを比較してみます。

通常の組み方
Sub ReadDataByRange()
  With FilePathSheet    '「ファイルパス一覧」シートのオブジェクト名
    Dim Row As Long
    
'データが2行目から始まっているので、データ件数はusedrangeで得た行数から1を引いたものになる
    For Row = 1 To .UsedRange.Rows.Count - 1
      Debug.Print Row & "  " & .Cells(Row + 1, 2).Value & _
                        ", " & .Cells(Row + 1, 3).Value
    Next

  End With
End Sub
クラスモジュール使用(その1)
Sub ReadDataByClass1()
  Dim Row As Long
  For Row = 1 To FilePathData.Count
    Debug.Print Row & "  " & FilePathData.Item(Row).FilePath & _
                      ", " & FilePathData.Item(Row).Comment
  Next
End Sub
クラスモジュール使用(その2)
Sub ReadDataByClass2()
  Dim FP As New FilePathData
  
  Dim Row As Long
  For Row = 1 To FP.Count
    Debug.Print Row & "  " & FP(Row).FilePath & _
                      ", " & FP(Row).Comment
  Next
End Sub

随分見やすくなっているのがわかると思います。

メインロジックのプログラムが見やすくなると、ミスも減り、作業効率には良い影響を与えます。

もし、クラスモジュールに興味が出て来たようであれば、ぜひ、勉強してみてください。

参考サイト

おすすめ記事

エクセル基礎講座 「無料」動画マニュアル

「経理事務のためのエクセル基礎講座(初級編)」(動画マニュアル 総収録時間162分)を無料プレゼント中です!

このマニュアルで解説していることを一通り学べば、経理事務を行う上で最低限必要となる知識が得られます。

ご登録者の方には、合わせて、公認会計士が実体験を通して身に付けたエクセルを使う技をメールにてお伝えしていきます!

無料動画講座 登録フォーム

※ご登録頂いたメールアドレスに、エクセルを使いこなすための情報を配信するメールセミナー「エクセル倍速講座」も合わせて配信させていただきます。