表を配列として保持するクラス

表を配列化して保持するクラス

前回記事へのコメント

Edgeがバグっているせいなのか、なぜかブログにコメントが返せない状態です。

akashi-keirin.hatenablog.com

コチラに、id:imihito さん、thom (id:t-hom) さんのお二方からコメントをいただいているにもかかわらず、お礼を申し上げることすらできない状態。情弱の私には何が起こっているのか分かりません。

実は、thom (id:t-hom) さんのコメント

かつてVirtualRangeという配列を内包した仮想Rangeクラスを作ろうとしたことがあります。
RangeとCellsプロパティがあり、配列の高速性とシートの使い勝手を両立させたものです。
私は妄想を膨らませすぎて色々機能をつけようとしすぎて自滅しましたが興味があればぜひ作ってみてください。

を読んでビックリしたのです。

私がやろうとしていたのもそんな感じのことだったので!

もちろん、だいぶ程度の低いことではありますがw

表の内容を配列として持ち、利用する

私がやりたいと思っていたのは、

表を2次元配列として保持して、VLOOKUP的なことをしてくれるクラス

だったんですよ。

今まで、表引きをするときは、

akashi-keirin.hatenablog.com

こんな感じで、シートの中にVLOOKUP専用セルを作って対応していた。

しかしながら、このやり方はなんかイマイチだなあ、と。

もうちょっとプログラミング的なやり方(って何やねんw)がしたいと思ったのですよ。

折から

シートを参照するのは処理速度の観点からはイマイチ

という知識を得たもんだから、いっちょやってみましょうか、となったわけ。

とりあえずクラス化

クラスモジュールを挿入して、オブジェクト名を「VirtualTable」とする。

クラス名の「Virtual」はthom (id:t-hom) さんからのパクりw

リスト1 クラスモジュール
Option Explicit
'Fields'
Private isInitialized_ As Boolean
Private tableArray_ As Variant
Private returnValue_ As Variant
Private rowsCount_ As Long
Private columnsCount_ As Long

'Accessor'
Public Property Get rowsCount() As Long
  If Not isInitialized_ Then Exit Property
  rowsCount = rowsCount_
End Property
Public Property Get columnsCount() As Long
  If Not isInitialized_ Then Exit Property
  columnsCount = columnsCount_
End Property
Public Property Get valueOfCell(ByVal i As Integer, _
                                ByVal j As Integer) As Variant
  valueOfCell = tableArray_(i, j)
End Property

'Constructor'
Public Sub init(ByVal targetTableRange As Range)
On Error GoTo errorHandler
  tableArray_ = targetTableRange.Value
  rowsCount_ = UBound(tableArray_, 1)
  columnsCount_ = UBound(tableArray_, 2)
  isInitialized_ = True
  Exit Sub
errorHandler:
End Sub

'Method'
Public Function searchValueVertical( _
                  ByVal searchFor As Variant, _
                  Optional ByVal searchColumn As Long = 1, _
                  Optional ByVal returnValueColumn As Long = 1) As Variant
  Dim i As Long
  Dim tmp As Variant
  For i = 1 To rowsCount_
    tmp = tableArray_(i, searchColumn)
    If tmp = searchFor Then
      returnValue_ = tableArray_(i, returnValueColumn)
      searchValueVertical = returnValue_
      Exit Function
    End If
  Next
  searchValueVertical = False
End Function

クラスモジュールゆえ、記述が長くなってすまん。

Fields部
Private isInitialized_ As Boolean    '……(1)'
Private tableArray_ As Variant    '……(2)'
Private returnValue_ As Variant    '……(3)'
Private rowsCount_ As Long    '……(4)'
Private columnsCount_ As Long

まずは、内部変数の数々。

(1)の「isInitialized_」は、コンストラクタ実行済みかどうかを表すBoolean型変数。

VBAでは、コンストラクタに引数が渡せないので、別途initメソッドを持たせて対応することが多いと思う。

となると、怖いのがinitメソッドの実行し忘れ。この変数は、その対策用。

(2)の「tableArray_」は、2次元配列化した表をぶち込むための内部変数。

(3)の「returnValue_」は、クラス内部でなんらかの処理をした際に結果として得られた値をぶち込むための内部変数。いろんなデータ型に対応しないといけないので、Variant型にしている。

(4)の「rowsCount_」、「columnsCount_」は、それぞれ元の表の行数、列数をぶち込んでおく内部変数。

akashi-keirin.hatenablog.com

コチラでも紹介したように、表をそのままぶち込んだ2次元配列は、添字が「1」始まりなので、Forループで回すときの最終値(要するに添字の最大値)は元の表の行数・列数に一致する。

Accessor部
Public Property Get rowsCount() As Long    '……(5)'
  If Not isInitialized_ Then Exit Property    '……(6)'
  rowsCount = rowsCount_    '……(*)'
End Property
Public Property Get columnsCount() As Long
  If Not isInitialized_ Then Exit Property
  columnsCount = columnsCount_
End Property
Public Property Get valueOfCell(ByVal i As Integer, _
                                ByVal j As Integer) As Variant    '……(7)'
  valueOfCell = tableArray_(i, j)
End Property

コチラはアクセサ部分。(5)からの4行は、「rowCount」プロパティ取得用のgetterメソッド。「columnCount」プロパティについても、内容はほぼ同じ。

(6)の

If Not isInitialized_ Then Exit Property

では、isInitialized_の値を調べて、Falseだったら何もしないようにしている。

isInitialized_は、後述するinitメソッド実行後にTrueになるようにしている。

(*)についても、initメソッドのところで述べる。

(7)は、「valueOfCell」プロパティ取得用のgetterメソッド。

