概要

Excelを用いた業務で、データにフィルタをかけては別シートにコピーする作業を繰り返す必要があり、操作やコードの記述が煩雑になったため、クラス化して整理しようと思いました。

前提

Sheet1に下記のようなテーブル1がある。
Sheet2に下記のようなテーブル2がある。
クラスモジュールの名前はWSheetとする。

コード

基本設定

メンバ変数はPrivateにしました。
標準モジュールからWorksheetオブジェクトやListObjectオブジェクトを操作したい時もあるので、Property Getプロシージャでアクセスできるようにしておきます。

クラスモジュール

Option Explicit
Private WS_ As Worksheet
Private sheetName_ As String
Private tableName_ As String
Private listObj_ As ListObject

Public Property Get sheetName() As String
    sheetName = sheetName_
End Property
Public Property Get WS() As Worksheet
    Set WS = WS_
End Property
Public Property Get tableName() As String
    tableName = tableName_
End Property
Public Property Get listObj() As ListObject
    Set listObj = listObj_
End Property

クラスモジュール

Public Function CreateListObj(ByVal sheetName As String, ByVal tableName As String)
    sheetName_ = sheetName
    Set WS_ = Worksheets(sheetName)
    tableName_ = tableName
    Set listObj_ = WS_.ListObjects(tableName)
End Function

例）Sheet1にあるテーブル1とSheet2にあるテーブル2について、インスタンスを生成する。

標準モジュール

Dim table1 As WSheet: Set table1 = New WSheet
table1.CreateListObj "Sheet1", "テーブル1"

Dim table2 As WSheet: Set table2 = New WSheet
table2.CreateListObj "Sheet2", "テーブル2"

列名から列番号を取得する

折角テーブルで構造化参照を利用しているのに、VBAメソッドによってはわざわざ列番号を指定しないといけないケースがあります。
運用していく中で列の入れ替えが発生するかもしれませんし、「列番号なんて知らねぇ、俺はとにかく『氏名』って列を操作したいんだ！」という時もあるでしょう。
ということで、テーブル（ListObjectオブジェクト）と列名を与えて列番号を取得するメソッドを用意します。
これは後々作成する別のメソッド内でも利用します。

クラスモジュール

Public Function GetCol(ByVal itemName As String) As Integer
    GetCol = listObj_.ListColumns(itemName).Range(1).Column
End Function

例）テーブル1の「氏名」列が何番目か取得する

標準モジュール

Dim colNum as Integer
colNum = table1.GetCol("氏名")

テーブルのBodyを削除する

例えば月1で処理を回す時など、先月分のレコードを削除したい時があります。
そんな時は、テーブルのヘッダを残して全行削除します。
念のため.Range.AutoFilterでフィルタを解除した上で削除を行っています。

クラスモジュール

Public Function DeleteTableBody()
    With listObj_
        .Range.AutoFilter
        If Not (.DataBodyRange Is Nothing) Then
            .DataBodyRange.Delete
        End If
    End With
End Function

例）テーブル2からヘッダ以外の行を削除する

標準モジュール

table2.DeleteTableBody

ソート

配列を渡すことで、複数条件でソートできるようにします。
今回は昇順なのでOrder:=xlAscending。
降順にしたい時はOrder:=xlDescending。
これも引数で指定できるようにしても良いかもしれません。

クラスモジュール

Public Function SetSort(ByVal ConditionArr As Variant)
    With listObj_.Sort
        .SortFields.Clear
        Dim itemName
        For Each itemName In ConditionArr
            .SortFields.Add Key:=Range(tableName_ & "[[#All],[" & itemName & "]]"), Order:=xlAscending
        Next
        .Header = xlYes
        .Apply
    End With
End Function

例）テーブル1に対して、「年齢」を優先度1、「身長」を優先度2でソートする

標準モジュール

Dim sortConditionArr() As String: ReDim Preserve sortConditionArr(1)
sortConditionArr(0) = "年齢"
sortConditionArr(1) = "身長"
table1.SetSort sortConditionArr

オートフィルタ

クラスモジュールのメソッドを介さなくても、標準モジュールからWorksheetオブジェクトやListObjectオブジェクトを簡単に操作できます。

例）テーブル1に対して、「性別」が「男」でフィルターをかける

標準モジュール

table1.listObj.Range.AutoFilter .GetCol("性別"), "男"

列ごとにデータをコピーする

オートフィルタで抽出したデータを、別のシートの同じ列名の場所にコピーできるようにします。

クラスモジュール

Public Function DataBodyRangeCopy(ByVal targetWS As WSheet, ByVal itemName As String)
    listObj_.ListColumns(Me.GetCol(itemName)).DataBodyRange.Copy Worksheets(targetWS.sheetName).Cells(2, (targetWS.GetCol(itemName)))
End Function

例）テーブル1の「氏名」列のデータを、テーブル2の「氏名」列にコピーする

標準モジュール

table1.DataBodyRangeCopy table2, "氏名"

計算結果だけコピーしたい時等、値だけコピーの場合はこちら。

クラスモジュール

Function DataBodyRangePasteSpecial(ByVal targetWS As WSheet, ByVal itemName As String)
    listObj_.ListColumns(Me.GetCol(itemName)).DataBodyRange.Copy
    Worksheets(targetWS.sheetName).Cells(2, targetWS.GetCol(itemName)).PasteSpecial Paste:=xlPasteValues
End Function

例）テーブル1の「BMI」列のデータを、テーブル2の「BMI」列に値だけコピーする

標準モジュール

table1.DataBodyRangePasteSpecial table2, "BMI"

さいごに

コーディングがとても楽になりました。
クラス化してヨカッタ！