Class Stuff: VBA Custom Classes & Objects, Class Modules

[DocAElstein]

Administrative Notes: : this blog is not finished - this blog on class stuff to be finished next winter hopefully. April 2020 or later... pics in eileenslounge Nick basics


Class Stuff




Introduction To Classes

Class Stuff: VBA Custom Classes & Objects, Class Modules ( Custom Events).

Class Modules.
This is a bad start point. But it seems to be often peoples first experience with this “Class stuff”. Class modules are a small way down from the start of what it is all about, and for the sake of .. I don’t know what .. the subject is often started with the mechanics of setting up a class module. I think using this typical approach and start point seems to detract from someone getting the point.( Its as bit like explaining to someone in detail how to change a wheel on a car when he needs to know how to organize running a taxi company )
I think this results in that somebody trying to learn will .. not see the wood for the trees.
It is true that a class is created by inserting a Class Module (in your VBA project) to which you give a name, but that is just arbitrarily how the mechanics of it have been written.
So I won’t start the story there, by the inserting of a Class Module.

It is a better start point to say…” In VBA you can create your own custom objects by defining classes. Classes act as templates for new objects “

A brief introduction to objects and class objects in VBA. Object Orientated Programming,
If you understand a bit about Object Orientated Programming, OOP, then you will know that it is all about Objects, and an object can be a whole lot of diverse things. OOP is an annoyingly vague concept. The Objects tend to be organised hierarchically starting with the big ones at the top of the hierarchy, and going down through smaller ones.
http://www.excelfox.com/forum/showthread.php/2289-Named-Ranges-and-Named-Ranges-scope-Referencing-a-named-range#post10809 , , https://imgur.com/tirYIdz http://i.imgur.com/tirYIdz.jpg
Something running parallel to this hierarchical structure which loosely fits into it all, is that we define a Class as a blueprint or Template from which to build one or more objects of that type , or model, or Class. Class is also a vague concept and means like a template, blueprint , or set of instructions or procedures to define how objects built from the Class will look like.
At this point in the discussions, the VB Editor starts getting very misleading: it does not represent clearly the actual structure that we have for two reasons:
_ the positioning of thing is not consistent
_ some things are simply not shown, ( possibly deliberately as we have no access to them. )

Example Model of a workbook
The Workbook
__________________________________________ThisWork book
Lets restrict ourselves for the sake of simplicity to having a single workbook as the “start” . Then we could consider a workbook class from which our single workbook is made
ThisWorkbookClassObject___ThisWorkbookObject.
What we see in the VB Editor left hand side explorer window is somehow a representation of the actual Excel object, ( or “instance” of it ) , that we have in front of us. The imprecise definition of objects allows us to consider the code module ( which appears when you double click on ThisWorkbook in the left hand VB Editor explorer window ) as part of workbook object.


In the next post we review the existing Class objects, which inspection of the left hand side explorer window of the VB Editor appears to indicate that we have some access to.
We will see that they are somewhere in between a normal macro code module and a Class module. They could possibly be described as object modules, or Class object modules

Having considered those two existing class related things, we will go on to discuss the other two class related things, which we will see are more close to a "pure" class thing. Those will be the things related to the classic "Class module" and the UserForm








https://web.archive.org/web/20180518005747/http://www.cpearson.com/excel/classes.aspx

[DocAElstein]

Review of the existing Class objects that we already see in the VB Editor
( Workbook , Worksheets )

Workbook
We are considering that there is a workbook class from which our single workbook is made
__ThisWorkbookClassObject_______ThisWorkbookObject .
What we see in the VB Editor left hand side explorer window is somehow a representation of the actual Excel object, ( or “instance” of it ) , that we have in front of us. The imprecise definition of objects allows us to consider the code module ( which appears when you double click on ThisWorkbook in the left hand VB Editor explorer window ) as part of workbook object.
It is most likely that what we “see” and can manipulate will be the right-hand side of those two things, that is to say,
________________________________ThisWorkbookObject

The Workbook
So after double clicking on ___ThisWorkbook _ in the left hand VB Editor explorer window, you can consider that part of what you have opened up is a small part of the right hand side thing in these two things
__ThisWorkbookClassObject_______ThisWorkbookObject
The left hand side you have no sight of usually and cannot have do anything to. This makes some sense. If you could get inside and fiddle around with that then you could change fundamentally how a workbooks works and in doing so cause all sorts of corruptions and problems. Also you could probably get to see a lot of secret proprietary information about Excel and Microsoft Office…
The “thing” we are considering here, and trying to make some sense of, is the large empty window, which is usually referred to as the ThisWorkbook “code module”.
I am suggesting that it can loosely be considered to be part of the workbook object.
A simple way to confirm the above ideas is to run this simple macro in the large ThisWorkbook. Code module/ window. ( If you have a non English Excel then the name may be different. For example in my German Excel it is DieseArbeitsmappe )

Option Explicit
Sub MeWb() ' What am I. What object am I
MsgBox prompt:=Me.Name ' This will give your File name
MsgBox prompt:=Me.CodeName ' This will give ThisWorkbook ( or your language equivalent ) It is this code module name
End Sub

The above macro will tell you the workbook name, and the code module name as shown in the VB Editor. So that gives you some indication that the ThisWorkbook “code module” which you have the macro placed in, is somehow part of the your workbook object.
Why do we have a ___ ThisWorkbook _ object code module
We can’t make a workbook object. We pay Microsoft for the software that does that. But they give us some limited**** access to the object coding..
In particular some macros already exist which are hooked on to events that occur. They are there all the time , and very slightly degrade performance , since they run automatically every time something is done. But the effect is minimal as no code lines are inside the procedures. But we are allowed to add code lines to them. It would probably be more easy to understand if those Procedures were shown already in the large code window, but for some reason it was decide to hide them. To revel them you select them via the left hand and right hand drop down lists at the top of the ___ThisWorkbook _ code module:
https://imgur.com/GU2Cq9Q https://imgur.com/UOuFaJd http://i.imgur.com/GU2Cq9Q.jpg http://i.imgur.com/UOuFaJd.jpg
28282829

In that example in the last screenshot we could add coding which would be done every time the workbook was opened.
For example, this would give a message every time the workbook was opened

Private Sub Workbook_Open()
MsgBox Prompt:="Hello, you just opened the workbook"
End Sub

