WithEvents of Excel.Application Events

[DocAElstein]

WithEvents of Excel.Application. Encapsturbation

Post #2: Excel Events. Hanging codes on to them
As discussed in the referenced summary, http://www.excelfox.com/forum/showth...ge10#post10838 , the vaguearities of the Objects concept in Object Orientated Programming , OOP , leads to the idea of certain code modules being referred to simultaneously as both belonging to an object , and being the object itself…
This has and continues to cause computer engineers to confuse themselves, and in an attempt to make sense of order in a messed up system, they often organise their use of code modules in some way that gives at the appearance of some sense and order or theory..
Generally it is considered that things happen in Excel, that is to say Excel “works”, as a result of Events occurring to objects. Different objects will have different events associated with them
Those points are important to accept and bear in mind to avoid confusion in the following notes.

Organising and finding the Event routines, Encapsturbation
If we were very clever and had access to all possible Microsoft software then we could probably use some VBA or similar OOP coding to make ourselves the “Excel in front of us”. Some of that has been done for us to get us started.
As a side note, based on the introduction above, we note that Event routines will generally either be in, or chosen to be in, or only allowed to be put in “object modules”. Programmers will often talk about “Encapsulation” or keeping things “where they belong” without being specific but attempting to sound specific and organised in a disorganised situation. It can be considered as a form of Encapsturbation

Worksheet Events. The Workbook Events
These are the events codes most often encountered and often they are the only ones explained under the Theme of events codes. But in fact they are just a small selection of event codes whereby they have been partially already put in place in a way that only loosely ties up with some general order.
A example, consider a Worksheet object. This thing is simultaneously regarded as the spreadsheet of that worksheet or the worksheet code module. We do not have to make this: This has been pseudo done for us, and is somehow part of the Excel software which we purchased. When we run excel something pseudo of this form has been done , or is done, as the software starts up
_ A class object of a Worksheet has been made. Possibly one is made for each of the default worksheets that appear. (The number may vary depending on your version of Excel ) So there exist somewhere a very complicated class object and/or class object module , or modules, which we can’t see with the name Worksheet.
_ A few worksheet objects are made from that class, and for the case of a worksheet, they are “subscribed” to the Events associated with a worksheet. Internally some variables, say for the case of the first worksheet, Sheet1 , will be used pseudo like
Dim WithEvents Sheet1 As Worksheet
_ Set Sheet1= New Worksheet
Briefly, those two lines do the following:
the Dim gives some initial indication of the type of thing that Sheet1 refers to. This might mean referring to the text/ Blueprint info of the variable type of setting aside a copy of such. In any case it does not apparently require too much computer memory. The variable state, for the case of an object, is typically regarded as Nothing , at this point.
It is usually considered that a more substantial computer memory is used as a result of Set and New pair. This goes by the name of “instantiation” or similar and is regarded as “making” or “building” the object.
The exact coding and resulting structure of objects is not clear, that is to say not given openly, and it is not permitted to do those steps in coding for the case of a Worksheet!!!. We are not allowed to instantiate a Worksheet. It is possible that the coding results in some pseudo structure of like
_________________Workbook.xl__
____________ Worksheets Collection Worksheet Class
____ Class Sheet1 ___ Class Sheet2 _____ Class Sheet3
_________ Sheet1 ___ _ _____Sheet2______ _____ Sheet3

It is not clear or disclosed as which of the lower two levels we actually “see” in the code window or the spreadsheet. We see something of, and have some access to , the Sheet1, Sheet2, Sheet3 type “things”. Sometimes the lower two levels might be considered as the same. Sometimes the code module might be considered as the Class Sheet1 and the spreadsheet as Sheet1.
So it is all a bit Unclear and open to interpretations.
Consider some points:_
_ General coding is permitted in the worksheet code modules in addition to the Event codes. General coding is not permitted in the Class module object which we can create.
_ We cannot subscribe a variable to the events of a Worksheet, or rather we are permitted to do it from the syntax, but the resulting codes simply do not work!!!…. ( see macro below )
_ We do not need to do the following to step through a code placed in such a module via F8:
Tools Options.JPG : http://i.imgur.com/KZUyPnB.jpg
Break in class modules.JPG : http://i.imgur.com/75Eg6UE.jpg
So those three facts might suggest that we are at the lower level … better described as an object than a class module

