2021/01/23

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




1.背景

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

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

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

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

2.システム概要

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

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

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

以上までは前回仕様と同じですが、今回はそれに加えて図2-3のように、オプションボタン⑩をクリックすることで現れるオプション項目⑪を選択できるようにしています。
ダイアログのオプション選択部を表示
図2-3

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

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

また図2-5のように、各シートで検索列を指定③してから実行すると、例えば氏名列のみで全シート検索が出来ることになります。
各ワークシートに検索範囲を指定してからブック内検索
図2-5

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

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

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

寄り道
「複数ワークシートのグループ化」の状態で検索を実行すると、不思議な現象が出ることに気が付きました。
1つは、ワークシートのグループ化をした上で検索列を絞る(図2-6の状態)と、「検索場所=シート」の状態でもグループ化した全ワークシートに対して検索をしてくれます。
但し図2-6でも記しましたが、「検索のシートの順序」が順番通りにならないことがあるようです。(もしかしたら、シートを作った順序かもしれません。)

もう1つは、Findメソッド実行直後に「選択した検索列の先頭セルがActiveになる」ことです。
通常は、Findメソッドを実行した直後は「前回の検索セルがActive」であり、「Activeメソッドを実行して初めて、今回検出したセルがActive」になるのですが、ワークシートのグループ化+検索列を絞った状態(図2-5)では、Findメソッド実行直後には「Activeセルが、前回の検索セルから選択した検索列の先頭セルに移動」するのです。これを図にしたのが図2-7です。
ワークシートのグループ化+列選択状態でFind検索した時のActiveセルの動き
図2-7

理屈を色々考えてみましたが、良く分かりません。言えるのは「シートのグループ化+範囲選択した状態では、通常とは違うことが起こり得る」ことだと思います。
なおFindメソッドでは、シートのグループ化+検索列選択で全シート検索となるのですが、「組込ダイアログの検索」ではシートのグループ化のみで全シート検索となります(オプションの検索場所はシートのまま)。
このような動作は、Findメソッド・FindNextメソッドでは実現できないので、組込ダイアログの検索とメソッドとしてのFind・FindNextは微妙に動作が違うようです。

3.プログラムの流れ

3ー1.基本的な流れ

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

プログラムの流れ
図3-1

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

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

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

3ー2.ブック内での検索

3ー2ー1.検索の基本的な考え方

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

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

この変数には「初めて見つかったセルの位置」が入りますので、変数は「初出セル(コード上ではStartCell)」という名前にしておきます。
また、そのシートの中でセルが見つからなかった時にはFindメソッドからはNothingが返されます。Nothingの時には、そのまま次のシートに移動します。
この方法をフローチャートにしたのが、図3-2の右側です。
複数シート間を検索する基本ロジック
図3-2

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

複数シート間を検索していく流れ
図3-3

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

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

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

3ー2ー2.検索パターンの種類

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

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

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

3ー2ー3.コードの流れ(検出セルがゼロ個の場合)

図3-4の中の①(ワークシートが1つで、検出セルがゼロ個)の場合のプログラムの流れを図3-6に記しました。恐ろしいことに無限ループです。
この無限ループの原因は、図3-2のフローチャートが「どこかのシートには検出セルがあるだろう」という前提で出来ているからです。
検出セルが1つも無い、1ワークシートの場合のコードの流れ
図3-6

図3-5の中の④(ワークシートが2つで、検出セルがゼロ個)の場合も、図3-7の通り同様に無限ループになります。
検出セルが1つも無い、2ワークシートの場合のコードの流れ
図3-7

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

初出シート変数も追加したフローチャート
図3-8

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

このフローチャートを使って、図3-6(パターン①)と図3-7(パターン④)がどうなるかを図3-9に示します。
結果としては、検出セルが無い場合にもMsgBoxが出てプログラムを止めることが出来たことが分かります。
新フローチャートでの検出セルが無い場合のコードの流れ
図3-9

3ー2ー4.コードの流れ(検出セルが存在する場合)

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

ワークシートが1つで、検出セルが1つの場合(パターン②)が図3-10、検出セルが2つの場合(パターン③)が図3-11です。シート移動後に再検索しているので無駄な動きはありますが、両方とも結果はOKです。
1シート+検出セル1個の場合のコードの流れ
図3-10

