皆さんは、VBAでイベント というものを、使っていますか?
イベントは、エクセルをアプリケーションライクに使用する上で、とても大切です。
セルをダブルクリックした、シートがアクティブになったなどの操作に対してイベントハンドラが、動作をキャッチし特定の処理を行ってくれます。
VBAのイベントについては色々なサイトで書かれているので、当ブログでは少し特殊なイベントについて書きたいと思います。
・WithEventsについて
・WithEventsの使い方
・イベントの自作方法
といった、あまり知られていないけど、実は便利な技術を、解説します。
最後まで、よろしくお願いいたします!
WithEventsキーワードとは
WithEventsキーワードとは、別のブックのシートのイベントをハンドルしたり、オブジェクトのイベントをキャッチできるようになる、特殊なキーワードです。
WithEventsキーワードを使用するメリットは次の通りです。
・WithEventsを付けて宣言するだけで、そのオブジェクトのイベントを自由自在にハンドルできます。
・User Formのイベントをまとめる際など、非常に便利。
勿論、イベントプロシージャは必要です。
しかし勘違いされている方が非常に多いのですが、イベントプロシージャは、ハンドルされるブックに含まれるのではなく、ハンドルするブックに含まれているのです。
文章だけで説明されても、何を言っているのか分かりませんよね?
次に実際にコードサンプルを記載します。
BeforeCloseイベントをハンドルする
それでは、まずThisWorkbookモジュールに以下のコードを書いてください。
Option Explicit
Private WithEvents myNewBook As Workbook
Public Property Set NewBook(ByVal myBook As Workbook)
Set myNewBook = myBook
End Property
Private Sub myNewBook_BeforeClose(Cancel As Boolean)
MsgBox myNewBook.Name & "を閉じます。", vbInformation
Set myNewBook = Nothing
End Sub
続いて、標準モジュールを1つ挿入して、下のコードを写してください。
Option Explicit
Sub NewBookHdlSample1()
Set ThisWorkbook.NewBook = Workbooks.Add
End Sub
NewBookHdlSample1を実行してみて下さい。
実行すると、新しいブックが開かれます。
そのブックを閉じようとすると、メッセージボックスが表示されます。
但し、上のコードでは、NewBookHdlSample1を実行するたびに、ブックモジュールの変数「myNewBook」が更新されるので、最後に追加したBeforeCloseイベントしかハンドル出来ません。
追加したブック全てのイベントをハンドルして、同様の処理を行う場合、クラスモジュールを使用します。
下記にサンプルを記載します。
まずクラスモジュールを「clsHdlBook」で作成して下記のコードを写してください。
クラスの使い方が分からない方は、こちらの記事を読んでください。
https://www.yuu-progra.com/2020/03/06/excel-vba-class1/
Option Explicit
Private WithEvents myNewBook As Workbook
Public Property Set NewBook(ByVal myBook As Workbook)
Set myNewBook = myBook
End Property
Private Sub myNewBook_BeforeClose(Cancel As Boolean)
MsgBox myNewBook.Name & "を閉じます。", vbInformation
Set myNewBook = Nothing
End Sub
続いて、標準モジュールに下記を写してください。
Option Explicit
Sub NewBookHdlSample2()
'インスタンスへの参照をまとめるコレクション
'Staticステートメントを使って有効期間を「ブックを開いている間」にする
Static myClsCol As New Collection
'コレクションに追加する新規インスタンスへの参照を一時的に格納する変数
Dim myHdlBook As clsHdlBook
'インスタンスを生成して、その参照を変数に代入
Set myHdlBook = New clsHdlBook
'ブックを追加して、その参照をNewBookプロパティに設定
Set myHdlBook.NewBook = Workbooks.Add
'保持しておくコレクションに追加
myClsCol.Add myHdlBook
'不要メモリの解放
Set myHdlBook = Nothing
End Sub
このように、クラスを作成し、そのインスタンスを追加したブックの数だけ生成すれば良いのです。
少し難しいかもしれませんが覚えると、これまでVBAではできないと、諦めいた処理もVBAで出来るようになります。
よく、VBAでは本格的なプログラムが出来ないとか、プログラマは使ったらダメというような話を聞きますが、少なくともExcelの操作に関して、VBAより簡単に、素早く開発出来る言語はありません。
VBAでのイベントの自作
ExcelやAccessのイベントは、基本的に無限ループで監視を続けることにより自作可能です。
ループ内の処理で何かの状態をチェックし、変化が生じたタイミングをイベントの発生時とみなします。
しかし、この時処理によって制御が占有されると、操作不能になります。
それでは困るので、「DoEvents関数」を併用して使用します。
新規ブックを作成し、下記のコードを写してください。
Option Explicit
Sub ShtsIncreaseHandler()
Dim myWkShtsCnt As Long
Dim myGpShtsCnt As Long
With ThisWorkbook
myWkShtsCnt = .Worksheets.Count
myGpShtsCnt = .Charts.Count
Do
If myWkShtsCnt < .Worksheets.Count Then
MsgBox "ワークシートが" & .Worksheets.Count - myWkShtsCnt _
& "枚増えました。", vbInformation
End If
If myGpShtsCnt < .Charts.Count Then
MsgBox "グラフシートが" & .Charts.Count - myGpShtsCnt _
& "枚増えました。", vbInformation
End If
myWkShtsCnt = .Worksheets.Count
myGpShtsCnt = .Charts.Count
DoEvents
Loop
End With
End Sub
この関数を実行すると、ブックを閉じるまでシートの挿入やシートのコピーといった操作によって、シートが増えるたびにメッセージボックスを表示します。
上記コードは厳密にはイベントを作成することからは外れますが、イベントの基本となる考え方ですので、理解してください。
Event・RaiseEvent ステートメント
上でやった無限ループの疑似イベントを、自作のイベントに置き換えたいと思います。
Eventは「Eventステートメント」を使用して宣言します。
Eventステートメントはオブジェクトモジュールで使用できますが、クラスモジュール以外のオブジェクトモジュールに書いても、イベントをはんどるできません。
そして、「RaiseEventステートメント」の実行によりイベントを通知することが出来ます。
早速、実装してみましょう!
まず、クラスモジュールを「clsBook」という名前で作り、下記のコードを写してください。
Option Explicit
Public Event SheetsIncrease(ByVal WkShtsCnt As Long _
, ByVal GpShtsCnt As Long)
Private myBook As Workbook
Public Property Get Book() As Workbook
Set Book = myBook
End Property
Public Property Set Book(ByVal myNewBook As Workbook)
Set myBook = myNewBook
End Property
Public Sub WatchStart()
Dim myWkShtsCnt As Long
Dim myGpShtsCnt As Long
If myBook Is Nothing Then Exit Sub
With myBook
myWkShtsCnt = .Worksheets.Count
myGpShtsCnt = .Charts.Count
Do
If myWkShtsCnt < .Worksheets.Count _
Or myGpShtsCnt < .Charts.Count Then
RaiseEvent SheetsIncrease( _
.Worksheets.Count - myWkShtsCnt _
, .Charts.Count - myGpShtsCnt)
End If
myWkShtsCnt = .Worksheets.Count
myGpShtsCnt = .Charts.Count
DoEvents
Loop
End With
End Sub
Private Sub Class_Terminate()
Set myBook = Nothing
End Sub
SheetsIncreaseイベントは、ブックプロパティに参照を設定したブックのシートが増加したタイミングで発生します。
今回はブックを開いたときに、そのブックへの参照をプロパティに設定してから監視を始めます。
次にThisWorkbookモジュールに下記コードを写してください。
Option Explicit
Private WithEvents myBookClass As clsBook
Private Sub myBookClass_SheetsIncrease( _
ByVal WkShtsCnt As Long, ByVal GpShtsCnt As Long)
If WkShtsCnt > 0 Then
MsgBox "ワークシートが" & WkShtsCnt & "枚増えました。", vbInformation
End If
If GpShtsCnt > 0 Then
MsgBox "グラフシートが" & GpShtsCnt & "枚増えました。", vbInformation
End If
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set myBookClass = Nothing
End Sub
Private Sub Workbook_Open()
Set myBookClass = New clsBook
Set myBookClass.Book = Me
myBookClass.WatchStart
End Sub
これで、無限ループでおこなっていたイベントを、クラスを使用して本格的なイベントとして作成できました。
解説しているサイトが、非常に少なく、書籍にもあまり記載のない内容ですが、すごく便利なので是非、使いこなしてください!
EXCELVBAイベントまとめ
長い記事を読んでくださり、ありがとうございます。
イベントの自作等、考え方が少し難しいので疲れたのではないでしょうか。
イベントの自作を出来るようになると、一気に出来ることが増えます。
クラスで自作のオブジェクトを使用して、イベントを定義する、という流れを覚えてください。
クラスの記事でも書いたのですが、とても大切な考え方なので、クラスを利用するメリットをもう一度、記載します。
・プログラムの保全性が高まる
・独立性の高いクラスは再利用せいが高い
・クラスは複製を幾つでも作れるので、生産性の向上に繋がる
・ユーザー定義型とは異なり、データの安全性を確保できる
あまり、VBAではクラスを使用していたり、イベントを自作したりしているサンプルを紹介しているサイトは少ないと思いますが、クラスやイベントを習得してすると、全く違ったプログラムが書けると思います。
最後までお読みいただきありがとうございました!!