今回の記事は、セルの内容が変わったら、Excelになにか処理をさせる、WorksheetのChangeイベントを使った処理の話です。
わぁ〜、夏のイベントとか楽しそう〜！！。。。いやいや、このイベントは処理をさせるためのキッカケの話ですよ（笑）
さて、このChangeイベント、みなさんは使っていますか？？？
いやいや、、、なるべく使わないようにしているよ。。。なんて方も結構いらっしゃるのではないでしょうか？?
私は、もちろん、、、、、使わなくていいなら使いません（笑）
なぜかと言いますと、Changeイベントはコードをスタートさせるキッカケが多くなるので、コードを制御する場面が多くなるんですね。
もし、制御してないキッカケが残っていると、エラーが出て止まってしまうかもしれませんし、最悪の場合、無限ループに陥って強制停止、、、、なんてこともありえます。。。。実は、こんなことがよく発生するイベントなんです（笑）
だから、デバックが不完全なままでリリースしてしまうと、、、自動化で便利になるどころか、もう、ただの迷惑行為です（笑）気をつけたいですね！
これに対して、BeforeDoubleClickイベントは扱いやすいです！過去記事（BeforeDoubleClickイベントの記事）で書いてますので、良かったら読んでやってください（笑）
、、、、というわけで、今回はChangeイベントのクセモノをなんとかするという話です！
今回つくるサンプルは、番号を入力すると、その番号にあったデータをマスタから探し、入力した番号の右隣のセルに表示させ、次の項目の入力待機のため、さらに右隣のセルをVBAで選択させるものを作ります。セルの自動選択以外は、VLOOKUP関数と同じ動きです。
このマスタデータは別のExcelファイルとします。そして、番号と連動して表示させたあとは右隣に2項目入力があるものとします。
サンプルの割に、実用するかのようなサンプルですが、、、、実は、つい最近バイト先でつくったのです（笑）なので、その簡易版をサンプルにしようと思ったわけです。
では、まずマスタデータを準備します。
今回は東京の地区名と地域コード一覧をネット上から拾ってマスタとしましょう。
こんな感じにしました。
次は入力用のシートです。
先程と拡張子が変わり、マクロ対応ブックです。
それでは、この入力ファイルのVBAを開いて、コードを入力していきます。
最初は、マスタファイルを読み込み専用で開いてデータを配列に記憶させてからファイルを閉じるコードにしましょう。ちなみに、マスタファイルは入力用ファイルと同じフォルダにあるものとします。
いつもはコードをコピーできるようにHTMLで入力しているのですが、、、、今回は、コード紹介ではないので、上のように図で説明しておくだけにします。ご質問あればコメントで、、、
さて、、、これが実行されれば配列にマスタデータが記憶され、配列をPublicで宣言していますので、処理のたびに実行しなくても、エラーで処理が止まらない限り、記憶したデータは使い回せます。配列を添字フリー（数を決めない）で宣言って書いてますが、正確には「動的」っすね。まあ、どっちでもいいか〜（笑）
じゃあ、このコードを実行するタイミングですが、、、、、まあ、このファイルが開かれたときでいいんじゃないでしょうかね。
はい！これでファイルが開かれるたびに読み込みがされます。
このようにWorkbookのOpenイベントを使えば、ファイルが開かれるたびに実行されます。初期設定とかシートの整備とかさせるのに便利です。
次は、いよいよChangeイベント使っていきます！
はい、まずは上のようにしました。
まだこれ不完全ですので、このまま真似しなしでくださいね（笑）
何が不完全なのかExcelの挙動を確認しながら説明していきます！
まず、狙ったとおりに動くかやってみます。
うん、想定通りです。
では項目１の動きをみてみます。
よいですね。
では、項目２の動きは、、、
よし、問題ありません。
このままちょっと入力を続けてみると、、、
問題ありません。
当然ですね。この動きになるようにコードを組んでますので問題ありません。
「これで完成！」って言いたい気になるんですが、ちゃんと検証してみましょう。
まずは、入力したものを全部選択して消してみます。
ほら、このままじゃ使えませんね（笑）
型がちがうって言ってますね。こういうときは、デバックを選択して、ローカルウインドウで確認すると何が起きているかわかります。
今回は、ローカルウインドウでの説明はしませんので、もし機会があったら確認してみてください。
さて、、、一体何がおきているのか？ですが、簡単に言いますと、Changeイベントの対象セルが「複数」あるので、値も「複数」あるよ！ということなんです。一度に「複数」のセルを選択して削除、つまり「値を変更した」ので当然ですね。
このサンプルコードでは、buf_valっていう変数にTarget.valueで値をいれてますが、このとき、しれっと変数buf_valが２次元配列に化けてます（笑）
一気にたくさんのセルが変わったのですから、値が複数になるのは当然ですよね。
でも、、、、サンプルコードをよくみてください。行、列の位置によってIFやSelect Caseで条件分岐していますよね？なんで値は複数あるのに、行、列は１つのセルを想定した条件のまま、一致したのでしょうか？？？
このように、Changeイベントが発生したときの対象セルが、どういった戻り値になるのか知らないと使いこなせません。だから面倒くさいし、使いたくない（笑）このあたりの扱いづらさ、、、クセモノですね（笑）
では、セルが単一のときと、複数のときの各戻り値がどうなっているのかまとめてみます。
このようになっています。
実は、複数選択時の行、列は、なんと！左上隅だけのことなんです。なので、今回の不完全サンプルコードでは、どちらも同じ条件で一致してしまったわけです。
そして、値の型を見てください。
先程のエラーの「型がちがう」理由がわかるかと思います。
じゃあ、エラーを回避するために、これを加味してコードを書いていけばいいわけですが、、、。。実はこの不完全コード、、、値を表示する、移動させるだけしか対応していません（笑）
実際に動かすのであれば、もっと細かく、色々な場面を想定して書かねばなりません。このサンプルならば、、、
- 地区名が表示されている状態で、地区一覧にヒットしない番号を上書き入力されたときはどうする？
- 値が消された場合、選択されたの状態が単一、列指定、行列指定のどれなのか？
- コピペも上と同じく選択された状態によってそれぞれ考えなければいけない。
面倒くさいですね〜〜〜。さらに普通に運用するなら列もしくは行が削除されることも想定しなければなりません。現在行列を行番号、列番号で直接指定してますが、削除も考えると、シートに目印をつけて、なにか別の仕組みを入れて、相対的に位置を指定できる方法をとる必要があります。
このサンプルではそこまでやりません。ここでは、どちらがされても何も起きません。
では、、、、これらを加味してコードを書き加えるとどうなるかといいますと、、、、
こんなになります（笑）
もっと同じような挙動を一緒にして、圧縮すればもう少しマシになるかもしれませんが、、、結局、考えて、試行錯誤して圧縮する、、、面倒くさい事には変わらない（笑）
あと、今回はセルの内容が変わる無限連鎖が起きない処理なので使ってませんが、例えばセルに入力した値によって、同じセルの値を変える。みたいな、関数では考えられないような処理もVBAなら可能です。しかし、これを普通にコードで表現すると、、、、
入力して値が変わる→コードで値が変わる→コードで値が変わる・・・・と無限ループが始まります。コード上で値を変えてもChangeイベントは有効なので、変えたはなから、コードがまた最初から始まります。これが繰り返されちゃうわけです。こんな時は、コード上で値を変える箇所の前にイベントを止め、終わったら再開させる処理をします。
Application.EnableEvents = False
〜処理〜
Application.EnableEvents = Trueこんな感じです。
画面の更新を止めたり、再開したりするScreenUpdatingとよく似てますね。これはよく使いませんか？これのイベントバージョンだと思ってください。
この、EnableEventsを使う上で気をつけることなんですが、ちゃんとイベントを再開させることです。これしないと、Changeイベントだけじゃなく、全てのイベントが止まります。知らないとけっこうあせるかも（笑）
また、デバック時は、特に気をつけてください。イベントを止めたあと、エラーによって処理が止まった場合、イベント停止がキャンセルされ自動再開するわけではありません。イベントは止まったままです。
なので、復帰させるには、イベント再開のコードを実行しなければなりません。
こういったことがあるので、私からのオススメなのですが、、、もし、みなさんが、VBAで何かを開発する際、イベントを止める処理を入れるぞっとなった場合、デバック前に復帰用のイベント再開のプロシージャを作っておくと便利ですよ。１行だけのコードで十分です。
と、、、、まあ、このように、ほぼVLOOKUP関数の挙動と変わらないものを、あえてVBAでつくる意味があるのか？と思いませんか？？
確かに、関数を使わない利点も多いです。けれど、その利点に見合う手間なのか？！を考えないと、時間と手間が勿体ないことになります。
今回、私がバイト先で、できれば使いたくないと思っているにも関わらず、この方法を選択して作成したのは、、、キーボード、マウス以外の操作デバイスをメインで使わなくてはならない状況だったからです。
ホント面倒くさいんで、やむを得ない状況に追い詰められるまではおすすめしません（笑）
さて、いかがでしたでしょうか？
面倒くささは伝わったでしょうか（笑）この記事が、みなさんのお役に立てれば光栄です。
ここまで読んでいただき、ありがとうございました。
追加記事（2023年7月29日）
バイト先で書き殴ったコードを整理してたら、スッキリできたのでご紹介（笑）
このサンプルのメイン部分を書き換えると、、、
Dim rp_ed As Integer,ck_val As String,d_cnt As Boolean
Application.EnableEvents = False
If r>1 Then
If InStr(buf_add,":")=0 Then
rp_ed = 1
ck_val = buf_val
d_cnt = True
ElseIf InStr(buf_add,"A")=2 Then
rp_ed = UBound(buf_val,1)
ck_val = buf_val(1,1)
d_cnt = False
End If
For i=1 To rp_ed
If i>=2 Then ck_val = buf_val(i,1)
If ck_val = "" Then
ActiveSheet.Range(ActiveSheet.Cells(r+i-1,2),ActiveSheet.cells(r+i-1,4)).ClearContents
Else
fl = False
For j=0 To UBound(e_data,2)
If ck_val = e_data(0,j) Then
ActiveSheet.Cells(r+i-1,2)=e_data(1,j)
If d_cnt = True Then .Offset(0,2).Select
fl = True
Exit For
End If
Next j
If fl=False Then
ActiveSheet.Cells(r+i-1,2).ClearContents
If d_cnt = True Then .Offset(0,1).Select
End If
End If
Next i
End If
Application.EnableEvents = True
HTMLにベタ打ちで、コードが長くなるとしんどいので、SelectCaseの分岐は省略しました（笑）
これならスッキリな気がします。このサンプルだとあまり実感できないかもしれないですが、実際にバイト先で作ったコードは、読み込んだデータとヒットした場合、3列ぐらい表示させ、セル移動もいくつかパターンがあったのでよりスッキリが実感できるコードになったんですけどね〜。
あと、すごく大事な点がもう１つ。これがクセモノのラスボスですね。
今回のようにコピペをされたものをループさせて一気にたくさんの結果を出したいようなとき、やはりイベントは一連の作業が終わるまで止めておいたほうがよいです！
なので、今回は大外にイベント止めのコードを差し込んでます。
実際にバイト先で作った方には、念のため追記したコードのようにイベントを止めて作ってました。実際に動かしたとき変な悪さされるとやっかいですからね。
これぐらいのサンプルぐらいなら、無しでも不具合なく動くので気にしてませんでしたが、、、、試しにコピペの量を増やしてみると、、、、、、やはりコードの中で値を変える処理のあとにあっちこっちに飛んでます（笑）順番がめちゃめちゃ。なので、無限ループにならなくてもコード上で値を変えるときは、イベント止めましょう！！
いろいろ判明して、嵐のようにベタ打ちしたのでタイプミスあったらごめんなさい（笑）もし、自分で気がついたら修正しておきます。。。