1シート+検出セル2個の場合のコードの流れ
図3-11

次にワークシートが2つの場合です。
まず、どちらかのシートには検出セルが無く、もう一方のシートには検出セルが1個の場合です。
図3-12は検索開始側シートに検出セルが1つあった場合(パターン⑤)、図3-13は反検索開始側シートに検出セルが1つあった場合(パターン⑥)です。
どちらの場合も唯一の検出セルがActiveになっています。
2シート+開始シート側に検出セル1個のみの場合のコードの流れ
図3-12

2シート+反開始シート側に検出セル1個のみの場合のコードの流れ
図3-13

2つのシートのそれぞれに1個ずつ検出セルがある場合(パターン⑦)が図3-14です。この場合も問題無く交互にセルがActiveになります。
2シート+各シートに検出セル1個ずつの場合のコードの流れ
図3-14

一方のシートに検出セルが2つ(以上)あり、もう一方のシートには検出セルが無い場合を考えます。
検索開始側シートに検出セルがある場合(パターン⑧)が図3-15、反検索開始側シートに検出セルがある場合(パターン⑨)が図3-16です。問題ありません。
2シート+一方のシートに検出セルが2つ、もう一方のシートには検出セルが無い場合のコードの流れ
図3-15

2シート+一方のシートに検出セルが2つ、もう一方のシートには検出セルが無い場合のコードの流れ
図3-16

一方のシートに検出セルが2つ(以上)あり、もう一方のシートには検出セルが1つの場合を考えます。
検索開始側シートに複数の検出セルがある場合(パターン⑩)が図3-17、反検索開始側シートに複数の検出セルがある場合(パターン⑪)が図3-18です。問題ありません。
2シート+一方のシートに検出セルが2つ、もう一方のシートには検出セルが1つの場合のコードの流れ
図3-17

2シート+一方のシートに検出セルが2つ、もう一方のシートには検出セルが1つの場合のコードの流れ
図3-18

最後に、両方のシートとも2つ(以上)の検出セルがある場合(パターン⑫)が図3-19です。
2シート+各シートに検出セルが複数ある場合のコードの流れ
図3-19

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

3ー2ー5.フローチャートをコード化してみる

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

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

図3-21のコードを眺めると、同じコードが重複している部分に気が付くと思います。11~13行目と18~20行目、30~32行目です。もちろんこのコードでも正常に動作するのですが、同じコードがあちこちに存在すると、プログラム改造などの時に修正洩れの可能性が大きくなるため、できるだけ重複を少なくするのが大切です。

3ー2ー6.フローチャートの見直し

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

修正した内容は、図3-20の「③と④の順序を入替え」「⑫と⑬の順序を入替え」のみです。修正は少ないですが、これをコードにすると図3-23になります。
  1. '========== ⇩(2) 「ブック内検索のコード2」 ====================
  2. Private Sub 検索ボタン_Click()
  3.  Dim FoundCell As Range     'Find検索の戻り値
  4.  Set FoundCell = ActiveSheet.Cells.Find(What:=検索文字列, After:=ActiveCell, ・・・・ )    '←①
  5.  If FoundCell Is Nothing Then            '←⑪
  6.   If StartSheet = ActiveSheet.Name Then     '←⑬
  7.    StartSheet = ""                  '←⑮
  8.    MsgBox "検索が失敗しました"          '←⑯
  9.   Else
  10.    If StartSheet = "" Then              '←⑫
  11.     StartSheet = ActEnd IfiveSheet.Name    '←⑭
  12.    End If
  13.    StartCell = ""                  '←⑧
  14.    Call シート移動                  '←⑨
  15.    Call 検索ボタン_Click               '←⑩
  16.   End If
  17.  Else               '←②
  18.   If StartCell = FoundCell.Address Then     '←④
  19.    StartCell = ""                   '←⑧
  20.    Call シート移動                   '←⑨
  21.    Call 検索ボタン_Click               '←⑩
  22.   Else
  23.  
  24.    If StartCell = "" Then                '←③
  25.     StartCell = FoundCell.Address           '←⑤
  26.    End If
  27.    StartSheet = ""                    '←⑥
  28.    FoundCell.Activate                  '←⑦
  29.   End If
  30.  End If
  31. End Sub
