Class related Stuff New Instancing

**DocAElstein** · 12-26-2018, 04:35 PM

ss,chsajch

**DocAElstein** · 12-26-2018, 04:35 PM

What is complicated here? It was a pointer to one range, then it was assigned another. At no point is there anything that could be called copying. I'm afraid I genuinely don't understand where you're going with this.

_.________
But you were talking about a copy of an object. That is not at all the same thing as a copy of a pointer.
As I said, I may have once used the word copy out of context.
or
I may have been putting the argument forward of a Copy again , as I had not up until post #20 from shg seen a confirmation of the Local Copy idea. But I did always say words to the effect a “Copy “ made at the Call. So Really I was implying a locally made Copy...

Apologies again for any confusion.

I might prefer to say from now on to say
“The Local Variable of the same type as the original made at the time of the Call to refer to a Pigeon Hole of similar construction to that of the Original Variable, ( Same variable Type ) This will be given a copy of the Original’s “Pointer, Code instruction paper or what ever”.

I have said words to that effect more often than not.

That may help to offset the confusion ... I doubt...

_....

Edit:
Actually i cannot see where I used it out of context..? you unintentionally quoted it or read it out of context maybe
It would appear to me that for **Objects and most variable Types a local copy is made in a called routine, **yes I suggest even for an Object. - Otherwise there would be no way to change that actuall Object in the routine as you can clearly do.
For the ByVal call for an Object a copy of the Pointer is made which does refer to the original Object, so you cannot change the original Object, but can change its Properties. But a new “Pigeon Hole memory location” is created by the Called routine and instructions referring to actually changing the object ( and copying across as it were its Properties ) are included and can be made. As with all ByVal variables everything about them dies at the end of the called routine. These two Objects are simply sharing the same memory Locations containing all the values of all the Properties. The instruction part referring to Values referring to the Object itself, its address for example, are based on an offset to the relavent Pigeon Hole Location. .
……....... A New instance is a NewObject... or so I thought... And I would say again... you do make a local Copy of an Object..( and everything else ) . Or said a bit differently you use a temporary variable of the same type. ByVal.. But it dies at the end of the code....
.....

As long as you understand the "local copy" did not refer to a copy of the original object.
Sounds good
That is what I meant, but had not had it confirmed, so occasionally a slip of thought or whatever and I did not always make that clear.

14 may 2016
_ Is shg ( When a procedure gets an object ByVal, it gets a copy of the pointer to the object. You are free to change the object, e.g.,
rng(1,1).value = "bob"
... and free to to assign the pointer to a different object internal to the called routine, e.g.,
set rng = rng.offset(1)
... but in the latter case, the pointer is unchanged in the calling routine; the called routine just changed the local copy. ) talking about the part of the Pointer which contains info about things like the Address. shg says it’s not a typo
A pointer is a variable that contains a memory address.
Thanks for that.
No offence, I am very grateful for your help.
But, as I mentioned before, it makes things very difficult when you give such short answers like this.
( maybe you are just amusing yourself ?- why not – your well earned privilege )

So It is not clear exactly what you are referring to there as the local copy.
I could guess that could be the local copy of the Pointer?
But is this "Pointer" somewhere ( maybe in a Pigeon Hole or whatever )
So is "that" then effectively changed, or the contents there in. ?
Such a thing could be the “Local Variable Copy” ( Just not too upset Rory I will emphasise it is not a Copy of the Object , in the same sense that for example, something like a Long number it would be the local Copy Variable that would also be a copy of everything to do with a Long Number. So it would be a Copy of the Long Number. In the case of the Object this Local Copy Variable is a Copy of the Pointer and is itself somehow an “Object” or else we could not change it within the called routine

_......
I suspect you "know" what you are talking about.
I, and i bet many others , sadly do not

It would be so great if you could expand a bit on what you said, preferably in terms a Laymen like me could understand

And if not, thanks anyway again for coming back here.
I myself ( in my profession ) had many frustrating hours trying to explain things that were obvious to me to others who had no idea. I feel your pain Alan
shg An object pointer is a memory address that points to an object (more precisely, its vTable (Virtual Function ~), which contains the names of the object's properties and methods, the arguments they require, and their entry points (memory addresses)).

When an object is passed by reference, the same pointer used by the calling procedure is passed to the called procedure. If the called procedure changes the pointer (by assigning it to a different object), that change is reflected in the calling procedure when the called procedure terminates.

When passed by value, the calling procedure passes a COPY of the pointer. The called procedure can still do anything it likes, including changing (its local copy of) the pointer, but the copy is discarded when the called procedure terminates, so the calling procedure's pointer is unchanged.
Rory 15 may 2016 [color=Blue]A pointer is a Long (or LongPtr on 64bit Office). The number it contains is a memory address (to the vTable of whichever interface was declared for the object variable, since an object can implement more than one). So passing it ByRef or ByVal is the same as passing a Long.[/color]
Hi Rory,
Thanks for the reply.

Not sure if
shg: ....The only thing there is a copy of is a four-byte pointer.
and
You, Rory: A pointer is a Long (or LongPtr on 64bit Office)....
Are saying the same, may be ?
_.....................
and
A pointer is a Long (or LongPtr on 64bit Office). The number it contains is a memory address .... So passing it ByRef or ByVal is the same as passing a Long.
_.. I am afraid that makes no sense to me. Presumably then if I copy the pointer I copy a Long number.?!
_.. If I meet a builder in the town who is prepared to sort my house as after 20 years I am still not getting anywhere, he will require my Address, etc...
_. Say f I have a piece of paper with my Address on it, and instructions of what / where I want stuff and stuff done. Then it makes no difference if I give him that paper and he references that or if he copies the paper and references that.
_..Never mind
_.. Thanks again everyone. It has helped me get as far as I need.
_................................................. ..................

Thread Summary.

_1) the initial theme, Set __ = ___ or Set ___ = New ____ ( as example the Worksheet was used )
and maybe inevitably it has digressed a bit into

_2 ) General Declaring of variables, how they are handled in passing , with particular emphasis with regard to the differing to the general rule of the Object variable.
_...
So
_ 1 )
Set __ = ____
and
Set ___ = New ____

