2021/01/23

年賀状リスト等の宛名検索（ブック内検索可）

• １．背景
• ２．システム概要
• ３．プログラムの流れ
• 　３ー１．基本的な流れ
• 　３ー２．ブック内での検索
• 　　３ー２ー１．検索の基本的な考え方
• 　　３ー２ー２．検索パターンの種類
• 　　３ー２ー３．コードの流れ（検出セルがゼロ個の場合）
• 　　３ー２ー４．コードの流れ（検出セルが存在する場合）
• 　　３ー２ー５．フローチャートをコード化してみる
• 　　３ー２ー６．フローチャートの見直し
• ４．フォームの作成
• ５．標準モジュールのコード
• ６．フォームモジュールのコード
• 　６－１．フォームレベルの変数宣言
• 　６－２．フォーム起動時の初期化
• 　６－３．文字列挿入ボタン
• 　６－４．検索ボタン
• 　６－５．閉じるボタン
• 　６－６．オプションボタン
• 　６－７．スピンボタン
• 　６－８．Findメソッドの実行
• 　６－９．Findメソッドのオプション値生成
• 　６－１０．シート移動
• 　６－１１．初出セル・初出シート変数の初期化
• 　６－１２．条件変更時の変数初期化
• ７．アドインとしてExcelにマクロを登録
• ８．最後に

１．背景

前回「年賀状リスト等の宛名検索と追記」で住所録内を氏名などで検索し、見つかったところでマークを追加するシステムを紹介しました。しかし、その時は「アクティブなシート内のみ」の検索しか出来ませんでした。これでは複数シートに住所録を作っている場合には不便です。

そこで今回は、「複数シートに渡って検索が可能」なシステムを紹介します。
基本的な機能は前回と同じですが、そこにExcel組込ダイアログの「検索と置換ダイアログ（図１－１）」と同様に「オプション」画面を作り、「ブック内検索」を含めた６項目のオプション項目の選択が出来るようにしました。

図１－１

なおオプションの中には「セルの書式」の項目もありますが、Excel上では数多くの書式を設定できますので「書式で検索するためには、書式をある程度絞り込んで設定するツールも必要になる」と考え、今回は対象から外しました。

今回システムと前回「年賀状リスト等の宛名検索と追記」の違いは、このオプション有無のみです。なお異なる部分だけの説明では無くシステム全てについて説明しますので、前回と重複する部分が多いことについてはご了承下さい。

２．システム概要

今回のシステムはアドイン登録することで、図２－１のようにExcel上のボタン①から起動することができます。なお、サンプルファイルではシート上の「ダイアログ表示」ボタンからも起動・試行することが可能です。
アドイン登録の方法は、前回の「７．アドインとしてExcelにマクロを登録」を参照下さい。
またサンプルファイル、及び説明用の図中の住所録は、疑似的に作成（https://hogehoge.tk/personal/を使用）された住所録に対し多少修正を加えたものを使用しています。
疑似データではありますが、氏名や電話番号は実在する可能性はありますので、データの取り扱いにはご注意下さい。

図２－１

表示した検索ダイアログ②では図２－１のように、検索ボックス③に検索文字列を入力し、マークを記入する列④をスピンボタンで選択し、記入するマークの文字列を設定⑤します。

そして、検索対象範囲（例えば図２－２の氏名の列）を指定⑥し、ダイアログ上の「次を検索⑦」ボタンを何回かクリックすることで目的のデータ行を見つけ、そこで「文字列挿入⑧」ボタンをクリックすると、指定した列のセルに⑤で指定した文字列が追記⑨（元の文字列の後ろに追加）されます。
なお、⑥の検索対象を指定しない（単一セルを選択した状態）場合には、シート内の全てのセルが検索対象になります。
また⑦の「次を検索」ボタンはデフォルトボタンにしていますので、ダイアログにフォーカスがあれば「キーボードのリターンキーで次を検索」することができます。この機能により「検索にはリターンキー」「文字列挿入⑧にはマウスクリック」と、２種類の入力手段を使って効率の良い作業が可能となります。
また検索途中で、記入列や記入文字列を変更することは可能です。

図２－２

以上までは前回仕様と同じですが、今回はそれに加えて図２－３のように、オプションボタン⑩をクリックすることで現れるオプション項目⑪を選択できるようにしています。

図２－３

オプションの項目は、組込ダイアログの検索のオプションとほぼ同じで、「検索場所」「検索方向」「検索対象」「大文字・小文字の区別」「完全一致か否か」「全角・半角の区別」の６項目です。ただし組込ダイアログに存在する「書式検索」については、今回は対象外としました。

例えば「検索場所」オプションを既定の「シート」から「ブック」にする①ことで、図２－４のようにブック内の全ワークシートを検索②することが可能になります。

図２－４

また図２－５のように、各シートで検索列を指定③してから実行すると、例えば氏名列のみで全シート検索が出来ることになります。

図２－５

もちろん図２－６のように、複数ワークシートをグループ化②（Ctrlキーを押しながらシートのタブをクリックしていく、またはShiftキーを押しながら最後のシートのタブをクリック）し、その上で検索列を指定③すると、例えば氏名列のみで全シート検索が出来ることになります。

図２－６

但し「複数ワークシートのグループ化」したまま検索をすると、検索順序が予期していない順序になる可能性がありますので、気にする方は注意願います。
また「複数ワークシートのグループ化」＋「検索列を指定しない」＋「ブック内検索」をすると、検索のシートが切り替わる時に、ワークシートのグループ化が解除されてしまいます。

終了するには「閉じる」ボタンをクリックするか、キーボードの「ESCキー」を押すとダイアログが閉じます（Hide）。Hideしただけですので、再度起動ボタン（図２－１の①）をクリックすると、前回の検索文字列などを残したままダイアログが起動します。

「複数ワークシートのグループ化」の状態で検索を実行すると、不思議な現象が出ることに気が付きました。 １つは、ワークシートのグループ化をした上で検索列を絞る（図２－６の状態）と、「検索場所＝シート」の状態でもグループ化した全ワークシートに対して検索をしてくれます。 但し図２－６でも記しましたが、「検索のシートの順序」が順番通りにならないことがあるようです。（もしかしたら、シートを作った順序かもしれません。） もう１つは、Findメソッド実行直後に「選択した検索列の先頭セルがActiveになる」ことです。 通常は、Findメソッドを実行した直後は「前回の検索セルがActive」であり、「Activeメソッドを実行して初めて、今回検出したセルがActive」になるのですが、ワークシートのグループ化＋検索列を絞った状態（図２－５）では、Findメソッド実行直後には「Activeセルが、前回の検索セルから選択した検索列の先頭セルに移動」するのです。これを図にしたのが図２－７です。 図２－７ 理屈を色々考えてみましたが、良く分かりません。言えるのは「シートのグループ化＋範囲選択した状態では、通常とは違うことが起こり得る」ことだと思います。 なおFindメソッドでは、シートのグループ化＋検索列選択で全シート検索となるのですが、「組込ダイアログの検索」ではシートのグループ化のみで全シート検索となります（オプションの検索場所はシートのまま）。 このような動作は、Findメソッド・FindNextメソッドでは実現できないので、組込ダイアログの検索とメソッドとしてのFind・FindNextは微妙に動作が違うようです。

３．プログラムの流れ

３ー１．基本的な流れ

プログラムの主要な流れは図３－１のように、ダイアログ上の「次を検索」ボタンと「文字列挿入」ボタンの２つによって動作します。


図３－１

まず「次を検索」ボタン①をクリックすると、Findメソッド②が実行されます。Findメソッドには複数の引数がありますので、ダイアログ上から「検索文字列④」および検索場所を除いたオプションの５項目（図３－１の青字部分）を「Findメソッドの引数」としてまとめ③、Findメソッドに渡します。
Findメソッドは、検索が成功した時には検出したセル範囲、検索が失敗（見つからなかった）したときにはNothingを返します。ですのでその戻り値で判別⑤し、成功した時はそのセル位置をActive⑥に、失敗した時にはその旨をメッセージ表示⑦します。
オプション項目の内の「検索場所」は「シート or ブック」の選択ですが、シート内検索とブック内検索とでは処理内容が大きく違いますので、処理の分岐に使用しています。

この図３－１では、検索成功・失敗を選別後の実行内容はとても単純に書かれていますが、これは「検索場所＝シート」の場合です。今回システムに機能追加している「検索場所＝ブック」の場合はかなり複雑になるので、次項（３－２項）で詳細説明します。