図3-23

図3-21で重複していたブロックが、図3-23でも55~57行目・63~65行目と残っていますが、フローチャートを修正しただけでも3→2箇所に減少させることが出来ています。
図3-23よりももっとコード行数を減らせたり、重複コードを無くす工夫の余地(例えばGOTOステートメントを使う等)はあるとは思いますが、今回は図3-22のフローチャートの手法で作りたいと思います。

4.フォームの作成

今回のフォームは、図4-1のように作成しました。
フォームの作成
図4-1

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

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

5.標準モジュールのコード

本システムは、図5-1のFind_Start2プロシージャからスタートします。
(前回のプロシージャ名は「Find_Start」としましたが、今回システムも同時にアドインすると、どちらのシステムの起動プロシージャか分からなくなっていまうため、今回システムの起動は「Find_Start2」という名前に変更しました。)
  1. '========== ⇩(3) システムの起動 ====================
  2. Sub Find_Start2()
  3.  If ActiveSheet.Type = xlWorksheet Then
  4.   UserForm1.Show 0
  5.  End If
  6. End Sub
図5-1

79行目のShowメソッドでダイアログがモードレス(ダイアログ起動中もシートを操作可能)で起動されますが、検索は「ワークシート上でのみ可能」ですので、アクティブになっているシートがワークシートか否かを78行目で確認しています。
シートの種類は図5-2のように5種類あり、その内の「住所録として使用できるワークシート」のみで動作するようにするためです。
XlSheetTypeのメンバー
名前内容検索が可能
xlChart-4109グラフシート
xlDialogSheet-4116Excel5 ダイアログシート
xlExcel4IntlMacroSheet4Excel4International マクロシート
xlExcel4MacroSheet3Excel4マクロシート
xlWorksheet-4167ワークシート
図5-2

なおExcel4マクロシート(図5-2の3番目,4番目)は、ワークシート相当みたいでExcel組込ダイアログの検索も実行できますが、今回は対象外としています。

6.フォームモジュールのコード

6-1.フォームレベルの変数宣言

フォームモジュールの宣言部(先頭部分)で、モジュール内で共有して使用する変数の宣言を行っています。
  1. '========== ⇩(4) 変数宣言 ====================
  2. Dim StartSheet As String       '検索開始時のワークシート名
  3. Dim StartCell As String        '対象シートでの検索開始時のセルアドレス
図6-1

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

6-2.フォーム起動時の初期化

フォームを初回に起動する時に呼び出されるInitializeイベントプロシージャが図6-2です。
  1. '========== ⇩(5) フォーム初回起動時 ====================
  2. Private Sub UserForm_Initialize()
  3.  Me.Caption = "検索と追記"
  4.  Me.CommandButton1.Caption = "文字列挿入"
  5.  Me.CommandButton2.Caption = "次を検索"
  6.  Me.CommandButton3.Caption = "閉じる"
  7.  Me.CommandButton4.Caption = "オプション≫"
  8.  Me.CommandButton2.Default = True
  9.  Me.CommandButton3.Cancel = True
  10.  Me.SpinButton1.Max = Application.Columns.Count
  11.  Me.SpinButton1.Min = 1
  12.  Me.Frame1.BorderStyle = fmBorderStyleSingle
  13.  Me.Frame1.BorderColor = RGB(128, 128, 128)
  14.  Me.Frame1.Height = 1
  15.  Me.Frame1.Caption = ""
  16.  Me.ComboBox1.Style = fmStyleDropDownList
  17.  Me.ComboBox1.AddItem "シート"
  18.  Me.ComboBox1.AddItem "ブック"
  19.  Me.ComboBox1.ListIndex = 0
  20.  Me.ComboBox2.Style = fmStyleDropDownList
  21.  Me.ComboBox2.AddItem "行"
  22.  Me.ComboBox2.AddItem "列"
  23.  Me.ComboBox2.ListIndex = 0
  24.  Me.ComboBox3.Style = fmStyleDropDownList
  25.  Me.ComboBox3.AddItem "数式"
  26.  Me.ComboBox3.AddItem "値"
  27.  Me.ComboBox3.AddItem "コメント"
  28.  Me.ComboBox3.ListIndex = 0
  29.  Call CommandButton4_Click
  30. End Sub
