表を配列として保持するクラス
表を配列化して保持するクラス
前回記事へのコメント
Edgeがバグっているせいなのか、なぜかブログにコメントが返せない状態です。
コチラに、id:imihito さん、thom (id:t-hom) さんのお二方からコメントをいただいているにもかかわらず、お礼を申し上げることすらできない状態。情弱の私には何が起こっているのか分かりません。
実は、thom (id:t-hom) さんのコメント
かつてVirtualRangeという配列を内包した仮想Rangeクラスを作ろうとしたことがあります。
RangeとCellsプロパティがあり、配列の高速性とシートの使い勝手を両立させたものです。
私は妄想を膨らませすぎて色々機能をつけようとしすぎて自滅しましたが興味があればぜひ作ってみてください。
を読んでビックリしたのです。
私がやろうとしていたのもそんな感じのことだったので!
もちろん、だいぶ程度の低いことではありますがw
表の内容を配列として持ち、利用する
私がやりたいと思っていたのは、
表を2次元配列として保持して、VLOOKUP的なことをしてくれるクラス
だったんですよ。
今まで、表引きをするときは、
こんな感じで、シートの中に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_」は、それぞれ元の表の行数、列数をぶち込んでおく内部変数。
コチラでも紹介したように、表をそのままぶち込んだ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列目)でなくてもよい
という点が挙げられるかと。
使ってみた
こんなふうに表を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)を指定して呼び出しているので、
この画像の左側の表の37行2列目の値が返ることになる。
(b)の
.searchValueVartical("中野 浩一", 1, 2)
では、searchValueVarticalメソッドに、引数("中野 浩一", 1, 2)を渡しているので、同じく
この画像の左側の表の1列目から「中野 浩一」を探して、マッチした行の2列目の値が返ることになる。
実行結果
一応、意図通りの結果が得られた。
おわりに
これからちょこちょこ機能を追加して、使い勝手の良いものにしていけたらいいなあ。
VirtualTableクラスは今……
こんなふうになっています。