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

毎日暑い日が続きますねぇ、コンチクショウ。

エアコンが効いた部屋から一歩も出たくないあてくしです

こんちくわ壁|ω・)ﾉ

さて今回は前回の記事で積み残していたFindCommonAreaメソッドのオハナシです。

でわいきます￣▽￣)ﾉ

これってコードにすると結構ややこしいし、解析手法によっては抽出条件数は一定ではないのです。そこで、せっかく表専用ツールを作るのだから抽象化したコードで、しかもいくつの条件を使った抽出にも対応するメソッドを作ってしまおうと思ったのです。

というわけで、前回積み残していたFindCommonAreaメソッドに移ろうと思いますが、上記の目的を達成するためにキーとなる Intersect メソッドとParamArrayキーワードについて簡単に説明しておこうと思います

Intersectメソッド

Inrtersectメソッドは複数のセル範囲について共通する領域をRange()オブジェクトとして返す関数です。

Intersect(Arg1 As Range, Arg2 As Range, [Arg3], [Arg4], [Arg5], [Arg6]) as Range　'[Arg3]以降は任意

例えば以下のようなケースでは、
　 f:id:FukuCyndiP:20200815173818p:plain

Intersect(Range("A5:E8"),Range("B1:C12")) 'Range("B5:C8")

と記述することで、２つのRangeオブジェクトに共通するRange("B5:C8")が返ってきます。

因みに共通する部分がなかった場合には、

f:id:FukuCyndiP:20200815173843p:plain

とVBEが実行時エラーを吐き出すことになります。

ParamArrayキーワード

いくつの条件を使った抽出にも対応するメソッドを作ろうと、上に書きました。その "いくつの" を実現するのがParamArrayです。メソッドは抽出の条件となるCell範囲を引数として受けることになるのですが、VBAにはこの引数を可変長配列として受け取る仕組みとしてParamArrayキーワードが用意されています。

Sub/Function メソッド名(ParamArray varname() as type)
    
End Sub

ParamArrayキーワードは以下の掟に従って使うことになります
(by タカーシ@パーフェクト Excel VBA)

パラメータ配列は引数リストの最後の引数のみ適用でき、自動的にオプションになる
ほかの引数はオプションにできない
ByVal, ByRefキーワードは付与できず、パラメータ配列は自動的に参照渡しになる
パラメータvarnameの後ろに丸かっこの記述が必要
パラメータのデータ型typeはVariant型とする
パラメータ配列のインデックスの下限値は OptionBase ステートメントの影響を受けずに常に0となる

というわけで、先週掲載したコードを見てみましょう。

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

引数関係は以下のようなイメージになっています。

f:id:FukuCyndiP:20200815173904p:plain targetColumnTitleが文字列となっているのは、最終的に取得したいセル範囲が表のどの列に属するかをコード上で明確にするためです。

コード上の処理は以下の通り

ParamArrayで配列として取得したrefArea()のそれぞれのRange()オブジェクトからその行範囲を取り出して行範囲に変換したrefRows()を作成(例 Range("A3:A6")→Rows("3:6"))
refArea配列の最初の要素refArea(0)を一旦変数commonRowに代入
Intersect関数を使ってcommonRowとrefArea配列の各要素との共通領域(行範囲)を取得して、comonRowの中身を置き換える(配列の要素でループ)
ループ終了後に取得されたcommonRowと引数targetColumnTitleから取得しておいたtargetColumnAreaとの共通範囲をIntersect関数で取得する

まとめ

今回の記事では表に記載された複数の条件に合致する範囲を抜き出すFindCommonAreaメソッドを紹介しました。コーディングの方法としては複数のRangeオブジェクトに共通する範囲を返すIntersect関数を利用するとともに、PararmArrayキーワードを使って条件数を制限しない形で引数を受け取る仕組みも合わせて組み込むことができました。

私たちが実験結果の解析で利用する表は、パッと見では行x列の２次元で構成されているクロス集計表の形をしているのですが、実験結果の数値の他にサンプル名や複数の実験条件を記載した列を複数含むことがほとんどで、

見た目２次元でも解析は3次元、4次元じゃん!!

みたいなことによくなります。

様々な切り口で結果を集計して考察するためではあるのですが、オートフィルタの抽出機能で結果を集計するにしても解析対象サンプル数や解析の切り口が多くなってくるとそれも時間のかかる大変な作業になってきます（抽出結果を別シートに転記することになることも少なくない)。それがさらにルーチンワークになると、もぅ・・・ね。

というわけで、そんな時のためのVBAワンポチ解析の友としてFindCommonAreaメソッドを作成したので、今後活躍してくれると期待しております。

因みに

引数として受け取ったRangeオブジェクトを行範囲に変換する、

Range("A3:A6")→Rows("3:6")

の処理では変数を使って複数の行を選択することが必要だったわけですが、

Rows(var1 & ":" & var2)

みたいな書き方が必要だったのが何気にイマイチでした。

当初は、

Range("A1:A10").Rows

で、スマートに取得することを頭に描いていたのですが、コレで帰ってくる範囲のアドレスは $A$1:$A$10 なんですよね。。

まぁ、決まりだからしょうがないのですけどね。。
なんか忘れている気がしなくもないけど -ω-)

でわまた～￣▽￣)ﾉｼ

それわVBA案件ですね

エクセルVBAネタを書いています

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

目次

背景

Intersectメソッド

ParamArrayキーワード

FindCommonAreaメソッド

まとめ

因みに