It lead to the Theme of Instancing.
A code in Post # 11 summarised the Theme of instancing. Instancing briefly could be regarded as “making New”. This means a Virgin copy of the “Blue Print / Template / un filled in form of instructions on how to build something.” for the Object Class.
Generally something we are “given” such as a Worksheet will not be a “Virgin” Blue Print. It is highly likely then that it will be very dependent on other existing things. Whilst not theoretically impossible to do, creating a New virgin instance of such will involve some complicated “wiring”. Generally the option to do that is not given to us. We are given the option of copying an existing instance to effectively give a New instance. A code was also tacked on in the code Window which both does this adding and shows what effectively done internally to get a new instance of a worksheet through .Add Method
So in Post # 13 a suggested summary again

‘ Generally for Objects where there is dependency, that is to say....... they cannot exist independently and / or there are other Objects which are affected by the existence of such Objects..... , you will not be allowed to make a New Instance. Instancing by the user directly will not be allowed. This is likely because there will be some very complicated "Wiring" involved. There will need to be information given, for example, as "where should it go", as other Objects may be effected. So those things are best left to a Function or Method, ( a .Add or .Insert , for example ). There will always be arguments associated and require them ( if you ,leave them about, VBA at compile will try to guess ( based on other available information and / or arguments), what you want, or always using a particular default when you omit an argument )

So my original suggestion of an additional implicitly defaulted
Set ws = New Worksheet
Before
Set ws = Worksheets(“Sheet1”)
Was a load of old bollox. The two things are alternatives. Without New, we are not instancing. In both cases we are assigning to an Object. For the New that Object has yet to be built, but the New virgin copy of the Blue print will be reference by the variable ws. For without New we have a final Object, ( but can lightly change it or indeed instance that indirectly through a Method. In such a case we will have an Instance and an additional ( so “new” ) one at that! It is not a virgin of the Class.

So generally
Set ___ = New ____ means virgin and we can usually create such an instance
And
Set __ = ____ means not virgin and an option for instancing directly will not be given usually.
_.....................

_2 ) ByRef ByValue stuff...
I did some notes for myself on this "can of worms" some time ago to save time when answering Threads on this Theme. I have not been able to advance much on those explanations, but based on the comments here in this Thread I have just made some minor alterations. I will not repeat them again here !
Here they are.

http://www.excelforum.com/showthread...t=#post4381274
http://www.excelforum.com/showthread...t=#post4381275
http://www.excelforum.com/showthread...t=#post4381420

_...............................

Thanks again everyone
Alan

**DocAElstein** · 12-26-2018, 04:35 PM

Pike 2
13 may 2016
Pike 13 May 2016 https://www.excelforum.com/excel-pro...ml#post4386481

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

Originally Posted by Doc.AElstein
So why can I not do line 2 here
1 Dim ws As Worksheet
2 Set ws = New Worksheet.
As long as I do line 1, then Intellisense offers me Worksheet

but you digress
to the question with the code

Code:

Option Explicit sub test() Dim ws As Worksheet Set ws = New Worksheet End Sub

VBA 's IntelliSense feature is like a mini version of the VBA Help system. It offers you assistance with VBA syntax. IntelliSense helps by giving you hints and alternatives as you type. It only offers assistance to speed typing nothing more. You must still know what to use in the routine .. and it has been established that you can not use New with dependant objects

**DocAElstein** · 12-26-2018, 04:35 PM

From 14 May , 2016 , slowly getting somewhere

For example this might have been a typo...
Quote Originally Posted by shg
When a procedure gets an object ByVal, it gets a copy of the pointer to the object. You are free to change the object, e.g.,
rng(1,1).value = "bob"
... and free to to assign the pointer to a different object internal to the called routine, e.g.,
set rng = rng.offset(1)
... but in the latter case, the pointer is unchanged in the calling routine; the called routine just changed the local copy.
Or if not a typo then that is lost in the uncertainty about exactly what a pointer is in this context.

_ Did he mean the original object or that and the original pointer
_ Is he talking about the part of the Pointer which contains info about things like the Address.
_ And did he mean to say at the end local copy object or local copy pointer, or both...
But again I am repeating my suggestions. And you know the game: I re explain and re explain then get moaned at that the thread is long and hard to follow

