Excel 이벤트 발생 순서

Excel에서 이벤트를 이용할 때 WorkSheet 이벤트와 WorkBook 이벤트 그리고 Application Class 이벤트 등 사용하는 이벤트의 장단점이 있으므로 그 발생 순서를 이해하고 사용하면 자동화 하는데 많은 도움이 됩니다.

3가지 사이의 발생순서와 Sheet를 이동하거나 WorkBook을 이동할 때 이베트가 발생하는 순서 등이 어떻게 되는지 볼 수 있도록 이벤트를 기록하도록 작성한 샘플입니다.

 

Excel_Event_Sequence.xlsm

0.04MB

Worksheet 이벤트에는 해당 시트에서만 작동하는 기능을 넣는 것이 좋습니다.

                  이벤트에 ByVal Sh As Object 같은 Worksheet가 전달되는 것이 없습니다.

Workbook 이벤트에는 파일의 시작, 종료, 저장 및 여러 시트에 공통으로 적용할 수 있는 기능을 넣는 것이 좋습니다.

                  이벤트에 ByVal Sh As Object 같은 Worksheet가 전달되는 것이 이으나

                  ByVal Wb As Workbook 처럼 Workbook이 전달되는 것은 없습니다.

Application 이벤트 Class 에는 여러 Workbook에 대해서 공통으로 적용할 기능을 구현하는 것이 좋습니다.

                   Add-In에 사용할 경우 모든 Workbook, Worksheet에 공용으로 사용할 수 있습니다.

                  이벤트에 ByVal Wb As Workbook와 ByVal Sh As Object 가 전달되므로 구분하여 사용할 수 있습니다.

 

Application 이벤트 Class는 아래와 같이 사용을 준비합니다.

 

Class에 WithEvents 키워드를 사용하여 아래와 같이 정의해 주고, 

     Private WithEvents App As Application

 

Class의 Initialize에서는 아래와 같이 App개체에 Application을 연결합니다.

    Private Sub Class_Initialize()
        Set App = Application
    End Sub

 

일반 모듈에서 새로 정의한 Class를 사용할 변수를 선언해 주고,

    Public XL As clsEvents

 

마지막으로 Workbook_Open이벤트에서 해당 Class 변수를 시작해 줍니다.

    Private Sub Workbook_Open()
        Set XL = New clsEvents
    End Sub

 

이제 각각의 이벤트가 어떤 순서로 언제 발생하는지 보기 위해 진행합니다.

 

이벤트를 가진 Application Class를 만들기 위해서 Class 모듈을 추가하고 이름을 "clsEvents"로 하고 아래와 같이 코딩합니다.

Private WithEvents App As Application
Private Sub App_NewWorkbook(ByVal Wb As Workbook)
    WriteLog Wb, ActiveSheet, "ClassEvents", "Class_Initialize"
End Sub
Private Sub App_SheetActivate(ByVal Sh As Object)
    WriteLog ActiveWorkbook, Sh, "ClassEvents", "App_SheetActivate"
End Sub
Private Sub App_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    WriteLog ActiveWorkbook, Sh, "ClassEvents", "App_SheetBeforeDoubleClick:" & Target.Address
End Sub
Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name = LogSh.Name Then Exit Sub
    WriteLog ActiveWorkbook, Sh, "ClassEvents", "App_SheetChange:" & Target.Address & "(" & Target.Cells(1, 1).Value2 & ")"
End Sub
Private Sub App_SheetDeactivate(ByVal Sh As Object)
    WriteLog ActiveWorkbook, Sh, "ClassEvents", "App_SheetDeactivate"
End Sub
Private Sub App_WindowActivate(ByVal Wb As Workbook, ByVal Wn As Window)
    WriteLog Wb, ActiveSheet, "ClassEvents", "App_WindowActivate:" & Wn.Caption
End Sub
Private Sub App_WindowDeactivate(ByVal Wb As Workbook, ByVal Wn As Window)
    WriteLog Wb, ActiveSheet, "ClassEvents", "App_WindowDeactivate:" & Wn.Caption
End Sub
Private Sub App_WorkbookActivate(ByVal Wb As Workbook)
    WriteLog Wb, ActiveSheet, "ClassEvents", "App_WorkbookActivate"
End Sub
Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
    WriteLog Wb, ActiveSheet, "ClassEvents", "App_WorkbookBeforeClose"
End Sub
Private Sub App_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)
    WriteLog Wb, ActiveSheet, "ClassEvents", "App_WorkbookBeforeSave"
End Sub
Private Sub App_WorkbookDeactivate(ByVal Wb As Workbook)
    WriteLog Wb, ActiveSheet, "ClassEvents", "App_WorkbookDeactivate"
End Sub
Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
    WriteLog Wb, ActiveSheet, "ClassEvents", "App_WorkbookOpen"
End Sub
Private Sub Class_Initialize()
    Set App = Application
    WriteLog ActiveWorkbook, ActiveSheet, "ClassEvents", "Class_Initialize"
End Sub
Private Sub Class_Terminate()
    WriteLog ActiveWorkbook, ActiveSheet, "ClassEvents", "Class_Terminate"
    Set App = Nothing