一方検索が成功した後「文字列挿入」ボタン⑧をクリックすると、まず「文字列を書き込むセル位置」を算出します。行位置は、検索されたActiveセル⑥の位置から取得⑩し、列位置はダイアログ上の「記入する列⑫」から取得⑪します。この⑩⑪の行位置・列位置から書き込む位置を特定し、また書き込む文字列はダイアログ上の「記入する文字列⑬」から取得して、特定したセル位置に書込み⑨ます。

３ー２．ブック内での検索

３ー２ー１．検索の基本的な考え方

今回使用しているFindメソッドは、引数After（検索開始セル）にActiveCellを指定すれば、ActiveCellの次から検索が開始され、シート内の一番最後まで検索が進んだら先頭に戻って検索を継続するものです。Findメソッドを実行し続ければ、同じシート内を検索し続けることになります。

グルグルと同じシート内を検索していても仕方ないので、シート内の検索が一通り完了したら違うシートに移動させる必要があります。「一通り検索が完了」したことを知るために、ここではある変数を設けます。
まず、そのシートを検索開始する前に、その変数を空にしておきます。その上で検索を開始し「初めて見つかったセル」の場所で、このセルの位置を変数に保存します。「初めて見つかったセル」か否かは、変数が空かどうかで判断をします。
Findメソッドは同じシート内をくり返し検索し続けますので、いつかは「初めて見つかったセル」を再検出するはずです。セルを見つけるたびに変数の値をチェックする様にしておき、変数の値がそのセルの位置であれば「一通り検索が完了」したことになります。
そして「一通り検索が完了」したら、隣のシートに移動してから同様に検索を進めれば良いのです。ここでシートを移動した時には「検索開始する前に変数を空」にしておく必要があります。そうしないと、新しいシートでの「初めて見つかったセル」が記録できなくなってしまいます。

この変数には「初めて見つかったセルの位置」が入りますので、変数は「初出セル（コード上ではStartCell）」という名前にしておきます。
また、そのシートの中でセルが見つからなかった時にはFindメソッドからはNothingが返されます。Nothingの時には、そのまま次のシートに移動します。
この方法をフローチャートにしたのが、図３－２の右側です。

図３－２

図３－２の左側の図は、２つのシート上に検索文字列が入ったセル（〇〇と入力されているセル）が存在する状態を示しています。この時に「シート」の先頭（左上角）がActiveであった状態から検索を開始すると、検索順序（SearchOrder）が行優先（xlByRows）であれば「①→②→③→④→①→･･･」という順序で検索されることを期待するはずです。この動く様子を図３－２の右側のフローチャートに沿って時系列で表したものが図３－３になります。左から右へと時間が流れています。
なお「」は、ユーザーが検索開始ボタンをクリックしたことを表します。


図３－３

図３－２の左側の図とフローチャートを見比べながら、変数の値に注目してたどってみます。
まず初めて「検索開始」ボタンをクリックすると、シート１で１つ目のセル（B3セル①）を発見します。そのセルはシート１の中では初めて発見したセルですので、変数「初出セル」には「B3」という値が入ります。そしてB3セルをActive①にします。
次に「検索開始」ボタンをクリックすると、シート１で２つ目のセル（C5セル②）を発見します。その時点では変数「初出セル」には「B3」という値が入っていますので、変数値は変更せずにC5セルをActive②にします。
次に「検索開始」ボタンをクリックすると、一周回ってきてシート１でB3セル①を発見します。この時点では変数「初出セル」には「B3」という値が入っています。「B3セル＝変数値 B3」ですので、このセルは「初めて発見したセル」ということが分かります。ですので、変数「初出セル」を空にしてからシート２へ移動します。

シートを移動したら、「検索開始」を（ユーザー操作では無く）マクロから起動します。変数「初出セル」は空になっている状態からスタートします。
シート２のActiveセルは左上角と仮定すると、検索をしてシート２でC2セル③を発見します。変数「初出セル」が空ですので、このセルがシート２としての「初めて発見したセル」ということになります。ですので変数「初出セル」には「C2」という値を入力し、C2セルをActiveにします。
次に「検索開始」ボタンをクリックすると、シート２で２つ目のセル（B4セル④）を発見します。この時点では変数「初出セル」には「C2」という値が入っていますので、変数値は変更せずにB4セルをActive②にします。
次に「検索開始」ボタンをクリックすると、一周回ってきてまたシート２でC2セル③を発見します。この時点では変数「初出セル」には「C2」という値が入っており「C2セル＝変数値 C2」ですので、このセルはシート２の中で「初めて発見したセル」ということが分かります。ですので、変数「初出セル」を空にしてから隣のシート１へ移動します。

シートを移動したら、「検索開始」を（ユーザー操作では無く）マクロから起動します。変数「初出セル」は空になっている状態から検索スタートし、シート１でB3セル①を発見します。以降はくり返しになります。

３ー２ー２．検索パターンの種類

では、上記の基本的考え方でＯＫかを、複数シート上に検出されるセルが存在するのか、また複数存在するのかを「検索パターン」を作って考えてみます。

図３－４は、ワークシートが１つしか無いのに「ブック内検索」を指定した時です。図中の四角枠は「検索に合致したセル」を表し、AやBは、セルの位置を表すものとします。例えば②は検出されるセルが１箇所、③は２箇所という具合です。
ワークシートの下の赤字は、検出されるセル位置の順序の期待値、また検出されなければMsgBoxを表示させる（①や④の場合）を示します。

図３－４

また図３－５は、ワークシートが２つの場合です。シート１の方から検索がスタートされるものとします。
ワークシートが３つ以上の場合でも、その中の２つだけを取り出せば、以下のパターンのどれかに当てはまると思います。
また１つのワークシート内に検索合致するセルが何個あるかについても、「ゼロ個」「１個」「２個以上」で表せると考え、０・１・２個のパターンとしました。

図３－５

３ー２ー３．コードの流れ（検出セルがゼロ個の場合）

図３－４の中の①（ワークシートが１つで、検出セルがゼロ個）の場合のプログラムの流れを図３－６に記しました。恐ろしいことに無限ループです。
この無限ループの原因は、図３－２のフローチャートが「どこかのシートには検出セルがあるだろう」という前提で出来ているからです。

図３－６

図３－５の中の④（ワークシートが２つで、検出セルがゼロ個）の場合も、図３－７の通り同様に無限ループになります。

図３－７

そこで無限ループにならない様に、シート内で検索一周したことを検知する変数「初出セル（コード上ではStartCell）」と同じように、ブック内を一周したことを検知する変数「初出シート（コード上ではStartSheet）」を作り、その変数に「初めて検索したシート名」を書き込み、ブック内を一周したら停止（メッセージを出す）させることにしました。
これをフローチャートにしてみると、図３－８のようになります。


図３－８

図３－８は、コードにすることを意識して作っています。図３－２と比較する際は、「初出セル」→「StartCell」、「初出シート」→「StartSheet」と置き換えて読んで下さい。

このフローチャートを使って、図３－６（パターン①）と図３－７（パターン④）がどうなるかを図３－９に示します。
結果としては、検出セルが無い場合にもMsgBoxが出てプログラムを止めることが出来たことが分かります。

図３－９

３ー２ー４．コードの流れ（検出セルが存在する場合）

ではその他のパターンについても、図３－８のフローチャートを使って検証してみます。

ワークシートが１つで、検出セルが１つの場合（パターン②）が図３－１０、検出セルが２つの場合（パターン③）が図３－１１です。シート移動後に再検索しているので無駄な動きはありますが、両方とも結果はＯＫです。

図３－１０


図３－１１

次にワークシートが２つの場合です。
まず、どちらかのシートには検出セルが無く、もう一方のシートには検出セルが１個の場合です。
図３－１２は検索開始側シートに検出セルが１つあった場合（パターン⑤）、図３－１３は反検索開始側シートに検出セルが１つあった場合（パターン⑥）です。
どちらの場合も唯一の検出セルがActiveになっています。

図３－１２


図３－１３

２つのシートのそれぞれに１個ずつ検出セルがある場合（パターン⑦）が図３－１４です。この場合も問題無く交互にセルがActiveになります。

図３－１４

一方のシートに検出セルが２つ（以上）あり、もう一方のシートには検出セルが無い場合を考えます。
検索開始側シートに検出セルがある場合（パターン⑧）が図３－１５、反検索開始側シートに検出セルがある場合（パターン⑨）が図３－１６です。問題ありません。

図３－１５


図３－１６