Last night someone with the best intentions came in to help, but had not had time to read thoroughly and see the issues. I ended up having to explain his own explanations in his own Web Site., !! No offence there at all what so ever. I am very grateful for the effort. But once again a vicious circle. The Thread gets longer .. and I repeat ... and ... then.....

So i will leave it, and see if any more clarifications come in.
I have more than done my bit too straighten it out.
It is not a typo.
_ Is he talking about the part of the Pointer which contains info about things like the Address.
A pointer is a variable that contains a memory address.
Thanks for that.
No offence, I am very grateful for your help.
But, as I mentioned before, it makes things very difficult when you give such short answers like this.
( maybe you are just amusing yourself ?- why not – your well earned privilege )

So It is not clear exactly what you are referring to there as the local copy.
I could guess that could be the local copy of the Pointer?
But is this "Pointer" somewhere ( maybe in a Pigeon Hole or whatever )
So is "that" then effectively changed, or the contents there in. ?
Such a thing could be the “Local Variable Copy” ( Just not too upset Rory I will emphasise it is not a Copy of the Object , in the same sense that for example, something like a Long number it would be the local Copy Variable that would also be a copy of everything to do with a Long Number. So it would be a Copy of the Long Number. In the case of the Object this Local Copy Variable is a Copy of the Pointer and is itself somehow an “Object” or else we could not change it within the called routine

_......
I suspect you "know" what you are talking about.
I, and i bet many others , sadly do not

It would be so great if you could expand a bit on what you said, preferably in terms a Laymen like me could understand

And if not, thanks anyway again for coming back here.
I myself ( in my profession ) had many frustrating hours trying to explain things that were obvious to me to others who had no idea.
I feel your pain
Alan
……The only thing there is a copy of is a four-byte pointer

An object pointer is a memory address that points to an object (more precisely, its vTable (Virtual Function ~), which contains the names of the object's properties and methods, the arguments they require, and their entry points (memory addresses)).

When an object is passed by reference, the same pointer used by the calling procedure is passed to the called procedure. If the called procedure changes the pointer (by assigning it to a different object), that change is reflected in the calling procedure when the called procedure terminates.

When passed by value, the calling procedure passes a COPY of the pointer. The called procedure can still do anything it likes, including changing (its local copy of) the pointer, but the copy is discarded when the called procedure terminates, so the calling procedure's pointer is unchanged.

yeah, we got that bit. Thanks. So which bit haven't you got? Or have you got it now? never mind
thanks for all your help
_.____________
Rory 15 may 2016 A pointer is a Long (or LongPtr on 64bit Office). The number it contains is a memory address (to the vTable of whichever interface was declared for the object variable, since an object can implement more than one). So passing it ByRef or ByVal is the same as passing a Long.
Not sure if....The only thing there is a copy of is a four-byte pointer. … and … … A pointer is a Long (or LongPtr on 64bit Office )....
Are saying the same, may be ?

and

_.. I am afraid that makes no sense to me. Presumably then if I copy the pointer I copy a Long number.?!
_.. If I meet a builder in the town who is prepared to sort my house as after 20 years I am still not getting anywhere, he will require my Address, etc...
_. Say f I have a piece of paper with my Address on it, and instructions of what / where I want stuff and stuff done. Then it makes no difference if I give him that paper and he references that or if he copies the paper and references that.
_..Never mind
_.. Thanks again everyone. It has helped me get as far as I need.
_................................................. ..................

Thread Summary.

_1) the initial theme, Set __ = ___ or Set ___ = New ____ ( as example the Worksheet was used )
and maybe inevitably it has digressed a bit into

_2 ) General Declaring of variables, how they are handled in passing , with particular emphasis with regard to the differing to the general rule of the Object variable.
_...
So
_ 1 )
Set __ = ____
and
Set ___ = New ____

It lead to the Theme of Instancing.
A code in Post # 11 summarised the Theme of instancing. Instancing briefly could be regarded as “making New”. This means a Virgin copy of the “Blue Print / Template / un filled in form of instructions on how to build something.” for the Object Class.
Generally something we are “given” such as a Worksheet will not be a “Virgin” Blue Print. It is highly likely then that it will be very dependent on other existing things. Whilst not theoretically impossible to do, creating a New virgin instance of such will involve some complicated “wiring”. Generally the option to do that is not given to us. We are given the option of copying an existing instance to effectively give a New instance. A code was also tacked on in the code Window which both does this adding and shows what effectively done internally to get a new instance of a worksheet through .Add Method
So in Post # 13 a suggested summary again

‘ Generally for Objects where there is dependency, that is to say....... they cannot exist independently and / or there are other Objects which are affected by the existence of such Objects..... , you will not be allowed to make a New Instance. Instancing by the user directly will not be allowed. This is likely because there will be some very complicated "Wiring" involved. There will need to be information given, for example, as "where should it go", as other Objects may be effected. So those things are best left to a Function or Method, ( a .Add or .Insert , for example ). There will always be arguments associated and require them ( if you ,leave them about, VBA at compile will try to guess ( based on other available information and / or arguments), what you want, or always using a particular default when you omit an argument )