The story is similar with the object for a workbook. We have a single code module which probably fits into a structure of
______________________Excel Application
______________________Workbook Class
_______________________Workbook.xl__
Once again we cannot instantiate a Workbook. We can write normal codes in the code module that we “see” , etc.. etc… So similar arguments to the worksheet case suggest what we see is a ( single in this case ) object module

Encapsturbation Attributing Encapsulation
Encapsturbation is a combination of general and self inflicted actions attempting to give a seeming order to the situation. Attributing factors , rules and general practice are as follows.
Firstly and not always immediately for any obvious reasoning, ( other than Encapsturbation ) , a couple of things related to where things go and how to get at the codes
_ Where to put WithEvents
.(i) WithEvents can only go as procedure level variable. ( So outside of a routine, and placed towards the top of a code module). This will make some sense after writing practical routines: The object must “exist” in order to “catch” events, so a variable inside a routine will be of limited use as the variable will “go out of scope”, that is to say no longer “exist” after the routine ends.
.(ii) WithEvents can only go in an object or class object code module.
_( Drop down list, help , pseudo intellisense
For no obvious particular logical or scientific reason there are a couple of drop down list in code modules. (We mentioned in the previous post that for our common case of the worksheet, it seemed to somehow conveniently fit in ). For object modules you typically can select in the left list between two main heading types.
One type is meaning general routines and there is just one of those.
The other type refers to the routines for all the WithEvents objects variables .
Selecting the general will result in a list in the right drop down of all normal procedures in the code module.
Selecting the object listed which is subscribed to events will result in a long list in the right drop down of all available routines. This probably does make some sense: Once subscribed the routines are “hooked in” and there. We can choose to add code lines in if we wish. One does come in automatically when you select the object: You need to have the cursor within a code in order to get the relevant list available in the right drop down list: One comes in when you click on the object in the left drop down box. The others come in when you click on them. I would personally have put them all in to avoid confusion, but maybe someone thought it would just look tidier to leave them out until they were actually added to …
In the following code and screenshots I am in a worksheets object code model and have subscribed an object variable, WsTyp2 , to the event codes of worksheet. This appears to be accepted syntaxly and a second duplicated set of codes are included in the right drop down list. ( The Event routine , Private Sub WsTyp2_SelectionChange(ByVal Target As Range) , using WsTyp2 appears however to be complete without function. This is probably as we are not permitted to Set it to a New instance ). Even when assigning to the existing instance, with , Set WsTyp2 = Me , we still find that the Events code using the subscribed object, WsTyp2 , does not respond to any event . Furthermore neither event code works in a module other than a worksheets code module, which suggests that some form of class object is present which we have no access to determines how the existing events cods work )
Worksheet Events.JPG: http://i.imgur.com/vq9gQ9u.jpg , WsTyp2 Events.JPG : http://i.imgur.com/GWSNDS9.jpg

Dim WithEvents WsTyp2 As Worksheet
Sub MakeAWorksheetNot()
 On Error GoTo Bed:
 Set WsTyp2 = New Worksheet ' Errors
 Set WsTyp2 = Me
Exit Sub: ' Code end without error
Bed:
 MsgBox prompt:=Err.Number & vbCrLf & Err.Description: Debug.Print Err.Number & vbCrLf & Err.Description ' Ctrl+g from the VB Editor to get copyable message in the Immediate window
End Sub
Private Sub WsTyp2_SelectionChange(ByVal Target As Range)
 MsgBox prompt:="You will never see this"
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 MsgBox prompt:="You will see this if you select a cell in worksheet with name" & vbCrLf & Me.Name
 Resume Next
End Sub

_.____

Application Events
This is probably the better start point as these codings have not been tampered , manipulated , prepared in the somewhat hap hazard way that the those already discussed have. As such it is a bit clearer how to go about using them. They can be thought more generally, or more fundamentally, as Event coding and Excel Workbooks