一方のシートに検出セルが２つ（以上）あり、もう一方のシートには検出セルが１つの場合を考えます。
検索開始側シートに複数の検出セルがある場合（パターン⑩）が図３－１７、反検索開始側シートに複数の検出セルがある場合（パターン⑪）が図３－１８です。問題ありません。

図３－１７


図３－１８

最後に、両方のシートとも２つ（以上）の検出セルがある場合（パターン⑫）が図３－１９です。

図３－１９

これで図３－４・図３－５の全パターンについて、図３－８のフローチャートで正しく動きそうな事がわかりました。
このように図にしたり表にしたりしてチェックしなくても、フローチャートとパターンを頭に思い浮かべながらコードを組み立てることは可能だと思います。但しそのようにして作ったコードで、もしうまく動かなかった時に、コードをゴチャゴチャ変更するのは完成を遠ざけるだけだと思います。
そのような場合は、今回のようにフローチャートとパターンを紙に書いて「コードを１行１行たどりながら、変数値などをチェック」した方が結局近道になると思います。
（ちなみに、説明文の中では「図３－８のフローチャートをまず作ってから、図３－９以下の動作チェックをしている」ように書いていますが、実際には動作チェックをしながらフローチャートを追記・修正し、またチェックを繰り返すという試行錯誤を経て、最終的に出来上がったのが図３－８のフローチャートです。）

３ー２ー５．フローチャートをコード化してみる

では、このフローチャートをコードに落としてみます。ここでのコードは最終的なプログラムコードでは無く、チェックのためのコードです。
フローチャートと比較し易いように、コード側とフローチャート側に同じ番号を付けてみます。フローチャート自体は図３－８と同じです。

図３－２０

1. '==========　⇩(1)　「ブック内検索のコード１」　====================
2. Private Sub 検索ボタン_Click()
3. 　Dim FoundCell As Range　　　　 'Find検索の戻り値
5. 　Set FoundCell = ActiveSheet.Cells.Find(What:=検索文字列, After:=ActiveCell, ････ )　　　 '←①
7. 　If FoundCell Is Nothing Then 　　　　　　　　　　　　 '←⑪
9. 　　If StartSheet = "" Then 　　　　　　　　　　　　　　 '←⑫
10. 　　　StartSheet = ActiveSheet.Name 　　　　　　　　　 '←⑭
11. 　　　StartCell = "" 　　　　　　　　　　　　　　　　　　　 '←⑧
12. 　　　Call シート移動 　　　　　　　　　　　　　　　　　　 '←⑨
13. 　　　Call 検索ボタン_Click 　　　　　　　　　　　　　　 '←⑩
14. 　　ElseIf StartSheet = ActiveSheet.Name Then 　　　 '←⑬
15. 　　　StartSheet = "" 　　　　　　　　　　　　　　　　　　 '←⑮
16. 　　　MsgBox "検索が失敗しました" 　　　　　　　　　 '←⑯
17. 　　Else
18. 　　　StartCell = "" 　　　　　　　　　　　　　　　　　　　 '←⑧
19. 　　　Call シート移動 　　　　　　　　　　　　　　　　　　　 '←⑨
20. 　　　Call 検索ボタン_Click 　　　　　　　　　　　　　　　　 '←⑩
21. 　　End If
23. 　Else 　　　　　　　　　　　 '←②
25. 　　If StartCell = "" Then 　　　　　　　　　　　　　　　　 '←③
26. 　　　StartCell = FoundCell.Address 　　　　　　　　　　 '←⑤
27. 　　　StartSheet = "" 　　　　　　　　　　　　　　　　　　　 '←⑥
28. 　　　FoundCell.Activate 　　　　　　　　　　　　　　　　　 '←⑦
29. 　　ElseIf StartCell = FoundCell.Address Then 　　　 '←④
30. 　　　StartCell = "" 　　　　　　　　　　　　　　　　　　　 '←⑧
31. 　　　Call シート移動 　　　　　　　　　　　　　　　　　　　 '←⑨
32. 　　　Call 検索ボタン_Click 　　　　　　　　　　　　　　　 '←⑩
33. 　　Else
34. 　　　StartSheet = "" 　　　　　　　　　　　　　　　　　　 '←⑥
35. 　　　FoundCell.Activate 　　　　　　　　　　　　　　　　 '←⑦
36. 　　End If
38. 　End If
39. End Sub

図３－２１

図３－２１のコードを眺めると、同じコードが重複している部分に気が付くと思います。１１～１３行目と１８～２０行目、３０～３２行目です。もちろんこのコードでも正常に動作するのですが、同じコードがあちこちに存在すると、プログラム改造などの時に修正洩れの可能性が大きくなるため、できるだけ重複を少なくするのが大切です。

３ー２ー６．フローチャートの見直し

ということで重複コードの低減を目的に、図３－２０のフローチャートを見直してみたのが図３－２２です。

図３－２２

修正した内容は、図３－２０の「③と④の順序を入替え」「⑫と⑬の順序を入替え」のみです。修正は少ないですが、これをコードにすると図３－２３になります。

40. '==========　⇩(2)　「ブック内検索のコード２」　====================
41. Private Sub 検索ボタン_Click()
42. 　Dim FoundCell As Range　　　　 'Find検索の戻り値
44. 　Set FoundCell = ActiveSheet.Cells.Find(What:=検索文字列, After:=ActiveCell, ････ )　　　 '←①
46. 　If FoundCell Is Nothing Then 　　　　　　　　　　 '←⑪
48. 　　If StartSheet = ActiveSheet.Name Then 　　　 '←⑬
49. 　　　StartSheet = "" 　　　　　　　　　　　　　　　　 '←⑮
50. 　　　MsgBox "検索が失敗しました" 　　　　　　　　 '←⑯
51. 　　Else
52. 　　　If StartSheet = "" Then 　　　　　　　　　　　　 '←⑫
53. 　　　　StartSheet = ActEnd IfiveSheet.Name 　　 '←⑭
54. 　　　End If
55. 　　　StartCell = "" 　　　　　　　　　　　　　　　　 '←⑧
56. 　　　Call シート移動 　　　　　　　　　　　　　　　　 '←⑨
57. 　　　Call 検索ボタン_Click 　　　　　　　　　　　　　 '←⑩
58. 　　End If
60. 　Else 　　　 　　　　 　　　　 '←②
62. 　　If StartCell = FoundCell.Address Then 　　　 '←④
63. 　　　StartCell = "" 　　　　　　　　　　　　　　　　　 '←⑧
64. 　　　Call シート移動 　　　　　　　　　　　　　　　　　 '←⑨
65. 　　　Call 検索ボタン_Click 　　　　　　　　　　　　　 '←⑩
66. 　　Else
　
67. 　　　If StartCell = "" Then 　　　　　　　　　　　　　　 '←③
68. 　　　　StartCell = FoundCell.Address 　　　　　　　　　 '←⑤
69. 　　　End If
70. 　　　StartSheet = "" 　　　　　　　　　 　　　　　　　　 '←⑥
71. 　　　FoundCell.Activate 　　　　　　　　 　　　　　　　 '←⑦
72. 　　End If
74. 　End If
75. End Sub

図３－２３

図３－２１で重複していたブロックが、図３－２３でも５５～５７行目・６３～６５行目と残っていますが、フローチャートを修正しただけでも３→２箇所に減少させることが出来ています。
図３－２３よりももっとコード行数を減らせたり、重複コードを無くす工夫の余地（例えばGOTOステートメントを使う等）はあるとは思いますが、今回は図３－２２のフローチャートの手法で作りたいと思います。

４．フォームの作成

今回のフォームは、図４－１のように作成しました。

図４－１

フォーム上には検索文字列を入力するTextBox1と追加文字列を入力するTextBox2を配置します。また記入する列を設定するSpinButton1とその設定内容（列位置）を表示するLabel1を配置します。
また主なマクロを操作するボタンとして、文字列挿入ボタン（CommandButton1）・検索ボタン（CommandButton2）・終了ボタン（CommandButton3）を置きます。

ここまでは前回と一緒です。前回に対して追加したコントロールは赤い太字で示したコントロールになります。
オプション項目は、組込ダイアログでも表示On-Offを切り替えられるようになっているため、同様に切替ボタンとしてCommandButton4を配置します。
そしてオプション項目には、選択項目が３つ、チェックをつける項目が３つありますので、ComboBox１～３、CheckBox１～３を下部に配置します。また、オプションとの仕切り線のためにFrame1を置きます。
以上のコントロールのプロパティ（表面の文字列など）は、図６－２のInitializeイベントプロシージャ内で設定します。またその他の説明用のLabelは、フォーム設計時に表示文字列を設定します。