So my original suggestion of an additional implicitly defaulted
Set ws = New Worksheet
Before
Set ws = Worksheets(“Sheet1”)
Was a load of old bollox. The two things are alternatives. Without New, we are not instancing. In both cases we are assigning to an Object. For the New that Object has yet to be built, but the New virgin copy of the Blue print will be reference by the variable ws. For without New we have a final Object, ( but can lightly change it or indeed instance that indirectly through a Method. In such a case we will have an Instance and an additional ( so “new” ) one at that! It is not a virgin of the Class.

So generally
Set ___ = New ____ means virgin and we can usually create such an instance
And
Set __ = ____ means not virgin and an option for instancing directly will not be given usually.
_.....................

_2 ) ByRef ByValue stuff...
I did some notes for myself on this "can of worms" some time ago to save time when answering Threads on this Theme. I have not been able to advance much on those explanations, but based on the comments here in this Thread I have just made some minor alterations. I will not repeat them again here !
Here they are.

http://www.excelforum.com/showthread...t=#post4381274
http://www.excelforum.com/showthread...t=#post4381275
http://www.excelforum.com/showthread...t=#post4381420

_...............................

Thanks again everyone
Alan
No, your analogy is incorrect .. you are taking copy and new literally
its a virtual world with memory addresses .. memory pointers to programmatic objects .. all you are doing 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
No new or copy is ever made.There is only ever one of the object deep in side 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.

with your building analogy think of if as hundreds of different people living in the one house just not at the same time.
The builder can only visit one person at a time. as far as the builder is concerned he reads a message describing the house and what needs to be done.
He visits the house and deals with the message
He can only deal with one message at a time and has to queue the work.
in the virtual world it is the same house
I think I did get all that, and wrote words to that effect here and elsewhere in my Summaries and explanation. I mention just one object and through offsets we “see” all on the screen. What is actually held in memory are complex offsets, or maybe as you suggest, “visits” at different time or whatever.

Just a bit of word play. New or Copy are just referring to “somehow making a distinction so we “see more than one” “. Somehow VBA must have a way of knowing which is which.
In the meantime no one probably knows exactly what is going on. Or has not the time to explain. I think further quick comments taking words out of context, or every one interpreting what everyone else reads into it just goes round in circles.

But I think the summaries in and referenced in post #46 are OK, or as good as any you’ll get I think.

Thread Solved.

If anyone wants to add no problem. Why not.

I am out of here, .. man !

Thanks again for all your help, here and elsewhere. Appreciate that

Alan
why would you say "no one probably knows exactly what is going on" is beyond me.. you will just have to buy a book about the Excel Object Model and learn the correct terminology.

**DocAElstein** · 12-26-2018, 04:35 PM

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

As I am on the subject of Worksheet Class Object. Class modules and … "Worksheet Class modules"?????, I will break off at this point and look at some discussions starting here…
https://eileenslounge.com/viewtopic....317564#p317564
https://eileenslounge.com/viewtopic.php?p=317564#p317564

**DocAElstein** · 12-26-2018, 04:35 PM

https://www.excelfox.com/forum/showt...ll=1#post24214
https://www.excelfox.com/forum/showthread.php/2966-Class-related-Stuff-New-Instancing?p=24214&viewfull=1#post24214

looking at some discussions starting here…
https://eileenslounge.com/viewtopic....317564#p317564
https://eileenslounge.com/viewtopic.php?p=317564#p317564

https://eileenslounge.com/viewtopic....317565#p317565
Fromn you various musings:

>telling you as people often do, that the worksheets code module is a class module , is wrong…. At least I think
You think wrong. Userform, Sheet, and ThisWorkbook are all (special forms* of) classes. You can happily add your own events and properties to them, for example.

And then
Public example as ThisWorkbook
(or, if you are working in another class module
Public WithEvents example as ThisWorkbook **)
both work exactly as you'd expect if they were your own classes created from scratch
I think a Userfom, is a class module, the others not

_.____

* Not going into detail here, but if you really want to go down the rabbit hole, try a Google for VB_PredeclaredId

** If you are unsure, then trying to declare WithEvents in a module is a quick and dirty way to determine whether that module is actually a class module or not
I don’t know why a normal module won’t allow a declare WithEvents. I don’t see how it being allowed in the other modules proves that they are class modules . Something in these other modules allows for, and they primarily are about, event coding. Maybe that has some thing to do with it

https://eileenslounge.com/viewtopic....317579#p317579
https://eileenslounge.com/viewtopic....317582#p317582

_ 1) 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)
Totally agree. Ties up as far as I can tell with all I said or wrote anywhere ever… well at least since I first woke up from my technology coma and met OOP (which VBA isn’t, but tries to act like –(according to other smarter people here and elsewhere, I add quickly, Lol ) )