End Sub

 

각각의 Worksheet에는 아래와 같이 이벤트를 모두 동일하게 기록하도록 합니다.

Private Sub Worksheet_Activate()
    WriteLog ActiveWorkbook, ActiveSheet, "Worksheet", "Worksheet_Activate"
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    WriteLog ActiveWorkbook, ActiveSheet, "Worksheet", "Worksheet_BeforeDoubleClick:" & Target.Address
End Sub
Private Sub Worksheet_Calculate()
    WriteLog ActiveWorkbook, ActiveSheet, "Worksheet", "Worksheet_Calculate"
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
    If ActiveSheet.Name = LogSh.Name Then Exit Sub
    WriteLog ActiveWorkbook, ActiveSheet, "Worksheet", "Worksheet_Change:" & Target.Address & "(" & Target.Cells(1, 1).Value2 & ")"
End Sub
Private Sub Worksheet_Deactivate()
    WriteLog ActiveWorkbook, ActiveSheet, "Worksheet", "Worksheet_Deactivate"
End Sub

 

Workbook의 "현재_통합_문서" Class에는 아래와 같이 좀 더 많은 이벤트를 기록하도록 합니다.

이중 Workbook_Open 이벤트에는 Event를 가진 Application Class를 시작하도록 합니다.

Private Sub Workbook_Activate()
    WriteLog ActiveWorkbook, ActiveSheet, "현재_통합_문서", "Workbook_Activate"
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    WriteLog ActiveWorkbook, ActiveSheet, "현재_통합_문서", "Workbook_BeforeClose"
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    WriteLog ActiveWorkbook, ActiveSheet, "현재_통합_문서", "Workbook_BeforeSave"
End Sub
Private Sub Workbook_Deactivate()
    WriteLog ActiveWorkbook, ActiveSheet, "현재_통합_문서", "Workbook_Deactivate"
End Sub
Private Sub Workbook_NewSheet(ByVal Sh As Object)
    WriteLog ActiveWorkbook, Sh, "현재_통합_문서", "Workbook_NewSheet"
End Sub
Private Sub Workbook_Open()
    Set LogSh = Worksheets("Sheet1")
    Application.EnableEvents = False
    'LogSh.Cells.Clear
    LogSh.Columns(1).NumberFormat = "yyyy-mm-dd hh:mm:ss"
    LogSh.Range("A1:E1") = Array("시간", "WorkBook", "Sheet", "Object", "Event")
    WriteLog ActiveWorkbook, ActiveSheet, "현재_통합_문서", "Workbook_Open"
    Application.EnableEvents = True
    Set XL = New clsEvents
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    WriteLog ActiveWorkbook, Sh, "현재_통합_문서", "Workbook_SheetActivate"
End Sub
Private Sub Workbook_SheetBeforeDelete(ByVal Sh As Object)
    WriteLog ActiveWorkbook, Sh, "현재_통합_문서", "Workbook_SheetBeforeDelete"
End Sub
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    WriteLog ActiveWorkbook, Sh, "현재_통합_문서", "Workbook_SheetBeforeDoubleClick:" & Target.Address
End Sub
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    WriteLog ActiveWorkbook, Sh, "현재_통합_문서", "Workbook_SheetCalculate"
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name = LogSh.Name Then Exit Sub
    WriteLog ActiveWorkbook, Sh, "현재_통합_문서", "Workbook_SheetChange:" & Target.Address & "(" & Target.Cells(1, 1).Value2 & ")"
End Sub
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    WriteLog ActiveWorkbook, Sh, "현재_통합_문서", "Workbook_SheetDeactivate"
End Sub
Private Sub Workbook_WindowActivate(ByVal Wn As Window)
    WriteLog ActiveWorkbook, ActiveSheet, "현재_통합_문서", "Workbook_WindowActivate" & ":" & Wn.Caption
End Sub
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
    WriteLog ActiveWorkbook, ActiveSheet, "현재_통합_문서", "Workbook_WindowDeactivate" & ":" & Wn.Caption
End Sub

 

일반 모듈에는 이벤트를 기록하는 프로시저를 둡니다.

Public LogSh As Worksheet
Public XL As clsEvents
Public iNew As Long

Public Sub WriteLog(Wb As Workbook, Sh As Worksheet, obj As String, eventString As String)
    Application.EnableEvents = False
    With LogSh
        iNew = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        .Range(.Cells(iNew, 1), .Cells(iNew, 5)) = Array(Now(), Wb.Name, Sh.Name, obj, eventString)
    End With
    Application.EnableEvents = True
End Sub

 

파일탐색기에서 이 파일을 더블클릭해서 Excel을 실행하면 아래와 같이 이벤트가 기록됩니다.

자세히 보면 처음 열릴 때에는 Worksheet_Activate 이벤트가 발생하지 않는 것을 알 수 있습니다.

Workbook_Open  →  Workbook_Activate  →  WindowActivate 

이벤트를 이용한 자동화 프로그램을 작성할 때에는 이런 부분이 아주 중요합니다.