５．標準モジュールのコード

本システムは、図５－１のFind_Start2プロシージャからスタートします。
（前回のプロシージャ名は「Find_Start」としましたが、今回システムも同時にアドインすると、どちらのシステムの起動プロシージャか分からなくなっていまうため、今回システムの起動は「Find_Start2」という名前に変更しました。）

76. '==========　⇩(3)　システムの起動　====================
77. Sub Find_Start2()
78. 　If ActiveSheet.Type = xlWorksheet Then
79. 　　UserForm1.Show 0
80. 　End If
81. End Sub

図５－１

７９行目のShowメソッドでダイアログがモードレス（ダイアログ起動中もシートを操作可能）で起動されますが、検索は「ワークシート上でのみ可能」ですので、アクティブになっているシートがワークシートか否かを７８行目で確認しています。
シートの種類は図５－２のように５種類あり、その内の「住所録として使用できるワークシート」のみで動作するようにするためです。

XlSheetTypeのメンバー

名前	値	内容	検索が可能
xlChart	-4109	グラフシート
xlDialogSheet	-4116	Excel5 ダイアログシート
xlExcel4IntlMacroSheet	4	Excel4International マクロシート	〇
xlExcel4MacroSheet	3	Excel4マクロシート	〇
xlWorksheet	-4167	ワークシート	〇

図５－２

なおExcel4マクロシート（図５－２の３番目,４番目）は、ワークシート相当みたいでExcel組込ダイアログの検索も実行できますが、今回は対象外としています。

６．フォームモジュールのコード

６－１．フォームレベルの変数宣言

フォームモジュールの宣言部（先頭部分）で、モジュール内で共有して使用する変数の宣言を行っています。

82. '==========　⇩(4)　変数宣言　====================
83. Dim StartSheet As String　　　　　　 '検索開始時のワークシート名
84. Dim StartCell As String　　　　　　　 '対象シートでの検索開始時のセルアドレス

図６－１

８３行目の「StartSheet」は、検索の処理の中で使われる「初めて発見したシート名」を入れる変数です。
８４行目の「StartCell」は、同じく「初めて発見したセル位置」を入れる変数です。
「StartSheet」と「StartCell」については図３－２２のフローチャートの中で説明した変数ですので説明は割愛します。

６－２．フォーム起動時の初期化

フォームを初回に起動する時に呼び出されるInitializeイベントプロシージャが図６－２です。

87. '==========　⇩(5)　フォーム初回起動時　====================
88. Private Sub UserForm_Initialize()
89. 　Me.Caption = "検索と追記"
90. 　Me.CommandButton1.Caption = "文字列挿入"
91. 　Me.CommandButton2.Caption = "次を検索"
92. 　Me.CommandButton3.Caption = "閉じる"
93. 　Me.CommandButton4.Caption = "オプション≫"
95. 　Me.CommandButton2.Default = True
96. 　Me.CommandButton3.Cancel = True
98. 　Me.SpinButton1.Max = Application.Columns.Count
99. 　Me.SpinButton1.Min = 1
101. 　Me.Frame1.BorderStyle = fmBorderStyleSingle
102. 　Me.Frame1.BorderColor = RGB(128, 128, 128)
103. 　Me.Frame1.Height = 1
104. 　Me.Frame1.Caption = ""
106. 　Me.ComboBox1.Style = fmStyleDropDownList
107. 　Me.ComboBox1.AddItem "シート"
108. 　Me.ComboBox1.AddItem "ブック"
109. 　Me.ComboBox1.ListIndex = 0
111. 　Me.ComboBox2.Style = fmStyleDropDownList
112. 　Me.ComboBox2.AddItem "行"
113. 　Me.ComboBox2.AddItem "列"
114. 　Me.ComboBox2.ListIndex = 0
116. 　Me.ComboBox3.Style = fmStyleDropDownList
117. 　Me.ComboBox3.AddItem "数式"
118. 　Me.ComboBox3.AddItem "値"
119. 　Me.ComboBox3.AddItem "コメント"
120. 　Me.ComboBox3.ListIndex = 0
122. 　Call CommandButton4_Click
124. End Sub

図６－２

８９行目は、フォームのタイトル部に「検索と追記」という表示をします。
９０～９３行目は、各ボタンの表面に文字を表示しています。

９６行目は「閉じる」ボタンをCancelボタンに設定しています。Cancelプロパティに設定（=True）できるのも、１つのフォーム内で1つのコマンドボタンだけです。
Cancelボタンに設定すると、そのボタンに対しては「キーボードのESCキーを押す」＝「Cancelボタンをクリックする」ことになります。他のボタンにフォーカスがあっても、この機能は有効です。

９８～９９行目は、記入する列を切り替えるスピンボタンの設定です。
今回のスピンボタンは、A列・B列・C列・ … ・XFD列（Excelのバージョンで最終列は異なります）と切り替えをする役目です。その列位置をR1C1参照スタイルのように、第1列・第2列・第3列・ … ・第16384列と数値で表し、MIN値を1、MAX値を16384 と置くことで「実際に存在する列位置のどれかを選ぶ」ことが出来るようにします。
実際に存在する最大の列位置は、９８行目のように「Application.Columns.Count」で「そのバージョンに合った最大列位置」が得られますので、それをMax値としています。尚、Min値は１でどのバージョンでも同じなので、９９行目でMin値を設定します。

スピンボタンの初期設定値はMin=0、Max=100 で、またスピンボタンの持っている値（Value値）の初期値はValue値=0です。
それを図６－３の右側のように、設定値を変更する必要があります。なお、スピンボタンの値（＝初期の書込み列位置）は１（＝A列）とすることにします。


図６－３

変更をする設定項目は「Min値」「Max値」「Value値」の３つですが、設定する順序も考える必要があります。
今回は図６－４のように「Max値」→「Min値」の順序で設定しています。「Value値」は特に設定していませんが、Value値はMin値とMax値の間に必ず居ますので「Min値を0→1に変更する時に、自動的にValue値も押し上げられて0→1に値が変更」されます。


図６－４

ですので、今回の場合はMin値を変更するだけでValue値をあえて変更する必要はありません。また、Value値が変更されるとスピンボタンのChangeイベントが発生しますが、今回は「Min値を0→1に変更した時点でイベントが発生」することになります。
よって９９行目を実行した後、図６－１１のSpinButton1_Changeイベントが呼び出され、Label１の「記入列」の表示としては「初期状態のA列」が表示されることになります。

フォームの初回起動時は図６－２のInitializeイベントがまず発生し、フォームを実表示する時には図６－５のActivateイベントプロシージャが呼び出されます。なお、フォームを一度閉じた（＝Hideした）後に再度呼び出した場合は、Initializeイベントは経由せずにActivateイベントプロシージャからスタートします。

１０１～１０４行目は、仕切り線っぽく見せるためにFrameコントロールを幅１（Frame1.Height = 1）にしています。色も灰色っぽくRGB関数で指定しています。

１０６～１２０行目は、３つのComboBoxのリストを作成しています。Styleに「fmStyleDropDownList」を指定しているのは、「編集や絞り込みなどの直接入力を禁止し、リストボックスとして使用」するためです。
また初回起動時には、２～３項目ある内の一番上を選んでいる状態にするため、各ComboBox.ListIndex = 0 としています。（ListIndexはゼロから始まる数値です）

１２２行目では、初回起動時に「オプション」ボタンをマクロ側からクリックし、オプション部分を隠すようにしています。
これは、オプションボタンをクリックした時に現在の「フォームの高さ」を調べ、いくつ以上だと「オプションを開いていると判断されるので閉じる」、またそれ以外では「オプションを閉じていると判断されるので開く」というトグル機能（１つのボタンでOn-Offの切り替えをする）を持たせています。
ですので、そのまま起動すればオプション部が表示されます（オプション部が見える形でコントロールを配置しているため）が、「開いているから閉じる」という機能を利用して、初回にオプション部を隠しています。

125. '==========　⇩(6)　フォーム表示時　====================
126. Private Sub UserForm_Activate()
127. 　Me.TextBox1.SetFocus
128. End Sub

図６－５

Activateイベントプロシージャでは、１２７行目で「検索文字列（TextBox1）」にフォーカスを当てた状態でフォームを起動させています。
これは、起動時に検索ワード部分にフォーカスを当てておくことで、ユーザーがマウスクリックでTextBox1を選択しなくてもワードを入力できるようにしておくためです。また、フォームを閉じる時には「閉じる」ボタンをクリックしますが、Hideした状態のままでは「閉じる」ボタンにフォーカスが当たっていますが、フォーム表示時に「ボタン以外にフォーカスを当てる」ことで、起動直後でもキーボードのEnterキーを押せば検索が開始する効果も狙っています。

