エクセルのイベントプロシージャで起動するマクロ
エクセルVBAには特定の操作がおこなわれたことをきっかけにして、マクロを動かす方法があります。これらはイベントプロシージャと呼ばれますが、イベントプロシージャには多数の種類が存在しています。
こちらの記事では、イベントプロシージャのなかから、特定のセルの範囲が更新されたことをきっかけにして起動するマクロのサンプルを紹介します。
なぜ、膨大な事務作業でも定時で退社できるのか。
実務をプロから学べる「ユースフル」の動画は永年見放題。Q&A機能で分からないを放置しないから安心。
詳しくは以下のリンクをクリック
ワークブックのイベントの指定方法
この記事ではイベントプロシージャを利用して何かしらのきっかけで起動するマクロを紹介します。イベントプロシージャには複数の種類があり、用途も指定方法もさまざまです。
例えば、指定したエクセルを起動したとき、指定したエクセルを閉じる前といったタイミングで起動するマクロを作りたいのであれば、ワークブックのイベントプロシージャを利用することになります。
ワークブックのイベントプロシージャについてまとめた記事がありますので、詳しくは以下のリンクより記事をご覧になってください。
マクロを利用する入力用ワークシート
まずは、こちらで紹介するマクロで処理に利用する架空のデータが書かれたシートです。どこかの会社にあるような商品リストをイメージしてもらえるとわかりやすいかと思います。
サンプルデータシートの画像
サンプルデータシートの説明
B列に商品名、C列に単価、D列に在庫数が入力されており、F列に前回発注日を日時で入力されております。これらのセルは商品リストの使用者が編集し、都度データが更新される想定のものです。
商品リストの使用者が変更した値から、E列には合計金額を計算して表示、G2セルはデータを更新した日時が自動で入力されるようになっています。
入力用のセルと入力された値によって計算結果が表示されるシートだよ。
使用するイベントプロシージャ
セル範囲の変更きっかけに起動するマクロをつくるために、以下のイベントプロシージャをつかっていきます。
指定するイベントプロシージャ
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
「Workbook.SheetChange」イベントは、ワークブック(エクセルファイル)に含まれるシートのセルが変更されたことをきっかけにするイベントプロシージャです。つまり複数のシートが存在すればそれらすべてのシートのセルの変更でイベントが発生します。
引数のShは変更したシート、Targetは変更したセル範囲が代入されるよ。
特定のセル範囲を書きかえると更新日時を現在の日時にするマクロ
サンプルデータシートである商品リストが更新されたことをきっかけにして、G2セルに記入された更新日時の値を現在の日時に自動で更新するマクロのサンプルコードは以下のとおりです。
VBAサンプルコード(Workbook_SheetChange)
Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'特定のセル範囲を代入する変数
Dim Target_Range As Range
'B4:G13のセル範囲を変数に代入する
Set Target_Range = Worksheets("サンプルデータ").Range("B4:G13")
'対象シートが更新されたかの判定式
If Sh.Name = "サンプルデータ" Then
'代入したセル範囲が更新されたかの判定式
If Intersect(Target, Target_Range) Is Nothing Then
'特定のセルの範囲以外の処理があればここに書く。
Else
With ThisWorkbook.Worksheets("サンプルデータ")
'更新日時が記入されたG2セルを現在の日時に更新する
.Range("G2") = Format(Now(), "yyyy/mm/dd hh:mm")
End With
End If
End If
End Sub
サンプルコードの解説
03行目
Workbook_SheetChangeイベントを指定しています。Workbook_SheetChangeイベントはシートやセルが変更されると起動するといったものです。Workbook_SheetChangeイベントには引数が2つあり、1つはシートが代入されるShと、もう1つはセル範囲が代入されるTargetです。
06行目
シートとセル範囲の特定のため、変数Target_Rangeを宣言しています。
09行目
“サンプルデータ”という名前のシートのB4セルからG13セルの範囲を変数Target_Rangeに代入します。
12行目と15行目
ここでは、IF文をつかって条件判定をしています。イベントプロシージャのWorkbook_SheetChangeは、セルの更新をきっかけにマクロが起動します。そして起動と同時に更新されたシートとセルを引数として取得しています。例えば、Sheet1のA1セルを更新したきっかけで起動した場合、ShにはSheet1が、TargetにはA1セルが代入されることになります。
このはたらきをIF文の条件に利用しており、シート名とセルの範囲の2つの条件でその先の処理をするかを判定しています。なお、これらの条件に1つでも当てはまらない場合はなにも処理をせずにマクロを終了します。
具体的には、Shに代入されたシート名が”サンプルデータ”であるかの判定と、Targetに代入された更新されたセルが、Target_Range(B4セルからG13セル)に含まれた範囲に入るのかをIntersectメソッドをつかって判定しています。
Intersectメソッドとは
Intersectメソッドは、引数に指定したセル範囲の重なる範囲を返します。例えば、A1からB3のセル範囲と、B1からC3までのセル範囲を引数に指定してセルのアドレス表示を実行した場合は「$B$1:$B$3」が返されます。
補足
Intersectメソッドを利用した判定でセル範囲を比較して重ならない場合はNothingが返ります。
20行目
サンプルデータシートのG2セルを現在の日時に更新します。表示形式を指定するためにFormat関数を使用しています。
直近でサンプルデータのシートを更新した日時が自動で入力されるね。
特定のセル範囲が更新されると合計金額を自動で計算するマクロ
サンプルデータシート(商品リスト)の商品名・単価・在庫数が更新されたことをきっかけにして、E列に記入された合計金額の値を自動で計算するマクロになります。
VBAサンプルコード(Workbook_SheetChange)
Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'特定のセル範囲を代入する変数
Dim Target_Range As Range
'B4:D13のセル範囲を変数に代入する
Set Target_Range = Worksheets("サンプルデータ").Range("B4:D13")
'対象シートが更新されたかの判定式
If Sh.Name = "サンプルデータ" Then
'代入したセル範囲が更新されたかの判定式
If Intersect(Target, Target_Range) Is Nothing Then
'特定のセルの範囲以外の処理があればここに書く。
Else
With ThisWorkbook.Worksheets("サンプルデータ")
Dim i As Long: i = Target.Row '編集された行を変数に代入する
.Cells(i, 5) = .Cells(i, 3) * .Cells(i, 4) '合計金額の入力
End With
End If
End If
End Sub
サンプルコードの解説
09行目
宣言した変数Target_RangeにサンプルデータシートのB4からD13セルの範囲を代入しています。B列は商品名、C列は単価(円)、D列は在庫数が入力されているので、この範囲に入っているセルが変更されたことをきっかけにするためです。
12行目と14行目
更新されたセル範囲の判定式については、更新日時を書きかえるマクロと同じくIntersectメソッドを使用しています。ただし、こちらでは更新されたセルがB4からD13セルの範囲かどうかを判定しています。これにより商品名・単価（円）・在庫数が更新されると処理が実行される分岐に入り、そのあとの処理が実行されることになります。
18行目
変数iを宣言し、イベントプロシージャの引数である変数TargetからRowプロパティをつかって、更新されたセルの行を取得してiに代入します。例えば、サンプルデータシートのスマートウォッチの単価(円)が更新された場合は、Targetには$C$6セルが代入されているため、そこから行数を取り出して”6″がiに代入されることになります。
19行目
18行目の処理で、変数iには行数が入ってますので、E列の合計金額を算出するために、iと同じ行の単価と在庫数の掛け算をして合計金額を算出しています。これにより、サンプルデータシート上で書きかえられた行に対してピンポイントで処理を実行することができます。
書きかえたセルの行数を利用するところがポイントだよ。
特定のセル範囲の書きかえを禁止するマクロ
サンプルデータシートのE列やG列は自動計算の結果を出力するセルであるため、このシートの使用者による更新や削除を禁止したい範囲となります。こちらのマクロでは、特定のセル範囲を禁止したいので、指定した範囲にあるセルが変更されたときに直前(ユーザーの変更がされる前)の状態に戻すことで変更を禁止することを実現しています。
マクロのサンプルコードは以下のとおりです。
VBAサンプルコード(Workbook_SheetChange)
Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'特定のセル範囲を代入する変数
Dim Target_Range As Range
'E4:E13とG4:G13セル範囲を変数に代入する
Set Target_Range = Worksheets("サンプルデータ").Range("E4:E13,G4:G13")
'対象シートが更新されたかの判定式
If Sh.Name = "サンプルデータ" Then
'代入したセル範囲が更新されたかの判定式
If Intersect(Target, Target_Range) Is Nothing Then
'特定のセルの範囲以外の処理があればここに書く。
Else
Application.EnableEvents = False 'イベントを無効にする
MsgBox "編集したセルは変更できません。", vbOKOnly + vbCritical, "不正操作を検知"
Application.Undo '直前の処理に戻す
Application.EnableEvents = True 'イベントを有効にする
End If
End If
End Sub
サンプルコードの解説
09行目
宣言した変数Target_RangeにサンプルデータシートのE4からE13セルとG4からG13セルの範囲を代入しています。こちらは数式や自動入力されるので、ユーザーにセルの値を変更されたくない部分になります。
12行目と14行目
更新されたセル範囲の判定式については、ほかのマクロと同じくIntersectメソッドを使用しています。ただし、こちらでは更新されたセルがE4からE13セル、G4からG13セルの範囲かどうかを判定しています。これにより合計金額または、発注フラグが変更されると処理が実行される分岐に入り、そのあとの処理が実行されることになります。
17行目
Application.EnableEventsプロパティをFalseにすることで、イベントプロシージャを実行しないようにします。これは、後のApplication.Undoメソッドの処理で直前(ユーザーに更新されるまえ)の状態に戻されると、それをきっかけにしてイベントプロシージャが起動してしまい無限ループが発生することを防ぐためです。
19行目
Application.Undoメソッドの処理で直前(ユーザーに更新されるまえ)の状態に戻します。例えば、マジックペンの合計金額は10,000円ですが、これを書きかえられた場合に10,000円に戻すといった動作になります。
ただし、もとに戻す処理そのものもセルの変更に当たるため、イベントプロシージャの実行のきっかけとなってしまいます。これを予防するために、17行目でイベントによるマクロ起動が発生しないようにしているわけです。
20行目
Application.EnableEventsプロパティをTrueに戻します。これを忘れてしまうと1度しか起動しないマクロになり、以降はイベントによるマクロの起動が発生しなくなるといった状態を回避するためです。
無効にしたイベントを有効に戻すのを忘れないようにね。
まとめ
今回はエクセルVBAのイベントプロシージャのWorkbook.SheetChangeを利用した簡単なマクロを3つ紹介しました。特定のセル範囲が変更されたことをきっかけにするイベントプロシージャは使いやすく、利用できる場面も多いため取り上げてみました。
代表的な例では、式となる値を入力するセルが更新された場合に特定の計算結果を自動出力する処理が考えられますが、これらは数式や関数でも実装が可能です。しかし、マクロで実装する方法と異なるメリットとして、数式や関数が記入されたセルが使用者によって削除されてしまうリスクが回避できる点や、計算式が入力されたセルが多くなるとファイルの容量が大きくなり処理が重たくなることを防ぐことができます。
紹介したマクロを参考にぜひチャレンジしてみてね。
コダマのもりブログはにほんブログ村に登録しています。
ブログの記事が役に立ったと感じて頂けたら、フォローお願いいたします。
コメント