引数で行数(i)、列数(j)を受け取って、対応するセルの値を返す、というもの。セルの値のデータ型は分からないので、Variant型にしている。

Constructor部
Public Sub init(ByVal targetTableRange As Range)
On Error GoTo errorHandler
  tableArray_ = targetTableRange.Value    '……(8)'
  rowsCount_ = UBound(tableArray_, 1)    '……(9)'
  columnsCount_ = UBound(tableArray_, 2)
  isInitialized_ = True    '……(10)'
  Exit Sub
errorHandler:
End Sub

コンストラクタと言っても、Newした時点で自動的に実行してくれるわけではないので注意。

(8)の

tableArray_ = targetTableRange.Value

でVariant型の内部変数tableArray_に表をそのままぶち込む。

んで、(9)からの2行

rowsCount_ = UBound(tableArray_, 1)
columnsCount_ = UBound(tableArray_, 2)

で内部変数rowsCount_、columnsCount_に、tableArray_の次元ごとの添字最大数、すなわち表の行数、列数をぶち込んでおく。

あとは、(10)でisInitialized_をTrueにする。

initメソッド実行中にエラーが出たら、何もせずにinitを抜けることになるので、isInitialized_はFalseのはずだ。

Method部
Public Function searchValueVertical( _
                  ByVal searchFor As Variant, _
                  Optional ByVal searchColumn As Long = 1, _
                  Optional ByVal returnValueColumn As Long = 1) As Variant
  Dim i As Long
  Dim tmp As Variant
  For i = 1 To rowsCount_    '……(11)'
    tmp = tableArray_(i, searchColumn)
    If tmp = searchFor Then    '……(12)'
      returnValue_ = tableArray_(i, returnValueColumn)    '……(13)'
      searchValueVertical = returnValue_    '……(14)'
      Exit Function
    End If
  Next
  searchValueVertical = False    '……(15)'
End Function

とりあえず、一つだけメソッドを実装してみた。

第1引数searchForが検索値、

第2引数searchColumnは、検索値を探す表の列番号、

第3引数returnValueColumnは、検索値とマッチした場合に何列目の値を返すのか、ということ。

要するに、VLOOKUPの簡易版みたいなイメージ。

単純なコードなので、簡単に説明しとく。

(11)からのForループで、検索値を検索対象列の1行目から順に比較して、マッチしていたら返り値用の列の値を返す、というだけの処理。

従って、検索対象列に検索値とマッチする値が2つ以上あったら、一番上にあるやつ以外は無視される、という仕様。今のところは。

Forループの内部では、(12)の

If tmp = searchFor Then

で検索対象地の i 列目の値と検索値を比較し、一致していたら、(13)の

returnValue_ = tableArray_(i, returnValueColumn)

でまず内部変数returnValue_に返り値用の列の値をぶち込み、

(14)の

searchValueVertical = returnValue_
Exit Function

でreturnValue_の内容をreturnして処理を抜ける。

あと、Forループを全て実行してなおこのFunctionから抜けていないということは、検索値にマッチしなかったということだから、(15)の

searchValueVertical = False

でFalseを返すことにする。検索値にマッチした結果「""」(空文字列)が返った場合と、検索値にマッチしなかった場合を区別するために。

とりあえず、以上のような簡単なクラスにした。

ちなみに、searchValueVerticalメソッドがVLOOKUP関数よりも優れている点としては、

検索対象列が表の左端(1列目)でなくてもよい

という点が挙げられるかと。

使ってみた

f:id:akashi_keirin:20180212233440j:plain

こんなふうに表を2つ用意して、次のコードで実験。

スト2 標準モジュール
Public Sub testVirtualTable()
  Dim Sh As Worksheet
  Set Sh = ThisWorkbook.Worksheets("Sheet1")
  Dim vtlTable1 As New VirtualTable
  Dim vtlTable2 As New VirtualTable
  vtlTable1.init Sh.Range("A1").CurrentRegion
  vtlTable2.init Sh.Range("J1").CurrentRegion
  With vtlTable1    '……(16)'
    Debug.Print .valueOfCell(37, 2)
    Debug.Print .searchValueVertical("中野 浩一", 1, 2)
  End With
  With vtlTable2
    Debug.Print .valueOfCell(7, 4)
    Debug.Print vtlTable2.searchValueVertical("関勝", 1, 4)
  End With
End Sub

VirtualTableクラスのインスタンスを2つ生成して、それぞれinitメソッドを実行し、valueOfCellプロパティの値、searchValueVarticalメソッドの返り値をイミディエイトに表示するだけのコード。

一応(16)だけ簡単に説明しとく。

With vtlTable1
  Debug.Print .valueOfCell(37, 2)    '……(a)'
  Debug.Print .searchValueVertical("中野 浩一", 1, 2)    '……(b)'
End With

vtlTable1について、(a)では、

.valueOfCell(37, 2)

valueOfCellプロパティを、引数に(37, 2)を指定して呼び出しているので、

f:id:akashi_keirin:20180212233440j:plain

この画像の左側の表の37行2列目の値が返ることになる。

(b)の

.searchValueVartical("中野 浩一", 1, 2)

では、searchValueVarticalメソッドに、引数("中野 浩一", 1, 2)を渡しているので、同じく

f:id:akashi_keirin:20180212233440j:plain

この画像の左側の表の1列目から「中野 浩一」を探して、マッチした行の2列目の値が返ることになる。

実行結果

f:id:akashi_keirin:20180212233448j:plain

一応、意図通りの結果が得られた。

おわりに

これからちょこちょこ機能を追加して、使い勝手の良いものにしていけたらいいなあ。

VirtualTableクラスは今……

akashi-keirin.hatenablog.com

 

akashi-keirin.hatenablog.com

 こんなふうになっています。