Class Stuff: VBA Custom Classes & Objects, Class Modules

[DocAElstein]

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

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 MyWkSht Sheet3.JPG

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

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

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

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

Option Explicit
Dim WsGlob1 As String
Private WsGlob2 As String
Sub WhatAmI()
 Let WsGlob1 = Me.Name
 MsgBox prompt:="The Tab name of this worksheet is  " & WsGlob1
 Let WsGlob2 = Me.CodeName
 MsgBox prompt:="The code name of this worksheet is  " & WsGlob2
End Sub

If we change the declarations to Public like this…_

Option Explicit
Public WsGlob1 As String
Public WsGlob2 As String

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

Sub WhatAmI() ' In any code module
 Let Sheet3.WsGlob1 = Worksheets("MyWhSht").Name
 Let Sheet3.WsGlob1 = Sheet3.Name
 MsgBox prompt:="The Tab name of this worksheet is  " & Sheet3.WsGlob1
 Let Sheet3.WsGlob2 = Worksheets("MyWhSht").CodeName
 Let Sheet3.WsGlob2 = Sheet3.CodeName
 MsgBox prompt:="The code name of this worksheet is  " & Sheet3.WsGlob2
End Sub

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