プログラミングをする上で欠かせないのが「配列処理」。Google Apps ScriptでもExcel VBAでも、この配列処理は必須の処理です。配列を使わずに例えば、Excelのマクロ的に1つずつセルを取得しては処理して移動みたいな動作は、プログラムの動作速度上非常に遅いだけでなく、プログラムが冗長になりメンテナンス性が損なわれます。

でかいシートデータを配列で取得して、様々な加工処理をメモリ上で行い、最後に一気に書き込み等をするといった作業が配列の主な役割ですが、VBAの場合およそ3種類ほどこの配列的な操作方法があります。今回はこの処理についてまとめてみました。

※配列の基礎的な内容ではなく、応用編になります。

今回使用するサンプルファイル

※VBA-JSONは本項目の中では異色ですが、非常に有効な手段ですので身につけておいて損はありません。

※サンプルファイルのデータは、テストデータ・ジェネレータ様のサイトを利用させていただきました。

ソース

通常の配列操作

指定範囲を配列データとして取得する

Excelのシートに於いて、指定した範囲のデータを配列として取得する事が可能です。1セルずつ取得して検証を行うのではなく、まとめてデータ処理を行う事で、動作の高速化コードの短縮化に繋がり、プログラムの見通しが非常によくなります。以下に自分がよく使うコードについて記載をしてみます。

  • dummyarrが配列の入れ物になります
  • 対象となるのはサンプルファイルのdummyという名のシートです。Worksheetsとして指定します。
  • 全データを今回取得するのですが、データの最終行を知る必要があるので、UsedRange.Rows.Countにてデータとして有効な最終行の行番号を取得しています。
  • Range指定にて、範囲指定としてA2:Lの最終行を指定し、dummyarrに格納しています。
  • 格納されたデータは二次元配列として格納されています。
  • withを使うとスッキリしたコードとして書けます。

動的に配列に値を追加する

JavaScriptなどは配列を用意してその配列にデータを追加したい時、pushしたりすればすんなり簡単に配列を追加する事が可能です。また、配列の中の順番を入れ替えてみたり、中身を操作してみたりもメソッドが用意されてるので非常に楽に配列操作が可能になっています。

しかし、VBAの場合言語自体が古い時代のもので、動的にホイホイそのままレコードの追加みたいな事ができません。ReDimというメソッドが用意されているのですが、今回のようなシートのデータの場合、配列の再定義ができるのは二次元目だけ(つまり列数)しか再定義が出来ません

これでは行数を増やせないので困ります。そこでExcelのTranspose関数を組み合わせて、拡張後にも再度Transpose関数で行列入れ替えをして戻すというテクニックで指定の行数分だけ配列を拡張する事が可能です。また、この方法の場合、再定義後に配列のデータが消えるといった事もないので、動的に配列を拡張し、データの追加が可能です。

  • 1回目のTranspose関数で配列データの縦横を入れ替えています。
  • ReDim Preserveにて要素数として指定のdlength分だけ行を拡張しています。
  • 最後値を返す前に、再度Transpose関数にて行列を入れて元通りにして、返しています。

上記のchgReclength関数を利用して1行追加し、その行に値を追加する場合は以下のようなコードになります。

  • 元のデータは4999件でしたが、chgReclength関数にて1件レコードを追加して5000件になっています。
  • その後最終行の要素に1つずつ値を格納し、debug.printで電話番号を参照してチェックしています(今回のケースで言えば、03-1259-9644が返ってくるはずです)

配列データのシート一括書き込み

VBAの場合配列データは、範囲指定をしてそこに配列を突っ込めばそのまま書き込みができる利便性があります。また、こうすることで1セルずつ書き込みといったような時間の掛かる処理をする必要がなくなり、短時間で処理が可能です。但し書き込みをする時に、要素数を足していた場合には、指定の範囲と配列の範囲がきちんと一致しているか?注意が必要です。

  • 書き込み先の範囲指定も取得時と同様に範囲指定を行う。
  • Rangeの指定範囲に対して、配列を渡してあげています。但しこの時のレコード数はlastrowが5000なので1加えた5001としています(でないと追加した1行が欠損する)
  • 無事に書き込みができれば、5000件目として追加したデータが5001行目に出てきます。