６－３．文字列挿入ボタン

図６－７の「次を検索」ボタンで「検索文字列が見つかったセルがActive」になっている状態の時、「文字列挿入」ボタンをクリックした時に動作するイベントプロシージャが図６－６です。
（但し、検索していない状態でも「文字列挿入」ボタンはクリックできます。その場合には「Activeになっているセルの行 ｘ 指定した列」で定まるセル位置に文字列が挿入されます。）

129. '==========　⇩(7)　文字列挿入ボタンのクリック　====================
130. Private Sub CommandButton1_Click()
131. 　Dim WriteCell As Range　　　 '文字列を書き込むセル範囲
133. 　If Not ActiveSheet.Type = xlWorksheet Then Exit Sub
135. 　Set WriteCell = ActiveSheet.Range(Me.Label1.Caption & ActiveCell.Row)
137. 　WriteCell.Value = WriteCell.Value & Me.TextBox2.Value
139. 　Me.CommandButton2.SetFocus
140. End Sub

図６－６

１３３行目のIf文「If Not ActiveSheet.Type = xlWorksheet Then ･･･」は、文字列を書き込もうとした時のシートが「書き込むことが可能なワークシート以外だったら」中止をする、というものです。

たとえば、ブック中にワークシートとそれ以外（例えばグラフシート）が混ざっていたとします。本システムのダイアログは図５－１で説明した通り「ワークシート以外では起動しない」ようになっていますが、モードレスで起動していますので、シートを手動で切り替えることが可能です。
ですので「ワークシート以外のシート上にダイアログが存在する」ことも可能ですので、その状態（例えばグラフシート上）で「書込み」ボタンをクリックされると、書込み出来ないのでエラーが発生してしまいます。
そのため、「書き込もうとしているシートがワークシートか否か」を調べてから、その先に進むようにしています。（ちなみに、前回のシステム（検索範囲がシート内のみ）では、このコードが入っていないために、エラーが出る可能性があります。）

１３５行目は、書き込むセルを特定して変数WriteCellに代入しています。そのセルの位置は以下のようになります。特にセルの位置については、列＋行の文字列を結合させて「A1参照スタイル」で指定している形になります。
　・シート：現在Activeになっているシート　＝　ActiveSheet
　・行位置：検索してActiveとなったセルの行位置　＝　ActiveCell.Row
　・列位置：スピンボタンで選択した列位置　＝　Me.Label1.Caption（Label1上のA1参照スタイルでの列位置）

１３７行目は、書き込むセルに記入されていた文字列の後ろに「追加文字列（TextBox2.Value）」をくっつけてから、再度書き込むセルに戻します。

１３９行目では、フォーカスを「次を検索ボタン（CommandButton2）」に移します。図６－２の９５行目のところで説明しましたが、どこか他のボタンにフォーカスが当たっていると「Enterキーを押してもDefaultボタンをクリックした事にならない」ため、「DefaultボタンであるCommandButton2」にフォーカスを当てています。
なお「ボタン以外にフォーカスが当たっていれば良い」ので、１３９行目を「Me.TextBox1.SetFocus」に置き換えてもＯＫです。

６－４．検索ボタン

「次を検索」ボタンをクリック、またはフォームがアクティブになっている状態でキーボードのEnterキーを押した時に呼び出されるイベントプロシージャが図６－７です。

141. '==========　⇩(8)　検索ボタンのクリック　====================
142. Private Sub CommandButton2_Click()
143. 　Dim SS As Range　　　　　　　　　　　　　 '検索するセル範囲
144. 　Dim FoundCell As Range　　　　　　　　　 'Findメソッドの戻り値（セル範囲、またはNothing）
146. 　If Not ActiveSheet.Type = xlWorksheet Then Exit Sub
148. 　If Selection.Count = 1 Then 　　 '選択している範囲
149. 　　Set SS = ActiveSheet.Cells
150. 　Else
151. 　　Set SS = Selection
152. 　End If
154. 　Set FoundCell = myFind(SS, ActiveCell)
156. 　Select Case Me.ComboBox1.ListIndex　　　 'シート内検索かブック内検索か
158. 　　Case 0 　　 'シート内検索
160. 　　　If FoundCell Is Nothing Then
161. 　　　　MsgBox "検索が失敗しました"
162. 　　　Else
163. 　　　　FoundCell.Activate
164. 　　　End If
168. 　　Case 1 　　 'ブック内検索
170. 　　　If Not StartCell = "" Then
171. 　　　　If myFind(Range(StartCell), Range(StartCell)) Is Nothing Then
172. 　　　　　Call SearchReSet
173. 　　　　End If
174. 　　　End If
176. 　　　If FoundCell Is Nothing Then 　　 '検索失敗
178. 　　　　If StartSheet = ActiveSheet.Name Then
179. 　　　　　StartSheet = ""
180. 　　　　　MsgBox "検索が失敗しました"
181. 　　　　Else
182. 　　　　　If StartSheet = "" Then
183. 　　　　　　StartSheet = ActiveSheet.Name
184. 　　　　　End If
185. 　　　　　StartCell = ""
186. 　　　　　Call NextSheet
187. 　　　　　Call CommandButton2_Click
188. 　　　　End If
190. 　　　Else　　　　　　 '検索成功
192. 　　　　If StartCell = FoundCell.Address Then
193. 　　　　　StartCell = ""
194. 　　　　　Call NextSheet
195. 　　　　　Call CommandButton2_Click
196. 　　　　Else
197. 　　　　　If StartCell = "" Then
198. 　　　　　　StartCell = FoundCell.Address
199. 　　　　　End If
200. 　　　　　StartSheet = ""
201. 　　　　　FoundCell.Activate
202. 　　　　End If
204. 　　　End If
206. 　End Select
207. End Sub

図６－７

１４６行目は、ワークシート以外のシートをActiveにしている時に検索ボタンをクリックしても検索開始しないようにしています。
これは例えばグラフシート上で検索を実施しようとすると、まず１４８行目の「Selection.Count」でエラーが発生してしまうのを防止する意味があります。（図６－６の１３３行目と、ほぼ同じ役割です。）
また別な手段として「ブックの中に少なくとも１つのワークシートがあればワークシートに移動してから検索を実行する」というロジックを組む方法もあるのですが、「別なブックのワークシート上でダイアログ起動」＋「１つもワークシートが無い（グラフシートのみのブックも作り得る）ブックで検索実行」した場合には、MsgBoxで「ワークシートがありません」等のエラー処理が別途必要になってしまいます。
そう考えると今回は面倒なロジックはやめて、CommandButton1と同様に「ワークシート以外では動かない」ことにしました。
（このエラー防止機能は、前回のマクロには入っていませんでした。）

１４８～１５２行目は、シート内での検索範囲を設定しています。
氏名の列とか、住所の列とかを故意的に選択している場合は、「その列の中だけで検索する」ことをユーザーは期待しているはずです。そして、そのように列を選択している時には、複数のセル範囲を選択していることになりますので「Selection.Count」は複数の値を返してきます。
一方、組込ダイアログの検索を使用するときも「シート全体について検索したい時」は、「単一のセルを選択」しているはずです（シートの左上をクリックして、シート全体をActivateにする人もいるとは思いますが）。ですので「Selection.Count = 1」の時は「シート全体に対して検索実行」をユーザーは期待していると判断することにしました。
ということで「Selection.Count = 1」の時は、１４９行目で「シート全体（Cells）」を検索範囲（変数SS）に、それ以上の時は１５１行目で「現在選択している範囲（Selection）」を検索範囲に設定しています。

１５４行目は、myFindプロシージャ（図６－１３）を呼び出しています。
myFindプロシージャには２つの引数を渡しており、第一引数はFindメソッドの検索範囲、第二引数は検索開始セル（引数After）を指定します。（Findメソッドのその他の引数については、myFindプロシージャ内からダイアログ上のコントロールの状態を読み取り（図６－１３の２４２行目）、その値を引数に検索を行っています。）

myFindプロシージャの戻り値はFindメソッドと同じで、
　・検索文字列が見つかった時：見つかったセル範囲（Rangeオブジェクト）
　・見つからなかった時　　　：Nothing
を返してきますので、その値を変数FindCellに代入しています。
１５６行目以降では、そのFindCell値で処理をしていくのですが、その処理方法は「シート内検索とブック内検索では大きく違う」ため、まず１５６行目で「シート内検索かブック内検索か」で仕訳けを行っています。つまりシート内検索であれば１６０～１６４行目を実行し、ブック内検索であれば１７０～２０４行目を実行します。

