WithEvents of Excel.Application Events

[DocAElstein]

Walkthrough revision. No explanations

I tried hard in the last post to start at the beginning and give clear concise explanations. ( I started doing things in the simpler way, which is rarely explained anywhere***. I finally did the more advanced “class way”).
Inevitably it turned into a long Blog, and it is easy to get confused, especially as my approach does not tie up with most other Blogs on the subject.
In this post I will try to avoid as much explanation as possible, and concentrate on walking through the same example again that I have used throughout. I will do it in the 2(4) ways, giving finally a workbook for each way. One example will be of the Class Way, and three will be of the Non Class Way.
I will start with the more advanced way, the class way. ( In most Blogs that is the only way shown*** ).
I will then do 3 examples of the simple ( non class ) way
I will assume I want the thing to be up and running automatically as soon as I open the workbook. ( So this means that in all example I will have to make some use of the already as standard available Private Sub Workbook_Open() which can be got at via the drop down lists in the ThisWorkbook object code module
ThisWorkbook Workbook_Open.jpg : http://i.imgur.com/10Bp8Wp.jpg , http://i.imgur.com/P6TE7uf.jpg

---

What do we want.. / the previous example
The example previously considered want to do something similar to this standard already available event macro.

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

I want to do this , purely as an example, using the WithEvents of Excel.Application ideas of this Thread

---

The Class way

In the Class way we make a blue print of the coding type we need. This needs to be in a class module. ( We wont actually get to see the coding copy finally used)
Insert a Class module , and give it some arbitrary name
InsertAClassModule.JPG , NameAClassModule.JPG
http://i.imgur.com/Op5rhAV.jpg , http://i.imgur.com/Cxx7nxZ.jpg
We need the three main code parts in this class module:
_ the dimming with events of an arbitrary named variable;
_ the instantiating of that variable;
_ the event coding.
Class Custom_WsChange.JPG : http://i.imgur.com/GemN8yw.jpg
Class : _ Custom_WsChange http://i.imgur.com/dZgJ5M7.jpg http://i.imgur.com/1ldUusp.jpg

Dim WithEvents  WsSht_1 As Excel.Application
Private Sub Class_Initialize() ' Routine to Instantiate WsSht_1  
 Set WsSht_1 = Excel.Application
End Sub
Sub WsSht_1_SheetChange(ByVal Sh As Object, ByVal Target As Range)
 If Sh.Name = "Sheet1" Then
 If Target.Address = "$A$1" Then MsgBox prompt:="You just changed the value in the first cell in worksheet """ & ActiveSheet.Name & """ in the Workbook """ & ActiveSheet.Parent.Name & """"
 Else '
 End If
End Sub

We cant use / run any of those macros, because they are just a Blue Print of coding to be made. The way that class things work is that when we instantiate a variable , that is to say Set it to being of the class, then effectively we get a “real” set of the macros, ( although we can’t see them )
Either
_we can do that all in the ThisWorkbook like this
VariableAndSetClassInThisWorksheetObjectCodeModule .JPG http://i.imgur.com/GwvKUI7.jpg

Private Watcher As Custom_WsChange
Private Sub Workbook_Open()
 Set Watcher = New Custom_WsChange
End Sub

Or
_ alternatively we can put the Private Watcher As Custom_WsChange in a normal code module
VariableInNormalCodeModue SetClassInThisWorksheetObjectCodeModule.JPG : http://i.imgur.com/nSHMDe7.jpg

By Setting the variable , Watcher , we effectively create an “invisible” copy of those macros, and at the same time the Instantiating Initialise macro is done

ClassWay.xls https://app.box.com/s/5hkwnz9c5lvudne5b1418qukt830nd0w

---

Simple ( non Class ) ways
The instantiating done in the Class way can be thought of as having the effect of making an object and object code window ( which we cant see) containing the main macros and at the same time causing the Private Sub Class_Initialize() ' Routine to Instantiate WsSht _1 to run
The simpler ways are possible because
_we already have some object code windows which we do see, and which we can add the actual coding to.
and
_ either by design or by accident, we find that the various with event things are available and seem to “work” as if we were in the “invisible” object code module produced by the instantiating in the Class way

We can probably come up with about half a dozen simple ways to do this, based on where various codings are put. We will consider 3 examples

---

Simple ( non Class ) way. (Example 1)

We can put the three main code parts in an existing object code module, for example in any worksheet object code module. ( It does not have to be that of the worksheet to which the coding is applied to). For example, we put the main macros in the second worksheet object cod module
SimpleWay1MainMacros.JPG : http://i.imgur.com/REXWgek.jpg
We see that due to the with events declaration, we have the required event macro available in the drop down lists
SimpleWay1AvailabeEventMacrosAfterWithEventsDeclar ation.JPG : http://i.imgur.com/dOompwu.jpg
The only minor difference is that the macro set off previously to instantiate the variable that is subscribed to the Excel application events will be a normal routine. (We will have to arrange that this macro is done).

Dim WithEvents WsSht_1 As Excel.Application
Public Sub WsSht_1_InitializeIt() ' Routine to Instantiate WsSht_1
 Set WsSht_1 = Excel.Application
End Sub
Sub WsSht_1_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name = "Sheet1" Then
    If Target.Address = "$A$1" Then MsgBox prompt:="You just changed the value in the first cell in worksheet """ & ActiveSheet.Name & """ in the Workbook """ & ActiveSheet.Parent.Name & """"
    Else '
    End If
End Sub

We no longer need any variable to be set to that of any class. Instead we effectively short circuit that step, and the Workbook_Open macro inside the ThisWorkbook object code module , Calls directly the actual macro Sub WsSht_1_InitializeIt().
Note: we need to add the object having the coding to the Call:
CallInstanciateSimpleWay1.JPG http://i.imgur.com/EDWm5rS.jpg

Private Sub Workbook_Open()
 Call Worksheets("Sheet2").WsSht_1_InitializeIt
End Sub

SimpleNonClassWay1 : https://app.box.com/s/naxfwg3ms8omvpcybrjpzjdw3o7i4w36

---

Simple ( non Class ) way. (Example 2)

This is a minor variation on the last way.
We can put the Sub WsSht_1_InitializeIt() instantiating macro in a normal code module.
The Workbook_Open macro inside the ThisWorkbook object code module remains similar , ( assuming we still use the same procedure name, WsSht_1_InitializeIt() )
We can either omit the object before the macro name, or replace it with the name of the normal code module.
SimpleWay2ThisWorkbookObjectCodeModule.JPG http://i.imgur.com/wyLsCjq.jpg

Private Sub Workbook_Open()
 'Call WsSht_1_InitializeIt
 ' or
 Call Modul1.WsSht_1_InitializeIt
End Sub

We will need to make two other small but important changes:
_ in the object code module containing the with events declaration: We must change Dim WithEvents WsSht_1 As Excel.Application to Public WithEvents WsSht_1 As Excel.Application
SimpleWay2ObjectCodeModule.JPG http://i.imgur.com/gq4OJuE.jpg

Public WithEvents WsSht_1 As Excel.Application
Sub WsSht_1_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name = "Sheet1" Then
    If Target.Address = "$A$1" Then MsgBox prompt:="You just changed the value in the first cell in worksheet """ & ActiveSheet.Name & """ in the Workbook """ & ActiveSheet.Parent.Name & """"
    Else '
    End If
End Sub

_ That last change above will let the variable be referenced outside of the object code module in which it is. We then need the minor modification to the instantiating macro in the normal code module so that it refers to the object:
SimpleWay2NormalCodeModule.jpg : http://i.imgur.com/rxpZXDk.jpg

Sub WsSht_1_InitializeIt() ' Routine to Instantiate WsSht_1
' Set Worksheets("Sheet2").WsSht_1 = Excel.Application
' or
 Set Sheet2.WsSht_1 = Excel.Application
End Sub

SimpleNonClassWay2 : https://app.box.com/s/l1dtodagtappmdffagm4ktedo1qzgh2c

---

Simple ( non Class ) way. ( Example 3 )
( Everything in the ThisWorkbook object code module )

This is probably the simplest way.
We use the ThisWorkbook object code module for all the main macros. The Instantiate macro is the Workbook_Open macro
AllMacrosInThisWorkbookObjectCodeModule.JPG http://i.imgur.com/2fzKaAC.jpg

Public WithEvents WsSht_1 As Excel.Application
Private Sub Workbook_Open() ' Routine to Instantiate WsSht_1
 Set WsSht_1 = Excel.Application
End Sub
Sub WsSht_1_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name = "Sheet1" Then
    If Target.Address = "$A$1" Then MsgBox prompt:="You just changed the value in the first cell in worksheet """ & ActiveSheet.Name & """ in the Workbook """ & ActiveSheet.Parent.Name & """"
    Else '
    End If
End Sub

We need no other macros anywhere!
SimpleNonClassWay3(AllCodingInThisWorkbookObjectCo deModule) : https://app.box.com/s/rya16vhhyigg7466l6z1v2iudvbpcliz