Some notes to support other posts: A brief introduction to objects and class objects in VBA
This is to support a Tips and Tutorial on advanced Event coding. ( http://www.excelfox.com/forum/showth...ication-Events ) It is difficult to look at advanced events coding without hitting some fundamental ideas behind objects and class objects in VBA.
This thing, "Tabelle2" , ( https://imgur.com/hHHdxyD ) .._
Attachment 2114 , _.. could loosely be described as a ""worksheet" object with a code in it"…
Right mouse click Or double click in VBA explorer Project window to get code module.JPG : https://imgur.com/gsz6s2NCode:Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then MsgBox prompt:="You just changed the value in the first cell in worksheet " & Me.Name & " in the Workbook " & Me.Parent.Name End Sub
That coding results in you getting a simple message if you change the value in the first worksheet cell :
Automatic message after change value in first cell .JPG : https://imgur.com/WFINlbq , https://imgur.com/hHHdxyD
_ The actual object: where what how to get at or change
_ what precisely/ physically any object is, is not precisely defined. Consequently what we actually use, and where, in order to "use" an object is somewhat abstract and can be different at different times or for different purposes. As example, In the code example above we were using the second worksheet in a workbook. That worksheet object could "physically" be described as the spreadsheet we "see" when clicking on the second tab. Writing into cells could be described as using the worksheet object. But you will see that in the simple routine above, we referred to the second worksheet object using ".Me" ( Me.JPG : https://imgur.com/R5nJ4n9 ). This is because the code module and code window shown in the screenshots above is also often considered to be that worksheet object. This should confuse you. The concept is not precise. I think possibly in the last 20 years there were too many people employed in the computer industry who had nothing to do. They may have gone a bit mad in their boredom.
_ Class. Class object
_ If we "go back up" the programming hierarchy from, say a worksheet, then we would often have a class object which could / is sometimes seen as actually physically being a Class code module. So that would be a code module similar "looking" to our worksheet code module, but placed somewhere further "up" the hierarchy. A "Class" in VBA is as vague a concept as most VBA stuff follows the word definition of something along the lines of a blueprint or template or Type.
One could thing of the Class as the instructions, as simple text , on how to build something, and a VBA object could be built following those instructions.
A Variable used for an object will generally need to be declared ( Dimed ) to a specific type, and early on in VBA programming one may have, unknowingly, used a Class without realising it, for example , in code lines like these , the word Range , refers to the class Range
Dim Rng As Range
_ Set Rng=Range("A1")
In general, any object will be of a certain type , and the coding or information needed to use those objects will to a large extent be contained in its class. This may or may not be "see able" or accessible to us: it may or may not have a class code module. Such a code module, if it exists, can , and often is, loosely define as that Class object and which we then may or may not be able to access, see and/ or change:…
Class Class object WorksheetType2.JPG : https://imgur.com/PPUfc2w
Class Class object.JPG : https://imgur.com/3WDRcpU
It is very confusing to try and get a clear picture of this structure in the VBA Project window because Microsoft Excel and Microsoft Excel VBA is a disorganised mess:
On the one hand: We see in the VB Editor VBA Project window the individual worksheet objects modules, but not the Class object module from which they "come".
On the other hand: We can add a Class module , which we see then in the VBA Project window, MakeClass.JPG: https://imgur.com/GoKHDoq , but usually we cannot see the individual objects which we make from that Class.
[Class "WorksheetType2" made by us, seen as module ] _ [Class "Worksheet" made by Microsoft, invisible to us ]
___ [ "ShTyp2_1" ] _ [ __ ] [ _ ] ….. ___________________________ ["Sheet1"] ["Tabelle2"] ["MySheet"] ["Sht_4"]…..
So we could make one of those Classes / class modules , for example from the VB Editor VBA Project window by selecting the appropriate right mouse click option… _..
InsertClassModule.JPG : https://imgur.com/vcZSEAj , https://imgur.com/u1orh81
_.. and change its name to, for example , WorksheetType2 via the VBA Project properties window
NameClass.JPG : https://imgur.com/S6u7Gbf
We could add some simple coding "within that object" to "make that object" , for example a simple "Name" Property.
BuildAClass.JPG : https://imgur.com/4WGRbDC
(There is no significance to what that Name Property for the Class WorksheetType2 is at this stage. For the Class Worksheet the Name property is given further significance due to other coding in the Worksheet Class module which we do not have any access to. )
Class Module, Named by us - "WorksheetType2"
We can then use that class "WorksheetType2" in a similar way to which we use the existing class "Worksheet". We even get the options added to the intellisense drop down lists:Code:' Class (Modules) : https://www.youtube.com/watch?v=jHa8W52mD1k&index=65&list=PLS7iHfqXNVhK3yzd_4XS5k4zsvnu2mkJC : https://www.youtube.com/watch?v=MjbmsVDnAL0 Public Name As String
SimpleWorksheetNamingCode.jpg : https://imgur.com/5pYovYt
SimpleWorksheetNamingCode .jpg : https://imgur.com/v8ZUVVx
So in any code module, we can now do like:
The way that our given name WorksheetType2 is used in coding such as that above, supports the idea that in the case of a Class the code module itself can be thought of as the Class objectCode:Sub NameAWsType2() ' Make a Worksheet object Dim Ws4 As Worksheet Set Ws4 = Worksheets.Item(4) ' Make a WorksheetType2 object Dim WsTyp2 As WorksheetType2 Set WsTyp2 = New WorksheetType2 ' Name the worksheets Let Ws4.Name = "Sht_4" Let WsTyp2.Name = "ShTyp2_1" ' Access the names MsgBox prompt:=Ws1.Name & vbCrLf & WsTyp2.Name End Sub
Just to help clarify. There will be somewhere "hidden" from us, a Worksheet class module, and that will include a vast amount of coding, some of which will include functions / methods which will be associated with the Worksheet Name Property. I guess if we had access to that it might be dangerous as we might change something that could cause a chaos somewhere, as other things will likely be organised in the Excel we use, based on how that coding is.
The word New "creates" an object (a process called instantiating ).
The internal coding which we have no access to will have created the Worksheets already "existing".
We have to do this instantiating for any objects we create, either
through instancing a Class which we have made, as we are discussing here
or
by accessing other objects not included as default in Excel, often referred to as Binding ( http://www.excelfox.com/forum/showth...ing-Techniques )
As I am not allowed such access to the Worksheet class, I cannot use Set __ = New ___ , I can only assign a variable to the existing object like Set __ = ___
Finally, I try to here to sketch in
_ the "invisible" Class object module for the standard Excel worksheets,
and
_ two object modules for the objects I might "make" from the see able Class object module which we "made" with the coding above
Class Object Mess.JPG : https://imgur.com/r6hrPSK
Attachment 2116
[Class Worksheet]_ [First worksheet object]
_____________________[Second worksheet object]
_ [Class WorksheetType2 ] __ [First object (ShTyp2_1)]
________________________________[Second object]
Also we have a code module, which is not so often called an object, and a Thisworkbook ( In German DieseArbeitsmappe ) code module usually regarded as an object.
It is a mess because it is a mess. :-)
Here is a special "Excel" file which I have which has 6 worksheets.
It has the Class object modules and object modules for
the Application Excel
and
the worksheets. ( Each worksheet has a Class object with just one worksheet "made" from it )
Alans Full Excel.JPG : https://app.box.com/s/iaozdmu9jhu33wo9r2ntcdhkkz1bwu9g , https://imgur.com/0k2NDVX
Attachment 2115
[Class ExcelAppThisWorkbook] _ [ThisWorkbook object]
_[ Class Worksheet1 ] ________ [First worksheet object]
_ [Class Worksheet2 ] ________ [Second worksheet object ]
_ [Class Worksheet3 ] ________ [Third worksheet object]
_ [Class Worksheet4 ] ________[ Forth worksheet object]
_ [Class Worksheet5 ] ________ [Fifth worksheet object]
_ [Class Worksheet6 ] ________ [Sixth worksheet object]
_ [Class Worksheet7 ] ________ [Seventh worksheet object]
Ref
http://www.cpearson.com/excel/classes.aspx ( RiP Chip Pearson http://excelmatters.com/2018/04/30/rip-chip-pearson/ )
Reply With Quote
Bookmarks