|
診断(13)
全シートにわたって検索する
「でっかいど～」
「ほっかいど～！って，先生，いきなり古いギャグを叫んでどうしたんですか？」
「うむ，今年は新年早々，でっかい黒鯛が釣れた」
「まぁ素晴らしい。それぐらい上手に女の人も釣れるといいのに…」
「（ぐさっ）相変わらずきついのぅ」
「先生が早く落ち着いてくれないと，安心してお嫁に行けませんからね」
「そんな相手がいるの？」
「いませんよ」
「わしも君も淋しいのぅ」
「一緒にしないでください。私は選んでいるだけです」
「今夜は，二人で鍋でもするか？」
「はいっ！」
「やっぱり，暇なんじゃないか」
「次の方ど～ぞ～」
|
「ふむふむ，Excelの検索機能は，アクティブなワークシートしか検索できんからな」
「[全シートを検索]みたいなオプションはないんですか？」
「ないようじゃな」
「じゃあ，マクロでもできないんじゃ？」
「ゲール君，わしを誰だと思っておる。わしの辞書に『不可能』という文字はない」
「本当ですか？」
「まぁ，なんだ，その…この問題は解決できるということだ」
「では，よろしくお願いします」
「うむ，文字列を検索する場合は，Findメソッドを使うか，ループ処理で一つひとつのセルを照合するかのどちらかになる」
「違いはなんですか？」
「Findメソッドは，一回限りの検索であれば，一行のステートメントで実現できるからお手軽。しかも高速。しかし，繰り返しの検索をする場合は，少し複雑なプログラムになるし，用意されているオプションも限られているから，思い通りのマクロが作れないこともある」
「なるほど。一つずつセルをチェックする方法は？」
「そちらは，Instr関数を使うなり，一文字づつ比較するなり，照合の手段は自由だから，かなり複雑な条件で検索ができる。しかし，検索範囲が広いと処理に時間がかかる」
「う～ん，帯に水菓子たすきに長イモ」
「帯に短し，たすきに長し！」
「そうそう，それです」
「確かに，それぞれの方法にはメリットとデメリットがある。それを知ったうえで，最適の方法を選ぶことが肝心じゃ」
「なるほど。で，今回はどっちを使うんですか？」
「うむ，複数のシートを検索する場合は，ループを組んでセルの値を照合するほうが簡単じゃ。相談者の方がパニックになっておるのはFindメソッドを使ったからで，その処方箋としては，その方法で解決してあげたほうがよかろう。よって今回は，Findメソッドを使った全シート検索マクロを作る」
「では，早速」
「心得た。まず，こうしたアプリケーションでは，検索する文字列を入力する手段を用意せにゃならん。他にいくつかボタンも必要じゃから，ユーザーフォームを使うとしよう（図1）」
|
|
| 図1●検索文字列を入力するフォーム |
「先生，ボタンが三つありますね」
「うむ。[最初から検索]ボタンは検索に関するすべてをリセットして，最初のシートから検索を開始する。[次を検索]ボタンは，同じキーワードで次を検索するボタンじゃ。[終了]ボタンはフォームを閉じるためのボタンじゃな」
「[検索]ボタンが一つあればいいんじゃないですか？」
「汎用性を排除すればそれでよい。しかし，一度検索した後，続けて違うキーワードで検索するとしたら？」
「え～と，一度フォームを閉じて，また開いてキーワードを入力して…あー，面倒くさ！」
「わしに怒るな」
「検索と，検索の設定を初期化するメソッドが必要なんですね？」
「そうじゃ。しかし，それをそのままボタンにするのも芸がない。そこで，設定を初期化して検索するボタンと，同じ条件で次を検索をするボタンにしたんじゃ」
「偉い！」
「お褒めにあずかり恐悦至極」
「苦しゅうない。よきに計らえ」
「はは～！ では，続いてコードの説明を。まずは変数の宣言から（リスト1）」
Option Explicit
Dim SEARCH_WORKSHEET_No As Integer '<--------(1)
Dim myFIND_CELL As Range '<--------(2)
Dim myFIRST_FIND_CELL As Range '<--------(3)
Dim myPREVIOUS_CELL As Range '<--------(4)(1)は，検索の対象となるワークシートのインデックスナンバーを格納する変数です。また，検索にヒットしたセルを格納する変数(2)，任意のシートで最初にヒットしたセルを格納する変数(3)，前回の検索でヒットしたセルを格納する変数(4)をそれぞれ宣言します。
「先生，これらの変数は，プロシジャの外で宣言するんですか？」
「今回のマクロでは，複数のプロシジャで共通の変数を利用する。変数には宣言の方法によって2種類あることは知っておるな」
「はい，パブリック変数（グローバル変数）と，プライベート変数（ローカル変数）です」
「うむ，パブリック変数を使えば，どのモジュールのどのプロシジャでも利用できるが，ブックを閉じるまで解放されない。使い過ぎるとアプリケーション・フリーズの原因にもなる。よって，今回はプライベート変数を使う」
「先生，プライベート変数は，複数のプロシジャで利用できないんじゃありませんか？」
「それが，できるんだなぁ」
「えぇ？そんなことができるんですか？」
「プロシジャの中で宣言したプライベート変数は，そのプロシジャでしか利用できない。プロシジャの終了と同時に解放される。しかし，プロシジャの外，つまりモジュールの宣言部分で宣言したプライベート変数は，そのモジュール内のプロシジャに限り，共通に利用できるんじゃ。もちろん，変数の値も保持されておる」
「ん～，何だか複雑です」
「そうじゃな，VBAの仕様には複雑でわかり難い面もある。こればかりはしょうがない」
「じゃあ，どうしてそれをプライベート変数と呼ぶんですか？」
「変数を宣言したモジュール内という限られた範囲でしか利用できない。つまり，パブリックではないからプライベート変数というわけじゃ」
「うーん，やっぱりプロシジャ内のプライベート変数とごっちゃになりそう。でも，仕方ないから次をお願いします」
「[最初から検索]ボタンをクリックした場合に実行されるプロシジャを解説しよう（リスト2）。先頭のワークシートから，検索にヒットするまで順番にワークシートを変えながら検索を行い，最後まで見つからなかった場合は，メッセージボックスを表示して終了する」
Private Sub CommandButton1_Click()
SEARCH_WORKSHEET_No = 1 '<-------------------(1)
Worksheets(SEARCH_WORKSHEET_No).Activate '<-----(2)
Set myFIND_CELL = Worksheets(SEARCH_WORKSHEET_No). _
Cells.Find(Me.TextBox1.Text) '<------------(3)
If myFIND_CELL Is Nothing Then '<------------(4)
Do '<--------------------------------------(5)
SEARCH_WORKSHEET_No = SEARCH_WORKSHEET_No + 1 '<--(6)
If SEARCH_WORKSHEET_No > ThisWorkbook.Worksheets.Count Then '<-(7)
MsgBox "全シート検索完了", vbInformation '<--(8)
Exit Sub '<----------------------------(9)
End If
Set myFIND_CELL = Worksheets(SEARCH_WORKSHEET_No). _
Cells.Find(Me.TextBox1.Text) '<--------(10)
Loop While myFIND_CELL Is Nothing
End If
Set myFIRST_FIND_CELL = myFIND_CELL '<--------(11)
Worksheets(SEARCH_WORKSHEET_No).Activate '<---(12)
myFIND_CELL.Select '<-------------------------(13)
End Subまず，変数SEARCH_WORKSHEET_Noに1を代入して初期化します(1)。これにより，(3)のステートメントで検索の対象になるのは，インデックス番号が1のワークシートになります。その前に，インデックス番号が1のワークシートをアクティブにしておきます。
TextBox1に入力された文字列をシートの中で検索します(3)。最初の検索で見つからない場合は(4)，次の検索を実行するループに入ります(5)。
変数SEARCH_WORKSHEET_Noに1を加算します(6)。そしてSEARCH_WORKSHEET_Noの値が，ワークシートの総数を超えた場合は(7)，メッセージを表示し(8)，プロシジャを抜けます(9)。検索対象のワークシートがまだ残っている場合は，TextBox1に入力された文字列で再度検索を行います(10)。
検索にヒットしたセルを，変数myFIRST_FIND_CELLに代入します(11)。そのワークシートをアクティブにし(12)，ヒットしたセルを選択状態にします(13)。
「先生，Findメソッドのexpression（対象となるオブジェクト）は，WorksheetsじゃなくてCellsなんですね」
「そうじゃ。ワークシートを指定するとエラーになる（図2）」
|
|
| 図2●Findメソッドのexpressionにワークシートを指定してエラーになったときのメッセージ |
Worksheets(1).Find("123").Activate
「このようなコードはエラーになる。検索の対象となるのはセルの集合体なんじゃ」
「でもワークシートは，セルの親オブジェクトですよね？」
「うむ，確かにそうじゃが，親オブジェクトは，集合体でもなければ，コンテナでもない」
「コンテナ？」
「Rangeオブジェクトはセルの集合体，つまりコンテナであり，Cellsオブジェクトもまたしかり。Findメソッドのexpressionとなるのは，この二つのオブジェクトじゃ。ワークシート全体を検索の対象とする場合は，どうしても上のようなコードを書いてしまいがちだから注意せねばならん」
「先生，(4)のステートメントを解説してください」
「このステートメントは，Findメソッドが失敗した場合，つまり，検索文字列がワークシートに存在しない場合を判断する条件分岐じゃ。(3)のステートメントで，検索がヒットした場合は，変数myFIND_CELLにセルが代入される。逆にヒットしなかった場合はNothingとなる。Nothingを比較する場合は，＝演算子ではなくIs演算子を使うのが味噌じゃ」
「(7)では，変数SEARCH_WORKSHEET_Noの値をチェックするんですね？」
「変数SEARCH_WORKSHEET_Noの値は，ワークシート・オブジェクトのインデックス番号として利用する。この数値がワークシートの総数（Worksheets.Count）を超えたということは，直前の検索対象が最後のワークシートだったことを意味する」
「つまり，全シートの検索が終了したということだから，マクロを終了するんですね？」
「その通りじゃ」
「先生，(11)のステートメントは変数に代入したセルを，さらに変数に代入しています。これはなぜでしょう？」
「myFIRST_FIND_CELLは，次の[次を検索]ボタンをクリックしたときに実行されるプロシジャで利用するんじゃ。詳細は，次のプロシジャの解説で明らかにしよう」
「わかりました」
「(12)は，ワークシートをアクティブにするステートメントですよね？ ワークシートをアクティブにするステートメントは実行に時間がかかるから，マクロでは極力使わないのではないですか？」
「うむ，今回のマクロでは，どうしても必要なんじゃ。試しに，このステートメントをコメントにして実行してみたまえ」
「あら，エラーになっちゃいました（図3）。どうしてですか？」
|
|
| 図3●Findメソッドの実行で表示されたエラー・メッセージ |
「問題は，(13)のステートメントで発生する。セルに対してSelectメソッドを実行する場合，そのセルの親オブジェクトであるワークシートはアクティブでなければならない。全シートを検索する今回のマクロでは，検索にヒットしたセルの親オブジェクトのワークシートがアクティブとは限らん。だから，このステートメントが必要なんじゃ」
「面倒だなぁ」
「そうじゃのぅ。しかし，これも仕様。受け入れるしかない」
「わかりました。では，次のプロシジャを」