2) An class module is part of a development environment that allows a programmer to define those variables, properties, procedures, and events
Totally agree. I might word it a bit differently. The object module is put in the development environment as that is the obvious place to put it. Doesn’t have to be though. It could have been designed to be a floating window like the Immediate window is. I can imagine a few occasions when it would have been convenient to drag it around a bit like I do with the Immediate window. Personally I would find that useful. I suppose the thing would then still technically be part of the development window. But that is all minor technicality stuff.
I am interested that you are using the term object module. Curious why and to what you are referring to. Maybe a typo?
Although we use the worksheets and ThisWorkbook modules for many things, (often its down to personal preference), as I understand it they were made available to us mainly to get at the events, but allows a programmer to define those variables, properties, procedures, and events .. is a statement I am perfectly happy and in agreement with.

3) An object itself does not have a module; it is your IDE that has modules.
That is a technicality again open to perception I think. The worksheets object module as I call it is related to the worksheet. Doesn’t matter if the window they give you that allows you, for example, to add coding to events, is ordered in a neat way in the IDE. That is point _2 which I agree with.
If I use Me. In coding a worksheets code its referring to some way to the worksheet. The object definition is vague, by design. It sounds reasonable to me to regard the worksheet code module as part of the worksheet object, or at least it sounds reasonable to me to regard the worksheet code module as part of greater total worksheet "thing".
Obviously no one living has access or a grip anymore to any of the real innards of Office. It’s open to a reasonable logical interpretation that matches what you see and get. A worksheet code module can easily be seen to be related to a worksheet. They are, to the user, part of the end thing, a worksheet, that we "have", and/or experience or can reasonably perceive.

That is a technicality again open to perception I think
Ok, so let's talk about traditional compiled software. It has source code which is compiled to object code . And it is the object code that gets distributed. The object code does not include the source code, and the end user thus does not have access to that source code. They only have access to functionality defined by that source code. And back in VBA OOP, the source code for a class is the class module. part of the problem, I suspect, is that you are conflating two concepts, and using the word 'module' to mean two things (VBA modules, which contain source code, and the compiled code that provides the functionality of the program - whether in an object or not. But that compiled code is NOT a module;' hence my comment, by the way, that "There is no module for you to see")
…… see later , next post https://www.excelfox.com/forum/showt...ll=1#post24215

4) An instantiated object is a black box. The internal working are never directly accessible to the consumer of the object. Agree mostly. – same comments more or less as in _3 no one living has access or a grip anymore to any of the real innards of Office. It’s open to a reasonable logical interpretation that matches what you see and get.
I am thinking that a worksheet is an instantiated object, or something very similar. Hence,
Set Wsx = New Worksheets("Sheetx")
,wont work. You must dump the New
Obviously no one on this planet has access anymore to the innards. Or if they do, they don’t dare try, for fear of breaking it.
I would suggest that we do have a very minor minuscule access to some of it, maybe via an interface to protect the real innards, and that is , for example, for the case of a worksheet, what we can do with the worksheet code module, (or even manually adding things to the spreadsheet I suppose. ( We can also do that with coding in the worksheet code module, - personal preference – I like to, but I think the main reason we are given it is for a convenient way to add to event codings. Others prefer to do other coding things to the worksheet from normal modules. I would fight to the death for their right to be like that, even if I don’t want to. ) )
Well ... it depends ... but yes, sheetx is an object, not a class. It is an instance of the worksheet class, a worksheet object