図6-2

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

95行目は「次を検索」ボタンをDefaultボタンに設定しています。Defaultプロパティに設定(=True)できるのは、1つのフォーム内で1つのコマンドボタンだけです。
Defaultボタンに設定すると、そのボタンに対しては「キーボードのEnterキーを押す」=「Defaultボタンをクリックする」ことになります。ただし「フォーム内の他のボタンにフォーカスがある時にEnterキーを押した時」には「Defaultボタンでは無く、フォーカスのあるボタンが押されたことになる」ことには注意が必要です。

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

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

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

スピンボタンの設定値変更
図6-3

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

スピンボタン設定値の変更の順序
図6-4

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

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

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

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

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

  1. '========== ⇩(6) フォーム表示時 ====================
  2. Private Sub UserForm_Activate()
  3.  Me.TextBox1.SetFocus
  4. End Sub
図6-5

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

6-3.文字列挿入ボタン

図6-7の「次を検索」ボタンで「検索文字列が見つかったセルがActive」になっている状態の時、「文字列挿入」ボタンをクリックした時に動作するイベントプロシージャが図6-6です。
(但し、検索していない状態でも「文字列挿入」ボタンはクリックできます。その場合には「Activeになっているセルの行 x 指定した列」で定まるセル位置に文字列が挿入されます。)
  1. '========== ⇩(7) 文字列挿入ボタンのクリック ====================
  2. Private Sub CommandButton1_Click()
  3.  Dim WriteCell As Range    '文字列を書き込むセル範囲
  4.  If Not ActiveSheet.Type = xlWorksheet Then Exit Sub
  5.  Set WriteCell = ActiveSheet.Range(Me.Label1.Caption & ActiveCell.Row)
  6.  WriteCell.Value = WriteCell.Value & Me.TextBox2.Value
  7.  Me.CommandButton2.SetFocus
  8. End Sub
図6-6

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

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

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

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

139行目では、フォーカスを「次を検索ボタン(CommandButton2)」に移します。図6-2の95行目のところで説明しましたが、どこか他のボタンにフォーカスが当たっていると「Enterキーを押してもDefaultボタンをクリックした事にならない」ため、「DefaultボタンであるCommandButton2」にフォーカスを当てています。
なお「ボタン以外にフォーカスが当たっていれば良い」ので、139行目を「Me.TextBox1.SetFocus」に置き換えてもOKです。

6-4.検索ボタン

「次を検索」ボタンをクリック、またはフォームがアクティブになっている状態でキーボードのEnterキーを押した時に呼び出されるイベントプロシージャが図6-7です。
  1. '========== ⇩(8) 検索ボタンのクリック ====================
  2. Private Sub CommandButton2_Click()
  3.  Dim SS As Range              '検索するセル範囲
  4.  Dim FoundCell As Range          'Findメソッドの戻り値(セル範囲、またはNothing)
  5.  If Not ActiveSheet.Type = xlWorksheet Then Exit Sub
  6.  If Selection.Count = 1 Then '選択している範囲
  7.   Set SS = ActiveSheet.Cells
  8.  Else
  9.   Set SS = Selection
  10.  End If
  11.  Set FoundCell = myFind(SS, ActiveCell)
  12.  Select Case Me.ComboBox1.ListIndex    'シート内検索かブック内検索か
  13.   Case 0 'シート内検索
  14.    If FoundCell Is Nothing Then
  15.     MsgBox "検索が失敗しました"
  16.    Else
  17.     FoundCell.Activate
  18.    End If
  19.   Case 1 'ブック内検索
  20.    If Not StartCell = "" Then
  21.     If myFind(Range(StartCell), Range(StartCell)) Is Nothing Then
  22.      Call SearchReSet
  23.     End If
  24.    End If
  25.    If FoundCell Is Nothing Then '検索失敗
  26.     If StartSheet = ActiveSheet.Name Then
  27.      StartSheet = ""
  28.      MsgBox "検索が失敗しました"
  29.     Else
  30.      If StartSheet = "" Then
  31.       StartSheet = ActiveSheet.Name
  32.      End If
  33.      StartCell = ""
  34.      Call NextSheet
  35.      Call CommandButton2_Click
  36.     End If
  37.    Else       '検索成功
  38.     If StartCell = FoundCell.Address Then
  39.      StartCell = ""
  40.      Call NextSheet
  41.      Call CommandButton2_Click
  42.     Else
  43.      If StartCell = "" Then
  44.       StartCell = FoundCell.Address
  45.      End If
  46.      StartSheet = ""
  47.      FoundCell.Activate
  48.     End If
  49.    End If
  50.  End Select
  51. End Sub
