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
Workbook object events coding.JPGWorkbook object events Open coding.JPG
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__
Reply With Quote
Bookmarks