https://www.excelfox.com/forum/showt...ll=1#post24213
https://www.excelfox.com/forum/showthread.php/2966-Class-related-Stuff-New-Instancing?p=24213&viewfull=1#post24213
Initial attempts at a Summary / Conclusion
There are no worksheet classes in the registry, (every class in Windows has it's own CLSID, and you can look them all up in the registry, ( see https://eileenslounge.com/viewtopic....316704#p316704 )
The New keyword in a typical variable declaring code line, can be thought of, at least to a first approximation, in simple layman terms**, as causes an object to be made before a variable is Set to that object.
It is a bit misleading perhaps to see a code line like Set Obj = New ClassX , since it makes it perhaps less obvious that a good Layman understanding is that New first effectively creates an object to which the Obj is then Set to
In other words, we could consider this:
Code:
Dim Obj As ClassX
Set Obj = New ClassX
, as really doing this pseudo coding
Code:
Dim Obj As ClassX
Set Obj = New …. here a particular object instanciated from, ( that is to say made using the) ClassX
Set Obj = ….. ' pause a split second while I set up some virtual table like thing in memory that will be pointered** to by the variable Obj.
Set Obj = New ..here a particular object instanciated from, ( that is to say made using the) ClassX ' Here, as I am dong in this line, and from now on, I am able to simply assign Obj , (or any object variable of the class type ClassX), to, in Layman terms**, an existing particular object, – that object created here
' I now have, in layman terms, an object which initially is pointered to by the address number in the variable Obj
Dim Obj2 As ClassX
Set Obj2 = objX ' obj2 is given the same "pointer", or in simple layman terms, a copy of the address to get to the same object pointered to, as that held in the variable objX
** Technically speaking an object variable is a Long (or LongPtr on 64bit Office). The number it contains is a memory address to the virtual Table of whichever interface was declared based on the class type, ClassX in our example. This is technically referred to as a pointer. Think of it, in layman terms as a just a pice of paper or a signpost with a number telling you in effect which address to go to in order to get hold of methods and properties available to that ClassX
Another way to say something similar: For someone learning VBA it might be worth saying that it would be more intuitive if the last two syntaxly correct code lines here
Dim objX As ClassX, obj2 As ClassX
Set objX = New ClassX
Set obj2 = objX
, were more visually like this pseudo equivalent, where
Set objX = CreateNewObject(FromType:= ClassX) ' A memory virtual table or similar interface is created to be able to contain names of the object's properties and methods, the arguments they require, and their entry points (memory addresses)
Set obj2 = objX ' obj2 is given the same "pointer", or in simple layman terms, a copy of the address to get to the same object pointered to, as that held in the variable objX
Worksheet Class Object. Class modules and … "Worksheet Class modules"?????.
You may hear something along the lines of that …..the ThisWorkbook and Worksheets modules are some form of Class modules….
I would respectively beg to differ. Take for example the worksheet related modules in the VB Editor
There are no worksheet classes in the registry, (every class in Windows has it's own CLSID, and you can look them all up in the registry, ( see https://eileenslounge.com/viewtopic....316704#p316704 )
In Technical jargon we say something like …. it 'aint been exposed for Automation, matey boy. The "application", (Excel in this case), exposes it , because it can !! , and is wired to do it. We can’t. (Excel.Sheet classes are in fact workbooks, which is perhaps for historical reasons, - a left-over from the old days when workbooks only had one sheet).
Worksheets are dependent objects - i.e. they can't exist in isolation, only as a child of a workbook. The New keyword cannot be used to create new instances of any intrinsic data type and cannot be used to create dependent objects.
The final worksheet we experience, is only ever one object deep inside the program as bits and bites which can be shared and reused hundreds of times by many addresses to compile the user interface on the screen: all that ever goes on is passing and changing a memory address to manipulate an object properties and methods in a development environment .. these addresses are complied to make a nice picture on the screen ….
The only access we are able to have to a final worksheet object is through the Worksheets(Index) Collection Object, (and then further via the Methods and Properties of any individual worksheet "contained" therein). Some form of Worksheets(index) Collection Class we do not have direct access to ##
Now trying to decipher that into English: The New keyword is about making an object from a certain type, as defined by its class. A class can be considered in simple layman terms as just a bit of paper , with instructions on how to make an object of that type. Or think of it as a workshop manual for a certain type of product. Having a Class Module possibility in the VB Editor is just for convenience: It is more convenient to access the information that way compared to having the piece of paper in your pocket or having the workshop manual on a shelf in your computer room. If we were not home users on a software where we have some access to manipulate the software itself, but rather a very clever advanced programmer, say, developing a popular top level computer game, then such a person might still work from bits of paper in his pocket or some Manual documenting how to set up similar things he does over and over again. (Indeed a good example for use of a Class module, for once you understand better how a class module is organised, is given here for an element in a computer game: xxxxxxxxxx )
Adding a worksheet
A Class related to a final worksheet that could be instanciated ( made from / using ) it, does exist. That has the class name of Worksheet
But we have no direct access to any worksheet Class in order to add a worksheet instance.
Via coding, ( or through the Manual operation which probably Calls that coding) we can use the VBA Worksheets.Add ( or Sheets.Add ) methods to add a worksheet.
Lets just think again about that in a slightly different way, but saying the same basic thing:
This will not work
Code:
Dim ws As Worksheet ' Prepare Memory for Variable of this type
Set ws As New Worksheet ' Error Klasse unterstützt keine Automatisierung oder unterstützt erwartete Schnittstelle nicht : Class does not support automation or support expected interface You can't set instance it because the powers that be say you can't. It really is as simple as that. it has simply been decided that in the VBA Software available to us Mortals that we should not be allowed to do such things. – Bill Gates & co being dependant on their living that we cannot create these things. ( But that adding a worksheet does no small amount of complex wiring up that you can't do yourself to keep everything working. ) So the Set = New is still happening, it's just in the Worksheets.Add function - so you don't see it. It's likely done like this because of all the internal wiring that needs setting up when a new worksheet is added to a workbook.
But this will
Code:
Dim ws As Worksheet ' Prepare Memory for Variable of this type
Set ws = ThisWorkbook.Worksheets.item(1) ' The variable becomes a pointer to an existing object
!! It is probably complex wiring to add a worksheet, without breaking something badly. So it is not allowed for us to do it. I expect the process can be considered to be in something similar to a Class, and something in the form of a worksheet Class module may be involved.
It will handle the default naming, etc., (and because of the historical reasons discussed , some deep down technical reason may somewhat peculiarly require one for each worksheet, even if these are identical)
Maybe something like this,- a Class module, and one of the things in it will be a Public Function Add() , ( which means for an instanciated worksheet object, it will become a method of that object)
Worksheet Collection Class
Code:
Public Function Add() as Worksheet
Set Add = New Worksheet
Add.Name = "Next Available default Name"
AddWorksheetClass Add ' This is a bit vague. I am thinking it creates a Class of some form for each individual worksheet. It or that may automatically instantiate somehow another worksheet in the Worksheet(Index) Collection Object
End Function
Reply With Quote
Originally Posted by buran
Bookmarks