表データを扱うクラスを作ってみた

超久しぶりな更新です。

こんちくわ 壁|ω・)ﾉ

さて久々な記事は、VBAを使った表データの集計用になかなかイケてる(当社比)ツールができちゃったんじゃね？～なオハナシです。

でわいきます￣▽￣)ﾉ

目次

背景

私は仕事柄実験データの解析をよくやっているのですが、1日の実験で得られるデータ数が500を超える(合計約100サンプル分のソースデータ)ことも少なくなくって、それぞれについて平均、標準偏差、データサンプル数計算とそれをグラフ化するだけでも

​ こんなんやってられっか～(ノ ﾟДﾟ)ノ ＝＝＝＝ ┻━━┻

ってなるわけで、当然VBAワンポチ解析に持ち込んでいます。

解析プログラムは構造化したソースデータを取り扱う形で作成していきますが、対象表データについて以下の情報を取得する機能単位をよく作成します

1. 特定の列のデータ範囲 (列タイトルセルを除いた残り)(Rangeオブジェクト)
2. 各列タイトル文字列を格納した配列
3. 各列タイトルのセル(Rangeオブジェクト)
4. 特定の条件を満たすデータ範囲

これまでは過去に作ったFunctionプロシージャを都度修正しながら使いまわししてはいるのですが、なんかコードが全体的に散らかるんですよね。

そんなもん、ListObject使えばなんかうまいこと書けるやろ～

というご意見もあるかと思いますが、以下の理由からクラスモジュールに各機能単位を押し込めることにしました。

理由①：表を取り扱うツールをまとめておけば管理や持ち運び(?)が楽
理由②：表から別の表への転記など１つのマクロで同時に複数の表を扱うケースがある
理由③：例えば表全体のRange()だけクラスに通せば、必要な情報をプロパティとして含んだ表オブジェクトを作成するみたいなことができる

クラスTableTools

今回作ったクラスでは以下のようなプロパティ、メソッドを実装しました

​ １. DefineTableメソッド(対象の表が含まれるワークシート名 as String, 表範囲 as Range)
インスタンスとして取り扱う表を定義するメソッド

​ ２. tableHeaderCellsプロパティ as Range
表のタイトル列全体を示す

​ ３. tableDataBodyRng プロパティ as Range
表範囲全体からタイトル行を除いた表データ全体を示す

​ ４. ColumnDataArea プロパティ(列タイトル as String) as Range
表の列タイトル文字列を引数として対応するデータ範囲を返す

​ ５. ColumnTitleCellプロパティ(列タイトル as String) as Range
表の列タイトル文字列を引数として対応するデータ範囲を返す

​ ６. FindCommonAreaメソッド(列タイトル as String, Param 参照範囲 as Range) as Range
特定の列データ範囲から特定の条件(Rangeオブジェクトで指定、複数対応可)に対応するデータ範囲を返す

クラスTableToolsコード

以下クラスモジュールTableToolに記述したコードです。

最初にフィールド部分とDefineTableメソッドですが、これがこのクラスのご本尊的なコードになります。表の必要な範囲の指定するために引数として取得した範囲をListObjectオブジェクト化して取り扱いました。

Option Explicit

Public tableHeaderCells As Range
Public tableDataBodyRng As Range
Private tableColumns As Collection
Private tableHeaders As Collection
Private targetWorksheet As Worksheet

Public Sub DefineTable(ByVal wsName As String, ByVal rng As Range)

    '引数 rngで指定するデータ対象領域をListObject化して以下の領域を特定して配列化する
    '  Title行：tableColumnItems
    '  １列目(各データのTitle列)： tableColumnItems
    
    Set targetWorksheet = Worksheets(wsName)
    With targetWorksheet
    '対象シート内のListObjectを全て解除する
          If .ListObjects.count <> 0 Then
                Dim temp As Variant
                For Each temp In .ListObjects
                     temp.Unlist
                Next
          End If
          
          With .ListObjects.Add(Source:=.Range(rng.Address), xllistobjectHasheaders:=xlYes)
                .Name = "tempTable"
                .TableStyle = ""
          End With
    
          With .ListObjects("temptable")
                Set tableColumns = New Collection
                Dim tempArea As Variant
                For Each tempArea In .ListColumns
                    Call tableColumns.Add(tempArea.DataBodyRange, tempArea.Range(1).Value)
                Next
                
                'カラムTitleが格納されているセル(Rangeオブジェクト)を集積する
                '   Item: Rangeオブジェクト
                '   Key: 各カラムTitle
                Set tableHeaderCells = .HeaderRowRange
                Set tableHeaders = New Collection
                For Each tempArea In tableHeaderCells
                    Call tableHeaders.Add(tempArea, tempArea.Value)
                Next

                Set tableDataBodyRng = .DataBodyRange
                
                .Unlist
          End With
  End With

