Post 3: Event coding in Excel spreadsheets. Application Events

Coding like that discussed in this post are often referred to loosely as "Application Events". In this thread I have been trying to show that they are a more general and fundamental usage of the event coding to which the more typically known worksheet event type codes belong. The more typically known event codes may sometimes be referred to loosely as "normal event codes" or "standard available event codes"

The general rules and Encapsturbation ideas etc. as discussed so far apply to "Application Events".
_(i) WithEvents can only go as procedure level variable. ( So outside of a routine, and placed towards the top of a code module).
_(ii) WithEvents can only go in an object or class object code module.
_(i) and _(ii) must be adhered to or a syntax error will arise. (i) makes some sense: In a routine it would not be much use as a variable for an object subscribing to, and therefore "watching for" , events as it would cease to exist at the end of a the routine.
_(iii) In addition, and further in line with Encapsturbation , it is general practice to insert and use a class module for all or most of the coding relating to a specific use of Application Events. This is a general practice, but is not required.

As a simple example, consider a simple equivalent to the very first example shown at the start of this thread. We had the following routine in a worksheets code module. ( We were using one of the already available "standard" worksheet event codes). Once again, just for convenience, we make use of Me , so the coding is intended to be used in a code module of a worksheet: Changes in that worksheet are effectively monitored with the event code.
Here again is that "standard event" routine:
Right mouse click Or double click in VBA explorer Project window to get code module.JPG : https://imgur.com/gsz6s2N
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then MsgBox prompt:="You just changed the value in the first cell in worksheet " & Me.Name & " in the Workbook " & Me.Parent.Name
End Sub
Here is some equivalent coding using "Application Events"
The first routine , Sub InstantiateWsMe() , needs to be run once to "make" the object, WsMe.
The second routine , Sub WsMe_SheetChange(ByVal Sh As Object, ByVal Target As Range) , will then run automatically when a cell in the worksheet with name given by Me.Name has its value changed.
Custom Listener_Sheet Change.JPG : https://imgur.com/kyE0qKh
Code:
Dim WithEvents  WsMe As Excel.Application ' This must go outside any routines, towards the top of a code module. The code module must be an object module or a class object module. These are rules ( for no particular reason that I can think of). If you do mot adhere to them you will get a syntax error. The first is possibly reasonable: In a routine it would not be much use as a variable for an object subscribing to events as it would cease to exist at the end of a  the routine.
Sub InstantiateWsMe() '  You must run this once to get the following code to work. If you add or delete any codes in this code module, then you must re run this routine
 Set WsMe = Excel.Application ' Important not to use New here, as we want to subscribe to the current instance of Excel in front of us.
End Sub
Sub WsMe_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name = Me.Name Then ' Then we are in this worksheet. Chenge Me.Name with the worksheet tab name like   "Sheet1" or "MySheet"  etc. for code to monitor a a particular worksheet
       If Target.Address = "$A$1" Then MsgBox prompt:="You just changed the value in the first cell in worksheet " & Me.Name & " in the Workbook " & Me.Parent.Name
    Else '
    End If
End Sub

Sub TestieCalls() ' This can be used to test the event code
 Call WsMe_SheetChange(Me, Me.Range("A1"))
End Sub
Coding of the above form is often referred to loosely as a "listener" or more often "custom listener" when using "Application Events"
( The above coding needs to go in a Worksheets code module, but only because I am using the Me for convenience to get at a worksheet. Generally such codes can go in any object code module, ( or Class object code modules ) )
_.____

Some, not all, event codes have things passed to them in the brackets (___), ( using coding we can't see ) when they are started. The one used here is passed the Sheet object in which the cell or cells are changed, as well as the Range object of the cells or cells which are changed.

( The last code , Sub TestieCalls() , allows us to test the event code )

_.___

For the case of the original code from this Thread, effectively a code line like Dim WithEvents WsMe As Excel.Application and a routine like Sub InstantiateWsMe() were effectively done for us, and we had part of the main code like this in each worksheet code object module:
Code:
Sub WsMe_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name = Me.Name Then

    Else '
    End If
End Sub
The above could be considered as what is behind the following in a worksheet code module, and so is a direct equivalent to it:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

It is not clear if any coding is shared in the application events and the normal events: Although a direct comparison was demonstrated in this thread with the routines, Sub WsMe_SheetChange(ByVal Sh As Object, ByVal Target As Range) and Private Sub Worksheet_SelectionChange(ByVal Target As Range) , I don't think you can do everything with the application events that you can with the normal events. I expect some coding is shared, and , as shown, in some cases we can come close to duplicating normal event codes with application event codes.


_.________________-

Just to summarise: This "standard" events routine , which one may become familiar with at an early stage in learning VBA …._
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

' We add codeiX here

End Sub
_.... can be considered as equivalent to Excel having already "done and run**" something like this "application event" coding: ( ** Sub InstantiateWsMe() mist have been run at least once )
Code:
Dim WithEvents WsMe As Excel.Application
Sub InstantiateWsMe() 
 Set WsMe = Excel.Application 
End Sub
Sub WsMe_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name = Me.Name Then

' We add codeiX here

    Else '
    End If
End Sub



That concludes the main part of the Thread on Application Events

Summary : This is what is happening/ This is how it works:
We made an object which we named arbitrarily WsSht_1 . This is subscribed to the event codes of the current open in front of us Excel. In one of the event routines now available to this object, or rather already there in this form:…._
Code:
Sub  WsSht_1_SheetChange(ByVal Sh As Object, ByVal Target As Range)

 End Sub
_... we added coding so that it looks now like this:_......
Code:
Sub  WsSht_1_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name = "Sht_1" Then
       If Target.Address = "$A$1" Then MsgBox prompt:="You just changed the value in the first cell in worksheet " & Me.Name & " in the Workbook " & Me.Parent.Name
    Else '
    End If
 End Sub
Hence after running the instantiating code to "build " the variable we were using , ( WsSht_1 ) , "into an object" , that object will "monitor" or "watch for" changes in a sheet, and take the action determine by our added coding.


The next posts have a few relevant further things which I may add to from time to time. In particular, we will do the same in a slightly different way, the "Class" way