Worksheet itself, however, is a class - but it is not publicly creatable (we are again getting into some of the slightly more esoteric areas of classes here), which means you, the user, cannot directly create an new instance of the class; i.e New does not work. The Worksheets object, however, CAN create an instance of the class, and return it (worksheets is what we call a 'factory object' https://en.wikipedia.org/wiki/Factor...programming%29

I think we are agreeing on things to do with the New for a worksheet declaration not working. But you have made some useful references for me to come back to , at a later date if I am able to , in order to get a better low level understanding. Thanks
Perhaps the .Add is a factory method?

So your stream of consciousness mutterings about not being able to see the Worksheet module is inaccurate. There is no module for you to see Possibly you have some typos there, or I missed something. That makes no sense as written. Maybe you meant something else and the shortened version slipped out, missing something along the way?
I think I can see what I perceive as a worksheet module,
(just as I think I can see what I perceive as a spreadsheet, even though technically no one ever has or can ever see a spreadsheet as they don’t really exist, but I suggest it’s a reasonable perception to have, that there "are" spreadsheets)
, we are currently disagreeing as to whether the thing we may perceive as a worksheet module, (what we see referred to as a Sheet in the IDE), should be perceived as a class module or a object module.
In my perceptions, a class module can be likened to a bit of paper with text on it. When some "thing" is instanciated from that class, and perhaps has a few properties assigned, etc, then I perceive it as something a bit more substantial with pseudo "real" coding in it, and I am inclined to call it/ perceive it, as an object

Notes original good bits /- new things gleaned
There are many pitfalls for someone learning, at beginner level- here I talking from the side of someone struggling to have learned, not from the side someone who already knows it all , since for those already informed people, anything that is not obviously totally wrong will sound OK
_(i) Classes are sometimes explained in simple terms as templates. I would go away from that, even if it’s not incorrect. It can and has lead to many people thinking of it as some form of partially filled in thing, like a form letter, that you copy and fill in. Thinking in terms of copying is a very bad thing when discussing objects in class discussions.
Class / object discussions and copy = No No No
_(ii) Related to _(i) I think it could be helpful to refer to what is in the class module as text, rather than coding, and perhaps explain that it will be used later from which to create coding, but try to avoid the use of the word copy if possible. Perhaps that text could be thought better conceptually as like that on a Stamp
Using that stamp is part of what goes on by the so called instantiating from a class
_ (iii) At the very least, I would avoid any mention about how the thisworkbook module, and worksheets module fit into it all. Give the innocent chap trying to learn half a chance to decide himself later whether the King has a new magic suit or whether he is standing there bxxxxxk naked in his birthday suit
So far the only stated publicly explanation I have seen for calling these class modules is that we can do the WithEvent declarations and related coding in them. If that is the only justification then its some technical convoluted justification and does not help. IMHO there are many reasons to at least think of them as more in the direction of some thing , ( dare I say an object ) , closely associated with the final worksheet object to which they are unquestionably associated with, ( dare I say perhaps even the part of that object itself ).

/- A worksheet is an instance of the worksheet class, - it is therefore a worksheet object

/-Maybe Worksheets.Add is a factory method: It returns an object of the Class worksheet. Worksheets is an object for returning other objects, in this case the other objects are worksheets form the worksheet Class

/- A Worksheet is a class - but it is not publicly creatable

/- An instantiated object is a black box. – I don’t necessarily disagree that is often the case, but we have in a form that we as humans can see a worksheet, along with, for convenience, primarily to add into event coding, a module associated with each worksheet

**DocAElstein** · 12-26-2018, 04:35 PM

https://www.excelfox.com/forum/showt...ll=1#post24215
https://www.excelfox.com/forum/showthread.php/2966-Class-related-Stuff-New-Instancing?p=24215&viewfull=1#post24215

3) An object itself does not have a module; it is your IDE that has modules.
That is a technicality again open to perception I think. The worksheets object module as I call it is related to the worksheet. Doesn’t matter if the window they give you that allows you, for example, to add coding to events, is ordered in a neat way in the IDE. That is point _2 which I agree with.
If I use Me. In coding a worksheets code its referring to some way to the worksheet. The object definition is vague, by design. It sounds reasonable to me to regard the worksheet code module as part of the worksheet object, or at least it sounds reasonable to me to regard the worksheet code module as part of greater total worksheet "thing".
Obviously no one living has access or a grip anymore to any of the real innards of Office. It’s open to a reasonable logical interpretation that matches what you see and get. A worksheet code module can easily be seen to be related to a worksheet. They are, to the user, part of the end thing, a worksheet, that we "have", and/or experience or can reasonably perceive.

That is a technicality again open to perception I think
Ok, so let's talk about traditional compiled software. It has source code which is compiled to object code . And it is the object code that gets distributed. The object code does not include the source code, and the end user thus does not have access to that source code. They only have access to functionality defined by that source code. And back in VBA OOP, the source code for a class is the class module. part of the problem, I suspect, is that you are conflating two concepts, and using the word 'module' to mean two things (VBA modules, which contain source code, and the compiled code that provides the functionality of the program - whether in an object or not. But that compiled code is NOT a module;' hence my comment, by the way, that "There is no module for you to see")
I am not sure if I was conflating, maybe I was doing it, and didn’t notice – I got a ton of great tasting Veggie meat balls for free recently, and I could literally get a van full more if I wanted, so I am trying to eat through them before they go bad, maybe that caused me to conflate, apologies if I did. I got plenty of windows open as usual, so that should limit the damage.
But I jest,

, - more seriously now…
I didn’t meant to give the impression I was conflating two different things. I think I was not intending to be talking about compiled coding as that scares me. I am thinking just that
_ a copy of the text in the class module inside some thing not too dissimilar (in terms of a square window and icons) in the VB Editor might be a nice thing to appear , or for you to imagine it to appear, after you instantiate an object from the class
,_ and that the name of the thing appearing might be the name of the variable used in the insanitation

I think that I was meaning the VBA modules, and meaning that in a similar vain of allowing myself to perceive that a spreadsheet exists, I was being so bold as to take the liberty to perceive that an object module looking not too dissimilar to the VBA worksheet modules existed after an instantiation of my custom class object.
I am very grateful that you qualified your "There is no module for you to see" , it makes sense what you are saying, I think I take your point.

I am at the level of high up end user that can reasonably suggest/ perceive that a spreadsheet, for example , exists, even if you might correctly convince me that it doesn’t. ( To be honest I long since had my suspicions myself, Lol – I figured Excel is more like a single cell, and what I perceive as a spreadsheet is some set of offsets synchronised to the voltage levels on the scan coils of my screen tube that goes across, then down then across, building up this picture to deceive me into thinking I have a spreadsheet,….. but that’s another Alan Theory for another day , perhaps )

I guess as a laymen I am saying that the source code that is the class can be thought of, in layman terms, as, just text on paper, whereas the code for example in a VBA worksheets code module that we physically see in the VB Editor and can run in step mode, is, as I perceive , a bit more substantial in some way, which way exactly I don't care to try to define just yet, but as it is more substantial, as such, it is perhaps, or has in it, slightly more "real" coding in a layman sense. But I am taking your point, - if I understand it correctly, that it is still source code.
I want a term that allows something to be a bit more substantial, in a perception sense, as a piece of paper with text on it, and an instantiated object’s (source) code was kind of making me think I could tie it into, and regard it as, part of, the thing we refer to as the worksheet (object)

