Class Stuff: VBA Custom Classes & Objects, Class Modules

Printable View

12-26-2018, 04:27 PM
DocAElstein

Class Stuff: VBA Custom Classes & Objects, Class Modules

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/showth...ange#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
__________________________________________ThisWorkbook
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/20180518...l/classes.aspx
12-26-2018, 04:28 PM
DocAElstein

2 Attachment(s)

Review of the existing Class objects that we already see in the VB Editor

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 )

Code:

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
Attachment 2828 Attachment 2829

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

Code:

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/showth...ication-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.

Code:

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:

Code:

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.

Code:

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

Code:

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…_

Code:

Public LsWkBkGlb1 As Long Public LsWkBkGlb2 As Long

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

Code:

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__
12-26-2018, 04:28 PM
DocAElstein

1 Attachment(s)

Review of the existing Class objects that we already see in the VB Editor

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

Worksheets
The Worksheets
__________________________________________WsCodeName(WstabName)
__________________________________________Sheet2(Sheet2)
__________________________________________Sheet3(MySht)
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..

Code:

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
Attachment 2830 Attachment 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.

Code:

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 )

Code:

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…_

Code:

Option Explicit Public WsGlob1 As String Public WsGlob2 As String

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

Code:

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.
12-26-2018, 04:28 PM
DocAElstein

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

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/showthrea...ication-Events ) )

Ref
** https://www.myonlinetraininghub.com/...-module#p19192
12-26-2018, 04:29 PM
DocAElstein

Custom Classes and Custom objects. General and UserForms

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.
12-26-2018, 04:32 PM
DocAElstein

2 Attachment(s)

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
Attachment 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
Attachment 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 / Seting 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.
12-26-2018, 04:32 PM
DocAElstein

5 Attachment(s)

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 :

Code:

Public CrColor As String

ClsCarColor.jpg : https://imgur.com/NKiwsVt http://i.imgur.com/NKiwsVt.jpg
Attachment 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

Code:

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:

Code:

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
Attachment 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

Code:

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
Attachment 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,

Code:

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:

Code:

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:

Code:

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
Attachment 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
12-26-2018, 04:33 PM
DocAElstein

1 Attachment(s)

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
Attachment 3530 :

Code:

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

Code:

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

Code:

Prublit – -- Let write it(As String) End Sub — - CrColor As String - -- Get reading it () As String End Sub ---
12-26-2018, 04:33 PM
DocAElstein

1 Attachment(s)

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

Code:

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

Code:

' 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

Code:

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 ) :

Code:

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
Attachment 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

Code:

' 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

Code:

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/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgzMCQUIQgrbec400jl4AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=UgwhVTFaD469mW9wO194AaABAg. 9gJzxwFcnPU9gORqKw5tW_
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=UgxvIFArksPprylHXYZ4AaABAg
https://www.youtube.com/watch?v=f7xZivqLZxc&lc=Ugxq4JHRza_zx3sz0fx4AaABAg
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
12-26-2018, 04:33 PM
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

Code:

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

Code:

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.
12-26-2018, 04:35 PM
DocAElstein

For later use
12-26-2018, 04:35 PM
DocAElstein

For later use
12-26-2018, 04:35 PM
DocAElstein

Disable Excel Close Button/ Control closing

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

Code:

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:

Code:

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 Sub

So 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:

Code:

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 , _..

Code:

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.
12-26-2018, 04:35 PM
DocAElstein

Disable Excel Close Button/ Control closing

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

Code:

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:

Code:

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 Sub

So 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:

Code:

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 , _..

