配列?構造体?いやレコードセットでしょ!エクセルVBA
二次元データの処理するとき、レコードセット使ってますよね??
え!?使ってない!!?
もし使ってなかったら、
今日から使い始めましょう!!
Excel VBA 本格入門 ~日常業務の自動化からアプリケーション開発まで~
- 作者: 大村あつし
- 出版社/メーカー: 技術評論社
- 発売日: 2015/05/16
- メディア: 大型本
- この商品を含むブログ (1件) を見る
なんでレコードセットがいいの?
レコードセットってそもそもがデータベースから取得したデータの塊を格納するためのもの。 だからデータの扱いなら任せとけっていうオブジェクトなワケです。
じゃあ具体的にどんなメリットがあるのか配列と構造体配列と比較していきましょー。
だって列(フィールド)ごとにデータの型を決められる
二次元のデータで全ての列(フィールド)が同じデータ型になることってあまりないですよね。
例えばエクセルに株価のヒストリカルデータを表示させる場合でも、一番左の列が日付(Date)、次の列が株価で整数(Long)か小数点(Double)、さらにメモ用の列があったり(String)。
レコードセットの場合、列(フィールド)ごとに型を決めることが出来ます(というか決める必要があります)。
Private Sub makeRecordset() Dim adoRS As ADODB.Recordset Set adoRS = New ADODB.Recordset With adoRS With .Fields .Append "blDate", adDate .Append "blPrice", adDouble .Append "blMemo", adVarChar, 32, adFldIsNullable End With .Open End With End Sub
レコードセットオブジェクトにフィールドを追加(Append)するときに、そのフィールドの名前と型を指定します。
Appendの後に続く引数で最初に来るのがフィールド名。
ここでは日付、株価、メモの3つのフィールドが必要なので、それに相応するフィールドを用意しています(フィールド名頭のblはbokulogってことです^^)。
次の引数がそのフィールドの型。日付型、浮動小数点型、可変長文字列型を指定しています。
可変長文字列型は最大32文字。メモを書かない日もあることからNullもOK。
一方、営業日のみのデータを前提とした場合は日付と株価は必ず存在するためNullはNG。
ちょっと煩わしいコードに見えますが、慣れらたなんてことはねーっス。
一方、配列の場合、指定できる型は一つだけ。
だから、いくつもの型が入り混じっているときはVariantにするしかないんです。
コレ、キッチリ型を決めたいぼくには屈辱でしかないッス。
次なる策は構造体配列。
これであれば列ごとに型を決めることが出来ます。
複数の型が一堂に会するデータの塊を扱うときは構造体配列を使うのもひとつの手です。
だってデータの追加が超絶ラク
AddNew からの Update
この呪文を唱えるだけでOKです。
With adoRS .AddNew !blDate = #11/23/2018# !blPrice = 21800 !blMemo = "さらに上昇!" .Update End With
こんなカンジ。
至極簡単すわ~。
配列だとこんなにサクっとは出来ない。
Redim Preserve ってのはあるけど二次元配列の場合、行の追加って感じにはならずに、横方向、つまり列の追加ってことになってしまうのが悲しい現実。
だから配列の場合は、Preserve使って都度都度データを追加するというよりは、最初に格納するデータの範囲を確認して、それにフィットするサイズの配列を作ってからデータを入れていくって流れにならざるを得ない。
構造体配列の場合は、Redim Preserve でイケます。
いつもの作業を自動化したい人の Excel VBA 1冊目の本
- 作者: 大村あつし
- 出版社/メーカー: 技術評論社
- 発売日: 2017/06/20
- メディア: 単行本(ソフトカバー)
- この商品を含むブログを見る
だってデータを一気に吐き出せて幸せ
CopyFromRecordset
この呪文を唱えるだけでOKなんです。
例えばCodeNameがwsRecordsetのワークシートのセル(1, 1)を起点にデータを吐き出す場合は、こう書くだけでOK.
With wsRecordset .Cells(1, 1).CopyFromRecordset adoRS End With
データの行数・列数を気にせずCopyFromRecordsetするだけで、レコードセットがすべてバコっと吐き出されます。
一方、配列の場合は、データを吐き出す前に行数・列数を把握してデータが吐き出されるセル範囲を指定する必要があります。
例えばこうです。
With wsRecordset .Cells(1, 1).Resize(UBound(varData), UBound(varData, 2)).Value = varData End With
なんかクドイっすよね。
それでも配列の場合はデータを一気に吐き出せるだけまだマシ。
構造体配列の場合は、一気に吐き出す術がありません。
1つ1つ丁寧に出していくしかないんです。
だってデータにフィルタを掛けるのも楽勝
エクセルのオートフィルタってやつ。
レコードセットでも出来ます。しかもシンプルに。
先ほどの株価の表で22,000円未満の日だけ抽出したい。
こんな時はこれだけです。
adoRS.Filter = "blPrice < 22000"
何やってるかすぐ分かりますよね。
さらに例えばいついつ以降のデータから抽出 でってのも簡単に加えられます。
adoRS.Filter = "blPrice < 22000 AND blDate >= #2018/11/20#"
分かりやすっ!
さらに並び替え。
adoRS.Filter = "blDate DESC"
コレ、配列あるいは構造体配列でやろうとするとここまで簡単には出来ない。
For~Next と If を組み合なんでわせて該当するデータ行を別の配列に入れたり、あるいはシートに吐き出していくしかありません。
ちょっとタラタラ長いコードが出来上がることになります。
- 作者: 吉田拳
- 出版社/メーカー: 技術評論社
- 発売日: 2016/06/08
- メディア: 単行本(ソフトカバー)
- この商品を含むブログ (3件) を見る
データを加工するときはレコードセット一択
どう考えてもシート上のデータの扱いにおいてレコードセットに勝るものはなし。
巷にレコードセットを推奨する話があまりないのが不思議で仕方ないです。
とはいえぼくもレコードセットの存在を知ったのはデータベースを弄るようになってから。
それはまでは配列、あるいは構造体配列でFor~Nextでゴリゴリっとしたコード書きまくってました。
そんな中、レコードセットとの出会いはぼくに革命を齎しました(言い過ぎ)。
だってそれまでコードをグリグリ書いてやったことを、たった1行で出来ちゃうんだもの。
この感動、みなさんにも味わっていただきたいです。