図6-7

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

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

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

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

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

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

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

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

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

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

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

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

176~204行目までは、図3-22のフローチャートをコード化したもの(ほぼ図3-23)です。

6-5.閉じるボタン

ダイアログの「閉じる」ボタンをクリック、またはキーボードのESCキーを押すと呼び出されるイベントプロシージャが図6-8です。
  1. '========== ⇩(9) 閉じるボタンのクリック ====================
  2. Private Sub CommandButton3_Click()
  3.  Me.Hide
  4. End Sub
図6-8

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

6-6.オプションボタン

オプション部を開閉するためのボタン(CommandButton4)をクリックする事で起動するClickイベントプロシージャが図6-9です。
  1. '========== ⇩(10) オプションボタンのクリック ====================
  2. Private Sub CommandButton4_Click()
  3.  If Me.Height > 200 Then
  4.   Me.Height = 135
  5.   Me.CommandButton4.Caption = "オプション≫"
  6.  Else
  7.   Me.Height = 225
  8.   Me.CommandButton4.Caption = "オプション≪"
  9.  End If
  10. End Sub
図6-9

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

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

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

6-7.スピンボタン

「記入する列」の変更のために、スピンボタンの左矢印・右矢印をクリックしたとき呼び出されるイベントプロシージャが図6-11です。
  1. '========== ⇩(11) スピンボタンの操作 ====================
  2. Private Sub SpinButton1_Change()
  3.  Dim colNo As Long       'スピンボタンの変更後の値(Value値)
  4.  Dim colABC As Variant     'ワークシートの列全体を表す文字列
  5.  colNo = SpinButton1.Value
  6.  colABC = Application.ConvertFormula("c" & colNo, xlR1C1, xlA1, xlRelative)
  7.  Me.Label1.Caption = Split(colABC, ":")(0)
  8. End Sub
図6-11

227行目では、SpinButton1の値(Value値)を取得し、変数colNoに代入しています。この値はR1C1参照スタイルの列位置を指すことになるのですが、標準状態のExcelのワークシートの列位置は「A1参照スタイルの列位置(A列、B列、・・・)で表示」されていますので、229~230行目でA1参照スタイルの列位置に変換をします。

229行目では「ConvertFormulaメソッド」を使って、R1C1参照スタイルからA1参照スタイルへの変換を行っています。
ConvertFormulaメソッドには、図6-12のように5つの引数があります。
引数役割データ型定数・値引数省略の場合必須
/Opt
Formula変換対象の数式
を含む文字列
Variant必須
FromReferenceStyle変換前の
参照形式
XlReferenceStyleA1形式=xlA1 = 1
R1C1形式=xlR1C1 = -4150
xlA1必須
ToReferenceStyle取得する
参照スタイル
VariantA1形式=xlA1 = 1
R1C1形式=xlR1C1 = -4150
FromReferenceStyle
で指定した値が適用
Opt
ToAbsolute変換された
参照型
Variant行:絶対参照+列:絶対参照=xlAbsolute = 1
行:絶対参照+列:相対参照=xlAbsRowRelColumn = 2
行:相対参照+列:相対参照=xlRelative = 3
行:相対参照+列:絶対参照=xlRelRowAbsColumn = 4
参照の種類は
変更されず
Opt
RelativeTo相対参照
の基点
Variant現在Activeに
なっているセル位置
Opt
図6-12

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

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

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

6-8.Findメソッドの実行

Findメソッドは、前回版ではCommandButton2_Clickイベントプロシージャ内に置いていましたが、今回はCommandButton2_Click内で2回実行(本検索+初めて検出されたセルの存在確認のため)する事にしたため、図6-13の別プロシージャとしました。