_.___

To explain again a bit better, If I may, referring to my ramblings from here , which I am busy revisiting and reviewing in light or our discussions…. (Please note to only look from/ beginning at that link which should take you to page #3 , starting at post #21)

So, This I can see with my eyes already , - it’s the custom code module, that I added, and named FileOpenWatcher

my custom class module that I have got.JPG

my custom class module that I have got.JPG (48.18 KiB) Viewed 301 times https://i.postimg.cc/nz67LYRK/my-cus...I-have-got.jpg

I got this next coding somewhere else, (less important where), and that coding, amongst other things, instantiates from that class, using a variable, MeWatcher. ( That variable, is then referring to my new instance of the class).
Do an instanciate and fill a property.JPG

Do an instanciate and fill a property.JPG (35.09 KiB) Viewed 301 times https://i.postimg.cc/DZWLFYgy/Do-an-...a-property.jpg

I think it would be nice to have had this sort of thing pop up after that Instanciation, and that popped up thing would had the name MeWatcher , and I would like if it’s icon was in the VB Editor left hand explorer window at some similar looking level as the worksheet code module icons. (So this next screen shot is not something I can see – it is what i would like to see, and what I currently like to perceive when trying to understand these things
What I would like to apppear after my instantiating.JPG

What I would like to apppear after my instantiating.JPG (48.89 KiB) Viewed 301 times https://i.postimg.cc/YSCQDSLF/What-I...tantiating.jpg

I would not want to be able to edit it that last thing, and perhaps after it popped up I should either be banned from editing my class module, or, if I do edit that coding, then those changes should be immediately reflected in identical changes in my MeWatcher module, and or visa versa. – One is what I describe as text on a piece of paper, and the other is "made" from it, so they need to look the same,and so changes in one would somehow automatically be reflected in the other. Or, alternatively, after the first instantiated object is made , then editing is banned, and if I wanted to change something I would have to delete the two things and start again.

If I had that thing that I would like to have, it would then be nice that a step debug mode run of coding , if appropriate, went through that MeWatcher module, rather than me having the option to "break into class module", FileOpenWatcher, - since I can’t run coding in my added class module, (which I think is good and correct), so I should not be allowed ever to step through it either. I admit that the option is a useful tool to have, but I would prefer it to be possible to do it through this alternative way I have suggested.

**DocAElstein** · 12-26-2018, 04:35 PM

later

**DocAElstein** · 05-22-2024, 08:38 PM

This is a copy of a post somewhere else, just copied here for convenience, as it has some conclusion on class stuff

This is post https://www.excelfox.com/forum/showt...ll=1#post24217
https://www.excelfox.com/forum/showthread.php/2966-Class-related-Stuff-New-Instancing?p=24217&viewfull=1#post24217
https://www.excelfox.com/forum/showt...age2#post24217
https://www.excelfox.com/forum/showthread.php/2966-Class-related-Stuff-New-Instancing/page2#post24217
This is frompost https://www.excelfox.com/forum/showt...age2#post24185
https://www.excelfox.com/forum/showthread.php/2965-Class-related-Stuff-Userforms/page2#post24185
https://www.excelfox.com/forum/showt...ll=1#post24185
https://www.excelfox.com/forum/showthread.php/2965-Class-related-Stuff-Userforms?p=24185&viewfull=1#post24185

An attempt at an initial summary on UserForm (module)

We are really talking about Class things and class ideas. Inevitably to get some understanding you must accept/ appreciate / be aware of, the hierarchical Object Orientated Programming, (OOP), concept that VBA is modelled on: Very simplified: we start, as it were towards the top, or top left, with classes and from those objects are made and they finally have Properties. The final objects with their properties ( and methods etc.), could be crudely be regarded as some "actual" or significant "things", whereas the class is just the details of how to make the objects, or a template to be used over and over again and added to or filled in appropriately to suit the particular instance like a classic simple example,
_ The concept of a car could be regarded as a Class,
_ a particular model as an instance of it,
_ and a color would be a property.
( _ A method could be a way to paint the car, although the OOP is deliberately a bit vague and Methods and Properties often are similar and people argue sometimes as to whether something is a Method or a Property. )
That is very simplified. It will do for now, but just note we could have something like this in the actual hierarchical structure
Class - - Class - - Object - - Object - - Object - - Properties
, or even more complicated. It is not a precise concept. It is what is finally given to us at the user interface

The VB Editor
It is almost impossible not to use objects from a class if you are using Excel. By default we have limited direct access to any classes other than to make objects from them, assign variables to them, or modify the properties of the objects made from them.
The VB editor is a tool we use , often referred to as the development environment, that , amongst other things, by default ,allows us to conveniently look at and develop/ change our objects.
But there are two sorts of Class that we can create, so as to have our own custom Class. We can use the VB Editor to do that as well

Use Keys Alt+F11 from Excel to get the VB Editor
, or alternatively
, right click on any worksheet tab and select view code, - https://i.postimg.cc/J04LpnsW/Right-...eet-coding.jpg
Attachment 5865
,That will show you initially a representation of some of the existing Excel objects associated with your file, which usually by default will be for the workbook itself, ThisWorkbook, and the worksheets you have. (If you took this second approach to get the VB Editor then initially the big window will be for any coding you may add for the worksheet who’s tab you selected.)
https://i.postimg.cc/nh69N8KW/Big-wi...u-selected.jpg
Attachment 5866
We are for now not interested in the existing things. They are associated with existing Excel objects.
It is important to realise that most object things in Excel follow the object orientated programming ideas, which for us in the current discussions means they were made from referring to some sort of blueprint / template / set of written instructions etc. We refer to these blueprint / template / set of written instructions etc. as a Class.
For most of the existing Excel objects, we have only limited, if any, direct access to the Class. That is because it will be propriety information allowing you to construct Excel, so trade secrets as it were.

We are interested in the available adding of a New Class UserForm. Associated with that UserForm Class thing is one of two class related module types that Microsoft makes available to us.
"Get one" with a bit of right clicking near the representation of your Excel file in the left hand VBA project window of the VB Editor,
https://i.postimg.cc/GmZzK54L/Do-a-b...-Form-opti.jpg , https://i.postimg.cc/kgVfxmXY/Do-a-b...orm-option.jpg
Attachment 5867 , Attachment 5868

You should then see the basic tools of
_ a simple pin board looking box or "form", usually with the default name of UserForm1 ,( both for the main class thing and the caption name on the pin board thing top left , ( more to that later ** ) )
, and
_ a Toolbox dialog should also be visible. (If it’s not visible select View->Toolbox from the VB editor top ribbon menu. We use the toolbox to add controls to our UserForm.)
The bottom left window should also now show the properties related to this UserForm
https://i.postimg.cc/W3Fgbm8z/User-F...-VB-Editor.jpg , https://i.postimg.cc/852WFFXH/User-F...-VB-Editor.jpg

Note that we have actually added a class, called UserForm1, and the class module that appears is part of the tools we have available for working on it.

I will change the Name property to suit the previous discussions of this Thread, to ufResults , ** , and note that the Caption did not change,
https://i.postimg.cc/FzN5szZd/Captio...User-Form1.jpg

, and we will leave it like that for now, as this will help along the way, to demonstrate a quirk with UserForm Class things, which was brought out by our discussions … Unlike normal classes, UserForms are auto-instantiating, so you can simply call them by name and a new instance of the class is created. ….. as you use ufResults for the first time, a new instance of the form is created.
It's confusing because you effectively get a variable of the same name as the class. ###
This last statement we will come back to after getting the basics behind us now.
Just note finally this ufResults is actually the name of the Class, and just as with, for example,

Important summary of what we have so far.
So we have a class now. Parts of this class is the added UserForm module. The name ufResults is effectively the name of the greater thing that is the Class. Exactly what that greater thing is, is deep in the innards of Excel and Office, it concerns how the thing eventually works , and is anyone’s guess in the meantime what / where it is. We are only interested in the available interface that we see, which we can use to determine what sort of object can be made from it. We are only concerned with tools and interface given to us to help shape how the blueprint / template / set of written instructions etc finally "looks". But note what we see and finally have is not much more significant than an instruction manual. Itself it will not "do anything". We use it to make the ( or as many as we like ) final object(s) that "does" something.
Generally a class is designed to be used over and over again, just like a template.

What is a class- what we have so far
As we said previously , it’s a blueprint / template / set of written instructions
In simple layman beginner terms its like a piece of paper in your pocket with some notes and instructions on how to build an object of this type / class. Apart from the information on it, as a thing its pretty insignificant in itself.
The whole point of the class module(s) available to us, is just to
_ make it more convenient to have the text / instructions / explaining diagrams etc. conveniently for you to get at in Excel and use automatically / programmatically, and thereby automatically build an object from
, rather than
_ having to painstakingly read from the bit of paper and manually construct the thing and manually add coding to it etc.
A main difference between A UserForm class module, and the other standard "normal" Class module, is that we have that main extra box with grid points on it, and other bits and pieces in the tool box, like empty lists and boxes, to make it a bit easier to fill in. In simplest layman terns, the difference between,
_ For a normal class module being like a blank piece of paper . Mainly you would put the text of coding you were interested in for you final object, on it. (Part of that typical coding text is there for you to select and add to, to save you a bit of time typing)
, and
_ The UserForm is similar, but like a bit of Graph paper along with some other pre prepared bits and pieces to stick on it. Associate with most of those things are then also available part of the text of coding that might be typically associated with those things, there for you to select and add to, to save you a bit of time typing

Coding associated with a UserForm Class
There isn’t any. (That is why we cannot, by default, debug / step mode any text in coding form that we add to it). A class module is just a piece of paper with text, and in the case of a UserForm Class , text and a few other graphical things on it.
Obviously, although technically correct, any quick look at Class module being in every day use, or being developed by somebody, and it’s associated bits and pieces in the VB Editor will suggest that something looking like coding is around. So what is that about. We will get on to that in the over next post