まず、シート内検索（ComboBox.ListIndex = 0）の場合は、１６０行目で、戻り値の代入された変数FoundCellの値を調べることで処理を分岐させています。
すなわち、戻り値がNothingの時は「見つからなかった」ことを意味しますので１６１行目でメッセージを出し、それ以外（セル位置を返してきた時）は１６３行目でそのセル位置をActiveにします。

なお、１６３行目でActivateの代わりに「Select」としてしまうと、せっかく氏名列などを検索範囲とした場合でもその範囲は解除されてしまいますので、ここでは解除されない「Activate」を使用しています。
（変数FoundCellには、Range型かそれ以外のNothingが入る可能性がありますので、変数FoundCellのデータ型はVariant型の方が正しいと思われるかもしれません。しかしRange型の初期値（セル位置が何も入っていない時）はNothingですので、Range型の宣言で問題はありません。）

次にブック内検索（ComboBox.ListIndex = 1）の場合です。

１７０～１７４行目は「変数初出セルが空で無い時」つまり「そのシートで初めて検出したセルの位置を変数初出セルに代入し、Findメソッドで一周回るのを待っている状態の時」に、その初出セルが「Findメソッドで検出可能になっているか否か」を確認しています。

これは、以下の２つのことを想定した対策です。
１つは、ユーザーが検索途中に「初出セルの値を手動で変更」してしまい、「初出セルが二度と検出されない状態になっている」場合です。初出セルが再び現れるのを待っている状態なのに、Findメソッドで検出されないのですから、永久にそのシート上で検索を続けることになってしまいます。これでは「シート移動」ができません。

もう一つは、例えばシート１を検索中とします。シート１の中に検出されたセルがあるとすれば、変数StartCellには最初に見つけたセル位置が保存されているはずです。
この状態から手動でシート２をActiveにした後、ダイアログの検索ボタンをクリックするとします。シート２の中に検出されたセルが存在すると、そのセルをActiveにしてくれます。続けて検索ボタンをクリックしていくと、シート２の中で検出されたセルを順々にActiveにしていきますが、シート２に於ける変数StartCellのセル（シート１の初出セル位置）が検出対象セルで無い場合には、「シート内で一周まわった」ことにいつまで待ってもならないため、永久にシート２から抜け出さないことになります。

これを防ぐコード内容として、まず「検出されるか否か」を求めるため「myFindプロシージャ（図６－１３）」を呼び出し、第一引数の「検索範囲」には単一の「初出セル」を指定します。第二引数は「After」で通常の検索時には「ActiveCell」で良いのですが、検索範囲（第一引数）が単一のセルのため、その範囲に無いセルをAfterにするとエラーが発生してしまいます。
ですので、Afterにも単一の「初出セル」を指定します。
こうすることで、戻り値がNothingであれば「初出セルは検出される状態にない（＝初出セルの値を検出できないように書き換えた　Or　初出セルの位置に検出文字列が無いシートに移動した）」ことを意味しますので、１７２行目で変数の初期化を行っています。

ただし仮にNothingが戻って来たとしても、本来の検索は既に１５４行目で終わっていますので、一周余計に検索したり、別なシート内を一周検索できなかったりすることもあります。これを防ぐ手段は、今のところ思いつきません。

１７６～２０４行目までは、図３－２２のフローチャートをコード化したもの（ほぼ図３－２３）です。

６－５．閉じるボタン

ダイアログの「閉じる」ボタンをクリック、またはキーボードのESCキーを押すと呼び出されるイベントプロシージャが図６－８です。

208. '==========　⇩(9)　閉じるボタンのクリック　====================
209. Private Sub CommandButton3_Click()
210. 　Me.Hide
211. End Sub

図６－８

内容としては、２１０行目で単純にダイアログを隠しているだけです。隠すだけですので、その後図５－１のFind_Start2プロシージャを再び呼び出せば、検索文字列などは閉じた時の状態のまま再表示されることになります。
（なおダイアログ右上の×印で閉じてしまうと、次回起動はInitializeイベントからスタートしますので、初期状態に戻ってしまいます。）

６－６．オプションボタン

オプション部を開閉するためのボタン（CommandButton4）をクリックする事で起動するClickイベントプロシージャが図６－９です。

212. '==========　⇩(10)　オプションボタンのクリック　====================
213. Private Sub CommandButton4_Click()
214. 　If Me.Height > 200 Then
215. 　　Me.Height = 135
216. 　　Me.CommandButton4.Caption = "オプション≫"
217. 　Else
218. 　　Me.Height = 225
219. 　　Me.CommandButton4.Caption = "オプション≪"
220. 　End If
221. End Sub

図６－９

フォームは、上部に検索文字列などを配置し、下部にオプション部を配置しています。初期はオプション部を非表示にし、オプションボタンをクリックすることで表示・非表示（フォーム高さを拡大・縮小）させることとしています。
１つのボタンでフォームを拡大・縮小させるには、「現在のフォームのサイズを測り、基準より大きいかったら縮め、基準より小さかったら拡大」するのが１つの方法です。
今回のフォームの拡大時・縮小時のサイズは、図６－１０のようになっています。

図６－１０

このサイズについては特に意図が無く「作ってみたらこのサイズだった」だけです。この拡大と縮小のサイズの中間（キッチリで無くてもOK）である200ポイントを基準にして、ダイアログを拡大縮小させています。
なお「せっかくFrameコントロールで作った仕切り線」の位置を使う方法も考えられます（例えば、If UserForm1.InsideHeight > frame1.Top Then ･･･とか）が、今回特にコントロールの位置が動くなどの複雑なことをやっている訳では無いので、単純な定数で基準寸法を決めています。

基準寸法よりフォームのサイズが大きかった時には、２１５行目でサイズを小さくし、且つ２１６行目でボタン表面の文字列も変更しています。この文字列は組込ダイアログのオプションボタンの表示文字と合わせています。
基準寸法より小さかった時には、逆にサイズを大きくしています。

６－７．スピンボタン

「記入する列」の変更のために、スピンボタンの左矢印・右矢印をクリックしたとき呼び出されるイベントプロシージャが図６－１１です。

222. '==========　⇩(11)　スピンボタンの操作　====================
223. Private Sub SpinButton1_Change()
224. 　Dim colNo As Long　　　　　　 'スピンボタンの変更後の値（Value値）
225. 　Dim colABC As Variant　　　　 'ワークシートの列全体を表す文字列
227. 　colNo = SpinButton1.Value
229. 　colABC = Application.ConvertFormula("c" & colNo, xlR1C1, xlA1, xlRelative)
230. 　Me.Label1.Caption = Split(colABC, ":")(0)
232. End Sub

図６－１１

２２７行目では、SpinButton1の値（Value値）を取得し、変数colNoに代入しています。この値はR1C1参照スタイルの列位置を指すことになるのですが、標準状態のExcelのワークシートの列位置は「A1参照スタイルの列位置（A列、B列､･･･）で表示」されていますので、２２９～２３０行目でA1参照スタイルの列位置に変換をします。

２２９行目では「ConvertFormulaメソッド」を使って、R1C1参照スタイルからA1参照スタイルへの変換を行っています。
ConvertFormulaメソッドには、図６－１２のように５つの引数があります。

引数	役割	ﾃﾞｰﾀ型	定数･値	引数省略の場合	必須 /Opt
Formula	変換対象の数式 を含む文字列	Variant	ー	ー	必須
FromReferenceStyle	変換前の 参照形式	XlReferenceStyle	A1形式＝xlA1 = 1 R1C1形式＝xlR1C1 = -4150	xlA1	必須
ToReferenceStyle	取得する 参照スタイル	Variant	A1形式＝xlA1 = 1 R1C1形式＝xlR1C1 = -4150	FromReferenceStyle で指定した値が適用	Opt
ToAbsolute	変換された 参照型	Variant	行：絶対参照＋列：絶対参照＝xlAbsolute = 1 行：絶対参照＋列：相対参照＝xlAbsRowRelColumn = 2 行：相対参照＋列：相対参照＝xlRelative = 3 行：相対参照＋列：絶対参照＝xlRelRowAbsColumn = 4	参照の種類は 変更されず	Opt
RelativeTo	相対参照 の基点	Variant	ー	現在Activeに なっているｾﾙ位置	Opt

図６－１２

