それでは、「その④：CSVで疑似データベース構築 前編」で書いた、サンプルコードを順番に説明していきます。
１つの記事で全部説明しようと思ってますので、恐ろしく長くなります。 それぞれの項目をこの下でアコーディオン表示にしました。
必要な▶だけクリックして開いて読んでいいただければと思います。
「ワークシートの指定方法について」
このサンプルではシートをインデックスで指定してます。例えばworkbookに配置した１行を見てみますと、
ThisWorkbook.Worksheets(1).Range("B2").Validation.Delete
とあります。ドットを「の」に置き換えて読んでみると、
「このブック」の「左から１枚目のシート」の「B２セル」の「入力規制」の「削除」と書いてあります。
この指定方法ですと、シート名とは関係なく左から１番目のシートを指定できます。なので、どんなシート名にしても動くようにするには向いています。
しかし、この「左から1番目のシート」は実際に運用するには問題になります。それはシートが１枚だけの環境ならいいのですが、シートを挿入したり、移動したりするとシートの順番が変わり、コードが思ったように動かなくなります。なので、実際に運用する場合、カッコ内にシート名を入れて絶対参照にしたほうが安心です。
その際、シート名はカッコの中でダブルクォーテーション(")でくくってください。シート名は日本語でも問題ありませんが、全半角の違いや空白もしっかり間違えないよう、コピペなどうまく使って入力しましょう。もし、変数に文字列を入れる場合は、カッコの中に変数を入れるだけでOKです。
さあ、これで問題が解決。。。したかに見えますが、「シートの名前が変更されたらどうなるの？」という別の問題がでてきます。そういう心配がある時は、シート見出しを非表示にしたり、シートを保護したりして、シート名を変える事自体を面倒にしておくと大体解決します。
ここまでしてもまだシート名を変更してくるツワモノがいる環境の場合は、もうExcelでなんとかするのは諦めたほうがいいです（笑）
Excelみたいに、みんながある程度使えるソフトで作るシステムあるあるです。私もExcelではデータベースをブラックボックス化する事に限界を感じて、PHPのサーバー世界に逃げたので（笑）まあ、それ以前にExcelは処理が遅い！（笑）
「ユーザー関数について」
基本、VBAのコード単位はSub〜End Subですが、Function〜End Functionというユーザー関数を使う方法もあります。
この違いは、値や処理結果の受け渡しがあるのかないのかです。（引数とか戻り値、返り値とか言われるやつ）
通常、各コード単位はバラバラで動きます。そのため、同じ変数名でも別物なのでコード同士で干渉しません。裏を返すと、別のコードで使っていた値が簡単に使えないわけです。この変数は使う前にDimを使って宣言（Excelに変数名と扱うデータの形を教えてあげること）をします。この宣言をする場所を変えると、コードをまたいで同じ変数名を使うことができます。「なんか便利そうだし、どこでも使える方法でいいじゃない！」と思いたくなりますが、小さいプログラムなら、まあいいかもしれませんが、プログラムが大きくなってくると、変数名の管理ができなくなり、知らない間に同じ変数名を使って、意図した結果が得られなくなってしまうことが起きます。信じられないかもしれませんが、ホントに起きます。エラーにならないデバックが一番やっかい！（笑）
そこで、値によって処理を変えたい場合や、違う値で同じ処理をする場合などは、Functionというユーザー関数がとても便利です。
さらに、作ったユーザー関数は、Excelの通常の関数と同様にシート上でも使うことができます（例外もあり）。シートで使うことも考えて、うまく設計すればシート上でデバックもできます。しかし、不便な点もあります。ユーザー関数単体で動かせません。そのため、デバックするには動かす用のコードを作る必要があることですかね。
今回のサンプルでもこの2種類を使っていますが、SubだからFunctionだからとそちらでは説明しません。もうすでに長いので（笑）
なので、図でまとめるとこんな感じです。
（実際はSubでも値渡しできるし、Publicで宣言とかあるんですが、、、省略します。）
▶Workbookに配置したコードについて▼Workbookに配置したコードについて
Workbookには、いろいろイベント（コード実行のきっかけとなる操作）が準備されていまして、今回使っているのは、「BeforeSave」というブックを「保存する前」に実行されるものです。今回入力したコードでしているのは、指定セルの入力規制のクリアです。
なぜこの処理を入れているのかと言いますと、このサンプルでは、セルの入力規制（リスト）機能を使って、ファイル一覧をドロップダウンリストで表現しています。
実は、この入力規制（リスト）機能には設定文字数に制限があり、ファイルを使っている間は特に制限を気にしなくても、問題なく動くのですが、文字数制限を超えたままファイルを保存し、次にファイルを開くと、異常が発生します。Excel側の設定制限を超える想定外のデータが入っているので当たり前ですね。
Excelが自動復旧してくれる場合もありますが、完全復旧は難しいですし、最悪ファイルが壊れてしまい、二度と使えなくなることがあります。
便利な機能なのに、恐る恐る使ったり、バックアップファイルを使う前提では本末転倒です。なので、使用者に保存される前に消してしまえば、回避できるじゃないかということです。
別の記事でも触れてます。よかったら見てやってください。
入力規制のリストを使わない場合や、データファイルの数の上限を管理できる場合は省略できる処理となります。
▶Worksheetに配置したコードについて▼Worksheetに配置したコードについて
まず、流れを図でみてください。
Workbookと同様にWorksheetにもいろいろイベントが準備されています。今回使ったのは「セルの値が変更された時」に発生するイベントを利用しています。
このコードは何をしているのかと言いますと、結合セルB2:D2に入力されたファイル名がDataBaseフォルダにある名前ならその内容を表示し、フォルダに無いか、セルが空白なら結合セルB2:D2以外の値をクリアします。
このイベントは、ボタンを押したり、ダブルクリックしたりするなど、操作きっかけ無しで、セルに値が入力されれば実行されます。大変便利なイベントなのですが、ご想像どおり、きっかけの頻度が多すぎるので少し書き方に注意が必要です。
例えば、「セルB2の値が変更されたらセルB2の値を変更する」と書いてしまうと、ずーっとセルB2の値を変更し続ける無限ループに陥ってしまい抜け出せなくなります。
このサンプルでは図の黄色の★マークのところで回避しています。
このApplication.EnableEventsは、準備されたイベントのきっかけスイッチをON・OFFにできる命令です。VBAでシートを処理する前に無効にしておいて、処理が終わったら有効に戻すわけです。この「戻す」処理を忘れてしまうと、これ以降のイベントすべてが無効になってしまい、コードのきっかけがなくなります。しっかり無効と有効はセットで使うようにしましょう。もし、有効にするのを忘れたコードを書いて実行してしまった場合、有効にするだけの命令を書いてもう一回実行すれば直ります。これだけに限らず、作成中はデバック用に作ったコードを復旧ボタンを作って配置するなど工夫すると作業がはかどりますよ。
それでは次は番号順に説明してきます。ここもアコーディオンです。番号の項目をクリックして開いてください。もう一回押すと閉じます。
With〜End Withでくくると、その範囲内でドットで始まるものは、同じ参照ができるようになります。例えば、ワークシートをWithで設定すると、その中でセルを指定するとWithで設定したシートのセルの事になります。
このサンプルではTargetです。これはイベントのきっかけの値が変更されたセルの事を指します。
このTargetは常に使える指定方法ではありません。本来セルを選択するのはRangeかCellsです。しかし、どちらも場所（行列）を指定しなければ参照できず、値が変わったセルはどれか？という柔軟な指定には向いてません。
なので、このようなイベントでは、きっかけになった情報をTargetに格納して使えるように準備されています。コードの始まりPrivate Subに続くカッコ内をみるとByVal Target As Rangeとあります。これは「このコード内でTargetを使うときっかけになったセルの情報になります」という意味です。
また、ユーザーの入力によってセルを指定する方法に、ActiveCellがあります。 これは「選択されているセル」を参照するものです。これはほぼTargetと同じように使えますが、残念ながらこのイベントではうまくいきません。なぜかというと、セルの値が変更されたあとコードが発動するイベントです。大抵の場合、セルに値を入力した後はエンターキーを押しますね。
はい！そうです！！そのとおり！！（笑）セルが１つずれます！！というわけで、参照も１つずれてしまいます。なので、ここではTargetを使いましょう。
①で説明したとおり、ドットで始まっていますので、この命令はTargetのことについてになります。値が変更されたセルの場所を調べる命令です。
このサンプルでは、"$B$2"と”$B$2:$D$2”の２つうちどちらか（Or)に当てはまれば実行するというものになっています。これは、今回のサンプルのきっかけが、「結合セル」になっているためです。
結合セルは少し厄介でして、ユーザーの操作によってセルの場所の戻り値がかわってしまいます。例えば、セルに値を入力した場合は"$B$2"となり、セルの内容をDeleteキーで削除した場合は"$B$2:$D$2"となります。
なんでかですって？
もう、そういうもんだと思ってください（笑）
まあ、Excelの名前ボックス見たり、シートで関数使ってみると、なんとな〜くわかると思います。
ちょっと話がずれますが、この結合セルについての処理は、Macの方によく起こる原因不明のExcelファイルクラッシュ要因の一つに関係があります。結合したセルの値は、結合セルの一番左上の値ですが、VBA操作だと結合していて表示していないセルにも値を設定できてしまい、実際には見えないですがデータ上入ってます。これを知らずにファイルを保存してしまうと、次に開くとき、ある程度の確率でファイルがクラッシュします。Excelも混乱してしまうんでしょうね。なのでMacの方は隅々までよ〜く見て、Excelから見て、ありえないものを排除するように努力するとクラッシュ減りますよ。
話を戻しまして、このような事から、セルの値を取得するにも少しズレが出てしまいます。Target.Valueでセルの値は表現できますが、この表現のままコードを作るとDeleteキーを押されて値が変わった場合はうまくいきません。なので、条件分岐で場所の判定をさせ、条件が一致したら、直接指定で変数に値を入れる方法を取ってます。このサンプルのck = ActiveSheet.Range("B2")の部分です。
③.Validation.Value = Falseを使った条件分岐
ここの条件分岐は条件が２つです。１つは値が空っぽのとき、もう一つは「入力規制のリスト以外の値が入力されたら」という条件です。この2つ目の条件が.Validation.Value = Falseの箇所です。
普通、入力規制のリスト設定がされている場合、リスト以外の値は入りません。なので、入力規制の設定時にリスト以外のものが入力されてもエラーメッセージを出さないようにVBAで設定します。その設定をしなければありえない条件です。（その設定方法はファイル一覧の取得のところで書きます。）
worksheetのコードの中でCSVファイル操作で使っている命令は、ファイルの検索をするDir()です。これはファイル操作ですが、以前の記事の「ファイルの検索」で詳しく説明していますのでこちらでは割愛します。そちらを御覧ください。
そして、入力されたファイル名がDataBaseフォルダに無いか、空白の場合はシートのファイル名入力以外を消す命令を入れています。「シートの内容を消す」という行為は何回も実行されますので、ユーザー関数を作ってModuleに配置しています。今回は戻り値無しで、値渡しだけです。渡した値（引数) によって消す場所をかえています。このユーザー関数のコードについては「シートのクリア」で詳しく書きます。
ユーザー関数や他のプロシージャ（Sub〜EndSub）の呼び出し方なのですが、ここではCallに続けて名前をつけて呼び出しています。これCall無しでもOKです。私は、後でコード見返したときにわかりやすいのでつけるようにしているだけです。でも全部Callをつけているかといえばそうでもなくて、戻り値があるユーザー関数の時は、通常の関数のように、変数名=ユーザー関数名（引数）のようにしてます。
このループ処理は、処理を開始する前に、条件と一致するか判断して、一致していれば処理を続けるという回数を指定しないループです。条件がf_name<>""ですから、f_nameが空白じゃなければループするということです。Dir()でファイル名がなくなるまで実行していますので、フォルダに検索ファイルがなくなり次第止まるループです。
Dir()の使い方はこちらの記事のファイル検索を見てください。
検索してヒットしたファイル名をbufという名前の配列に保存しています。
この中に限らず、今回のサンプルでちょっと分かりづらいかなと思われるのがこの「配列」です。これだけで記事がかけるぐらいのことなので、詳しく書くのはやめておきます。
ざっくり説明すると、配列はデータの集合体で、仮想ワークシートみたいなものです。その中で次元という考え方がありまして、行か列のどちらかだけの1次元、行×列の2次元、行×列 ×枚数の3次元という感じで、、、難しそう！！！（笑）データの集合体なので実際のシートと同じように操作できませんが、使えるようになると計算処理がかなり早くなります。
他の言語ですが過去の記事で少し触れています。参考になるかわかりませんが、よかったらそちらも御覧ください。
Excelを使っているので、ワークシートを使って値を保持、操作する方法が普通だと思いますが、シート使ってデータを操作するメリットは並び替えぐらいで、検索とか計算は配列使ったほうが断然早いです。CSVからのデータの読み込みもシートにいれるか配列にいれるかで速さが違いますし、そもそも別のExcelファイル開くのとCSVファイル開くのもかなり速さに差があります。シートに縛られるとできることが限られてくるので、配列は覚えて損はないと思います。
今回のコードに話を戻しまして、、、
ここで使っている配列は動的配列です。配列には静的と動的があり、扱うデータ数が固定か可変かで変わります。
この２つは、使い方も最初の宣言の仕方から違います。データ数がわかっている静的配列のときは、配列名（要素数）と宣言して、要素数を入れてデータ数を決め打ちします。一方、今回のように検索したファイル数が全部でいくつになるかわからないような動的配列のときは、配列名（）と要素数を入れずに空っぽで数を決めずに宣言します。これだけの違いなら大したこと無いですが、これだけで動的配列は使えません。要素数がかわるごとにExcelに数を教えてあげる必要があります。
宣言のやり直しということで、Redimという命令を使います。使い方は二通りありまして、値をリセットする場合と、残す場合です。今回は配列に記憶させながら要素数を増やしていく方法なので値を残すRedim Preserveを使います。Redim だけにすると、値がリセットされてしまいます。間違えないようにしましょう。
このコードでは、ループの中でファイルが見つかったら、配列を値を残して宣言し直し、配列の最後に記憶させて、変数cntを１つカウントアップしてます。cntが次の要素数となり、それは配列最後の位置と同じになります。
配列の要素数は０から始まります。なのでcntの初期値を０にしておき、ファイル名が見つかったらまず宣言を要素数０で実行し、そのまま配列０の位置に記憶します。そして、次にファイル名が見つかった時に備えて１つカウントアップしてあるわけです。
ここの条件分岐はファイル名を記憶させておいた配列の要素数を決めるための変数cntの値が０より大きかったら処理をする条件分岐になっています。cntが０より大きい時、ファイル名が１つ以上あったとしているわけです。
要素は０から始まるからファイル名が１つのときはcntの値は０なのでは？と勘違いしてしまいそうですが、今回の④のループは配列に記憶する処理をした後、必ず１つカウントアップしてます。ということはcntは要素数+1でループを抜けてくるわけです。逆にファイル名がない場合、ループ処理は実行されず、cntは初期値０のままループを抜けてきます。この状況を使ってファイル名があるのかないのかを判断しているわけです。
こういった条件分岐の条件は、必ずこうしろという定形はありません。要するに結果が間違いなく得られればいいわけです。今回の場合は、ファイル名が０かそれ以外かをどうやって証明するのか？ということです。できればなるべく手数を少なく求めたいですが、、、私は、後でコードを見返して、自分のわかりやすい方法が一番なんじゃないかと思いますね。だって後でわからなくなるとデバックのとき苦労しますから（笑）
次にファイル名があった場合、⑥へつなげます。その手前でd_up = UBound(buf)という命令がされています。これは配列bufの要素数の上限を変数d_upに記憶させる命令です。⑥で配列のデータの数だけ繰り返す処理をするため準備ですが、次のFor〜Nextと一緒にするほうがVBA的には正解です。見返して気づきました。PHPを使うときの癖がでています。。。。VBAのFor文はループ回数の判断式は最後のNextでやっているので１行目で上限が可変するかもしれない式をいれてもかまいません！１行目は最初の一回しか実行しませんので、配列の要素数が減っても、減る前の数で固定されます。
なので、、、
For i = 0 To UBound(buf)と、まとめてしまいましょう！
さて、ここで入力されたファイル名がDataBaseフォルダに入っているファイル名と一致しているかどうかを判断して、処理を分岐させたいわけですが、ここまで①〜⑤と順番にコードが流れてきて、判断する材料をそろえてきました。入力されたファイル名は②で変数ckに格納されており、そしてDataBaseフォルダに入っているファイル一覧は④で配列bufに入れてあります。この配列bufに入ったファイル名すべてと変数ckに入ったファイル名と比較すれば、判断できますね。
そして⑤で変数d_upにDataBaseフォルダに入っているファイル数も準備されています。なのでd_up回繰り返して比較すればよいわけです。
配列に入れたデータは配列名に（）をつけて番号で呼び出します。同じ変数名で番号だけ変えればいいので、繰り返し処理で一発です。ここではFor文で変数iが配列のデータ個数分繰り返します。なのでそのiを使ってbuf(i)としてます。これで最初から最後まで順番にデータと比べることができます。
このあとはまだ作成中。。。。ファイルの保存、読み込み、追加保存などなど書く予定。。。。
ひー、長い。。。まだ未完成ですが、完成を待つといつになるかわからないので、更新しながら書くことにしました。読まれた方は更新をお待ち下さい。【2022/2/11】
