WithEvents of Excel.Application Events

**DocAElstein** · 12-26-2018, 04:35 PM

( Post 7 )
Part #5B Using “ application events way ” in the typical “class / application way”
Brief description:
As seen in the previous posts, it is very easy to get lost, so it is good to summarise in simple terms what we are going to do:
We use two “standard available event routines” and one “application events routine”
And we build two objects, or rather, we build one new object, and just assign a new variable to an existing object for the second one.
We do a somewhat round about way, more complicated than necessary, as there seems to be some good programming reason for organising things like this in that way.
The standard available event routine which kicks in when the workbook is opened is used. This “builds” the first of two object, in this case, a non standard object from a type (Class). The “blue print” of that type we prepare earlier. This “blue print” information is determined ( written ) inside a Class module which we add. That Class thing has a standard available event routine in it, which is the second standard available event routine thing which we use. That second standard available event routine kicks in when the first object, an object from that type/class is built. We add coding within that event routine to assign an object variable to a second object. That second object is the main object we need. So effectively, the first object being built causes that second object to be built, or rather in this case of the second object, the variable is assigned to an existing object: The variable for that object is declared to the type which is the Excel application itself. In other words, the variable we use for that object is Dimed to the Excel thing we have in front of us. But it is Dimed in a special way, like “Dim WithEvents” . This means that we then have some extra non standard available event coding available to us, which will be the event routines of the type of object ( class) to which we Dim/declare. In this case we Dim/declare to the Excel application itself, and so our new variable has access to the event routines of our Excel application itself.
We choose to use the event routine that “monitors” workbooks being closed. The coding for that we write in the class module. So that becomes part of the second object. So once that object is built/ assigned it has that coding in it. We add in that routine the coding to determine what is done when a workbook is closed.

Full Description and process
Open the VB development window, for example using keys Alt+F8 when you have an Excel Application up and running in front of you.
We insert a new Class module thing: Right click anywhere in the VBA Project window and select to insert a Class module. As we want to declare ( Dim ) to this type, it would be useful to give it a different name.
Right Click in VBA Project window Insert Class module Rename.JPG : https://imgur.com/ZUJGnS4
For example, in line with the reference I am using ( https://stackoverflow.com/questions/...ng-of-workbook ) I choose the name CloseHelper
In this class code module we need the main ( second ) object which we want , which refers to the open Excel application in front of us. Lets use the variable , ClsLisWb , for this object. It is intended that this object “monitors closing of workbooks.
From the first post of this thread ( http://www.excelfox.com/forum/showth...ll=1#post10854 ) we know that we do not want to instantiate using Set ClsLisWb = New Excel.Application , as we want to use the existing Excel open in front of us, so we add the single code line , Set ClsLisWb = Excel.Application , to the Initialize routine, which is typically the first event routine which is offered to us from the drop down list in a newly added Class module, Class Initialize Event.JPG : https://imgur.com/CC5XZOB
The object, ClsLisWb , made by the Class initialize code is that which we want to “monitor” closing of workbooks. In the current way of doing things, the Class module effectively has written in it the blue print instructions for the object and sub objects of it, ( in this case ClsLisWb is an under object / sub object “belonging” to the parent object . ( I will arbitrarily name the first object , LisExcelLike ).
I will add here in the Class module an event routine which is now available to ClsLisWb . We look for one which monitors workbook closing and find this one: , Private Sub ClsLisWb_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean) , WorkbookBeforeClose.JPG : https://imgur.com/xVFWMmL
After the object ClsLisWb is “built/assigned”, then this routine will effectively be a method of ClsLisWb which automatically starts on a Workbook close event taking place.
So complete coding such as the following , within the Class module , will partially fulfil our requirements:
CloseHelper.JPG : https://imgur.com/kt46yRn
Class module CloseHelper

Code:

Option Explicit
Private WithEvents ClsLisWb As Excel.Application
Private Sub Class_Initialize() ' ' Routine to Instantiate ClsLisWb
 Set ClsLisWb = Excel.Application '
End Sub
Private Sub ClsLisWb_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
 Let Cancel = Not CunCls  '  With CanClose = True,  Cancel will be set to Not True = False
    If Not Wb Is ThisWorkbook Then Let Cancel = False 'To allow normal closing for other workbooks
' Secret code section not visible to us
    If Cancel = True Then
     'Do nothing and Exit this sub
    ElseIf Cancel = False Then
     'Close the workbook, Wb
    End If
End Sub

The small additional added coding from us above is similar to that from the last post, except that it includes an extra line so as to restrict the control of the closing to a particular workbook. ( To keep the example similar to the previous post, the closing control is restricted to the workbook in which the code is. ( we did not need that in the previous post ,as the corresponding event routine used , a standard one in that case, only applied to the workbook in which the routine was in. ) ).

So we have the class module part of our solution.
We would typically for convenience include the code to build the object LisExcelLike in the standard available event routine in the ThisWorkbook code module:
ThisWorkbook code module

Code:

Private LisExcelLike As CloseHelper
Private Sub Workbook_Open()
 Set LisExcelLike = New CloseHelper
End Sub

That code will kick off automatically when the workbook is opened.
Just to remind ourselves again of what goes on: The instantiating of LisExcelLike happens then automatically when the workbook is opened, which in turn results in the instantiating, or rather assignnng, of the main object of interest to us, the second object, ClsLisWb

Finally, as in the previous post example, we have a simple code in a normal code module to allow us to overwrite the close workbook prevention.
Normal Code module

Code:

Option Explicit
Public CunCls As Boolean
Sub CloseMe()
 Let CunCls = True
 ThisWorkbook.Close
 Let CunCls = False ' I don't know why this is here? I don't think it will ever be done!!! ???
End Sub