JSON形式の処理を利用した方法

素のVBAは古い言語であるため、JSONを扱う事が出来ません。しかし、VBA-JSONを使う事でJSONを扱うことが出来ます。JSON形式を用いた配列の処理は、一見すると面倒に見えますが単純な2次元配列として利用する場合、○列目といったような処理ではなく「列名」を用いて値の代入や参照ができるため、実務上非常に便利なシロモノです。

今回はVBA-JSON等を用いて、JSON形式の配列の作成およびその値の参照を行ってみたいと思います。事前に標準モジュールとしてJsonConverter.basを追加しておく必要があり、また参照設定としてMicrosoft Scripting Runtimeが必要です。

※最近はOfficeも64bitが標準になりつつあり、従来のScriptControlを使った手法は使えなくなりました。

JSON連想配列をデータの作成

VBA-JSONは文字列をJSONにする事はできますが、JSON文字列自体を作るようなメソッドはありません。VBAではJSONデータは普通にループを用いてゴリゴリ作成します。

  • タイトル行のデータだけ別の配列として取得しておきます。
  • レコード分だけループで回しながら、さらにループでタイトル行の配列の値を持って、JSONを組み立てて値を格納してゆきます。
  • 最後にVBA-JSONのJsonConverter.ParseJsonにてJSONとして取得させます。
  • 5000行のデータを元に構築させたら結構な時間が掛かりました・・・・あまり大規模なデータ処理には向かないと思います。
  • 一度JsonConverter.ParseJsonしてしまうと、そのオブジェクトに対して要素追加の手段がない(Scriptcontrolを使った場合、pushができるかもしれないけれど)

JSON連想配列データを参照

JsonConverter.ParseJsonしたオブジェクトから、JSONの値を取り出すためには、For eachで回して1行ずつ取り出す方法があります。通常の配列と違い一括で書き込み等はできない(もちろん、配列を用意してそこへJsonから取り出した値を格納したのち、配列を一発書き込みは可能)。

そこで1行ずつ取り出しては、1行ずつ書き込みをするコードを使います。

  • for eachにてjsonrecで1行取り出し、tempArrayにArray関数にて1行分の配列を作って、それを書き込みしています。
  • jsonrecで列名を指定すれば、列がランダムでもきちんと取り出せるため、元データ側にて「必要な列は用意されてる」「列名に変更はない」のであれば、その他の列が増えようが消えようが、コードはきちんと動作します(JSONを一番の理由はこれ)

ADOレコードセットを使った手法

Accessではおなじみの方法なのですが、Excel VBAではあまり使う機会が少ないかもしれない、けれど非常に便利で2次元配列を扱うのであるならば、レコードセットを使った配列処理がオススメです。配列のような簡便な機能ではなく非常に高機能で、ノウハウもたくさんあるので、メモリ上でデータ処理をしたいのであればレコードセットのほうがGoodです。

レコードセットの作成と追加

ExcelでADOを使う場合には、事前に参照設定を入れておきましょう(CreateObjectで入れなくても使えますが、入力補完が効かないので)。参照設定として追加する項目は「Microsoft ActiveX Data Objects 2.8 Library」です。

サンプルファイルにあるdummyという名前のシートデータを取得し、レコードを追加してみたいと思います。

  • ADOを使う場合は必ずフィールドの型指定が必要です(配列だと全部Variant型でなんて大雑把な方法ですが)
  • フィールドにはNullでも良いよといったオプション指定を追加する事が可能です。
  • adVarChar型などの場合、必ずフィールドのサイズ(文字数)を指定する必要があります。ただしこのサイズには上限があるので注意。
  • データの追加は、.AddNewにて追加し、.Updateにて確定します。以上に簡単に的確な場所へ追加が可能です。
  • 他にも.Filterにてデータのフィルタリングやソートが可能(例:rst.Filter = “血液型=AB”  rst.Filter = “血液型 DESC”)
  • レコードセットに対して、SQL文が利用可能です。
  • また、.Moveメソッドにてジャンプ、.bookmarkメソッドにてレコード位置を記憶などの豊富なメソッドが利用可能です。

