(Post 5 Part 4b)
Using the “Class way” to do it”
From the last Post:
Summary ( again ) : This is generally what we want:
We want an object which we name arbitrarily WsSht_1 . This is to be subscribed to the event codes of the “current open in front of us” Excel. In one of the event routines then available to this object, or rather already then there in this form:…._
Code:
Sub WsSht_1_SheetChange(ByVal Sh As Object, ByVal Target As Range)
End Sub
_... we will be adding coding so that it looks then 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 ( 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.
So now we will do the equivalent coding which is more typically done by professionals to do the same as that coding.
First a Class module is added and its name changed to , say, Custom_WsChange. This name, including the underscore, _ , is of arbitrary choice. Right mouse click in VB Explorer Insert Class Module.JPG : https://imgur.com/tz129ax
The coding we put in this new Class module almost identical to that from the previous post.
Class Custom_WsChange.JPG : https://imgur.com/GemN8yw
Class : _ Custom_WsChange https://imgur.com/1ldUusp
Code:
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 = "Sht_1" 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
The only difference is that the instantiate routine, ( Sub InstantiateWsSht_1() from the last post ) , is now in one of the two available pre subscribed and pre instantiated event code of Class Class Initialize.JPG : https://imgur.com/v5mKWhE
Code:
Private Sub Class_Initialize()
End Sub
At this stage we have the main equivalent coding to previously.
In the previous coding we needed to run Sub InstantiateWsSht_1().
The immediately obvious requirement now would be to run the equivalent Sub Class_Initialize()
But now we cannot run ourselves Private Sub Class_Initialize() : We cannot run directly any coding in the Class : The coding in the Class module that we can see, is effectively part the internally held “blue print” instruction information used to build objects from / of that Class . The information defines what is in an object made from that Class.
The key to getting this working is in building an object from this Class, Custom_WsChange. That may not be immediate obvious, but it will become apparent why later.
Build the “watcher “ object, WsSht_1 in the “Class way”
In any normal code module or object code module, we need to write the code lines to build an object of class Custom_WsChange
The first part of this will be to declare ( Dim ) a variable to the type of Class Custom_WsChange.
( One notes that intellisense will now offer us our newly made Class PrivateWatcher.JPG : https://imgur.com/5CTnREW )
Syntaxly, the Dim can go inside a routine , but it would not be much use as a variable for an object subscribing to events, and therefore “watching for”, as it would cease to exist at the end of a the routine. To go with this Dim , we need the some way to instantiate the object, ( “build it”).
This coding will do on any normal or object module
Watcher.JPG : https://imgur.com/fBFc5LE
Code:
Private Watcher As Custom_WsChange
Sub InstantiateWsSht_1()' Routine to Instantiate Watcher and thereby indirectly Instantiate WsSht_1
Set Watcher = New Custom_WsChange
End Sub
So that routine will need to be run once. I give it , ( by my choice ) , the same name as the (only) instantiating routine from the previous “non class way” , Sub InstantiateWsSht_1(). I do this because that is what it in effect finally does: This routine actually instantiates the object Watcher . That causes the routine in the Class module Private Sub Class_Initialize() to kick in. This is the action we can no longer do ourselves. This action is the main action that the “Class” is doing in the case of the this “Class way” of doing it”
Once this has taken place, we are where we were with the “not using Class way” :
Just to explain again: Here we build an object ( Watcher ) of Class Custom_WsChange . As this “comes into life” , its Sub Class_Initialize() event code builds the object WsSht_1.
( That code , Sub WsSht_1_SheetChange(ByVal Sh As Object, ByVal Target As Range) , must be in the Class module so that it becomes part of the object made from this Class : Generally codes in here become properties/ methods of the object built from the class. As such the functionality or actual coding is in a lower object comparable to our available worksheets code modules, but we cannot see it. Anyone that knows that in more concise detail is not telling, so that’s as good an explanation as you will get.
_.________________________
It is worth reminding ourselves / reviewing what we have done, ( only slightly differently this time), in the “Class way”
We are using both
pre subscribed and pre Instantiated event code
and
are own event coding using Application Event coding.
We have chosen to use a Class module for some of the coding.
This is what is happening/ This is how it works:
We make an object which we named arbitrarily Watcher . As this object “comes into existence” it in turn builds our object 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 the , ( automatic this time ) running of 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.
So the “Watcher” code is “there” ( “somewhere” ) as before. The slight difference to the case of previously having that code in a “see able” object code module, is that we cannot see the actual coding in the object WsSht_1 this time: What we see as the Class module which we added, is “one level back up” the OOP chain. It is the Class Custom_WsChange which determines how the object looks. But Excel just doesn’t show us the code module as it does for the objects made from Class Worksheet and ThisWorkbook . That is just the way someone at Microsoft decided to organise it.
_......
We are very close now to the final solution often given for such a requirement. We need to take it just one small step further now in order to reach the coding typically presented in such solutions.
_......
###If we take this a small step further we finally come to the point at which we often get plunged into the deep end when using application events:
In a typical use, the code to instantiate would be put in one of the pre subscribed and pre Instantiated event code in the ThisWorkbook code nodule.
With so many different event codes flying around and a Class module for good measure, its not surprising that a given solution or Tutorial on this subject often confuses.
Purely for convenience, we would typically choose the pre subscribed and pre Instantiated event code which “monitors” or “watches for” the opening of the workbook. In this particular case, that description does not suit too well. A better description would be that the following routine is more likely hard written in the opening software permanently, so that this will always be done, regardless of whether we add any coding into it or not. This latter description is also as valid as any for all event coding.
( To revel this code module, double click on the ThisWorkbook ( German Excel DiesesArbeitsmappe ) shown in the VBA Project Window )
ThisWorkbook Workbook_Open.JPG : https://imgur.com/kWtgiu9
Code:
Private Sub Workbook_Open()
End Sub
So we would simply do this,
Code:
Private Watcher As Custom_WsChange
Private Sub Workbook_Open()
Set Watcher = New Custom_WsChange
End Sub
We do not have to put the variable , Watcher , in this code module: it could have remained in any normal code module as a normal global variable to be accessed from anywhere. But for general tidiness, Encapsturbation , and probably sometimes for other indirect good programming reasons, it would be put near the coding which instantiates the object, Set Watcher = New Custom_WsChange
_._______________
A Final shortened, simplified, summary of the typical complete Class way
The workbook is opened.
This causes via a ThisWorkbook event code ( __Workbook_Open() ) , an object to be made from a type / Class which we have pre determined by adding a Class module and putting code in it
That object being made from that class in turn results in another object being made which is subscribed to events of the Excel application, ( the “Excel we see in front of us” ).
Within one of the available event codes we have added coding such that changes in a worksheet are “monitored”
Bookmarks