参照スタイル変換の考え方を以下のように考えました。
　・変換対象である引数Formulaには、列名だけを指定すると言う訳にはいかないので、「列全体」としてのセル範囲を指定する。
　・列全体をR1C1参照形式で表すには、例えばE列全体とすると「C5:C5」または「C5」と表示できる。
　・変換後、例えばE列全体は絶対参照では「$E:$E」となるが、相対参照だと「E:E」となる。相対参照の方が加工が簡単そう。
以上の考え方から、２２９行目の式は「Application.ConvertFormula("c" & colNo, xlR1C1, xlA1, xlRelative)」としました。第１引数のFormulaは、上記の列全体を表す「C5」相当、第４引数のToAbsoluteには相対参照の「xlRelative」を使っています。

このConvertFormulaの戻り値はcolABC変数に代入され、例えばE列全体であれば「E:E」という文字列が入ります。
この文字列は列名と列名の間に「：（コロン）」が入っていますので、２３０行目のSplit関数で一旦配列に格納し、その１番目（インデックス＝０）のデータを取り出して、Label1の表示にしています。
なお、配列の２番目（インデックス＝１）の値を使用しても結果は同じです。

なお、図６－２のInitializeイベントの９９行目でスピンボタンのMin値を移動した時、同時にValue値も変更（0→１）されることになり、図６－１１のSpinButton1_Changeイベントプロシージャが呼び出されますので、最初にフォームを起動した時には「１列目」である「A」という列名がLabel１に表示されることになります。

６－８．Findメソッドの実行

Findメソッドは、前回版ではCommandButton2_Clickイベントプロシージャ内に置いていましたが、今回はCommandButton2_Click内で２回実行（本検索＋初めて検出されたセルの存在確認のため）する事にしたため、図６－１３の別プロシージャとしました。

また、２回実行されるFindメソッドで異なる点は「検索範囲（SS）」と「引数After」の２つですので、それをプロシージャの引数としました。その他のFindメソッドの引数については、プロシージャ実行毎にダイアログのオプション等から取得することとしました。
なお、２回実行されるFindメソッドは、一連の流れの中で実行（図６－７の１５４行目・１７１行目）されるため、After以外のFindメソッドの引数はCommandButton2_Clickの中で取得する方が処理時間が短くなるとは思いますが、今回の方がプロシージャの独立性が高くなると判断しました。

233. '==========　⇩(12)　Findメソッドのオプション値生成　====================
234. Private Function myFind(SS As Range, myAfter As Variant) As Range
235. 　Dim myWhat As String
236. 　Dim myLookIn As Variant
237. 　Dim myLookAt As Variant
238. 　Dim mySearchOrder As Variant
239. 　Dim myMatchCase As Variant
240. 　Dim myMatchByte As Variant
242. 　Call myOption(myWhat, myLookIn, myLookAt, mySearchOrder, myMatchCase, myMatchByte)
244. 　Set myFind = SS.Find(What:=myWhat, _
245. 　　　　　　　　　　　　　　After:=myAfter, _
246. 　　　　　　　　　　　　　　LookIn:=myLookIn, _
247. 　　　　　　　　　　　　　　LookAt:=myLookAt, _
248. 　　　　　　　　　　　　　　SearchOrder:=mySearchOrder, _
249. 　　　　　　　　　　　　　　SearchDirection:=xlNext, _
250. 　　　　　　　　　　　　　　MatchCase:=myMatchCase, _
251. 　　　　　　　　　　　　　　MatchByte:=myMatchByte, _
252. 　　　　　　　　　　　　　　SearchFormat:=False )
254. End Function

図６－１３

２３５～２４０行目は、Findメソッドの引数を代入する変数宣言です。Findの引数は図６－１４のように９つありますが、今回システムのダイアログで設定しているのは「検索文字列（What）」およびオプションで設定可能な「検索方向（SearchDirection）」「検索対象（LookIn）」「大文字・小文字区別（MatchCase）」「完全同一（LookAt）」「半角・全角区別（MatchByte）」ですので、その６つについて変数宣言をしています。
なお、オプション内で選択できる「検索場所」はFindメソッドの引数では無いので、図６－７の１５６行目でロジック分岐のためにのみ使用しています。

２４２行目では「６つのFindメソッドの引数」を図６－１５のmyOptionプロシージャに渡し、「各変数に値が代入」されて戻ってきます。

２４４～２５２行目は１つのコードで、Findメソッドを実行しその戻り値をプロシージャの戻り値にしています。検索範囲についてはプロシージャの引数である「SS」で指定されます。
Findメソッドの引数は前述したように全部で９つあり、引数Afterについてはプロシージャの引数である「myAfter」を指定し、それ以外の引数で「my・・・」という変数名で値を渡しているのが、myOptionプロシージャから受け取った値になります。
図６－１４を見ても分かるように、引数の内「SearchDirection」と「SearchFormat」は既定値のままの値を渡していますので、わざわざ引数を設定をせず省略しても良いのですが、ここでは全引数を説明する意味で引数設定をしています。

図６－１４はFindメソッドの引数一覧です。なお一番右の「引数継承」欄で〇になっている引数は、Findメソッドを使用すると保存される引数で、次にFindメソッドを使用する時にこれらの引数を指定しない場合には保存した引数値が使われることになります。
（今回のシステムでは、引数の継承は使用していません。）

引数	役割	ﾃﾞｰﾀ型	定数・値	既定値	引数継承
What	検索データ	Variant	ー	ー
After	検索開始セル	Variant	ー	範囲の左上端ｾﾙ	〇
LookIn	検索する データの種類	Variant	数式＝xlFormulas= -4123 値＝xlValues= -4163 コメント＝xlComments= -4144	xlFormulas	〇
LookAt	検索の方法	Variant	完全に同一＝xlWhole = 1 部分一致＝xlPart = 2	xlPart	〇
SearchOrder	検索する順序	Variant	行を先に検索＝xlByRows =1 列を先に検索＝xlByColumns = 2	xlByRows
SearchDirection	検索の方向	Long	前方検索＝xlNext =1 後方検索＝xlPrevious =2	xlNext
MatchCase	大文字と小文字 の区別	Variant	区別する＝True 区別しない＝False	FALSE
MatchByte	全角･半角 の区別	Variant	区別する＝True 区別しない＝False	FALSE	〇
SearchFormat	検索の書式	Variant	書式を検索する＝True 書式を検索しない＝False	FALSE

図６－１４

６－９．Findメソッドのオプション値生成

図６－１３の２４２行目から呼び出されるのが、図６－１５のmyOptionプロシージャになります。引数としてFindメソッドの引数６つを入れる変数を受け取り、ユーザーがダイアログ内でどのような設定しているかを調査し、その結果を戻します。

255. '==========　⇩(13)　Findメソッドのオプション値生成　====================
256. Private Sub myOption( myWhat As String, myLookIn As Variant, myLookAt As Variant, _
257. 　　　　　　　　　　　　mySearchOrder As Variant, myMatchCase As Variant, myMatchByte As Variant)
259. 　myWhat = Me.TextBox1.Value 　　　　　　　　　 '検索する文字列
261. 　Select Case Me.ComboBox3.ListIndex 　　　　　　　　 '検索対象
262. 　　Case 0
263. 　　　myLookIn = xlFormulas
264. 　　Case 1
265. 　　　myLookIn = xlValues
266. 　　Case 2
267. 　　　myLookIn = xlComments
268. 　End Select
270. 　Select Case Me.CheckBox2.Value 　　　　　　　　　　 '完全一致か否か
271. 　　Case False
272. 　　　myLookAt = xlPart
273. 　　Case True
274. 　　　myLookAt = xlWhole
275. 　End Select
277. 　Select Case Me.ComboBox2.ListIndex 　　　　　 '検索方向
278. 　　Case 0
279. 　　　mySearchOrder = xlByRows
280. 　　Case 1
281. 　　　mySearchOrder = xlByColumns
282. 　End Select
284. 　myMatchCase = Me.CheckBox1.Value 　　　　　　　　　 '大文字小文字の区別
286. 　myMatchByte = Me.CheckBox3.Value 　　　　　　　　　 '半角全角の区別
288. End Sub

図６－１５

図６－１５は、引数順に値を取り込んでいます。
２５９行目で、「検索文字列（TextBox１）」の値を読み込み、変数myWhatにします。

２６１～２６８行目は、「検索対象」であるComboBox3のどの項目を選択しているかを調べて分岐させ、定数を変数myLookInに代入します。なお、ComboBoxは一番上がListIndex＝0になります。