図:参照設定を入れておくと楽です。

レコードセットのデータの参照

レコードセットの参照も非常に簡単です。今回は取得したレコードセットの中身のうち、電話番号だけを全部ピックアップするコードを書いてみました。

  • レコードセット追加直後は、レコードセットの末端にいる状態なので、まずは.MoveFirstでレコードの先頭まで移動します。
  • Do Until rst.EOFループにてレコードセットの終端までループさせます。
  • rst(フィールド名)にて、値を取り出す事が可能です。また、rst!フィールド名でも可能ですが、予約語などとバッティングした場合エラーになるので注意。
  • 必ず.MoveNextで次のレコードへ移動しないと、無限ループになるので注意

シートへ一発書き込みをする

配列の利点は、配列データを指定の範囲内に一括で書き出しができる点でした。他の配列系の処理は一括書き出しをするメソッドがないので不便ですね。レコードセットにはCopyFromRecordsetという便利なメソッドがあり、あっさり一発で出力が可能です。AccessからExcelをオートメーション処理で出力する際にもよく利用します。

  • 配列と違い、レコードのレコード数と列数を意識する事なくメソッド一発で、指定の位置からデータを吐き出せます。
  • レコード追加直後はレコードセット末端にいるので、必ず.MoveFirstで先頭に移動してから出力しましょう。でないと最後の1行だけが出力されてしまいます。
  • 一発書き出しなので非常に高速にシート上へデータの展開が可能です。
  • ADOでのレコード処理は非常に高速なので、速度面でもADOの利用は推奨です。

Dictionaryを利用した二次元配列

配列はデータの追加などが非常に面倒でどうにかしたい!という方が、ウェブを探して次に行き当たる方法が「Dictionaryを二重に利用した二次元配列構造」です。単一のDictionaryですと1次元でしかないので、KeyとItemの対の構造が出来ておしまいであるため、表のような二次元配列としては利用できません。

今回利用するDictionaryは、事前に参照設定を入れておきましょう(CreateObjectで入れなくても使えますが、入力補完が効かないので)。参照設定として追加する項目は「Microsoft Scripting Runtime」です。

図:Scripting Runtimeは非常によく利用します

Dictionaryデータの作成

親となるDictionaryとは別にループ内で子となるDictionaryを作成し、その中に配列のデータを流し込んでいます。Dictionaryはaddメソッドにて簡単にレコードデータを追加できますが、必ずキーと値というペアで設定が必要になります。そこで今回は、キーにタイトル行の値を利用し、値は各レコードの値を入れるようにしました(arrListというオブジェクト内にキーが入っていますので、arrList(1)でIDというキーが取り出せます)

  • oDic.Keys(i)にて、レコード番号が取り出せます
  • bufには、カンマ区切りでDictionaryのデータが構築されて出力されて来ます。
  • .addメソッドの1つ目の引数である「キー」にはタイトル行の名前を入れ、2つ目の引数である値に各レコードの値を格納しています。
  • 作り方はJavaScriptの多重連想配列の作り方に似ていて、一時配列を親の配列に都度pushするような感じです。
  • Debug.Print oDic(oDic.Keys(2))(arrList(2))とすると、3行目の3列目であるオカザキコウキという値が手に入ります。

Dictionaryの書き出し

Dictionaryは配列ではないので、一気に書き出しするような事はできませんが、前回のコードの最後ではカンマ区切りで1行のレコードが返ってきました。そこでこれをSplit関数で一次元配列にし、1行単位で書き込むようにしてみました。

  • bufの値はカンマ区切りなので、Split関数にて一次元配列にした状態のものを書き込むワークシートの範囲を指定して代入するだけで書き込みがなされます。
  • Dictionaryの操作は決して早いものではないので、大規模に使うのであればレコードセットの使用をおすすめします(階層が深くなるとわけわからなくなりますし)。

関連リンク

共有してみる: