ワークシートに対して※特定の操作が行われた時に実行されるのがイベントプロシージャです。今回はその中のChangeイベントについてエクセルの神髄のVBA100本ノック54本目を題材にしてご紹介します。
※特定の操作とは、セルが変更された時、セルをダブルクリックした時、セルを右クリックした時などです。
また、イベントプロシージャに必須のApplication.EnableEvents = Falseについても詳しく解説します。
イベントプロシージャを使用する方法
イベントプロシージャを使用する場合、標準モジュールに作成するのではなく、イベントを発生させたいオブジェクトモジュール内にプロシージャを作成します。
①仮にSheet1内で特定操作が行われた時に実行されるイベントプロシージャを作成する場合は、VBEのプロジェクトエクスプローラー内のSheet1を選択します。

左側のプルダウンからWorkSheetを選択、右側のプルダウンメニューから使いたいイベントの種類を選びます。今回はChangeを選択します。

すると、下記のようなChangeイベントプロシージャが自動的に表示されますので、プロシージャ内にコードを記述します。
Private Sub Worksheet_Change(ByVal Target As Range)
End Sub
主なワークシートイベント
イベント | 発生条件 |
Activate | ブック、ワークシート、グラフ シート、または埋め込みグラフがアクティブになったときに発生します。 |
BeforeDoubleClick | ワークシートをダブルクリックしたときに発生します。 |
BeforeRightClick | ワークシートを右クリックしたときに発生します。 |
Change | ワークシートが変更されたときに発生します。 |
SelectionChange | ワークシートで選択範囲を変更したときに発生します。 |
VBA100本ノック54本目
イベントを使い、商品コードを入れたら「マスタ」から取得した商品名と単価が直ちに表示されるようにしてください。
商品コードがマスタに存在しない場合は商品コードを赤字にしてください。
※金額はセルに計算式が入っています。
※商品コード列の既定の文字色は「自動」
ダウンロードデータはエクセルの神髄VBA100本ノック54本目より
「伝票」シート、「マスタ」シートの2シートあります。

下図は「伝票」シート

下図は「マスタ」シート

模範解答
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count > 1 Then Exit Sub
If Intersect(Target, Columns(2)) Is Nothing Then Exit Sub
If Target.Row = 1 Then Exit Sub
On Error Resume Next
Application.EnableEvents = False
Dim mst As Range
Set mst = ThisWorkbook.Worksheets("マスタ").Columns(1) '「マスタ」シートの1列目を変数mustに代入
Dim rng As Range, i As Long
For Each rng In Target
rng.Font.ColorIndex = xlAutomatic 'フォントの色を自動に設定
rng.Offset(, 1) = "" '変更したセルの1つ右隣セルを空白
rng.Offset(, 3) = "" '変更したセルの3つ右隣セルを空白
If Not IsEmpty(rng) Then '変数rngが空白でなければ
Err.Clear
i = WorksheetFunction.Match(rng, mst, 0)
If Err Then
rng.Font.Color = vbRed
Else
rng.Offset(, 1) = mst.Cells(i, 2)
rng.Offset(, 3) = mst.Cells(i, 3)
End If
End If
Next
Application.EnableEvents = True
End Sub
引数:Targetとは
コードを確認すると、Targetが頻出します。
If Target.Columns.Count > 1 Then Exit Sub
If Intersect(Target, Columns(2)) Is Nothing Then Exit Sub
If Target.Row = 1 Then Exit Sub
イベントプロシージャにおけるTargetの役割は、変更されたセルが引数Targetに渡されます。
よって、上記コードは、以下のように訳されます。シートの中でイベントが発生するセル範囲を限定しています。
If Target.Columns.Count > 1 Then Exit Sub '変更されたセルの列数が1以上ならば、マクロ終了
If Intersect(Target, Columns(2)) Is Nothing Then Exit Sub '変更したセルが2列目以外ならば、マクロ終了
If Target.Row = 1 Then Exit Sub '変更セルの行が1行ならば(1行目の変更であれば)マクロ終了

つまり、イベントが実行されるセル範囲を赤枠部分に限定するコードです。
Intersectメソッド
Intersectメソッドは、複数セル範囲の重なっている部分のセル範囲を取得します。
重なる部分がない場合はNothingを返します。
Application.Intersect(Arg1(セル範囲1),Arg2(セル範囲2),Arg3(セル範囲3),・・・・)
※Arg・・・Ragneオブジェクト
Intersectメソッド(例)
Sub test()
Debug.Print Intersect(Range("A1:E10"), Range("C1:H10")).Select
End Sub
セルA1からE10とセルC1からH10の重なる部分が選択されます。

Mach関数
Dim mst As Range
Set mst = ThisWorkbook.Worksheets("マスタ").Columns(1) '「マスタ」シートの1列目を変数mstに代入
i = WorksheetFunction.Match(rng, mst, 0)
Much関数は、指定した範囲内で探したいデータがどの位置にあるかを列・行番号で返す関数です。
=Mach(検査値,検査範囲,[照合の種類])
[照合の種類]・・1=以下、0=完全一致、-1=以上
よって、引数:Targetに入っている「検査値」を以下の検査範囲(「マスタ」シートの1列目)から探して、その行番号を変数iに代入しています。

Application.EnableEvents = False
イベントプロシージャ(今回はChangeイベント)は、セルに変更があった時にイベントが実行されます。
今回の場合は、「伝票」シートの「商品コード」フィールドのセルが変更された時にイベントが発生するわけです。
イベントが発生して、エラーの場合はフォントが赤くなる、または、「商品名」「単価」が入力される処理等が行われるわけですが、それらのセルの変更をきっかけにまたイベントが発生します。そしてまた、イベント発生でセルが変更されて、イベントが発生して・・・と無限ループとなります。
それを防ぐために、セルの変更が行われるコードの前にApplication.EnableEvents = Falseを設定しておくことでイベントは一旦停止されます。
つまり今回の場合であれば、フォントが赤くなる(セルの変更)処理についてはイベントは発生しないということになります。
そして、Application.EnableEvents = Trueとすることで、イベントの停止を解除しています。
Application.EnableEvents = False
Dim mst As Range
Set mst = ThisWorkbook.Worksheets("マスタ").Columns(1)
Dim rng As Range, i As Long
For Each rng In Target
rng.Font.ColorIndex = xlAutomatic
rng.Offset(, 1) = ""
rng.Offset(, 3) = ""
If Not IsEmpty(rng) Then
Err.Clear
i = WorksheetFunction.Match(rng, mst, 0)
If Err Then
rng.Font.Color = vbRed
Else
rng.Offset(, 1) = mst.Cells(i, 2)
rng.Offset(, 3) = mst.Cells(i, 3)
End If
End If
Next
Application.EnableEvents = True

まとめ
今回はイベントプロシージャのChangeイベント、そして「Target」と「Application.EnableEvents = False」をVBA100本ノック54本目を題材にして紹介しました。
エクセル関連の記事を多く掲載していますので、次回もお楽しみに!!
コメント