２７０～２７５行目は「完全一致」であるCheckBox2の状態を調べ、レ点の状態（レ点無し：Value＝False、有り：Value＝True）で定数を変数myLookAtに代入します。

２７７～２８２行目は「検索方向」であるComboBox2のどの項目を選択しているかを調べて分岐させ、定数を変数mySearchOrderに代入します。

２８４行目の「myMatchCase」及び２８６行目の「myMatchByte」は、Findメソッドの引数値がFalseーTrueで、CheckBoxのFalseーTrueと同データ型ですので、CheckBoxの値を直接引数に渡しています。

６－１０．シート移動

図３－２２のプロ―チャートなどで使用している「シートを移動」を実行するのが図６－１６です。
なお今回のシステムでは、ダイアログ起動時にワークシートからのみ起動できますし、また検索ボタンもワークシート上でしか実行できないようにしています。つまり検索するブックには「１つ以上のワークシートがある」というのが前提です。

289. '==========　⇩(14)　シート移動　====================
290. Public Sub NextSheet()
291. 　Dim IndexNo As Long
293. 　IndexNo = ActiveSheet.Index
295. 　Do
296. 　　IndexNo = (IndexNo Mod Sheets.Count) + 1
297. 　Loop Until (Sheets(IndexNo).Type = xlWorksheet) And (Sheets(IndexNo).Visible = True)
299. 　Sheets(IndexNo).Activate
300. End Sub

図６－１６

次のシートを指定するには、通常は「ActiveSheet.Next」を使ったり、シートのインデックスを１ずつ増やしていけば良いのですが、一番最後のシートに対してこれらを実行するとエラーが発生します。
ですので、この方法の場合にはIf文で「最後のシートの時は、先頭のシートを選ぶ」などの処理を入れる必要が出てきます。
これに代わり「Mod関数」を使って割り算の余りを使うと、「最後のシートの次は最初のシート」とすることが出来ます。

２９６行目の式「IndexNo = (IndexNo Mod Sheets.Count) + 1」に値を入れて確認してみます。シートの総数（Sheets.Count）は「３」とします。
　IndexNo＝１　：　１÷３　→　商＝０　余り＝１　：　(IndexNo Mod Sheets.Count) + 1　＝　２　→ 次のシートはシート２
　IndexNo＝２　：　２÷３　→　商＝０　余り＝２　：　(IndexNo Mod Sheets.Count) + 1　＝　３　→ 次のシートはシート３
　IndexNo＝３　：　３÷３　→　商＝１　余り＝０　：　(IndexNo Mod Sheets.Count) + 1　＝　１　→ 次のシートはシート１
このようにトグルでシートを回すことが出来ますので、今回はこの方法を使用します。

また２９５～２９７行目はDo～Loopで回していますが、その脱出条件は２９７行目の「Sheets(IndexNo).Type = xlWorksheet」及び「Sheets(IndexNo).Visible = True」で与えています。
これは、上記計算で出した「次のシート」が「表示されているワークシートになるまでDo～Loopを回す」ことになりますので、Do～Loopを抜けた時のIndexNoのシートは必ず表示されているワークシートとなるのです。
２９９行目では、そのIndexNoのシートをActiveにしています。

Do～Loopがイヤな方は、例えば図６－１７のように「Activeシートのシート番号を始点とするFor～Next文」でワークシートを見つけ、見つけたシートをActiveにするという方法もあります。

301. '==========　⇩(15)　シート移動２　====================
302. Sub NextSheet2()
303. 　Dim i As Long
304. 　Dim Sh As Object
306. 　For i = ActiveSheet.Index To ActiveSheet.Index + Sheets.Count
307. 　　Set Sh = Sheets((i Mod Sheets.Count) + 1)
308. 　　If (Sh.Type = xlWorksheet and Sh.Visible = True) Then Exit For
309. 　Next i
311. 　Sh.Activate
312. End Sub

図６－１７

また図６－１８のように、「隣のシートに移動してみてから、ワークシートじゃなかったらもう一度移動してみる」という方法もあります。なおDo～Loopが無いとは言え、再帰呼び出しですので無限呼出しが無いことの確認は必要です。

313. '==========　⇩(16)　シート移動３　====================
314. Public Sub NextSheet3()
315. 　If ActiveSheet.Index = Sheets.Count Then
316. 　　Sheets(1).Activate
317. 　Else
318. 　　ActiveSheet.Next.Activate
319. 　End If
321. 　If (Not ActiveSheet.Type = xlWorksheet) Or (ActiveSheet.Visible = False) Then
322. 　　Call NextSheet3
323. 　End If
324. End Sub

図６－１８

どの方法でも同じ結果が得られるはずですので、自分で理解し易いコードが良いと思います。

６－１１．初出セル・初出シート変数の初期化

図６－１９は、変数「初出セル」と「初出シート」を初期化するプロシージャです。図６－７の１７２行目、及びオプション・検索文字列を変更した時点（図６－２０）で呼び出されるものです。
様々な場面で初期化が必要なのでプロシージャ化しましたが、内容としては変数の初期化のみです。

325. '==========　⇩(17)　初出セル・初出シート変数の初期化　====================
326. Private Sub SearchReSet()
327. 　StartSheet = ""
328. 　StartCell = ""
329. End Sub

図６－１９

６－１２．条件変更時の変数初期化

オプション部及び検索文字列を変更した時は、「それまでの検索とは条件が変わる」ため、変数「初出セル」と「初出シート」を初期化する必要があります。ですのでオプション項目のChangeイベントが発生した時には、図６－１９を呼び出し変数の初期化を行っています。

330. '==========　⇩(18)　条件変更時の変数初期化　====================
331. Private Sub TextBox1_Change()
332. 　Call SearchReSet
333. End Sub
335. Private Sub CheckBox1_Change()
336. 　Call SearchReSet
337. End Sub
339. Private Sub CheckBox2_Change()
340. 　Call SearchReSet
341. End Sub
343. Private Sub CheckBox3_Change()
344. 　Call SearchReSet
345. End Sub
347. Private Sub ComboBox1_Change()
348. 　Call SearchReSet
349. End Sub
351. Private Sub ComboBox2_Change()
352. 　Call SearchReSet
353. End Sub
355. Private Sub ComboBox3_Change()
356. 　Call SearchReSet
357. End Sub

図６－２０

なお、せっかく各オプション項目＋検索文字列のChangeイベントがあるので、初めにフォームモジュールレベルの変数をいくつか用意し、Changeイベント発生時にそのオプション値を変数に保存しておけば、Findメソッドを実行するたびに、毎回ダイアログのコントロール状態を確認しなくて良くなると思います。ただし、オプションの初期状態はInitializeイベントで変数に初期値を入れる必要はあります。

７．アドインとしてExcelにマクロを登録

「住所録ファイル」と「検索システムファイル」が異なる場合は、２つ同時に開けば検索可能にはなりますが、あまり使い勝手は良くありません。そこで「検索システムファイル」をExcelの機能の１つに登録し、Excel上部のリボンのボタンを押せばシステムを起動できるようにできます。
その方法については「年賀状リスト等の宛名検索と追記　アドイン登録」を参照下さい。

８．最後に

前回（年賀状リスト等の宛名検索と追記）に対して、基本的には「ブック内検索」を追加しただけですが、結構めんどうなシステムになってしまいました。どれほどの価値があるのか分かりませんが、活用いただけたら幸いです。

なお組込ダイアログの検索でブック内検索をした場合、例えば氏名列だけを検索範囲にしようと列選択したとしても、検索を開始した直後に列選択が解除されてしまいます。（シート内検索では当然ながら検索範囲は保持されます。）
その点、今回のシステムは検索範囲が解除されることなく検索を進めてくれるので、「ブック内検索を検索範囲を絞って行う場合」には有難いかもしれません。

「複数シート間で検索する方法」については他のサイトでも解説されていますが、そのロジックとしては「For Each 〇 In Worksheets」でワークシートを送り、その内側でDo～Loopを使って検索がヒットするまでFindNextを実行する、というのが多いようです。
そのロジックを使っても、今回と同様の動作を作ることはたぶん可能だと思いますが、今回のような手法もあると思ってもらえば良いとおもいます。

年賀状リスト等の宛名検索（ブック内検索可）（it-048.xlsm）

セキュリティ向上を目的として「インターネット経由でダウンロードしたOfficeファイル（Excel等）のマクロは、既定でブロック」されるようにOfficeアプリケーションの既定動作が変更になりました。（2022年4月より切替開始） 解除の方法については「ダウンロードファイルのブロック解除方法」を参照下さい。