また、2回実行されるFindメソッドで異なる点は「検索範囲(SS)」と「引数After」の2つですので、それをプロシージャの引数としました。その他のFindメソッドの引数については、プロシージャ実行毎にダイアログのオプション等から取得することとしました。
なお、2回実行されるFindメソッドは、一連の流れの中で実行(図6-7の154行目・171行目)されるため、After以外のFindメソッドの引数はCommandButton2_Clickの中で取得する方が処理時間が短くなるとは思いますが、今回の方がプロシージャの独立性が高くなると判断しました。
  1. '========== ⇩(12) Findメソッドのオプション値生成 ====================
  2. Private Function myFind(SS As Range, myAfter As Variant) As Range
  3.  Dim myWhat As String
  4.  Dim myLookIn As Variant
  5.  Dim myLookAt As Variant
  6.  Dim mySearchOrder As Variant
  7.  Dim myMatchCase As Variant
  8.  Dim myMatchByte As Variant
  9.  Call myOption(myWhat, myLookIn, myLookAt, mySearchOrder, myMatchCase, myMatchByte)
  10.  Set myFind = SS.Find(What:=myWhat, _
  11.               After:=myAfter, _
  12.               LookIn:=myLookIn, _
  13.               LookAt:=myLookAt, _
  14.               SearchOrder:=mySearchOrder, _
  15.               SearchDirection:=xlNext, _
  16.               MatchCase:=myMatchCase, _
  17.               MatchByte:=myMatchByte, _
  18.               SearchFormat:=False )
  19. End Function
図6-13

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

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

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

図6-14は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
図6-14

6-9.Findメソッドのオプション値生成

図6-13の242行目から呼び出されるのが、図6-15のmyOptionプロシージャになります。引数としてFindメソッドの引数6つを入れる変数を受け取り、ユーザーがダイアログ内でどのような設定しているかを調査し、その結果を戻します。
  1. '========== ⇩(13) Findメソッドのオプション値生成 ====================
  2. Private Sub myOption( myWhat As String, myLookIn As Variant, myLookAt As Variant, _
  3.             mySearchOrder As Variant, myMatchCase As Variant, myMatchByte As Variant)
  4.  myWhat = Me.TextBox1.Value           '検索する文字列
  5.  Select Case Me.ComboBox3.ListIndex          '検索対象
  6.   Case 0
  7.    myLookIn = xlFormulas
  8.   Case 1
  9.    myLookIn = xlValues
  10.   Case 2
  11.    myLookIn = xlComments
  12.  End Select
  13.  Select Case Me.CheckBox2.Value            '完全一致か否か
  14.   Case False
  15.    myLookAt = xlPart
  16.   Case True
  17.    myLookAt = xlWhole
  18.  End Select
  19.  Select Case Me.ComboBox2.ListIndex       '検索方向
  20.   Case 0
  21.    mySearchOrder = xlByRows
  22.   Case 1
  23.    mySearchOrder = xlByColumns
  24.  End Select
  25.  myMatchCase = Me.CheckBox1.Value           '大文字小文字の区別
  26.  myMatchByte = Me.CheckBox3.Value           '半角全角の区別
  27. End Sub
図6-15

図6-15は、引数順に値を取り込んでいます。
259行目で、「検索文字列(TextBox1)」の値を読み込み、変数myWhatにします。

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

270~275行目は「完全一致」であるCheckBox2の状態を調べ、レ点の状態(レ点無し:Value=False、有り:Value=True)で定数を変数myLookAtに代入します。

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

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

6-10.シート移動

図3-22のプロ―チャートなどで使用している「シートを移動」を実行するのが図6-16です。
なお今回のシステムでは、ダイアログ起動時にワークシートからのみ起動できますし、また検索ボタンもワークシート上でしか実行できないようにしています。つまり検索するブックには「1つ以上のワークシートがある」というのが前提です。
  1. '========== ⇩(14) シート移動 ====================
  2. Public Sub NextSheet()
  3.  Dim IndexNo As Long
  4.  IndexNo = ActiveSheet.Index
  5.  Do
  6.   IndexNo = (IndexNo Mod Sheets.Count) + 1
  7.  Loop Until (Sheets(IndexNo).Type = xlWorksheet) And (Sheets(IndexNo).Visible = True)
  8.  Sheets(IndexNo).Activate
  9. End Sub