End Sub

このコードではクラスでメンバーを定義するための下準備として以下の２つのコレクション、tableColumns, tableHeaders を作成しています

それぞれのコレクションのイメージとしては以下のような感じです

ColumnDataAreaプロパティは tableColumns コレクションのItem (列データ範囲)を Key (列タイトル文字列) で呼び出します

Public Property Get ColumnDataArea(ByVal columnTitle As String) As Range
'指定のカラムタイトル列のデータ範囲を返す
    Set ColumnDataArea = targetWorksheet.Range(tableColumns.item(columnTitle).Address)
End Property

ColumnTitleCellプロパティも同様にtableHeaders コレクションのItem (セルRange オブジェクト)を Key (列タイトル文字列) で呼び出します

Public Property Get ColumnTitleCell(ByVal columnTitle As String) As Range
'表のカラムタイトル群（1行目）に含まれる指定の文字列を含むセルを返す
    Set ColumnTitleCell = targetWorksheet.Range(tableHeaders.item(columnTitle))
End Property

FindCommonAreaメソッドはソースコードは載せておきますが、説明が少々ややこしいので次の記事で書こうと思いますｗ

Public Function FindCommonArea(ByVal targetColumnTitle As String, ParamArray refAreas() As Variant) As Range
'表内の複数の範囲に共通する範囲を返す

        Dim targetColumnArea As Range
        Set targetColumnArea = ColumnDataArea(targetColumnTitle)
        
        Dim refInitialRows() As Variant
        Dim refLastRows() As Variant
        Dim refRows As Variant
      
        
        ReDim refInitialRows(UBound(refAreas))
        ReDim refLastRows(UBound(refAreas))
        ReDim refRows(UBound(refAreas))
        Dim commonRow As Range
        With targetWorksheet
            Dim i As Long
            For i = LBound(refAreas) To UBound(refAreas)
                refInitialRows(i) = refAreas(i).Cells(1).Row
                refLastRows(i) = refAreas(i).Cells(1).Row + (refAreas(i).Cells.count - 1)
                Set refRows(i) = .Rows(refInitialRows(i) & ":" & refLastRows(i))
                Set commonRow = refRows(LBound(refAreas))
                Set commonRow = .Range(Intersect(commonRow, refRows(i)).Address)
            Next
              
            Set FindCommonArea = .Range(Intersect(targetColumnArea, commonRow).Address)
        End With
End Function

まとめ

今回の記事では表データ集計解析マクロ作成時にこりゃ使い勝手がいいぞい(当社比)なプロパティ、メソッドを詰め込んだクラスを紹介しました。表データの集計や解析用マクロでは表全体のデータ範囲や特定の列に含まれるデータ範囲やその列タイトルセルを取得する場面がたくさんあります。これまでは対応するメソッドを毎回標準モジュールに用意していたのですが、コードが整理できなくっていまいちな感じでした。今回その辺をクラスとしてまとめたわけですが、実際に使ってみてコード全体を整理しつつシンプルで読みやすく記述できるようになったなぁと思います。

TLを見ていると、独自のライブラリを作成して運用されている方が多いように思えますが、今回は小さな処理ではありますが、目的別に専用のライブラリを準備しておくと後々資産になるんだなぁと実感しました。

因みに

今回のコーディングでは、表タイトル文字列から対応するデータ範囲を呼び出すために、１対１の対応関係を集積するCollectionの仕組みを利用することを思いついたことが個人的には大きな発見でした。同様に１対１を集積するDictionaryももちろん利用できます思います。事前バインディングまたは事後バインディングが必要な点でひと手間がかかりますが、登録したKeyを配列として呼び出すことができるなどCollectionではできないことも多いので、目的に応じて使い分けるといいかなぁと思います。

次は積み残しているFindCommonAreaメソッドについて書こうと思います

でわまた～ ￣▽￣)ﾉｼ