VBA：TIPS 設定を読み取る

Excel のVBA を書いているとき、というか、コード書きはじめのとき私は最初に設定ファイルを読み込ませるコードをよく書きます。

設定ファイルといってもExcel なので、シートの中に書いてしまえばいいので、それを読み取るわけです。
これをうまく活用すると設定からプログラムを呼び出したり、プロパティ操作をするようなFWが作れたりします。

大したコード、大した内容ではないのですが、さくっとツールを作りたいときには、設定をConst にしたりして終わらせてしまう人もいるかもしれませんが、拡張していくにつれて同じ値の取得に似たようなコードをずらずら書くことになります。

定数や値の取得には、enum がVBAは使えますので、本当はこの辺をうまく拡張してクラスファイル化すると便利だったりしますが、タダのツールで・・という程度の時に書くコード。そんな感じのもの。

さて、私は常々からドキュメントを作るときには、C3 の位置にタイトルを付けてから作ることにしています。
これは、ドキュメントを書き始めるときに余計な思考停止を防ぐためです。
必ず、C3 の位置に

■ プロジェクト概要

みたいな書こうとしていることのタイトルを付けることで、そのあとがサクサクかけるわけです。

これは、私がドキュメントがうまく書けないというメンバにいつも教える方法です。
まぁ、なんでC3 なのかまでは不要な話なので教えてませんが、見栄えとしても段落落ちしてとてもよいです。

ちなみに私はExcelファイルを開いて最初にすることは、セル幅を正方形になるように列幅調整することです。
全体行列名選択ボタン（行番号一番左上の■）をおして、列名の上で右クリックし、幅調整で２または３を設定するだけです。

Excel方眼用紙は悪だというのをよく耳にしますが、一律それで片づけるほうが悪だと私はあえて一蹴しましょう。
この作業をしてから必要幅に後から調整することで見栄えの調整がすごく楽ですし、効率的です。
重要なのは書きはじめまでの時間、作業を短縮し、思考停止を防ぐこと。
もちろん、結合なんて馬鹿なことはしないでくださいね。
これは、Excel の作業効率を下げる、禁忌のキホンです。

そして、このセル列が重要です。
例えば、

■ プロジェクト概要
　　１.ほにゃららら
　　２．はにゃらら
　　　A.わいあわ
　　　B.うららら

■ 前提
　　１．○○であること
　　２．▲▼がないこと

■ 参考の一覧

　　Ｎｏ　　タイトル　　概要　　詳細
　　１　　　◎◎のこと　あれ　　これ
　　２　　　■■のこと　どれ　　それ

みたいな構造になっていくわけですが、この時、列がそろっていればおおよそ、次に始まる行列も開始位置も決まっていたりします。
（というか、必ず意識して２行2列後ろと決めています。）

するとやればいいことは、一つ。

■ ほにゃらら

で始まる行を探せばいいのです。
そのあとは、最終行、最終列を探す処理。

これをセットにしていつでも取得できるようにしておくと、行番号がどんなに移動しようとも関係ないのです。
列番号も移動する可能性があるときは、オフセット（加算値）を指定できるようにだけオプション引数を用意してあげれば完成です。

ん？何に使うの？と思う人は、仮にこういうシートが設定ファイルだったらと考えてみてください。

■ 参考の一覧

ではなく

■ 出力メッセージ一覧

だったらどうでしょう。

■ 設定１
■ 設定２
■ 出力メッセージ一覧

という設定だけのシートから■ 出力メッセージ一覧 という文字列を探して、そこから一定のルールで書かれている行列の値を取得できるようにすれば設定ファイルの読み書きできるユーティリティの完成です。

どこまで設定ファイル操作を作りこむかは、作りたいツールのボリュームとの相談になります。
ただ、基本的に必要なのは以下のようなものになります。

Dim row as long

Dim startRow as long

Dim lastRow as long
startRow = getStartRow("シート１","このタイトル見つけて")

lastRow = getLastRow("シート１",startRow)
for row = startRow to lastRow -1

　　　取得したり、変更したりする処理

next

かなり簡単なコードですが、この威力は使えばわかります。
シートがどんどん増えていく必要があったり、増えてしまう場合でも難なく最初の5行を用意するだけでいつでも設定の読み書きができるようになって非常に効率的です。
コピペで済むんですから・・。

ちなみに最終行を取る方法は、いくつかありますが、(xlUP)や(xlDown) などを利用した方法は、設定が1行もない時にほとんど機能しないので開始行-１から開始して開始行と同じだったら開始行を返す。。みたいな処理が必要になります。

自分のなかで行列の先頭列には、No を付けて、セルの条件式は「=前のセルの値＋１」みないな式を書いておくルールを敷いておくと、終端行を特定するのは容易なので以下のようなもので充分です。

public function getLastRow(sh_name as string ,startrow as long, optional col as integer= 5 )

dim row as long
row = startRow

do while worksheets(sh_name).rows(row, col).value <> ""

 row = row +  1

loop
getLastRow = row
end function

行が存在しなかったら開始行と同じになりますので、戻り値をもとに処理を継続するか決めるだけです。

ちなみに開始行を特定する方法は、入ろいろあります。
一番手っ取り早いのは、maxRow を決めて行の全検索する方法です。

正直言って、最近のマシンパワーであれば２０００行くらいをなめるのは、ほぼほぼ一瞬ですから設定ファイル読み込みの都度にgetStartRow をしてもよいですし、プログラムが始まる最初に設定ファイル読み込み処理みたいなのを作って、一気にEnum 系のクラスにしてしまうのもありです。
ただし、後者は、値の変化対応にセルの値変化時（ShettChangeAfterあたり？）でプロパティ更新の処理が必要になり、余計なイベントをフックすることにもなるのでお勧めしません。
無用でしょうが、参考までにコードにするとこんな感じ。

public function startLastRow(sh_name as string , serchTitle as string, optional startRow as long = 2, optional col as 

 integer= 3, optional maxRow as long = 2000 )
 dim row as long
 startLastRow = startRow

 do while worksheets(sh_name).rows(row, col).value <> serchTitle

  if row >= maxRow then

    exit function

  End if

  row = row +  1

 loop
 startLastRow= row

end function

取得してきた行がMax行と同じであればなかったということで、そのための処理を呼び出し側に書けばよいのです。
そのためにもMaxRow はできるだけ定数に落としたほうがよいです。
比較することにもなりますので。

こういう地味だけど作るの面倒な奴は、癖にしておくとツールのようにとっかんとっかんで作りたいときは、あとあと楽になってきますね。

という小技でした。

とかいって、ツール作るたびに毎回同じコードを書いているのですが・・。（笑）

※ 補足というか、引用内でプレタグが効くようになっていて、はてなさんに感動しました。
　 細かいバージョンアップですがありがたいです。感謝！