図6-16

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

296行目の式「IndexNo = (IndexNo Mod Sheets.Count) + 1」に値を入れて確認してみます。シートの総数(Sheets.Count)は「3」とします。
 IndexNo=1 : 1÷3 → 商=0 余り=1 : (IndexNo Mod Sheets.Count) + 1 = 2 → 次のシートはシート2
 IndexNo=2 : 2÷3 → 商=0 余り=2 : (IndexNo Mod Sheets.Count) + 1 = 3 → 次のシートはシート3
 IndexNo=3 : 3÷3 → 商=1 余り=0 : (IndexNo Mod Sheets.Count) + 1 = 1 → 次のシートはシート1
このようにトグルでシートを回すことが出来ますので、今回はこの方法を使用します。

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

Do~Loopがイヤな方は、例えば図6-17のように「Activeシートのシート番号を始点とするFor~Next文」でワークシートを見つけ、見つけたシートをActiveにするという方法もあります。
  1. '========== ⇩(15) シート移動2 ====================
  2. Sub NextSheet2()
  3.  Dim i As Long
  4.  Dim Sh As Object
  5.  For i = ActiveSheet.Index To ActiveSheet.Index + Sheets.Count
  6.   Set Sh = Sheets((i Mod Sheets.Count) + 1)
  7.   If (Sh.Type = xlWorksheet and Sh.Visible = True) Then Exit For
  8.  Next i
  9.  Sh.Activate
  10. End Sub
図6-17

また図6-18のように、「隣のシートに移動してみてから、ワークシートじゃなかったらもう一度移動してみる」という方法もあります。なおDo~Loopが無いとは言え、再帰呼び出しですので無限呼出しが無いことの確認は必要です。
  1. '========== ⇩(16) シート移動3 ====================
  2. Public Sub NextSheet3()
  3.  If ActiveSheet.Index = Sheets.Count Then
  4.   Sheets(1).Activate
  5.  Else
  6.   ActiveSheet.Next.Activate
  7.  End If
  8.  If (Not ActiveSheet.Type = xlWorksheet) Or (ActiveSheet.Visible = False) Then
  9.   Call NextSheet3
  10.  End If
  11. End Sub
図6-18

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

6-11.初出セル・初出シート変数の初期化

図6-19は、変数「初出セル」と「初出シート」を初期化するプロシージャです。図6-7の172行目、及びオプション・検索文字列を変更した時点(図6-20)で呼び出されるものです。
様々な場面で初期化が必要なのでプロシージャ化しましたが、内容としては変数の初期化のみです。
  1. '========== ⇩(17) 初出セル・初出シート変数の初期化 ====================
  2. Private Sub SearchReSet()
  3. StartSheet = ""
  4. StartCell = ""
  5. End Sub
図6-19

6-12.条件変更時の変数初期化

オプション部及び検索文字列を変更した時は、「それまでの検索とは条件が変わる」ため、変数「初出セル」と「初出シート」を初期化する必要があります。ですのでオプション項目のChangeイベントが発生した時には、図6-19を呼び出し変数の初期化を行っています。
  1. '========== ⇩(18) 条件変更時の変数初期化 ====================
  2. Private Sub TextBox1_Change()
  3. Call SearchReSet
  4. End Sub
  5. Private Sub CheckBox1_Change()
  6. Call SearchReSet
  7. End Sub
  8. Private Sub CheckBox2_Change()
  9. Call SearchReSet
  10. End Sub
  11. Private Sub CheckBox3_Change()
  12. Call SearchReSet
  13. End Sub
  14. Private Sub ComboBox1_Change()
  15. Call SearchReSet
  16. End Sub
  17. Private Sub ComboBox2_Change()
  18. Call SearchReSet
  19. End Sub
  20. Private Sub ComboBox3_Change()
  21. Call SearchReSet
  22. End Sub
図6-20

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

7.アドインとしてExcelにマクロを登録

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

8.最後に

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

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

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

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

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