Excel Object Model: Order of Events

This is the result of some investigations I did to find out in what order Excel fires some selected workbook and worksheet events.

Two workbooks (wbOne.xlsm and wbTwo.xlsm) are created, each with two worksheets (abc, def and uvw, xyz respectively).

For each workbook and worksheet, a code (VBA) module is inserted that uses the function evt (in func.vb) to report the events.

The two workbooks can be created with create.wsf. It requires the command line (VBS) Office app creator and must be invoked in cmd.exe or PowerShell like so:

cscript create.wsf

option explicit

private Sub workbook_open()
    evt "workbook_open"            , me.name
end sub

private sub workbook_activate()
    evt "workbook_activate"        , me.name
end sub

private sub workbook_windowActivate(byVal wn as window)
    evt "workbook_windowActivate"  ,  wn.activeSheet.name
end sub

private sub workbook_windowDeActivate(byVal wn as window)
    evt "workbook_windowDeactivate", wn.activeSheet.name
end sub

private sub workbook_WindowResize(byVal wn as window)
    evt "workbook_windowResize"    ,  wn.activeSheet.name & " - " & wn.width & "x" & wn.height & " @ " & wn.top & "," &wn.left
end sub

private sub workbook_deactivate()
    evt "workbook_deactivate"      , me.name
end sub

private sub workbook_beforeClose(cancel as boolean)
    evt "workbook_beforeClose"     , me.name
end sub

private sub workbook_sheetChange(ByVal Sh As object, byVal target as range)
    evt "workbook_sheetChange"     , sh.name & ", target.address = " & target.address
end sub

option explicit

private sub worksheet_activate()
    evt "worksheet_activate", me.name
end sub

private sub worksheet_deactivate()
    evt "worksheet_deactivate", me.name
end sub

private sub worksheet_change(byVal target as range)
    evt "worksheet_change", me.name & ", target = " & target.address
end sub

private sub worksheet_selectionChange(byVal target as range)
    evt "worksheet_selectionChange", me.name & ", target = " & target.address
end sub

option explicit

sub evt(name as string, txt as string) ' {
    debug.print format(name & ":", "!" & string(26, "@")) & txt
end sub ' }


sub positionWindows()

    with application.windows("wbOne.xlsm")
        .left   =   12
        .width  =  323
        .top    =   42
        .height =  344
    end with

    with application.windows("wbTwo.xlsm")
        .left   =  339
        .width  =  323
        .top    =   42
        .height =  344
    end with

    with application.vbe.mainWindow
        .visible = true
        .left    =    6
        .width   =  883
        .top     =  516
        .height  =  500
    end with

end sub

<job>
<script language="VBScript" src="..\..\..\VBS-MS-Office-App-Creator\create-MS-Office-app.vbs " />
<script language="VBScript">

   option explicit

   createWorkbook "wbOne.xlsm", "abc", "def"
   createWorkbook "wbTwo.xlsm", "uvw", "xyz"

   sub createWorkbook(name, shName1, shName2)

       dim wb
       set wb = createOfficeApp("excel", currentDir() & name)

       dim app
       set app = wb.application

       if wb is nothing then ' {
          wscript.echo("Could not create excel worksheet.")
          wscript.quit(-1)
       end if ' }


       wscript.echo "typename(app) = " & typename(app)
       wscript.echo "typename(wb ) = " & typename(wb )

       dim sh_abc
       set sh_abc = insertSheet(wb, shName1, "sh" & shName1)

       dim sh_def
       set sh_abc = insertSheet(wb, shName2, "sh" & shName2)

       insertModule app, currentDir() & "worksheetModule.vb", "sh" & shName1, 1
       insertModule app, currentDir() & "worksheetModule.vb", "sh" & shName2, 1
       insertModule app, currentDir() & "workbookModule.vb" , "thisWorkbook", 1
       insertModule app, currentDir() & "func.vb"           , "func"        , 1

       wb.save
       app.quit

   end sub

</script></job>