【VBA】イベントについて

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 NewBookHdlSample1()
    Set ThisWorkbook.NewBook = Workbooks.Add
End Sub

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

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

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

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