Code:

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.
12-26-2018, 04:35 PM
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/...ng-of-workbook ) I choose the name CloseHelper
In this class code module we need the main ( second ) object which we want , which refers to the open Excel application in front of us. Lets use the variable , ClsLisWb , for this object. It is intended that this object "monitors closing of workbooks.
From the first post of this thread ( 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

Code:

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

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

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

Code:

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

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

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

Code:

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

Rem Ref
' ' http://www.eileenslounge.com/viewtopic.php?f=27&t=31331
' https://stackoverflow.com/questions/...ng-of-workbook
12-26-2018, 04:35 PM
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/...ng-of-workbook ) I choose the name CloseHelper
In this class code module we need the main ( second ) object which we want , which refers to the open Excel application in front of us. Lets use the variable , ClsLisWb , for this object. It is intended that this object "monitors closing of workbooks.
From the first post of this thread ( 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

Code:

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

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

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

Code:

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

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

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

Code:

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

Rem Ref
' ' http://www.eileenslounge.com/viewtopic.php?f=27&t=31331
' https://stackoverflow.com/questions/...ng-of-workbook
12-26-2018, 04:35 PM
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/...ng-of-workbook ) I choose the name CloseHelper
In this class code module we need the main ( second ) object which we want , which refers to the open Excel application in front of us. Lets use the variable , ClsLisWb , for this object. It is intended that this object "monitors closing of workbooks.
From the first post of this thread ( 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

Code:

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

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

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

Code:

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

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

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

Code:

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

Rem Ref
' ' http://www.eileenslounge.com/viewtopic.php?f=27&t=31331
' https://stackoverflow.com/questions/...ng-of-workbook
12-26-2018, 04:35 PM
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/...ng-of-workbook ) I choose the name CloseHelper
In this class code module we need the main ( second ) object which we want , which refers to the open Excel application in front of us. Lets use the variable , ClsLisWb , for this object. It is intended that this object "monitors closing of workbooks.
From the first post of this thread ( 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

Code:

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

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

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

Code:

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

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

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

Code:

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

Rem Ref
' ' http://www.eileenslounge.com/viewtopic.php?f=27&t=31331
' https://stackoverflow.com/questions/...ng-of-workbook
05-22-2024, 08:38 PM
DocAElstein

later
05-22-2024, 08:38 PM
DocAElstein

later again
05-22-2024, 08:39 PM
DocAElstein

5 Attachment(s)

This is post https://www.excelfox.com/forum/showt...ll=1#post24163 https://www.excelfox.com/forum/showt...age3#post24163

Some Extra notes to go with this main forum post
https://eileenslounge.com/viewtopic....ffd8b7#p317533

That forum post is one of those increasing occurrences recently of a one off post answering a question. There relevance to the Thread and their quality varies a lot. This one was fairly relevant but more of a quick example files perhaps picked up from somewhere, and I doubt we will ever see the OP again and I doubt he knows much about what he posted.
But it gave me a chance to try and get some Class ideas of mine a bit better understood

It is probably helpful to download the uploaded file, and have it open it in Excel, whilst working through the next few posts

First what is it all about
This appears to be an example of my suggested second of two main areas that class things may be used ……_ 2 The area where I have mostly seen …… This usually involves tackling the area of WithEvents …. …

I started with a new virgin simple .xls file, and put a Class Module in it
Attachment 5835

I went through copying across your ( OP SamPi ) coding and changing it a bit and adding some 'comments to help me explain what was going on.
(By the way, What actually happens (should happen) is that …
Provided you first open the main file with all the class stuff in it, ( that was your (OP SamPi) OpenExcelFile.xlsm , and it is now my WatchMyFileOpenings.xls), then after that , if you open a file with the name test.csv , then, a message box should pop up and tell you about it )

First the Class module stuff.
This is my slightly modified version of your (OP SamPi) coding put inside the Class module.
I gave the class module the name FileOpenWatcher , but that does not mean much. Initially, before we put text of coding into it, its just like a blank sheet of paper.
Finally as below, it’s not much more. It is just like text on a bit of paper. Whilst not completely incorrect, it might be dangerous to envisage this as a template. This might cause one to think of it as a thing to be copied and used over and over again. Later with hindsight, this conception can be misleading. It is better to think of it as some notes in your pocket on a piece of paper, that you work from to make something over and over again. Think of there being other text notes and information, which we personally do not concern ourselves with, and when we use all the information, the text of the coding is just one thing we use. Generally it is a bad idea to consider copying of a class or coding in a class module. It can be misleading to the actual underlying concepts.
The class module is generally designed to be used over and over again, in the similar way that a function is. In this example we only use it once. Here is the full text. Perhaps that text could be thought better conceptually as like that on a Stamp

Class module, FileOpenWatcher: ( https://i.postimg.cc/TPPmLX2K/File-O...ode-module.jpg ) of WatchMyFileOpenings.xls

Code:

' This is not coding that will ever be done. It is like a stamp from which we effectively make the coding to put in an actual class object module: This is a Class module. Not an actual class object module Option Explicit Public WithEvents ExApp As Excel.Application ' This makes ExApp a variable/ object that some how is like a running Excel, and also the WithEvents means it has access to all those codings that it has that kick off when something happens ' This and the next macro is the text of coding we need to tell us if we open the file "test.csv" Private Sub ExApp_WorkbookOpen(ByVal Wb As Workbook) ' This will become a property in a final Instanciated object, amnd I intend to use it Dim s As String Let s = Wb.Name If s = "test.csv" Then Call MyMacro End Sub Sub MyMacro() ' This is available also in intellisense to a final instanciated object, although in this example I will not use it, - its calles from the routine above. Splitting the two is not done for any particular reason MsgBox Prompt:="You just opened test.csv" End Sub

https://i.postimg.cc/4KMkxb6y/Class-...en-Watcher.jpg
That coding is not actual coding that will ever be done. It is like a blue print, or a Stamp , from which an actual object module will be made with that coding in it, (by an instanciate later in coding in another code module –
In other words, something looking just like that, could be considered to appear, Stamped out as it were, by executing the following code lines
Dim MeWatcher As FileOpenWatcher
Private Sub Workbook_Open() ' This is done when this file opens
Set MeWatcher = New We must image this appearing, as we do not see it)
The full coding(text) window shown above is in a true Class module, as I tried to explain in my previous post , so its just like text on a piece of paper, and on its own pretty useless.

Lets explain the build up of that text (which will later be coding):
The very first line, Public WithEvents ExApp As Excel.Application , does something along the lines of making the variable, ExApp ,somehow be of a type like the Excel you are using, but not specifically the actual one you are using.
The extra WithEvents means it gets all the usual Excel event codings so we can tap into them, ( and for convenience, after you make this line we get them extra things listed in the drop down list of the Class module
Attachment 5836
https://i.postimg.cc/KzKv0fGL/Drop-d...ith-Events.jpg
We have chosen the event coding that kicks in when a workbook is opened, as that is what we need for our particular example
As generally with event coding, it can be considered that it is always there and runs when the event occurs ( the event of opening of a workbook in this example ), but we can add coding into it, so that this added coding also kicks in when the event occurs ( the event of opening of a workbook in this example )

In this case the coding is fairly simple, to do a message box pop up, when a file opens with the name test.csv

Code:

' This and the next macro is the sort of coding we need to tell us if we open the file "test.csv" Private Sub ExApp_WorkbookOpen(ByVal Wb As Workbook) ' This will become a property in a final Instanciated object, amnd I intend to use it Dim s As String Let s = Wb.Name If s = "test.csv" Then Call MyMacro End Sub Sub MyMacro() ' This is available also in intellisense to a final instanciated object, although in this example I will not use it, - its calles from the routine above. Splitting the two is not done for any particular reason MsgBox Prompt:="You just opened test.csv" End Sub

So that’s it for the class module. It’s only half the full story. The second half is in the next post

A last important thing again to remember: This is just like a piece of paper with a text of coding on it that will never run itself.
We may "copy" that text and put it somewhere so as to use as "real" coding to be run.
The "copy", is effectively done by these 2 sort of code lines later,
Dim MeWatcher As FileOpenWatcher
Private Sub Workbook_Open() ' This is done when this file opens
Set MeWatcher = New
The use of "copy" is perhaps a bit unwise, better is to imagine it stamped out

With that we are finally doing is instantiating: A class describes the variables, properties, procedures, and events of an object (but a class is not an object itself; an object comes into existence when a class is instantiated)
05-24-2024, 03:00 AM
DocAElstein

This is post https://www.excelfox.com/forum/showt...ll=1#post24165 https://www.excelfox.com/forum/showt...age3#post24165

So I have added text to the existing text of an available _WorkbookOpen coding, and that text is identical to the text of the sort of coding that would do the business of checking to see if I opened test.csv , and tells me about it if I did/do
But important to remember That the text in coding format in last post is not coding that will ever be done. It is just the blue print text , like text on a piece of paper, or on a stamp
We need to make an actual object, A class object module, and put a copy of this coding in it. Better said, we want to stamp that text out where it will become VBA source coding
That is this next bit

Make the my watcher thing
It is convenient for this thing to be made when I open my main file, WatchMyFileOpenings.xls
So, just for that reason, I put the following coding in the ThisWorkbook code module of my main file, WatchMyFileOpenings.xls

ThisWorkbook: code module of WatchMyFileOpenings.xls

Code:

Option Explicit Dim MeWatcher As FileOpenWatcher ' This must go here, not in the coding below, or else the variable will not exist after the coding below ends Private Sub Workbook_Open() ' This is done when this file opens Set MeWatcher = New FileOpenWatcher ' Make the object MeWatcher which is something like a running Excel Set MeWatcher.ExApp = Excel.Application ' I think this makes the obkect, MeWatcher, the actual running Excel End Sub

Attachment 5837

The main thing will be in the variable I choose to call MeWatcher In other words that MeWatcher is the variable pointing to an object thing which actually will be somewhere in existence, monitoring when a file might be opened, and if that happens, it checks to see if it is test.csv , and if it is, it tells me with a pop up message box

Within the coding that kicks in when I open the file, WatchMyFileOpenings.xls , the two lines make that object MeWatcher
First Set MeWatcher = New FileOpenWatcher makes the object of the type we want, but its missing a few things. A Parallel could perhaps be a worksheet object, but with no specific Name, position, etc.,so we are not quite there, and we would not yet see, for example, a worksheet code module appear in the VB Editor left explorer window, or maybe we would, but it would look empty
At the moment the object is a sort of Excel Application type, but not a specific one .
The next line Set MeWatcher.ExApp = Excel.Application , will, I think, do the final job, making our actual running Excel the final object in the variable MeWatcher, or perhaps more accurately said , the application in that variable is the final object in the variable MeWatcher
What effectively then happens could be regarded as a class object code module being made and appearing looking somewhat similar to the worksheets object code modules or ThisWorkbook as seen in the VB Editor left explorer windows, like this , and that has a copy of the Class module coding in it.
https://i.postimg.cc/PCh7v2pT/Me-Wat...ode-module.jpg
So we could reasonably perceive that we have something like this appearing, which if it did, would take a position and "level" in the explorer view or OOP hierarchy, in the VB Editor , similar to that of the worksheets and the ThisWorkbook things
This is what it could be perceived to look like, just a perception suggestion from me

Object module, MeWatcher ( perception suggestion )

Code:

' This is , or can reasonably be perceived to be, the "real" coding that is done Option Explicit Public WithEvents ExApp As Excel.Application ' This makes ExApp a variable/ object that some how is like a running Excel, and also the WithEvents means it has access to all those codings that it has that kick off when something happens ' This and the next macro is the sort of coding we need to tell us if we open the file "test.csv" Private Sub ExApp_WorkbookOpen(ByVal Wb As Workbook) ' This will become a property in a final Instanciated object, amnd I intend to use it Dim s As String Let s = Wb.Name If s = "test.csv" Then Call MyMacro End Sub Sub MyMacro() ' This is available also in intellisense to a final instanciated object, although in this example I will not use it, - its calles from the routine above. Splitting the two is not done for any particular reason MsgBox Prompt:="You just opened test.csv" End Sub

That is , or can reasonably be perceived to be, the "real" coding that is done. And by virtue of the code line somewhere else of , Set MeWatcher.ExApp = Excel.Application , the variable , or object, MeWatcher , is effectively my running Excel

Unfortunately, that does not happen, or rather it does, or could be perceived to happen, but Microsoft just neglected to make it appear for us, so we must imagine it does.

At this point we have an object, MeWatcher, of the sort, FileOpenWatcher , which we want, which is one that is a bit like a running Excel.
That last code line, Set MeWatcher.ExApp = Excel.Application makes the variable in our new class object code module, (the one we can’t see), ExApp the actual running Excel

That all takes a few careful readings through to get it

The point is with all this class stuff, is that it is just that little bit more complicated such that you must think a few things carefully through to understand. A simple short explanation often does more harm than good, JIMHO, since, for example, telling you as people often do, that the worksheets code module is a class module , is wrong…. At least I think….. for now: So far, - the best experts in this field, won’t comment on this statement…. and I have asked many times… well actually, not quite, I am tangling with Mike about it currently from about here …. https://eileenslounge.com/viewtopic....317564#p317564
See also the next post

Alan
05-24-2024, 03:00 AM
DocAElstein

Here are some notes from the discussions started just after the last post, specifically these discussions, here:
https://eileenslounge.com/viewtopic....317564#p317564
https://eileenslounge.com/viewtopic....317565#p317565
https://eileenslounge.com/viewtopic....317573#p317573
https://eileenslounge.com/viewtopic....317574#p317574
https://eileenslounge.com/viewtopic....317579#p317579
https://eileenslounge.com/viewtopic....317582#p317582
https://eileenslounge.com/viewtopic....317583#p317583
https://eileenslounge.com/viewtopic....317591#p317591
https://eileenslounge.com/viewtopic....317598#p317598
https://eileenslounge.com/viewtopic....317606#p317606

Whilst letting all those discussion go through my head, and many more beside, I realised along the way, that in fact I could do the example of the last two posts above, without a class module. So in the next few post I does that
06-01-2024, 07:24 PM
DocAElstein

5 Attachment(s)

https://www.excelfox.com/forum/showt...ll=1#post24189

Background to getting a solution without a class module
( Background to getting non class module solution to this solution
https://eileenslounge.com/viewtopic....317547#p317547
https://www.excelfox.com/forum/showt...age3#post24163
https://www.excelfox.com/forum/showt...age3#post24165 )

Lets review what we did in the two posts using the Class module

We inserted a Class module. We put text, in coding form, into/ onto it. Think of that code module and text now as text on a stamp The first main line of that text,
__| Public WithEvents ExApp As Excel.Application
, is of the form of coding which declares a variable of the type to point to a memory location capable of dealing the Excel Application. In Layman terms it can be thought of as somehow organising something to "be" like the Excel we are using. The WithEvents makes available to that "thing", or would if it was actual coding, the event coding associated with Excel.
From those event codings, we are interested finally in the event of when a file is opened. So the next text below is that of coding that organises that a message box pops up if a file by the name of test.csv is opened

Code:

' This and the next macro is the text of coding we need to tell us if we open the file "test.csv" Public Sub ExApp_WorkbookOpen(ByVal Wb As Workbook) ' This will become a property in a final Instanciated object, and I intend to use it Dim s As String Let s = Wb.Name If s = "test.csv" Then Call MyMacro End Sub Sub MyMacro() ' This is available also in intellisense to a final instanciated object, although in this example I will not use it, - its calls from the routine above. Splitting the two is not done for any particular reason MsgBox Prompt:="You just opened test.csv" End Sub

Here below is the final complete text of the Class module "Stamp"

Class module, FileOpenWatcher

Code:

' This is not coding that will ever be done. It is like a stamp from which we effectively make the coding to put in an actual class object module: This is a Class module. Not an actual class object module Option Explicit Public WithEvents ExApp As Excel.Application ' This makes ExApp a variable/ object that some how is like a running Excel, and also the WithEvents means it has access to all those codings that it has that kick off when something happens ' This and the next macro is the text of coding we need to tell us if we open the file "test.csv" Private Sub ExApp_WorkbookOpen(ByVal Wb As Workbook) ' This will become a property in a final Instanciated object, and I intend to use it Dim s As String Let s = Wb.Name If s = "test.csv" Then Call MyMacro End Sub Sub MyMacro() ' This is available also in intellisense to a final instanciated object, although in this example I will not use it, - its calles from the routine above. Splitting the two is not done for any particular reason MsgBox Prompt:="You just opened test.csv" End Sub

One advantage of this class way of doing it is that this stamp of text can be used over and over again, along similar lines to why we use functions. Using the idea of the Stamp, this stamp is effectively used by these instanciating code lines:
Dim MeWatcher As FileOpenWatcher
Private Sub Workbook_Open()
Set MeWatcher = New FileOpenWatcher

In other words an instanciation is done, then , as a result of doing that instanciation , MeWatcher becomes a variable pointing to a thing/ object capable of doing something when a file is opened. I would argue that we have something, which we cannot see, but which could be regarded as associated with the object pointed to by MeWatcher. Another Layman perception idea would be that we have this invisible object code module with the name MeWatcher, looking like this https://i.postimg.cc/wM2Wn8zT/Object...suggestion.jpg

Our task in the next posts is to do the same without a use of a class module.
So let’s think about this …..
According to the Alan theory of class modules and object modules, after the instanciation, we are left with an object module, which can be thought of as identical looking to the text of the class module and it looks like that perception suggestion, although we cant see it, not like what as we can see similar things like the instanciated ThisWorkbook object code module and the worksheets object code modules.
Here below is that perception suggestion again.

Object module, MeWatcher ( perception suggestion )

Code:

' This is , or can reasonably be perceived to be, the "real" coding that is done Option Explicit Public WithEvents ExApp As Excel.Application ' This makes ExApp a variable/ object that some how is like a running Excel, and also the WithEvents means it has access to all those codings that it has that kick off when something happens ' This and the next macro is the sort of coding we need to tell us if we open the file "test.csv" Private Sub ExApp_WorkbookOpen(ByVal Wb As Workbook) ' This will become a property in a final Instanciated object, amnd I intend to use it Dim s As String Let s = Wb.Name If s = "test.csv" Then Call MyMacro End Sub Sub MyMacro() ' This is available also in intellisense to a final instanciated object, although in this example I will not use it, - its calles from the routine above. Splitting the two is not done for any particular reason MsgBox Prompt:="You just opened test.csv" End Sub

That is , or can reasonably be perceived to be, the "real" coding that is done, (or perhaps more accurately the VBA source coding used finally in the compiled coding which actually runs). And by virtue of the code line somewhere else of , Set MeWatcher.ExApp = Excel.Application , the variable , or object, MeWatcher , is effectively my running Excel

Now here is the key to getting the task done
As I just said, …

Quote:

Originally Posted by DocAElstein

According to the Alan theory of class modules and object modules, after the instanciation, we are left with an object module, which can be thought of as identical looking to the text of the class module and it looks like ….. although we cant see it, as we can see things like the instanciated ThisWorkbook object code module and the worksheets object code modules

Assuming, as my theory does, that the perceived object module from an instantiation from a custom class and the ThisWorkbook object code module and the worksheets object code modules are very similar things, possibly even from some object module class then their syntax, and OLE Automation entry protocols will be consistent without conflicts in any dependency chains allowing assynchronous gateways to the event codings. In layman terms this means
_ wot works when written in our class modules after instanciation
_ does as well in the worksheets and ThisWorkbook code modules

If we do a quick experiment with a ThisWorkbook module we see perhaps that we may be on to something…
Here is the default event coding available
https://i.postimg.cc/wvydVYRq/Defaul...ook-coding.jpg
Attachment 5897

Now if we add one of this WithEvents declarations, it looks promising
https://i.postimg.cc/k5hmKg4Y/Added-...ion-coding.jpg https://i.postimg.cc/Jzxm7qWL/Added-...kbook-Open.jpg
Attachment 5895 Attachment 5896

What I am saying here is that the WithEvents declaration seems to result in the same extra options in the dropdowns of the ThisWorkbook module as we have seen appearing in the class module dropdowns after the same WithEvents declaration.

We see the same in any worksheet module….
Before, as default
https://i.postimg.cc/hvC2b0JT/Worksh...nt-codings.jpg
Attachment 5898

, after adding an WithEvents declaration
https://i.postimg.cc/j54gCTt8/Worksh...ith-Events.jpg
Attachment 5899

So , based on my theories, the main thing to think about to get a solution without a class module, is two things:-
_1) Given that we have already have modules which I suggest cone from the same or similar strain/ origin/ class as my perception suggestion , then we can simply put the coding of the perception suggestion in any of the object modules.
_2) We can forget the instantiation: The object variable used will not be the MeWatcher of my perception suggestion object, but the name of the object module used.

So, we will do that in the next two posts.
06-01-2024, 07:24 PM
DocAElstein

1 Attachment(s)

https://www.excelfox.com/forum/showt...ll=1#post24194
https://www.excelfox.com/forum/showt...age3#post24194

Solution without Class module using a worksheet object code module

This post describes what to do. Justification is given a couple of posts back

We need to
_(i) Put the coding (which is given by either the text from the class module , or the coding from the perceptions suggestion ), in any worksheet code module
_(ii) Remove the two line instanciation coding.
( _(iii) We still need to assign the WithEvents of the Excel application object variable to the current running Excel )

Lets do the last two (ii) and (iii), first
We had this next codi9ng below, in the ThisWorkbook code module, where we conveniently had the instanciation added to the available Workbook_Open() coding, to get, in effect, perceptionally speaking, the VBA source code which we don’t see

Object module, MeWatcher

Code:

Dim MeWatcher As FileOpenWatcher ' This must go here, not in the coding below, or else the variable will not exist after the coding below ends Private Sub Workbook_Open() ' This is done when this file opens Set MeWatcher = New FileOpenWatcher ' Make the object MeWatcher which is something like a running Excel Set MeWatcher.ExApp = Excel.Application ' I think this makes the object, MeWatcher, the actual running Excel End Sub

( The object variable, MeWatcher, needs to be outside the routine, so that it does not "vanish" when the routine ends )

We are proposing/ suggesting that we don’t need the two instantiation lines. So that reduces to

Code:

Private Sub Workbook_Open() ' This is done when this file opens Set SheetX.ExApp = Excel.Application ' I think this makes the object, ExApp, the actual running Excel End Sub

( The SheetX. will become clear as we go on now to tackle the …._(i) Put the coding (which is given by either the text from the class module , or the coding from the perceptions suggestion ), in any worksheet code module ….

For (i) it is very simple. We are assuming that the a wiorksheet code module is of a similar source/ origin etc. as our perceived instanciated thing holding VBA source code which we don’t see , ( which is identical to the "stamp" of text of the class module ). The only difference is that it does not have the name of the instanciated object variable, MeWatcher, but rather a reference to / name of a worksheet
So what we have seen now many times as VBA source coding, or the text from the class "stamp", is all we need , this -

Code:

Public WithEvents ExApp As Excel.Application ' This makes ExApp a variable/ object that some how is like a running Excel, and also the WithEvents means it has access to all those codings that it has that kick off when something happens ' This and the next macro is the text of coding we need to tell us if we open the file "test.csv" Public Sub ExApp_WorkbookOpen(ByVal Wb As Workbook) ' This will become a property in a final Instanciated object, and I intend to use it Dim s As String Let s = Wb.Name If s = "test.csv" Then Call MyMacro End Sub Sub MyMacro() ' This is available also in intellisense to a final instanciated object, although in this example I will not use it, - its calls from the routine above. Splitting the two is not done for any particular reason MsgBox Prompt:="You just opened test.csv" End Sub

So finally, for the solution using a worksheets code module , we have coding in the ThisWorkbook module, and a worksheets code module

Object module, ThisWorkbook

Code:

Option Explicit Private Sub Workbook_Open() ' This is done when this file opens ' Set Sheet1.ExApp = Excel.Application ' I think this makes the object, ExApp, the actual running Excel 'or Set Worksheets("Sheet1").ExApp = Excel.Application End Sub

Object module, Sheet1

Code:

Option Explicit Public WithEvents ExApp As Excel.Application ' This makes ExApp a variable/ object that some how is like a running Excel, and also the WithEvents means it has access to all those codings that it has that kick off when something happens ' This and the next macro is the text of coding we need to tell us if we open the file "test.csv" Public Sub ExApp_WorkbookOpen(ByVal Wb As Workbook) ' This will become a property in a final Instanciated object, and I intend to use it Dim s As String Let s = Wb.Name If s = "test.csv" Then Call MyMacro End Sub Sub MyMacro() MsgBox Prompt:="You just opened test.csv" End Sub
06-01-2024, 07:24 PM
DocAElstein

1 Attachment(s)

https://www.excelfox.com/forum/showt...ll=1#post24196
https://www.excelfox.com/forum/showt...age3#post24196

Solution without Class module using ThisWorkbook object code module

This post describes what to do. Justification is given in the last post

We need to
_(i) Put the coding (which is given by either the text from the class module , or the coding from the perceptions suggestion ), in the ThisWorkbook code module
_(ii) Remove the two line instanciation coding.
( _(iii) We still need to assign the WithEvents of the Excel application object variable to the current running Excel )

Lets do the last two (ii) and (iii), first
We had this, in the ThisWorkbook code module, where we conveniently had the instanciation added to the available [FONT=Courier New]Workbook_Open()[7FONT] coding, to get, in effect, perceptionally speaking, the VBA source code which we don’t see

Object module, MeWatcher

Code:

Dim MeWatcher As FileOpenWatcher ' This must go here, not in the coding below, or else the variable will not exist after the coding below ends Private Sub Workbook_Open() ' This is done when this file opens Set MeWatcher = New FileOpenWatcher ' Make the object MeWatcher which is something like a running Excel Set MeWatcher.ExApp = Excel.Application ' I think this makes the object, MeWatcher, the actual running Excel End Sub

( The object variable, MeWatcher, needs to be outside the routine, so that it does not "vanish" when the routine ends )

We are proposing/ suggesting that we don’t need the two instantiation lines. So that reduces to

Code:

Private Sub Workbook_Open() Set Me.ExApp = Excel.Application End Sub

( The Me. will become clear as we go on now to tackle the …._(i) Put the coding (which is given by either the text from the class module , or the coding from the perceptions suggestion ), in the ThisWorkbook code module ….

For (i) it is very simple. We are assuming that the ThisWorkbook code module is of a similar source/ origin etc. as our perceived instanciated thing holding VBA source code which we don’t see , ( which is identical to the "stamp" of text of the class module ). The only difference is that it does not have the name of the instanciated object variable, MeWatcher, but rather ThisWorkbook and within it , as it were, we can also use Me.
So what we have seen now many times as VBA source coding, or the text from the class "stamp", is all we need , this -

Code:

Public WithEvents ExApp As Excel.Application ' This makes ExApp a variable/ object that some how is like a running Excel, and also the WithEvents means it has access to all those codings that it has that kick off when something happens ' This and the next macro is the text of coding we need to tell us if we open the file "test.csv" Public Sub ExApp_WorkbookOpen(ByVal Wb As Workbook) ' This will become a property in a final Instanciated object, and I intend to use it Dim s As String Let s = Wb.Name If s = "test.csv" Then Call MyMacro End Sub Sub MyMacro() ' This is available also in intellisense to a final instanciated object, although in this example I will not use it, - its calls from the routine above. Splitting the two is not done for any particular reason MsgBox Prompt:="You just opened test.csv" End Sub

So finally, for the solution using the ThisWorkbook module , we have just coding in the ThisWorkbook module, that’s it!

Object module, ThisWorkbook

Code:

' https://www.excelfox.com/forum/showthread.php/2451-Class-Stuff-VBA-Custom-Classes-amp-Objects-Class-Modules?p=24196&viewfull=1#post24196 Option Explicit Public WithEvents ExApp As Excel.Application ' This makes ExApp a variable/ object that some how is like a running Excel, and also the WithEvents means it has access to all those codings that it has that kick off when something happens ' This and the next macro is the text of coding we need to tell us if we open the file "test.csv" Public Sub ExApp_WorkbookOpen(ByVal Wb As Workbook) ' This will become a property in a final Instanciated object, and I intend to use it Dim s As String Let s = Wb.Name If s = "test.csv" Then Call MyMacro End Sub Sub MyMacro() ' This is available also in intellisense to a final instanciated object, although in this example I will not use it, - its calls from the routine above. Splitting the two is not done for any particular reason MsgBox Prompt:="You just opened test.csv" End Sub Private Sub Workbook_Open() Set Me.ExApp = Excel.Application ' or Set ThisWorkbook.ExApp = Excel.Application End Sub
06-02-2024, 01:49 PM
DocAElstein

Later
06-02-2024, 01:49 PM
DocAElstein

Later
06-02-2024, 01:49 PM
DocAElstein

Later
06-02-2024, 01:49 PM
DocAElstein

Later

All times are GMT +5. The time now is 10:36 AM.

ExcelFox is Not Associated With Microsoft®