ぼくLog

子持ち、車持ち、マンション持ちの僕の日常を綴ります。車と、ラクをしたい一心で覚えたエクセルVBAを中心になりそうです。

配列?構造体?いやレコードセットでしょ!エクセルVBA

f:id:yt4u:20181204225012j:plain

二次元データの処理するとき、レコードセット使ってますよね??


え!?使ってない!!?


もし使ってなかったら、
今日から使い始めましょう!!



なんでレコードセットがいいの?

レコードセットってそもそもがデータベースから取得したデータの塊を格納するためのもの。 だからデータの扱いなら任せとけっていうオブジェクトなワケです。

じゃあ具体的にどんなメリットがあるのか配列と構造体配列と比較していきましょー。


だって列(フィールド)ごとにデータの型を決められる

二次元のデータで全ての列(フィールド)が同じデータ型になることってあまりないですよね。

例えばエクセルに株価のヒストリカルデータを表示させる場合でも、一番左の列が日付(Date)、次の列が株価で整数(Long)か小数点(Double)、さらにメモ用の列があったり(String)。

f:id:yt4u:20181123212411j:plain


レコードセットの場合、列(フィールド)ごとに型を決めることが出来ます(というか決める必要があります)。

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にするしかないんです。

コレ、キッチリ型を決めたいぼくには屈辱でしかないッス


次なる策は構造体配列。 これであれば列ごとに型を決めることが出来ます。

f:id:yt4u:20181123213149j:plain

複数の型が一堂に会するデータの塊を扱うときは構造体配列を使うのもひとつの手です。


だってデータの追加が超絶ラク


AddNew からの Update

この呪文を唱えるだけでOKです。

  With adoRS
  
    .AddNew
    
    !blDate = #11/23/2018#
    !blPrice = 21800
    !blMemo = "さらに上昇!"
    
    .Update
  
  End With

こんなカンジ。
至極簡単すわ~。



配列だとこんなにサクっとは出来ない。
Redim Preserve ってのはあるけど二次元配列の場合、行の追加って感じにはならずに、横方向、つまり列の追加ってことになってしまうのが悲しい現実。
だから配列の場合は、Preserve使って都度都度データを追加するというよりは、最初に格納するデータの範囲を確認して、それにフィットするサイズの配列を作ってからデータを入れていくって流れにならざるを得ない。


構造体配列の場合は、Redim Preserve でイケます。


いつもの作業を自動化したい人の Excel VBA 1冊目の本

いつもの作業を自動化したい人の Excel VBA 1冊目の本


だってデータを一気に吐き出せて幸せ


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 を組み合なんでわせて該当するデータ行を別の配列に入れたり、あるいはシートに吐き出していくしかありません。
ちょっとタラタラ長いコードが出来上がることになります。


たった1秒で仕事が片づく Excel自動化の教科書

たった1秒で仕事が片づく Excel自動化の教科書


データを加工するときはレコードセット一択


どう考えてもシート上のデータの扱いにおいてレコードセットに勝るものはなし。

巷にレコードセットを推奨する話があまりないのが不思議で仕方ないです。

とはいえぼくもレコードセットの存在を知ったのはデータベースを弄るようになってから。
それはまでは配列、あるいは構造体配列でFor~Nextでゴリゴリっとしたコード書きまくってました。

そんな中、レコードセットとの出会いはぼくに革命を齎しました(言い過ぎ)。
だってそれまでコードをグリグリ書いてやったことを、たった1行で出来ちゃうんだもの。
この感動、みなさんにも味わっていただきたいです。

yt4u.hatenablog.com

yt4u.hatenablog.com