ぐりぐりももんがのうんちくゲレンデ行脚＆地方応援ブログ: 配列や連想配列は、Ｐｅｒｌ，ＰＬ/Ｉ、Ｊａｖａ、Ｃ言語、ＳＱＬのプログラミング言語に定義されていますが、ＶＢＡは両方を網羅した二刀流なのがすごい

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

複数のファイルを読み込むと、どんな番号が割り振られるのか判断が付かない。よって、システムに一任する”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

いいねと思ったら、三つポチっとね！