配列や連想配列は、Ｐｅｒｌ，ＰＬ/Ｉ、Ｊａｖａ、Ｃ言語、ＳＱＬのプログラミング言語に定義されていますが、ＶＢＡは両方を網羅した二刀流なのがすごい － 算数の二次元表＝ＲＤＢ（そのほか）

プログラミング言語のマスコット達

前回に続き、リレーショナルデータベース（ＲＤＢ）をしつこく取り上げます。
カンマ区切りのテキストデータまら、それはもう、立派なデータベースなんだ。

なんだか拍子抜けしてしまうほどに、データ構造は区切り文字のある簡単な物。
でも、このデータを読み込んで検索しながら必要な情報を得るのは一苦労です。

まあ、こんな作業のためにプログラミング言語が必要ですが、ＳＱＬとかそう。
一方、自分はＶＢＡしか触ったことがないけれど、ＲＤＢをかなり操れるのだ。

まかり間違っても、ワークシートにデータを書き込む処理はしてはいけません。
エクセルは、この作業に膨大な処理時間を費やしてしまうので無意味でしょう。

元々、データの要素をカンマで区切った、いたってシンプルなデータ構造です。
これをグリッド線の視覚的な表に再現するのに、ＣＰＵが膨大に処理するんだ。

無駄な時間がかかるだけなので、メモリーで処理できることは処理しちゃおう。
こうして、計算した結果だけをワークシートに描き出せばよいだけなのですよ。

だって、数万行とか、大量のデータをベタのワークシートで見る人がいますか？
何らかの目的で検索して抽出されたデータを閲覧すれば、ＯＫと思うのが普通。

だから、ＶＢＡのプログラムで必要な作業を実行させて、結果だけを見るんだ。
このため、ＲＤＢを操るコードを書くため、色々と試行錯誤を繰り返しました。

段々とわかってきましたけど、重要な記述は限られると思うようになりました。
単純に言うと、配列と連想配列に関わるコード・関数ですが、これがノウハウ。

これを簡単に教えちゃうと、みんなＲＤＢを楽々操作できるようになるからね。
だから、ホームページで公開されているコードは、不親切で落とし穴が大きい。

サンプルで単発の短いコードぐらいだと割り切りながら、色々ググってみたり。
まあ、ＶＢＡの教師やコンサルみたいな人たちのサイトだから、ＰＲが目的だ。

ちゃんと教えちゃったら、商売にならないはずで、詳しくは有料だということ。
だから、ネットで紹介するコードは鵜呑みせずに、しっかり調べ上げましょう。

といわけで、ＲＤＢ処理のキモとなるサンプルコード決定版を紹介してみます。
ヒントとして言えるのは、レコードを一行ずつ読み込むコードが出発点なんだ。

ＲＤＢのファイルを読み込むのに、システムはファイル番号を振る必要がある。
これをネットのサンプルだと、＃１などと決め打ちしていますが、競合するの。

複数のファイルを読み込むと、どんな番号が割り振られるのか判断が付かない。よって、システムに一任する”FreeFile”という関数が大切だけど、みんな無視。

ここら辺がノウハウでしょうけど、それと消費したメモリーの開放も重要だよ。
例えば、連想配列は”～.RemoveAll”で消して、配列は”Erase”で初期化します。

こうしないと、使えるメモリーが足りなくなる可能性があるので、ご用心なの。
もっとも、プロシージャ内の変数は、それが終われば消去されるので問題ない。

問題はパブリック変数に設定した場合で、あまり不用意に設定しない方が無難。
残りはループ処理だけど、データを先頭から順番で読み込んでＥＯＦで終了だ。

ＣＳＶファイルは、必ず終端の目印がついているので、無限にはならないんだ。
というわけで、最後にサンプルコードを載せておきますので、結構役立つはず。

本当は、ADO接続でアクセスのファイルを読み込んだ検索で、Seekメソッドを使うと、キーで検索された最初と最後のデータ、または検索された直前直後のデータが拾えるなど便利だったのですが、これを連想配列の操作で置き換えてみたら、ある程度できたので、ちょっと紹介してみんなにも使ったもらいたいと思ったのでした。（逆に、もっと洗練されたコードがあったら教えてください。）

★サンプルコード
データベースからキーで検索して、該当する複数レコードから最終レコードの要素（9番目）を取得。複数データを連想配列に流し込んで最終行を取得、配列で要素を分割して取得しています。

Sub FormStart()

Dim SerchItemCord As String
Dim LineData As String
Dim ColData As Variant
Dim i As Integer
Dim j As Integer
Dim Dic As Object

Set Dic = CreateObject("Scripting.Dictionary")　
'連想配列の設定
i = FreeFile
’読み込みファイルの番号を割り当て（PC側で任意に振る）

SerchItemCord = ActiveWorkbook.Cells(1, 1).Value
'検索キーをセル値から取得

Open ActiveWorkbook.Path & "\KuaiKuai.csv" For Input As #i
’CSVファイルから一行ずつレコードを読み出す設定

j = 0　'連想配列キー値の設定・ループ処理で１ずつ加算
Do While Not EOF(1)

Line Input #i, LineData
ColData = Split(LineData, ",") 'カンマ区切りで、各要素を配列化(0～10)

If ColData(0) = SerchItemCord Then
 '検索キー（先頭）の合致で連想配列に流し込み
Dic.Add j, LineData
j = j + 1　
End If

Loop
'ループ処理は、CSVファイルの最終値EOFになるまで（無限ループにならない）

If Dic.Count = 0 Then
Dic.RemoveAll
GoTo NoAction
End If
'検索データがないとき、連想配列を終了してコード終了

ColData = Split(Dic.Item(Dic.Count - 1), ",")
ActiveWorkbook.Celles(2, 1).Value = Val(ColData(9))
'検索されたレコードで最後にある9番目の要素を貼付け

Erase ColData
Dic.RemoveAll
Close #i
'変数指定条件の初期化・解放（ネットのサンプルでは、しらっと無視）

NoAction:
End Sub

いいねと思ったら、三つポチっとね！
  
にほんブログ村    にほんブログ村      にほんブログ村