Note the typical syntax of events codes: There is typically a single underscore, _ , in the middle. This separates the object on the left hand side and on the right hand side the event.
__Sub TheObjectToWhichTheCodingApplies_TheEventType(____ _________)
(Transgression , ( applicable here and to the next section on worksheet object code modules) : Note further, that , as we will later see, we can define any object variable, say , varObj , to “subscribe” to the events of an object. This means that that a variable representing an object, that is to say an object variable like varObj has the same access to all the events of the object to which it subscribes. We do this “subscribing”, for example , for the case of a variable, varObj like this:
Dim WithEvents varObj As Workbook
In this case we have “subscribed” varObj to the events of the Workbook Class
Having done this, we will now see the object variable appearing in the left hand drop down list along with Workbook,( https://imgur.com/gL1fvQ8 http://i.imgur.com/gL1fvQ8.jpg ) , and it has access to exactly the same event procedures as our Workbook
varObj Subscribed WithWorkbook.JPG : http://i.imgur.com/v0QCiYv.jpg
Important to note however, is that coding using the variable varObj will never work. It is not clear why this is so****. What we have done here in this slight transgression is to demonstrated the mechanics in place, in particular the use of WithEvents . The actual use of this WithEvents is discussed here: http://www.excelfox.com/forum/showthread.php/2294-WithEvents-of-Excel-Application-Events )

Normal coding and ThisWorkbook object module
Coding in the object module ran from within the module
We can add normal procedures, and they will also work within such a module. This fact is possibly an arbitrary decision by the makers of Excel of no significance… As the code module represents part of an actual object instance, the actual workbook, it makes possibly some sense that it can “do things”
For example, we could write a simple code to check our event code from above.

Option Explicit
Dim WithEvents varObj As Workbook ' ****
Sub TestOpenWbEventMacro()
Call varObj_Open
Call Workbook_Open
End Sub
Private Sub varObj_Open() ' ****Note: Micrrosoft have decided codes of this form will not react to the event
MsgBox prompt:="Hello, you just opened the workbook"
End Sub
Private Sub Workbook_Open()
MsgBox prompt:="Hello, you just opened the workbook using Private Sub Workbook_Open()"
End Sub

Coding in the object module ran from outside the module
(Methods)
We could also test the macro, using a macro in any other module. At this point it is another undocumented grey area to explain what is going on and why, that allows it to work…
_ You would first need to change the macros in the ThisWorkbook object code module to remove the restriction of them only accessible in that code module by changing the Private to Public:

Option Explicit
Dim WithEvents varObj As Workbook ' ****
Public Sub varObj_Open() ' ****Note: Micrrosoft have decided codes of this form will not react to the event
MsgBox prompt:="Hello, you just opened the workbook"
End Sub
Public Sub Workbook_Open()
MsgBox prompt:="Hello, you just opened the workbook using Private Sub Workbook_Open()"
End Sub
_ Having changed the coding in the ThisWorkbook code module above, it now appears as if the sub routines act as methods of the workbook, but there is no clear documentation to this. The following macro can be run from any code module.

Option Explicit
Sub TestOpenWbEventMacro()
Call ThisWorkbook.varObj_Open
Call ThisWorkbook.Workbook_Open
‘ The following are sometimes called methods of the
ThisWorkbook.varObj_Open
ThisWorkbook.Workbook_Open
End Sub

Variables ( Properties )
This is another grey area of understanding and definitions.
We can declare a simple variable at the top of the object code module. If we use Private or just Dim , it has the same effect, and the variable is only available in that module. In other words this coding must all go into the ThisWorkbook code module

Dim LsWkBkGlb1 As Long
Private LsWkBkGlb2 As Long
Sub PlayWithMeGlobiesInLisWkBk()
Let LsWkBkGlb1 = 1
Let LsWkBkGlb2 = 1
End Sub
If we change the declarations to Public like this…_

Public LsWkBkGlb1 As Long
Public LsWkBkGlb2 As Long
_.. then we will find that this will work in any code module

Sub PlayWithMeGlobiesInLisWkBk()
Let ThisWorkbook.LsWkBkGlb1 = 1
Let ThisWorkbook.LsWkBkGlb2 = 1
End Sub
The Public variables held in such a way in an object module are sometimes referred to as Properties of the object or as a form of global variable, that is to say a variable accessible in other modules. It is not clear and often experts argue as to what is going on here. As we will see later, a variable so declared via the class module way is fairly clearly defined as a property of an object that is made from the class module template, is fairly clearly defined then as a property of the finally made object. In the case of an object like ThisWorkbook, which already exists, the definition is less clear.
_._______________________

So in conclusion, it seems that we have a structure like the following, only some of which we have access to, and of that only some things are enabled to work: We have a single code module which probably fits into a structure of
______________________Excel Application
______________________Workbook Class
________[ClassModule]_____[ThisWorkbookObjectCodeModule]_Workbook.xl__

We have access to the object, ________[ClassModule]_____[ThisWorkbookObjectCodeModule]_Workbook.xl__

[DocAElstein]

Review of the existing Class objects that we already see in the VB Editor
( Workbook , Worksheets
Worksheets
The Worksheets
__________________________________________WsCodeNa me(WstabName)
__________________________________________Sheet2(S heet2)
__________________________________________Sheet3(M ySht)
We find that the behaviour of the worksheet code modules, ( obtained by double clicking on their representation in the left hand Explorer part of the VB Editor ) , is very similar to that of the ThisWorkbook code module discussed in the previous section.
It suggest that once again there exists for each worksheet a higher up the hierarchy object , likely a Class object we have no access to, and Microsoft have allowed us just one instance of each of these worksheet classes. It is possible, and likely based on the general OOP ideas, that those Classes are themselves produced from a higher Class.
So a structure like this:
_________________Workbook.xl__
____________ Worksheets Collection Worksheet Class
____ Class Sheet1 ___ Class Sheet2 _____ Class Sheet3
_________ Sheet1 ___ _ _____Sheet2______ _____ Sheet3


Likely reason for the worksheet code modules existence: Worksheet 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 the classes. 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. ( These pseudo code lines are a geuss of what happens internally , and of which we cannot influence).
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 this coding which we are suggesting possible 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 will work in the worksheet code modules in addition to the Event codes. ( We will see later that general coding does not work 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, in a similar way to which we considered for the Workbook, but the resulting codes simply do not work!!!….
_ We do not need to do the following to step through a code placed in such a module via stepping through a coding manually in debug ( F8 ) mode:
Tools Options.JPG : https://imgur.com/KZUyPnB , http://i.imgur.com/KZUyPnB.jpg
Break in class modules.JPG : https://imgur.com/75Eg6UE , 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
These findings and conclusions are consistent with those from the Workbook considerations, as are the next findings which are identical to the ThisWorkbook object code module

Normal coding and the worksheet object code module object module
Coding in the object module ran from within the module
We find that we can add normal procedures, and they will also work within such a module. This fact is possibly an arbitrary decision by the makers of Excel of no significance… As the code module represents part of an actual object instance, an actual worksheet, it makes possibly some sense that it can “do things”
For example, we could write a simple macro in a worksheet object code module , simple to that we did in the ThisWorkbook object code module, to help support our ideas about what the object code modules are..

Option Explicit
Sub WhatAmI()
MsgBox prompt:=Me.Name
MsgBox prompt:=Me.CodeName
End Sub
MyWkSht Sheet3.JPG : https://imgur.com/bBBhOZm http://i.imgur.com/bBBhOZm.jpg
2830 3526

The above macro, when run, will tell us out tab name and the name in the VB Editor explorer window , which is typically referred to as the code name. As before this seems to give us the approximate idea that the code module in which the macro is is part of the actual worksheet. It is not 100% clear, as is typically the case for things to do with objects in OOP.

Coding in the object module ran from outside the module
(Methods)
Once again things works as in the previous object code module, that is to say a macro such as the previous considered can be run from any module with a macro like the following. The macro being called is sometimes considered as a Method of the worksheet object, but once again it is not a 100% clear definition.

Sub CallWhatAmIMethodOf_Sheet3()
Call Sheet3.WhatAmI
Sheet3.WhatAmI
End Sub

Variables ( Properties )
We can declare a simple variable at the top of the object code module, as we did in the case of the ThisWorkbook object code module. If we use Private or just Dim , it has the same effect, and the variable is only available in that module. In other words this coding must all go into the worksheets object code module. ( I am using the last example macro slightly differently in this form for no particular reason – Just another way to demonstrate the use of the variables )

Option Explicit
Dim WsGlob1 As String
Private WsGlob2 As String
Sub WhatAmI()
Let WsGlob1 = Me.Name
MsgBox prompt:="The Tab name of this worksheet is " & WsGlob1
Let WsGlob2 = Me.CodeName
MsgBox prompt:="The code name of this worksheet is " & WsGlob2
End Sub
If we change the declarations to Public like this…_

Option Explicit
Public WsGlob1 As String
Public WsGlob2 As String



_.. then we will find that this will work in any code module

Sub WhatAmI() ' In any code module
Let Sheet3.WsGlob1 = Worksheets("MyWhSht").Name
Let Sheet3.WsGlob1 = Sheet3.Name
MsgBox prompt:="The Tab name of this worksheet is " & Sheet3.WsGlob1
Let Sheet3.WsGlob2 = Worksheets("MyWhSht").CodeName
Let Sheet3.WsGlob2 = Sheet3.CodeName
MsgBox prompt:="The code name of this worksheet is " & Sheet3.WsGlob2
End Sub
The Public variables held in such a way in an object module are sometimes referred to as Properties of the object or as a form of global variable, that is to say a variable accessible in other modules. It is not clear and often experts argue as to what is going on here. As we will see later, a variable so declared via the class module way is fairly clearly defined as a property of an object that is made from the class module template, is fairly clearly defined then as a property of the finally made object. In the case of an object code module belonging to any of the worksheets which was somehow already made for us, the definition is less clear. We are adding coding which could be considered to be from the blue print, but which obviously can’t be, since Microsoft didn’t know exactly what coding we were going to put in it: There is no direct blue print for it. Possibly something is in place in the custom class that is hidden from us, that allows for this. Or it might just be a coincidence, or by product, that we can do it, as Microsoft may have used the same basic coding to get this code window showing as they did to get the normal code window showing. So something’s will happen to work similarly as a result of this, independently to anything else and not directly related to any Class concepts.

[DocAElstein]

Short summary in words, existing Class objects that we already see, and what we don’t see, in the VB Editor

We can call almost anything an object. The concept of an object is very vague.
The Objects tend to be organised hierarchically starting with the big ones at the top of the hierarchy, and going down through smaller ones. But once again its not always strictly held to.
Something running parallel to this hierarchical structure which loosely fits into it all, is that we define a Class as a blueprint or Template from which to build one or more objects of that type , or model, or Class. Class is also a vague concept and means like a template, blueprint , or set of instructions or procedures to define how objects built from the Class will look like.
Class things will tend to be further up the hierarchical structure, but once again, that is a general idea or concept. There is no point in wasting time arguing the toss about being accurate about some think that is a vague concept left to an individuals interpretation, but based on certain concepts which I have outlined

There are “Class level”, Class objects that we don’t have any access to. They are probably extremely complex and define a lot of what an instance of Excel will be. At this level , or approximately we may view it as such, we can have a custom Class. We will see in the following posts that in effect that means we can insert and view a code module, used to define the object made from the “blue print” that the coding in a class module is.

For the case of the “Class level”, Class objects that we don’t have any access to, we can see the objects: We can argue that Excel itself and the Worksheets are those, but also the object code modules are parts of those: For example, you can look inside a worksheet object code module. ( For example, double click on it in the VB Editor to open it : http://i.imgur.com/9iTEib0.jpg : ) - That is a “real” object code module . Its made by Microsoft using a Class module that we can’t see and one we cant get direct access to it.

With our home made Class module it’s the other way around. We can see the class module. But the “real one” or “real ones” that gets made, we can’t see. ( I am not sure why that is. Maybe Microsoft just wanted it that way )
I think a lot of the documentation and Blog sites don’t have it quite correct. They often refer to the ThisWorkbook and the worksheet code modules as class modules. I don’t think they are. They are object modules made form class modules we don’t have direct access to.


We can argue the toss about why things are so organised. But no one knows for sure, and experts usually get upset when you talk about it**., since they prefer to know what they are talking about , which they don’t , half the time.
The class modules we can’t see are likely massive with a lot proprietary information. We will see that we are limited to a small number of coding types in our custom Class module.
The access to the object code modules is possibly primarily to allow us to fill in coding within the available event codings.
This event coding consideration is not directly relevant to our Custom Class situation: We don’t have a simple way to make custom events. ( This Theme is handled in a more complex way ( https://excelfox.com/forum/showthread.php/2294-WithEvents-of-Excel-Application-Events ) )




Ref
** https://www.myonlinetraininghub.com/excel-forum/vba-macros/set-variable-values-across-module#p19192




https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwhVTFaD469mW9wO194AaABAg.9gJzxwFcnPU9gORqKw5t W_ (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwhVTFaD469mW9wO194AaABAg.9gJzxwFcnPU9gORqKw5t W_)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugyb8nmKKoXvcdM58gV4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugyb8nmKKoXvcdM58gV4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwvvXcl1oa79xS7BAV4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwvvXcl1oa79xS7BAV4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgxvIFArksPprylHXYZ4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgxvIFArksPprylHXYZ4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)




https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://eileenslounge.com/viewtopic.php?f=27&t=35521&p=276185#p276185 (https://eileenslounge.com/viewtopic.php?f=27&t=35521&p=276185#p276185)
https://eileenslounge.com/viewtopic.php?p=276185#p276185 (https://eileenslounge.com/viewtopic.php?p=276185#p276185)
https://eileenslounge.com/viewtopic.php?p=276185#p276185 (https://eileenslounge.com/viewtopic.php?p=276185#p276185)
https://eileenslounge.com/viewtopic.php?p=276673#p276673 (https://eileenslounge.com/viewtopic.php?p=276673#p276673)
https://eileenslounge.com/viewtopic.php?p=276751#p276751 (https://eileenslounge.com/viewtopic.php?p=276751#p276751)
https://eileenslounge.com/viewtopic.php?p=276754#p276754 (https://eileenslounge.com/viewtopic.php?p=276754#p276754)
https://eileenslounge.com/viewtopic.php?f=30&t=35100&p=274367#p274367 (https://eileenslounge.com/viewtopic.php?f=30&t=35100&p=274367#p274367)
https://eileenslounge.com/viewtopic.php?p=274368#p274368 (https://eileenslounge.com/viewtopic.php?p=274368#p274368)
https://eileenslounge.com/viewtopic.php?p=274370#p274370 (https://eileenslounge.com/viewtopic.php?p=274370#p274370)
https://eileenslounge.com/viewtopic.php?p=274578#p274578 (https://eileenslounge.com/viewtopic.php?p=274578#p274578)
https://eileenslounge.com/viewtopic.php?p=274577#p274577 (https://eileenslounge.com/viewtopic.php?p=274577#p274577)
https://eileenslounge.com/viewtopic.php?p=274474#p274474 (https://eileenslounge.com/viewtopic.php?p=274474#p274474)
https://eileenslounge.com/viewtopic.php?p=274579#p274579 (https://eileenslounge.com/viewtopic.php?p=274579#p274579)
https://www.excelfox.com/forum/showthread.php/261-Scrolling-Marquee-text-on-Userform?p=864&viewfull=1#post864 (https://www.excelfox.com/forum/showthread.php/261-Scrolling-Marquee-text-on-Userform?p=864&viewfull=1#post864)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[DocAElstein]

Custom Classes and Custom objects. General and UserForms

We are approximately ¾ into the story and have reached the part which touches on the area often at the start of traditional explanations of these things. The previous discussions told us that we, when using Excel, are somewhere down from the true Class level, having access to objects already built from a Class which we had no direct access to.
Excel VBA does allow us to start at the top, all be it with a limited possibilities in what we can achieve and do. We have two possibilities.
The first starts with being able to add a code module which is at the level of a Class. This is misleadingly seen in the VB Editor as similar to the other modules, whereas its pseudo up a level , and is intended to be a Blue print / Template from which objects are then instantiated.
To confuse us further , those instantiated objects , would have a similar place in the OOP hierarchy as those two object types discussed which we can see. But the object code modules we can effectively make from our custom class module, are not represented in the VB Editor.
Having already made a confusing mess, as is typical with Microsoft, they like to take it further, and these unrepresented objects can be many, since we are free to instantiate as many objects from the Class as we choose.
The second, a UserForm is a slightly different thing altogether, and we will only discuss some basics of this in particular things relevant to our general Class discussions, for completeness. ( For anyone familiar with the “ stand alone “ Form/Window applications of traditional Visual Basic, the UserForm is very approximately a similar idea for a semi independent Window type application , but within Excel , rather than anywhere within Windows, as with the traditional “ stand alone “ Form/Window of Visual Basic )

Before discussing , further , some general notes specifically concerning the usefulness of using Class / Class modules, in particular the use of Class modules, ( not necessarily directly related to UserForms )…

Some general discussions about the ( non ) usefulness of using Class / Class modules
_ There is not much, if anything that can be done with Class Modules that can’t be done with normal VBA Functions. There is little if any, performance advantages. There is no increase in functionality or efficiency
_ For somebody writing a lot of complicated coding it can help that person to both organise and later remember what you were doing. Related coding can be kept in the same place, and it helps make the coding self documenting. At the same time it makes the coding more difficult for someone else to follow and understand. So it is a useful tool to help you organise complicated long coding, and if you want to share and make it a bit more difficult for someone else to understand.

[DocAElstein]

Custom Classes and Custom objects. General and UserForms
General Class / Class module use
The Class Module is a Blueprint/Template and the coding in it isn’t coding.
We are now right at where people start:
Right mouse click in the left hand side project explorer window near the representation of your workbook and select something like --- Insert --- Class Module
Add a Class module.jpg : https://imgur.com/X2DdZqY : http://i.imgur.com/X2DdZqY.jpg
2833

If you now double click on the Class1 representation that appears in the VB Editor, a large window appears similar to those code windows already discussed. Although we begin now to discus adding coding into this new window, we are in reality, using similar ideas and concepts to define how the coding in an instantiated object from the Class Blueprint/ template performs. It is convenient to use what looks like coding , because objects made from the template/Blueprint will perform as if such coding is running. The “coding” in the Class Module is actually the instructions / Blueprint / template showing exactly how the coding in the instantiated object modules would look like if we could see them as we can the ThisWorkbook and the Workbooks code modules discussed already.
A Class module is not really a code module,
Why by default you can’t step through Class modules in debug ( F8 ) mode
The fact that what you see is not actual coding, but a Blueprint for coding to use, explains why , by default, stepping through a coding manually in debug ( F8 ) mode, does not step through a Class module. What should happen is that it steps through the coding in an object model at the same level in the OOP hierarchy as the ThisWorkbook and the Workbooks code modules discussed already. The problem is that those actual object code modules do not exists. However the coding in those objects must mirror exactly the Template of the Class module, so VBA gives the option to allow the stepping through a coding manually in debug ( F8 ) mode to go through the class module as if it were the actual coding:
Tools Options.JPG : https://imgur.com/KZUyPnB http://i.imgur.com/KZUyPnB.jpg
Break in class modules.JPG : https://imgur.com/75Eg6UE http://i.imgur.com/75Eg6UE.jpg
Having said this, there could be some logical justification for organising the Class module as it is, since possibly it reflects the coding hidden behind many things which make Excel work…

Organisation of and using the Class Module
We are at a point where people first start…
The form of the text that can go in the Class Module is predefined. We may be able to put some text / code text in of some arbitrary form, but it will not be our choice if they do anything like we want. As noted, the text that is of any practical use will look like coding since it represents the coding inside the object code module which we can not see.

Event Coding.
The physical structure of the class code module, that is to say, as it appears on the screen to us, has a similar form to the two object modules already discussed. We only have a couple of Event macros already there
ClassEventMacros.JPG : https://imgur.com/DvLfX2G http://i.imgur.com/DvLfX2G.jpg
2834

They are fairly explanatory: They do things when we make or break an object…..
Just to remind us… We can’t do anything with the text , ( or coding as it appears to look ) , in the class module since it is just a set of instructions/ template for how actual code within an object made from it will look. So we need to instantiate, that is to say, bring into existence, an object made from this template: The stuff in the Class module id the template from which an actual object code module will be made. VBA does not let us see those modules
If we add text code lines within these two procedures, then in an instantiated object, the actual code lines of those forms , will be done at the instantiation, and at the termination of that object. ( The termination would typically be done in a code line in another module of the form Set Obj = Nothing. ( The instantiation/ bringing into life of the object we have already discussed is done by the typical initial Dim / Set ing of the object variable, Obj ) )
This is a very important concept that most people, including a lot of computer experts don’t understand, so I will say it again:
The text lines looking like code in the Class module are not real code. They represent what the actual code lines in an object code module of an object made from the class will look like. But we will never see those code lines. The makers of Excel decided to prevent us from seeing them. In the case of the two things already discussed ( worksheet object code modules and the ThisWorkbook object code module ) we had the opposite situation: We could see and manipulate the actual object code modules, but we could not see the Class modules used as the template from which to make those. We are guessing that somehow when we start excel, a hidden class module is used as a template to create the ThisWorkbook object code module , and possibly for each of the worksheets that we see, there is a similar class module which we can’t see, which is used as a template to create those.

[DocAElstein]

Public Variable Properties 1
We discussed in the case of the already created objects code modules, that it was unclear as to what we would call any variables which we added ( declared ) at the top of those already existent code modules.
For the cases of our custom objects created from Class modules that we have access to it follows more closely OOP concepts, at least in the case of declaring it as Public
A variable declared at the top of the text of the Class module will be a variable in the actual code module created using the Class module template. This can easily be related to general OOP concepts. We might decide that are Class is the blueprint for a car. A variable could be the car color.
So for example, we insert a Class module, giving it the name of Car , and adding a Public variable,
In Class module, name Car
RightClickInsertClassModule.JPG :
NameInsertedClassModule Car.jpg :

Public CrColor As String
ClsCarColor.jpg : https://imgur.com/NKiwsVt http://i.imgur.com/NKiwsVt.jpg
2843
That text of a code line above is to be put into a Class module which you have given the name Car

We would now be able do normal VBA OOP type programming in a code module to instantiate an instance of a car, that is to say, Dim ( declare ) an object variable to that Class, and then use the Property CrColor of that object variable.
So this following actual coding is to be put in any code module, not in a Class module– we cannot put this normal running code in a Class module because, as we have learnt, a class module just has the text of coding, but is not actually any coding that we can use.
In any normal code module

Sub MyCarColor()
Dim objCr As Car: Set objCr = New Car ' This is the normal codelines used to typically make an object variable of a particular Class
Let objCr.CrColor = "Yellow" ' "write" to the CrColor variable in objCr
MsgBox Prompt:="I have colored my Car " & objCr.CrColor ' "read" from the CrColor variable in objCr
End Sub
Often the simple variable so used is referred to as read and write type to distinguish it from two special ways of doing the same thing as the last simple coding. The special way , ( special Class Procedures text ), is discussed below. It basically does what the last simple coding does but in a way that allows you to do a few other things, including to make the variable only read or write from a normal code module.

Normal Procedures from Normal procedure text in Class Module
It is very likely that Normal procedures work within the object code module made from the text in the Class module. We can’t prove this , since we do not have access to the object code module. But if we write normal procedures as text in the Class module, then we find that the procedure appears as a Method of the instantiated object such that we can use it exactly as we did for the discussed in the sections above , “Coding in the object module ran from outside the module
(Methods)”
Just to be clear what we are talking about here: We put text in the form of a normal procedure into the Class module. We then instantiate a variable to be an instance of that class. This gives us an object code module ( which we cannot see ) in which a copy of that procedure coding is present. The result of this is that the procedure appears to be a method of the instantiated object.
For Example
This text should be put in the Class module we made in the last section, Car:

Public Sub SayHello()
MsgBox Prompt:="Hello from the object code module which was made from the code text in the template Class module named Car"
End Sub
ClsCarHelloProcedure.JPG : https://imgur.com/Z5H1G91 http://imgur.com/Z5H1G91.jpg
2844

We can now use the “say hello method” in our last macro in the normal code module. ( We will notice also that it is added to intellisense ( HelloProcedureOfferedInIntellisense.JPG : https://imgur.com/a5ofRio http://i.imgur.com/a5ofRio.jpg )
This following macro should be put in any normal code module

Sub MyCarColor() ' https://excelfox.com/forum/showthread.php/2451-Class-Stuff-VBA-Custom-Classes-amp-Objects-Class-Modules?p=13066&viewfull=1#post13066
Dim objCr As Car: Set objCr = New Car ' This is the normal codelines used to typically make an object variable of a particular Class
objCr.SayHello























Let objCr.CrColor = "Yellow" ' "write" to the CrColor variable in objCr
MsgBox Prompt:="I have colored my Car " & objCr.CrColor ' "read" from the CrColor variable in objCr
End Sub

The first thing that the above procedure will do on running is give you a message box which comes from a procedure, Sub SayHello() , which is in the object code module of objCr
HelloProcedureranfromHiddenObjCodeModule.JPG : https://imgur.com/fDl4xno http://i.imgur.com/fDl4xno.jpg
2845
( We cannot see that object code module from which the procedure is run: Just to remind ourselves again: Microsoft have decide not to let us see that object code module, objCr. ( Microsoft have decided the opposite for the Worksheets and ThisWorkbook object code modules: In those cases which we previously considered, we were able to see and manipulate the object code module, but we could not see the Class module with the blueprint code text from which the objects had been made. ) )


Private variable text in Class modules.
Variables defined in this way, ( as Private or just Dim ) in the text at the top of a Class module behave as might be expected in an instantiated object, just as variable only available in that object code module. Such declared variables are used a lot in conjunction with other procedures in the object code modules made from the Class module text.
For example, we might want to have a specific constant for use later by coding in the object code module taken from the Class module code text. The value of the constant could be filled , for example by the event macro which works automatically when an object is made from the class text.
For example:
Lets say we want to have a variable to hold the value the time at which an object is made using the Class module blueprint.
We add in the text at the top of the Class module a line of text which will appear as a code line at the top of the code module of any object made from the Class template.
Private TimeStamp As Date
Within the event procedure, Private Sub Class_Initialize() ( ' ClassCar Sub Class_Initialize().JPG : http://i.imgur.com/xe5Oj2w.jpg ) we can put a text which in the object using this text as a code line will fill the Time Stamp variable,

Private Sub Class_Initialize()
Let TimeStamp = Now()
End Sub

As an example demo of how we might then use this, we could add a line to the text in the Class module related to the procedure that will become a method to any object made from the Class module text template. This line could organise a second message box to tell us the time at which an object had been made.
So finally, the complete text in the Class module template would look something like this:

Public CrColor As String
Private TimeStamp As Date
Private Sub Class_Initialize() ' ClassCar Sub Class_Initialize().JPG : http://i.imgur.com/xe5Oj2w.jpg
Let TimeStamp = Now()
End Sub
Public Sub SayHello()
MsgBox Prompt:="Hello from the object code module which was made from the code text in the template Class module named Car" & vbCr & vbLf & "The procedure, Sub SayHello() is running from within an object code module which we cannot see"
MsgBox Prompt:="The object made from class Car was made at " & TimeStamp
End Sub
Our last example macro in a normal code module would stay the same:

Sub MyCarColor()
Dim objCr As Car: Set objCr = New Car ' This is the normal codelines used to typically make an object variable of a particular Class
objCr.SayHello
Let objCr.CrColor = "Yellow" ' "write" to the CrColor variable in objCr
MsgBox Prompt:="I have colored my Car " & objCr.CrColor ' "read" from the CrColor variable in objCr
End Sub

On running the above macro, a second message produced from the .SayHello method box would now come up:
Time Stamp Messsage Box.JPG : https://imgur.com/gIPSeps http://i.imgur.com/gIPSeps.jpg
2846

Lets just review what we did…
The Class module is really just a template full of text. ( It has a name to distinguish it from other class modules. We chose Car in our current example). That text is all copied to an actual code module, ( which we can’t see) , after a typical instantiating process in a normal code module of this form:
Dim objCr As Car: Set objCr = New Car
We chose the object variable name, objCr , arbitrarily. This name would be comparable to the Code Names of the objects already created for us by Microsoft: ThisWorkbook , Sheet1, Sheet2 , Sheet3 …. Etc.
The coding that already is placed in our , ( not visible to us) object code module , will look exactly like that in the Class text module template. Immediately the object code module is made, the Private Sub Class_Initialize() kicks in and fills the variable TimeStamp with the current date and time. That value in the variable stays constant as long as any macro bringing the objCr object into life is running. If at any point in such a macro we use the method SayHello(), via the command objCr.SayHello , then the procedure inside the ( not visible to us) object code module , Public Sub SayHello() , will run, and at that time the value inside the variable TimeStamp will be used in the following code line to tell us the time at which the object , objCr was brought into life:
MsgBox Prompt:="The object made from class Car was made at " & TimeStamp

[DocAElstein]

Special procedures in Class text module

Public Variable Properties 2 ( an Event Procedures)

2 Event procedures
We have briefly discussed one of the two, fairly self explanatory , special procedures available in a Class module. ( Private Sub Class_Initialize() )
ClassEventCoding.JPG : http://i.imgur.com/qIzN1GM.jpg
3530 :

Private Sub Class_Initialize() ' http://i.imgur.com/xe5Oj2w.jpg https://excelfox.com/forum/showthread.php/2451-Class-Stuff-VBA-Custom-Classes-amp-Objects-Class-Modules?p=13066&viewfull=1#post13066
' This procedure will run when I instantiate an object from this Class
Let TimeStamp = Now()
End Sub

Private Sub Class_Terminate()
' This will run when I Set an object variable, such as our objCr to Nothing
End Sub



2 ( 3) main special code texts Public Variable Properties 2
We have 2 main special code texts that may go only in the Class text module. These have been pre defined by Microsoft. They are specifically made available for the Class text module. ( There is also a third code text which is a slight variation on one of the other two !!! )
These two procedure texts have an awkward syntax, so it is a mystery why they have not been included in the drop down list for class things alongside the Initialize and Terminate
These two procedure texts allow for an alternative form of the simple Public variable property discussed previously. In the simple previous case we were able to assign ( write ) to the variable, ( we gave the string variable the word “Yellow” ) , and obtain back ( read ) that variable , ( we had a message Box which used the variable to tell us the color.
The special procedure texts allows us to have more control in the read and write process: One text procedure controls how the read/ assigning is handled, and the other controls the output/ writing of the variable value
The syntax is a bit tricky to remember.
Usually they are seen in pairs, but one might be omitted in order to make the property from the instantiated object only read or write

Here is an initially first view of the main pair . The next posts will explain them in detail

*Assuming we use a pair , we can summarise the syntax as follows:
There must be at least one argument, say, Nme , ( the last one ), in the Let …_
_ Public Property Let Xyz ( ___ , ___ , Nme As Vrtyp ) ‘ takes in Nme via code line in normal macro like Let Onj.Xyz = “Myname”
_ … The corresponding Get , below , returns a value in a similar way to how a normal VBA Function does. In the simplest case it returns the value brought in by the Let, ( Nme in this example ). Or it may have some relation to the value taken in by the Let. Or it can be something totally different. Or it can do something else and return no value, just as we sometimes do with a normal VBA Function. In this latter case there is no advantage over a simple procedure text in the class module, which also acts as a method of an object instantiated from the class.
_ Public Property Get Xyz ( ___ , ___ , _ ) As Vrtyp ‘ This works similar to a standard VBA Function.






Another summarising/ introduction explanation is…
We can do the same as in Public Variable Properties 1 in a less direct way, by effectively adding something in the 2 chains of --- reading --- and --- writing --- it.
The syntax is diabolically complicated, for no apparent reason.
This could be a pseudo equivalent syntax

Prublit –-- Let write it(As String) --- CrColor As String --- Get reading it () As String ---
In that pseudo equivalent , we expand those new things shown in the chains into Procedures, in which extra coding can be added to allow for things like data validation, allowing arguments to be passed into them to effect the outcome of the read or write process

Prublit –

-- Let write it(As String)


End Sub —
- CrColor As String -

-- Get reading it () As String


End Sub ---

[DocAElstein]

Special procedures in Class text module

Public Variable Properties 2

Public Variable Properties 2

Simple use – used as in Public Variable Properties 1
The best way to explain these strange pair of special procedure text is to compare their use to do the same as in our simple example of the car color property: Previously our variable was written at the top of the Class car module as variable, Public CrColor As String , and we noted that it was read and write , as demonstrated by our simple macro

Sub MyCarColor()
Dim objCr As Car: Set objCr = New Car ' This is the normal codelines used to typically make an object variable of a particular Class
Let objCr.CrColor = "Yellow" ' "write" to the CrColor variable in objCr
MsgBox Prompt:="I have colored my Car " & objCr.CrColor ' "read" from the CrColor variable in objCr
End Sub


To do the same in a more complicated way, using the special procedure text, we start by removing the Public CrColor As String from the top of the class module, since the property, CrColor will now be defined by the special procedures. – Simply said, it becomes the name of the procedure pair , ( Both have this same name ). We do however, still need to store our string color in a variable. We would typically do this by a Private variable in the instantiated object. Correspondingly, the text in the Class module must be there to make that code line appear in any instantiated instance. So something like this in the class module would do, ( It will become clearer later why exactly we need to do this )
Private PrvteCrColor As String

We know that this variable , as it appears in any instantiated object, is Private , and therefore will not Let itself be assigned via a code line in a normal module of like Let objCr. PrvteCrColor = "Yellow" . we need a way to fill the variable, from a normal code line. That is what the first of the special procedures does, or rather this is one of its most usual uses.

Here is that special procedure, along with the private variable discussed, in the class module, Car

' Public CrColor As String
Private PrvteCrColor As String
Public Property Let CrColor(Clr As String)
Let PrvteCrColor = Clr
End Property

Our previous simple macro in a normal code module will now work, at least initially: Once we instantiate our object, objCr to an instance of Car , the code line, Let objCr.CrColor = "Yellow" will recognise our specially defined property , CrColor
The way the Public Property Let CrColor(Clr As String) then works is possibly is expected: It takes into the variable Clr, the text “Yellow”.
We then assign that string to the private variable: In the code text inside the procedure, we are allowed to use most VBA coding. We have chosen to assign the private variable, PrvteCrColor , the value taken in via Clr

So to review where we are… put this text in the Class module

Option Explicit
' Public CrColor As String
Private PrvteCrColor As String
Public Property Let CrColor(Clr As String)
Let PrvteCrColor = Clr
End Property

Now try running again the below simple macro in any normal code module ( it should error ) :

Sub MyCarColor()
Dim objCr As Car: Set objCr = New Car ' This is the normal codelines used to typically make an object variable of a particular Class
Let objCr.CrColor = "Yellow" ' "write" to the CrColor variable in objCr
MsgBox Prompt:="I have colored my Car " & objCr.CrColor ' "read" from the CrColor variable in objCr
End Sub

No Read No Getting of the Car color.JPG : https://imgur.com/BXqE6Ly
2859
The error , Prohibited use of a property , comes at the attempt to Get at the property.

As you may have guessed, this is where the second main special class text procedure comes in. This takes the syntax and form similar to a normal VBA Function ###. A code line in a normal code module like our objCr.CrColor calls it into working, and if we want it to return a value, then as in the case of a Function we must ,( usually towards the end of the procedure ) , assign the function to the value to be returned, in our case the stored color string in the private variable, like
Let CrColor = PrvteCrColor

So to summarise, here would be the full class module text, and normal coding to do the read and write of our property using the typical special class procedure pair.

In Class text module

' Public CrColor As String
Private PrvteCrColor As String
Public Property Let CrColor(Clr As String)
Let PrvteCrColor = Clr
End Property
Public Property Get CrColor() As String ' this property returns the value, As String
Let CrColor = PrvteCrColor
End Property

In normal code module

Sub MyCarColor()
Dim objCr As Car: Set objCr = New Car ' This is the normal codelines used to typically make an object variable of a particular Class
Let objCr.CrColor = "Yellow" ' "write" to the CrColor variable in objCr
MsgBox Prompt:="I have colored my Car " & objCr.CrColor ' "read" (Get) from the CrColor variable in objCr
End Sub

###( One of the many varied, ( and unfortunately often inconstant ) definitions of a method is that it is a function of an object. We can see how this definition fits in here.)



() …. bit confusing Syntax
The Public Property Get xxxx() As Sxxyyz has a familiar syntax to the standard Function , having either nothing or arguments to be passed inside the ( )
The Public Property Let for some strange reason has the _ As Sxxyyz brought into the ()

One possible explanation for this is that we must somehow be able to refer to the Property value taken in.
The equivalent for the case of the Get is that the thing itself is the return value. In our example we do any referring in a code line like this:
Public Property Get CrColor() As String
Let CrColor = PrvteCrColor
End Property
which is the typical function value giving code line , typically towards the end of a function.

For the case of the Public Property Let we could possibly think it would look like
Public Property Let CrColor() Clr As String
Let PrvteCrColor = Clr
End Property
Although that might seem reasonable, it possibly does not “fit” very well into typical VBA code line structures. A more conventional looking way such as the following . possibly fits better:
Public Property Let (CrColorAs String)
Let PrvteCrColor = Clr
End Property

The end result of this, which we will see a bit more clearly in the next post, is that the number of arguments in the Public Property Let must be at least 1 and can be N+1 where N is the number of augments we have in the Public Property Get
N is the number of arguments we choose to have for the Property.
( Just to remind us: in the simple Public Variable Properties 1 way we don’t have any arguments in this sense. These arguments, N , are one of the extra possibilities that we have due to the more complicated Public Variable Properties 2 way. This is discussed in more detail in the next post


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwhVTFaD469mW9wO194AaABAg.9gJzxwFcnPU9gORqKw5t W_ (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwhVTFaD469mW9wO194AaABAg.9gJzxwFcnPU9gORqKw5t W_)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugyb8nmKKoXvcdM58gV4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugyb8nmKKoXvcdM58gV4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwvvXcl1oa79xS7BAV4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwvvXcl1oa79xS7BAV4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgxvIFArksPprylHXYZ4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgxvIFArksPprylHXYZ4AaABAg)
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg (https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[DocAElstein]

Special procedures in Class text module

Public Variable Properties 2


Extended use of the special Class module text Procedures.
The previous example was a simplified use to show the direct comparison with how they might typically be used to achieve the same as the simple Public variable as property. But we have much more flexibility due to these extra special procedures. Here are just a few notes and examples of some of the extra possibilities

Extra coding text within the procedures
Most normal VBA coding text is accepted inside the procedures. A simple example could be coding inside the Public Property Let to check the data given, and take action, such as a message box with a warning, followed by Exiting if the supplied data is unacceptable ..._
___If Len(Clr) > 20 Then MsgBox prompt:="That is too long for a color word": Exit Property
_... … etc.. etc… etc… The possibilities are almost endless.

Extra arguments
In addition, the syntax allows us to bring in any other variables, which are added before the main variable in the Public Property Let signature line
For example,
Public Property Let CrColor(Optional CrsNme As String, Clr As String)
for syntax reasons we must duplicate the extra information exactly in the paired Public Property Get
Public Property Get CrColor(Optional CrsNme As String) As String
( I am not restricted to Optional argument. The coding works ijn the conventional way regarding the argument definitions: If I did not choose Optional , then I would have to give them in any usage or I would get an error )
These extra arguments appear to be held internally somewhere as a form of pseudo global variable.


Demo example
To demonstrate this: Lets say we want to optionally provide a Car name, when giving the car color.
This would be the slight modification necessary in the class module test:
In class module Car

Private PrvteCrColor As String
Public Property Let CrColor(Optional CrsNme As String, Clr As String)
Let PrvteCrColor = CrsNme & " is " & Clr
End Property
Public Property Get CrColor(Optional CrsNme As String) As String ' this property returns the value, As String
Let CrColor = PrvteCrColor
End Property
We can now add some code lines to our macro in a normal code module to demonstrate the use of the extra lines thus:
In any normal code module

Sub My_CarColor()
Dim objCr As Car: Set objCr = New Car ' This is the normal codelines used to typically make an object variable of a particular Class
Let objCr.CrColor = "Yellow" ' "write" to the CrColor variable in objCr
MsgBox Prompt:="I have colored my Car " & objCr.CrColor ' "read" (Get) from the CrColor variable in objCr

Let objCr.CrColor("Mustang") = "Yellow"
MsgBox Prompt:=objCr.CrColor '
End Sub
The initial code lines work similarly to previously. The extra two lines at the end make use of the new argument possibility.

The first message box gives _ I have colored my Car _ is Yellow
The second message box gives _ Mustang is Yellow

Notes:
_1 ) Argument types
We have chosen arbitrarily in the last example, the extra argument to be Optional in this example, but the general rules associated with procedures and Functions apply , such that , for example , if we had not included the Optional statement in the signature line, then we would have had to give a value for this in both of the Let code lines in the above normal code module macro. Because we used Optional , we were able to use the previous code lines in addition to the new one which makes uses of any supplied argument value

_2) Review of paired nature and syntax structure
_2)(i) We do not have to have a pair, since the absence of one of the other gives us the possibility to restrict the property on the instantiated object to be just either read or write. ( *I personally would usually keep the things in pairs and then ‘comment out as/ if necessary the procedure not required. I would do this as the syntax is a bit tricky/ complicated and easy to mix up the slight differences in the matched pairs )
_2)(ii) *Assuming we use a pair , we can summarise the syntax as follows:
There must be at least one argument, say, Nme , ( the last one ), in the Let …_
_ Public Property Let Xyz ( ___ , ___ , Nme As Vrtyp ) ‘ takes in Nme via code line in normal macro like Let Onj.Xyz = “Myname”
_ … The corresponding Get , below , returns a value in a similar way to how a normal VBA Function does. In the simplest case it returns the value brought in by the Let, ( Nme in this example ). Or it may have some relation to the value taken in by the Let. Or it can be something totally different. Or it can do something else and return no value, just as we sometimes do with a normal VBA Function. In this latter case there is no advantage over a simple procedure text in the class module, which also acts as a method of an object instantiated from the class.
_ Public Property Get Xyz ( ___ , ___ , _ ) As Vrtyp ‘ This works similar to a standard VBA Function.

In simple general terms, to relate to standard VBA things, we might refer to the .Xyz property, as we would, for example to the standard VBA .Value property
_.____



!!! Public Property Set
In line with normal VBA declaration conventions and syntaxes, the word Set is used in place of Let if we are using an object variable type, ( in the above shown here : Vrtyp ) which is a an object
_ Public Property Set Xyz ( ___ , ___ , Obj As VrObject ) ‘
There is nothing new , special, or seemingly particular useful with the Public Property Set.
In the next post, I will do a simple example and possibly add later any example I ever find useful.

[DocAElstein]

For later use

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=QdwDnUz96W0&lc=Ugx3syV3Bw6bxddVyBx4AaABAg (https://www.youtube.com/watch?v=QdwDnUz96W0&lc=Ugx3syV3Bw6bxddVyBx4AaABAg)
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgxsozCmRd3RAmIPO5B4AaABAg.9fxrOrrvTln9g9wr8mv2 CS (https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgxsozCmRd3RAmIPO5B4AaABAg.9fxrOrrvTln9g9wr8mv2 CS)
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg (https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg)
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg.9fz3_oaiUeK9g96yGbAX 4t (https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg.9fz3_oaiUeK9g96yGbAX 4t)
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9g7pczEpcTz (https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9g7pczEpcTz)
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg.9fz3_oaiUeK9g7lhoX-ar5 (https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=UgyT1lo2YMUyZ50bLeR4AaABAg.9fz3_oaiUeK9g7lhoX-ar5)
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gD0AA-sfpl (https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gD0AA-sfpl )
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gECpsAVGbh (https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugx5d-LrmoMM_hsJK2N4AaABAg.9fyL20jCtOI9gECpsAVGbh)
https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg.9g9wJCunNRa9gJGhDZ4R I2 (https://www.youtube.com/watch?v=U76ZRIzBhOA&lc=Ugw6zxOMtNCfmdllKQl4AaABAg.9g9wJCunNRa9gJGhDZ4R I2)
https://www.youtube.com/watch?v=Sh1kZD7EVj0&lc=Ugz-pow-E8FDG8gFZ4l4AaABAg.9f8Bng22e5d9f8hoJGZY-5 (https://www.youtube.com/watch?v=Sh1kZD7EVj0&lc=Ugz-pow-E8FDG8gFZ4l4AaABAg.9f8Bng22e5d9f8hoJGZY-5)
https://www.youtube.com/watch?v=Sh1kZD7EVj0&lc=Ugxev2gQt7BKZ0WYMfh4AaABAg.9f6hAjkC0ct9f8jleOui-u (https://www.youtube.com/watch?v=Sh1kZD7EVj0&lc=Ugxev2gQt7BKZ0WYMfh4AaABAg.9f6hAjkC0ct9f8jleOui-u)
https://www.youtube.com/watch?v=Sh1kZD7EVj0&lc=Ugxg9iT7MPWGBWruIzR4AaABAg (https://www.youtube.com/watch?v=Sh1kZD7EVj0&lc=Ugxg9iT7MPWGBWruIzR4AaABAg)
https://www.youtube.com/watch?v=tzbKqTRuRzU&lc=UgyYW2WZ2DvSrzUKnJ14AaABAg (https://www.youtube.com/watch?v=tzbKqTRuRzU&lc=UgyYW2WZ2DvSrzUKnJ14AaABAg)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)



https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=UywjKEMjSp0&lc=UgxIySxHPqM1RxtVqoR4AaABAg.9edGvmwOLq99eekDyfS0 CD (https://www.youtube.com/watch?v=UywjKEMjSp0&lc=UgxIySxHPqM1RxtVqoR4AaABAg.9edGvmwOLq99eekDyfS0 CD)
https://www.youtube.com/watch?v=UywjKEMjSp0&lc=UgxIySxHPqM1RxtVqoR4AaABAg.9edGvmwOLq99eevG7txd 2c (https://www.youtube.com/watch?v=UywjKEMjSp0&lc=UgxIySxHPqM1RxtVqoR4AaABAg.9edGvmwOLq99eevG7txd 2c)
https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg (https://www.youtube.com/watch?v=SIDLFRkUEIo&lc=UgzTF5vvB67Zbfs9qvx4AaABAg)
https://www.youtube.com/watch?v=9P6r7DLS77Q&lc=UgzytUUVRyw9U55-6M54AaABAg (https://www.youtube.com/watch?v=9P6r7DLS77Q&lc=UgzytUUVRyw9U55-6M54AaABAg)
https://www.youtube.com/watch?v=9P6r7DLS77Q&lc=UgzCoa6tOVIBxRDDDbN4AaABAg (https://www.youtube.com/watch?v=9P6r7DLS77Q&lc=UgzCoa6tOVIBxRDDDbN4AaABAg)
https://www.youtube.com/watch?v=9P6r7DLS77Q&lc=UgyriWOelbVnw4FHWT54AaABAg.9dPo-OdLmZ09dc21kigjmr (https://www.youtube.com/watch?v=9P6r7DLS77Q&lc=UgyriWOelbVnw4FHWT54AaABAg.9dPo-OdLmZ09dc21kigjmr)
https://www.youtube.com/watch?v=363wd2EtQZ0&lc=UgzDQfo5rJqyVwvv2r54AaABAg (https://www.youtube.com/watch?v=363wd2EtQZ0&lc=UgzDQfo5rJqyVwvv2r54AaABAg)
https://www.youtube.com/watch?v=363wd2EtQZ0&lc=UgzHTSka7YppBdmUooV4AaABAg.9cXui6zzkz09cZttH_-2Gf (https://www.youtube.com/watch?v=363wd2EtQZ0&lc=UgzHTSka7YppBdmUooV4AaABAg.9cXui6zzkz09cZttH_-2Gf)
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgxhXnQ-mWYhrHWuM354AaABAg.9bepnegjnRu9iMmBDtf4m1 (https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgxhXnQ-mWYhrHWuM354AaABAg.9bepnegjnRu9iMmBDtf4m1)
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgxFIZ858qf7w_uA9bd4AaABAg.9dKpEpUk3YT9dVEGnka6 yj (https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgxFIZ858qf7w_uA9bd4AaABAg.9dKpEpUk3YT9dVEGnka6 yj)
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=Ugz8oC8iGd6-SPhpaQZ4AaABAg.9bhRt-kPXri9brzh_99JF9 (https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=Ugz8oC8iGd6-SPhpaQZ4AaABAg.9bhRt-kPXri9brzh_99JF9)
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=Ugz8oC8iGd6-SPhpaQZ4AaABAg.9bhRt-kPXri9bsrQIgXb3L (https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=Ugz8oC8iGd6-SPhpaQZ4AaABAg.9bhRt-kPXri9bsrQIgXb3L)
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzFkoI0n_BxwnwVMcZ4AaABAg (https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzFkoI0n_BxwnwVMcZ4AaABAg)
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgxwJDkFskrMW8EpcXt4AaABAg.9bmKMz5-Z1g9bmx0REIz41 (https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgxwJDkFskrMW8EpcXt4AaABAg.9bmKMz5-Z1g9bmx0REIz41)
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgxhXnQ-mWYhrHWuM354AaABAg.9bepnegjnRu9bmyko2YUvQ (https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgxhXnQ-mWYhrHWuM354AaABAg.9bepnegjnRu9bmyko2YUvQ)
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgxwJDkFskrMW8EpcXt4AaABAg.9bmKMz5-Z1g9bmzpPqfLRD (https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgxwJDkFskrMW8EpcXt4AaABAg.9bmKMz5-Z1g9bmzpPqfLRD)
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzZwbV_Y_7UFzHwNBh4AaABAg.9dKb0Vc7MOB9dVK8si3o nt (https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzZwbV_Y_7UFzHwNBh4AaABAg.9dKb0Vc7MOB9dVK8si3o nt)
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=Ugx6Ec_r4kb9EYOVgIt4AaABAg.9dOW613fb8V9dVIJECZI dC (https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=Ugx6Ec_r4kb9EYOVgIt4AaABAg.9dOW613fb8V9dVIJECZI dC)
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgwBho9tBLQ4nPVdYqd4AaABAg.9fWvoBWY3Da9g9cLjhPi az (https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgwBho9tBLQ4nPVdYqd4AaABAg.9fWvoBWY3Da9g9cLjhPi az)
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzZy1NAMBx5Uv4U2cJ4AaABAg.9f0XX-_JaGp9g9bYLMZiIy (https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzZy1NAMBx5Uv4U2cJ4AaABAg.9f0XX-_JaGp9g9bYLMZiIy)
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgyL-xp8IiiahmQ12kJ4AaABAg.9f7xHCpAEx29g9asFhVFfT (https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgyL-xp8IiiahmQ12kJ4AaABAg.9f7xHCpAEx29g9asFhVFfT)
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgxRxyFNNp3WHTzuiJJ4AaABAg.9fFR6ECmXk69g9afNBcS 4Z (https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgxRxyFNNp3WHTzuiJJ4AaABAg.9fFR6ECmXk69g9afNBcS 4Z)
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgwsdMh0FGDfvA249_B4AaABAg.9fLR6FHCIVI9g9aLlUyz og (https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgwsdMh0FGDfvA249_B4AaABAg.9fLR6FHCIVI9g9aLlUyz og)
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgwBho9tBLQ4nPVdYqd4AaABAg.9fWvoBWY3Da9g9_4422N zK (https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgwBho9tBLQ4nPVdYqd4AaABAg.9fWvoBWY3Da9g9_4422N zK)
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=Ugwyy8JXr56HJ8m_od94AaABAg.9gSFgqqJQNV9gTXco41b 5l (https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=Ugwyy8JXr56HJ8m_od94AaABAg.9gSFgqqJQNV9gTXco41b 5l)
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgyS8stMz5B9NrpPrbR4AaABAg.9gOjiS0rs8l9gTYl6Rld pA (https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgyS8stMz5B9NrpPrbR4AaABAg.9gOjiS0rs8l9gTYl6Rld pA)
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgyS8stMz5B9NrpPrbR4AaABAg.9gOjiS0rs8l9gTfhAWU9 ju (https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgyS8stMz5B9NrpPrbR4AaABAg.9gOjiS0rs8l9gTfhAWU9 ju)
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgyS8stMz5B9NrpPrbR4AaABAg.9gOjiS0rs8l9gTfuYQGm Ua (https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgyS8stMz5B9NrpPrbR4AaABAg.9gOjiS0rs8l9gTfuYQGm Ua)
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgyS8stMz5B9NrpPrbR4AaABAg.9gOjiS0rs8l9gTg3AmMP Uc (https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgyS8stMz5B9NrpPrbR4AaABAg.9gOjiS0rs8l9gTg3AmMP Uc)
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgyS8stMz5B9NrpPrbR4AaABAg.9gOjiS0rs8l9gTgEqh5w do (https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgyS8stMz5B9NrpPrbR4AaABAg.9gOjiS0rs8l9gTgEqh5w do)
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgxmUK0S_aZVZWz8-gt4AaABAg.9gLc3DfWfHl9gTZ3y6fL1H (https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgxmUK0S_aZVZWz8-gt4AaABAg.9gLc3DfWfHl9gTZ3y6fL1H)
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzZloYeY2wQr7-xTOh4AaABAg.9gB2bbbs9mB9gTZUkNYI8e (https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzZloYeY2wQr7-xTOh4AaABAg.9gB2bbbs9mB9gTZUkNYI8e)
https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzlM96nGEhW9J1Gpgd4AaABAg.9fmOFVcXZh49gT_8CYeQ gz (https://www.youtube.com/watch?v=tPRv-ATUBe4&lc=UgzlM96nGEhW9J1Gpgd4AaABAg.9fmOFVcXZh49gT_8CYeQ gz)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[DocAElstein]

For later use

[DocAElstein]

Not part of this blog - blog on class stuff to be finished next winter
Alan. April 2020






Further working examples
Disable Excel Close Button/ Control closing
( Post 6 )
Part #5 Disable Excel Close Button/ Control closing Cancel option Event code example
We can do another example as a solution to a question to “Disable Excel Close Button”. This is a good example of using an Event routine which has the Cancel option available

Part #5A Using “normal event routines” or “standard available event routines”
Event routines of the kind required for controlling Workbook events can typically be “found” from a group which we can find in the ThisWorkbook code module
So
Double-click ThisWorkbook to open its code module: then you can write in manually, or better still, evoke from the drop down lists a “Workbook procedure” with the name: Private Sub Workbook_BeforeClose(Cancel As Boolean )
Evoke a Private Sub Workbook_BeforeClose(Cancel As Boolean).JPG : https://imgur.com/T7w6FJN , https://imgur.com/J22uX3g

Private Sub Workbook_BeforeClose(Cancel As Boolean)
' here we can add code to this “already available to us” event coding. Effectively the workbook was Dimed WithEvents somehow to the hidden class or similar of the Workbook object which is effectively also already effectively instantiated as we "open" the Workbook
End Sub I am thinking that in such a code ….. the code has a secret hidden coding at the end which you can’t see. It really looks, pseudo, like this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

' here we can add code to this “already available to us” event coding. Effectively the workbook was Dimed WithEvents somehow to the hidden class or similar of the Workbook object which is effectively also already effectively instantiated as we "open" the Workbook

' Secret code section not visible to us
If Cancel = True Then
'Do nothing and Exit this sub
ElseIf Cancel = False Then
'Close this workbook
End If
End SubSo a solution , such as here http://www.eileenslounge.com/viewtopic.php?f=27&t=31331 , is to give you a way to have the default value of Cancel set to True within that code Private Sub Workbook_BeforeClose(Cancel As Boolean ) . ( I assume that the usual default value of Cancel will be False ).
So, as in that post, we add coding in that event procedure which will make Cancel True by default. This will result in the , Private Sub Workbook_BeforeClose(Cancel As Boolean ) preventing a close of the workbook.
For example, assume we have some global Boolean variable, CanClose , which by default will be False , (False is the default state for a Boolean Variable )
Then we add a code line to the event routine:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Let Cancel = Not CanClose ' With CanClose = True, Cancel will be set to Not True = False

' Secret code section not visible to us
If Cancel = True Then
'Do nothing and Exit this sub
ElseIf Cancel = False Then
'Close this workbook
End If
End Sub

The above coding will mean that if we do nothing else, Cancel will be set to True. So in normal use, such as by the user hitting the close button top right , Excel Close Button.JPG: https://imgur.com/ZvQCF2q , that procedure will prevent/cancel the closing.
Then we could make a procedure, Sub CloseMe() , to be put in a normal module , _..

Public CanClose As Boolean ' This will be False by default


Sub CloseMe()
Let CanClose = True
ThisWorkbook.Close
Let CanClose = False ' I don't know why this is here? I don't think it will ever be done!!! ???
End Sub
_... This will allow you to change the value of Cancel to True: It works as follows: If that procedure, Sub CloseMe(), is run, then it changes the global variable, CanClose , to True, and then the next line, _..
ThisWorkbook.Close
_.. cause the code, Private Sub Workbook_BeforeClose(Cancel As Boolean) to start, and with CanClose equal to True, Cancel being set to Not CanClose , will be set to its usual value of False, and the workbook will close as usual.


That all makes sense, I think.
_._________________________________


In the next post, I will do the equivalent with “application events way” in the typical “class / application way” that is typically done. As was discussed in previous posts, this is a slightly less direct way then necessary, but which is preferred for reasons of good programming practice.

[DocAElstein]

Not part of this blog - blog on class stuff to be finished next winter
Alan. April 2020






Further working examples
Disable Excel Close Button/ Control closing
( Post 6 )
Part #5 Disable Excel Close Button/ Control closing Cancel option Event code example
We can do another example as a solution to a question to “Disable Excel Close Button”. This is a good example of using an Event routine which has the Cancel option available

Part #5A Using “normal event routines” or “standard available event routines”
Event routines of the kind required for controlling Workbook events can typically be “found” from a group which we can find in the ThisWorkbook code module
So
Double-click ThisWorkbook to open its code module: then you can write in manually, or better still, evoke from the drop down lists a “Workbook procedure” with the name: Private Sub Workbook_BeforeClose(Cancel As Boolean )
Evoke a Private Sub Workbook_BeforeClose(Cancel As Boolean).JPG : https://imgur.com/T7w6FJN , https://imgur.com/J22uX3g

Private Sub Workbook_BeforeClose(Cancel As Boolean)
' here we can add code to this “already available to us” event coding. Effectively the workbook was Dimed WithEvents somehow to the hidden class or similar of the Workbook object which is effectively also already effectively instantiated as we "open" the Workbook
End Sub I am thinking that in such a code ….. the code has a secret hidden coding at the end which you can’t see. It really looks, pseudo, like this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

' here we can add code to this “already available to us” event coding. Effectively the workbook was Dimed WithEvents somehow to the hidden class or similar of the Workbook object which is effectively also already effectively instantiated as we "open" the Workbook

' Secret code section not visible to us
If Cancel = True Then
'Do nothing and Exit this sub
ElseIf Cancel = False Then
'Close this workbook
End If
End SubSo a solution , such as here http://www.eileenslounge.com/viewtopic.php?f=27&t=31331 , is to give you a way to have the default value of Cancel set to True within that code Private Sub Workbook_BeforeClose(Cancel As Boolean ) . ( I assume that the usual default value of Cancel will be False ).
So, as in that post, we add coding in that event procedure which will make Cancel True by default. This will result in the , Private Sub Workbook_BeforeClose(Cancel As Boolean ) preventing a close of the workbook.
For example, assume we have some global Boolean variable, CanClose , which by default will be False , (False is the default state for a Boolean Variable )
Then we add a code line to the event routine:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Let Cancel = Not CanClose ' With CanClose = True, Cancel will be set to Not True = False

' Secret code section not visible to us
If Cancel = True Then
'Do nothing and Exit this sub
ElseIf Cancel = False Then
'Close this workbook
End If
End Sub

The above coding will mean that if we do nothing else, Cancel will be set to True. So in normal use, such as by the user hitting the close button top right , Excel Close Button.JPG: https://imgur.com/ZvQCF2q , that procedure will prevent/cancel the closing.
Then we could make a procedure, Sub CloseMe() , to be put in a normal module , _..

Public CanClose As Boolean ' This will be False by default


Sub CloseMe()
Let CanClose = True
ThisWorkbook.Close
Let CanClose = False ' I don't know why this is here? I don't think it will ever be done!!! ???
End Sub
_... This will allow you to change the value of Cancel to True: It works as follows: If that procedure, Sub CloseMe(), is run, then it changes the global variable, CanClose , to True, and then the next line, _..
ThisWorkbook.Close
_.. cause the code, Private Sub Workbook_BeforeClose(Cancel As Boolean) to start, and with CanClose equal to True, Cancel being set to Not CanClose , will be set to its usual value of False, and the workbook will close as usual.


That all makes sense, I think.
_._________________________________


In the next post, I will do the equivalent with “application events way” in the typical “class / application way” that is typically done. As was discussed in previous posts, this is a slightly less direct way then necessary, but which is preferred for reasons of good programming practice.

[DocAElstein]

Not part of this blog - blog on class stuff to be finished next winter
Alan. April 2020



( 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/46682869/prevent-closing-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 ( jgzggtjgjhgjgjg ) 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
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
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
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







Rem Ref
' ' http://www.eileenslounge.com/viewtopic.php?f=27&t=31331
' https://stackoverflow.com/questions/46682869/prevent-closing-of-workbook

[DocAElstein]

Not part of this blog - blog on class stuff to be finished next winter
Alan. April 2020



( 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/46682869/prevent-closing-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 ( jgzggtjgjhgjgjg ) 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
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
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
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







Rem Ref
' ' http://www.eileenslounge.com/viewtopic.php?f=27&t=31331
' https://stackoverflow.com/questions/46682869/prevent-closing-of-workbook

[DocAElstein]

Not part of this blog - blog on class stuff to be finished next winter
Alan. April 2020



( 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/46682869/prevent-closing-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 ( jgzggtjgjhgjgjg ) 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
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
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
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







Rem Ref
' ' http://www.eileenslounge.com/viewtopic.php?f=27&t=31331
' https://stackoverflow.com/questions/46682869/prevent-closing-of-workbook

[DocAElstein]

Not part of this blog - blog on class stuff to be finished next winter
Alan. April 2020



( 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/46682869/prevent-closing-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 ( jgzggtjgjhgjgjg ) 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
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
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
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







Rem Ref
' ' http://www.eileenslounge.com/viewtopic.php?f=27&t=31331
' https://stackoverflow.com/questions/46682869/prevent-closing-of-workbook