Class related Stuff New Instancing

[DocAElstein]

This is post #1 in Thread 2966 #post 24199
https://www.excelfox.com/forum/showthread.php/2966-Class-related-Stuff-New-Instancing
https://www.excelfox.com/forum/showthread.php/2966-Class-related-Stuff-New-Instancing
https://www.excelfox.com/forum/showthread.php/2966-Class-related-Stuff-New-Instancing?p=24199&viewfull=1#post24199
https://www.excelfox.com/forum/showthread.php/2966-Class-related-Stuff-New-Instancing?p=24199&viewfull=1#post24199


Some notes salvaged from this Thread https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.youtube.com/watch?v=bRd4mJglWiM&lc=UgxRmh2gFhpmHNnPemR4AaABAg.A0opm95t2XEA0q3Kshmu uY (https://www.youtube.com/watch?v=bRd4mJglWiM&lc=UgxRmh2gFhpmHNnPemR4AaABAg.A0opm95t2XEA0q3Kshmu uY)
https://www.youtube.com/watch?v=bRd4mJglWiM&lc=UgxRmh2gFhpmHNnPemR4AaABAg (https://www.youtube.com/watch?v=bRd4mJglWiM&lc=UgxRmh2gFhpmHNnPemR4AaABAg)
https://eileenslounge.com/viewtopic.php?p=318868#p318868 (https://eileenslounge.com/viewtopic.php?p=318868#p318868)
https://eileenslounge.com/viewtopic.php?p=318311#p318311 (https://eileenslounge.com/viewtopic.php?p=318311#p318311)
https://eileenslounge.com/viewtopic.php?p=318302#p318302 (https://eileenslounge.com/viewtopic.php?p=318302#p318302)
https://eileenslounge.com/viewtopic.php?p=317704#p317704 (https://eileenslounge.com/viewtopic.php?p=317704#p317704)
https://eileenslounge.com/viewtopic.php?p=317704#p317704 (https://eileenslounge.com/viewtopic.php?p=317704#p317704)
https://eileenslounge.com/viewtopic.php?p=317857#p317857 (https://eileenslounge.com/viewtopic.php?p=317857#p317857)
https://eileenslounge.com/viewtopic.php?p=317541#p317541 (https://eileenslounge.com/viewtopic.php?p=317541#p317541)
https://eileenslounge.com/viewtopic.php?p=317520#p317520 (https://eileenslounge.com/viewtopic.php?p=317520#p317520)
https://eileenslounge.com/viewtopic.php?p=317510#p317510 (https://eileenslounge.com/viewtopic.php?p=317510#p317510)
https://eileenslounge.com/viewtopic.php?p=317547#p317547 (https://eileenslounge.com/viewtopic.php?p=317547#p317547)
https://eileenslounge.com/viewtopic.php?p=317573#p317573 (https://eileenslounge.com/viewtopic.php?p=317573#p317573)
https://eileenslounge.com/viewtopic.php?p=317574#p317574 (https://eileenslounge.com/viewtopic.php?p=317574#p317574)
https://eileenslounge.com/viewtopic.php?p=317582#p317582 (https://eileenslounge.com/viewtopic.php?p=317582#p317582)
https://eileenslounge.com/viewtopic.php?p=317583#p317583 (https://eileenslounge.com/viewtopic.php?p=317583#p317583)
https://eileenslounge.com/viewtopic.php?p=317605#p317605 (https://eileenslounge.com/viewtopic.php?p=317605#p317605)
https://eileenslounge.com/viewtopic.php?p=316935#p316935 (https://eileenslounge.com/viewtopic.php?p=316935#p316935)
https://eileenslounge.com/viewtopic.php?p=317030#p317030 (https://eileenslounge.com/viewtopic.php?p=317030#p317030)
https://eileenslounge.com/viewtopic.php?p=317030#p317030 (https://eileenslounge.com/viewtopic.php?p=317030#p317030)
https://eileenslounge.com/viewtopic.php?p=317014#p317014 (https://eileenslounge.com/viewtopic.php?p=317014#p317014)
https://eileenslounge.com/viewtopic.php?p=316940#p316940 (https://eileenslounge.com/viewtopic.php?p=316940#p316940)
https://eileenslounge.com/viewtopic.php?p=316927#p316927 (https://eileenslounge.com/viewtopic.php?p=316927#p316927)
https://eileenslounge.com/viewtopic.php?p=316875#p316875 (https://eileenslounge.com/viewtopic.php?p=316875#p316875)
https://eileenslounge.com/viewtopic.php?p=316704#p316704 (https://eileenslounge.com/viewtopic.php?p=316704#p316704)
https://eileenslounge.com/viewtopic.php?p=316412#p316412 (https://eileenslounge.com/viewtopic.php?p=316412#p316412)
https://eileenslounge.com/viewtopic.php?p=316412#p316412 (https://eileenslounge.com/viewtopic.php?p=316412#p316412)
https://eileenslounge.com/viewtopic.php?p=316254#p316254 (https://eileenslounge.com/viewtopic.php?p=316254#p316254)
https://eileenslounge.com/viewtopic.php?p=316046#p316046 (https://eileenslounge.com/viewtopic.php?p=316046#p316046)
https://eileenslounge.com/viewtopic.php?p=317050&sid=d7e077e50e904a138c794e1f2115da95#p317050 (https://eileenslounge.com/viewtopic.php?p=317050&sid=d7e077e50e904a138c794e1f2115da95#p317050)
https://www.youtube.com/@alanelston2330 (https://www.youtube.com/@alanelston2330)
https://www.youtube.com/watch?v=yXaYszT11CA&lc=UgxEjo0Di9-9cnl8UnZ4AaABAg.9XYLEH1OwDIA35HNIei0z- (https://www.youtube.com/watch?v=yXaYszT11CA&lc=UgxEjo0Di9-9cnl8UnZ4AaABAg.9XYLEH1OwDIA35HNIei0z-)
https://eileenslounge.com/viewtopic.php?p=316154#p316154 (https://eileenslounge.com/viewtopic.php?p=316154#p316154)
https://www.youtube.com/watch?v=TW3l7PkSPD4&lc=UgwAL_Jrv7yg7WWC8x14AaABAg (https://www.youtube.com/watch?v=TW3l7PkSPD4&lc=UgwAL_Jrv7yg7WWC8x14AaABAg)
https://teylyn.com/2017/03/21/dollarsigns/#comment-191 (https://teylyn.com/2017/03/21/dollarsigns/#comment-191)
https://eileenslounge.com/viewtopic.php?p=317050#p317050 (https://eileenslounge.com/viewtopic.php?p=317050#p317050)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[DocAElstein]

Pike 1
https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html#post4384726
https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html#post4384926
https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html#post4385758
https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html#post4386099
https://www.excelfox.com/forum/showthread.php/2966-Class-related-Stuff-New-Instancing?p=24200&viewfull=1#post24200


Doc
The New keyword can't be used to create new instances of any intrinsic data type and can't be used to create dependent objects.
Hi Pike
Thanks for the reply. Appreciate you catching this Thread. :)
Doc
The New keyword can't be used to create new instances of any intrinsic data type and can't be used to create dependent objects.
:rolleyes:
Sorry, I do not understand :(
( Are you making this up ;) Lol ( Just kidding ! ) )

_ 1 )Can you translate?

I mean .....”can’t be used to....

_ 1a ) ....create Intrinsic data type..”?

“create a very basic fundamental data ?????” No idea what that means. ( It could mean anything )
-.................

_ 1b ) ....create Dependent Objects.
_.. sorry no idea again what you are saying there

_1b(i) So here I gave the example of a UserForm Module or Class Module.
The variable I make for it and the Object I create will be dependent on it

_1b(ii) If i insert a Class module and put a few of those
Get
And
Let
things in that Module, then Iwill have stuff there. Any New Object is dependent on that stuff.

_................................
_2 ) Hello :)
_................................



_3) Did you read my suggested answer ?
_3a)
So it is troubling me that I cannot do the second line here, which I thought might be an implicit default

1 Dim ws As Worksheet ’ Prepare Memory for Variable of this form
2 Set ws As New Worksheet ‘ Should be necessary to copy the Blue Print
3 Set ws = ThisWorkbook.Worksheets.item(1) ’ Fill in a lot ( if not all ) of the copy of the Blue print in one go. ( In this case I fill in all details from the first tab counting from the left )

_3)
As often I may have answered my question by preparing this question. Is the answer that, when I open Excel it makes these instances, which I see? For Object types which have existing Instances, already filled in, line 2 simply is not allowed as “they” do not want you to make a sheet from scratch. They do not give you any way to do that. There is no WorksheetObject Folder in the VB Project Window. ( The Worksheets Object is an under Class, as it were, of the Class Microsoft Excel Object. It is confined there in. ***** )
So they give you one or more instances and let you copy them and change them a bit.

And Set ws = ThisWorkbook.Worksheets.item(1) and Set VarA = New UsersClassForm
Or
And Set ws = ___ and Set = New

are similar only. One Copies the Blue print. –You have to “fill it in” to make the Object ( And change it later if you wish )
The other returns an already completed ( filled in ) Blue Print Copy. ( Which you can change as you wish. Also you can copy such a “Final Product” But if you do that……



_......

_3b) Further:
If I might re word that explanation a bit...... to add a Virgin Bit ( New – Virgin – that makes sense ) and correct it and er labberate it a bit, based on a parallel Thread I have running
http://www.excelforum.com/showthread.php?t=1138300&page=2&p=4384440&highlight=#post4384440
So Another go:

Set ___ = New ___
And
Set ___ = ___

These are similar. One Copies the Blue print. That copy is completely empty and unused, a Virgin Copy. The other is taking a copy of an already filled in Blue Print.

To Er labberate: lets take... A user Form and ...a Worksheet as Example:
A UserForm
Dim fm1 As UserForm1
Set fm1 = New UserForm1

The Dim statement will have resulted in adequate memory to be set aside to “deal” with an Object of the Class given. Specifically we have a memory location referenced by its “Address”, fm1 , ( we may call this a Pigeon hole. ). This will be adequate so as to take “all” that will ( at least initially ) be required to “handle” such an Object. In particular, a set of code lines ( we may call this instructions on a piece of paper ), will be required. When a code progresses in any other places where fm1 is used the code will be directed here. But at present it is “empty”. Attempting to run any other code line referencing the variable fm1 will raise an appropriate error!!
After execution of the Set ___ = New ___ line a copy is made of the Blue Print, that is to say the Code Lines ( Instructions on a piece of paper ) and these are passed to the Pigeon Hole. So now when VBA is sent here on encountering fm1 it knows what to do hopefully in all situations where fm1 is used. The code line instructions are there. . Effectively these code lines ( instructions” ) are there to handle when you “fill the Blue print in” to make the Object, and / or use it ( And then change it later if you wish ). The pigion Hole now contains a Virgin Copy of the Class Blue print instructions

A Worksheet:
Dim ws As Worksheet

The Dim statement will have resulted in adequate memory to be set aside to “deal” with an Object of the Class given. Specifically we have a memory location referenced by its “Address”, ws , ( we may call this a Pigeon hole. ). This will be adequate so as to take “all” that will ( at least initially ) be required to “handle” such an Object. In particular, a set of code lines ( we may call this instructions on a piece of paper ), will be required. When a code progresses in any other places where ws is used the code will be directed here. But at present it is “empty”. Attempting to run any other code line referencing the variable ws will raise an appropriate error!!
After execution of the Set ___ = ___ line a copy is made of a Blue Print, that is to say the Code Lines ( Instructions on a piece of paper ) ( which have already been instanced and consequently filled in a few times. ) (- This may have been done some time ago by Bill Gates. Some of the software he used and developed is within our Excel Software. - Hence we “see” a worksheet or three on opening a new Excel File. ) These code lines instructions are passed to the Pigeon Hole. So now VBA when sent here on encountering ws will know what to do, hopefully!. The code line instructions are there. . Effectively these code lines instructions are there to handle what to do when you use the Object or change or copy it etc.. as you wish

_.........
My original question is answered thus: It is syntaxly Ok to do
Set ws = New Worksheet
It is not, as I suggested, an Implied Implicit Default. It is a valid VB step. Bill Gates and others used and use it ( somewhere. ) . It is the same basic idea as
Set fm1 = New UserForm1
However, it has simply been decided that in the VBA Software available to us Mortals that we should not be allowed to do such things. ( Possible there is some very vague relation here to what you are attempting to say with “dependent objects” ?? – Bill Gates & co being dependant on their living that we cannot create these things ?? ).


_4 ) Maybe if you have time you could comment on this last suggested answer. It seems to make some sense, ( or at least I can understand it )

Thanks Pike... ( sorry if the Posts are a bit hard to follow ;) )

Alan

Related Threads
Rem Ref http://www.excelforum.com/showthread.php?t=1138300&page=2&p=4384440&highlight=#post4384440
Rem Ref http://www.excelforum.com/showthread.php?t=1101544&page=9&p=4381274&highlight=#post4381274
Rem Ref http://www.excelforum.com/showthread.php?t=1101544&page=10&p=4381275&highlight=#post4381275
Rem Ref http://www.excelforum.com/showthread.php?t=1101544&page=10&p=4381420&highlight=#post4381420


P.s.
Yeh , your answer is short and sweet. Does not really answer the question though. I mean if I knew the answer I would understand it, or it would make some sense.. But I am looking for help
But I do appreciate your response and bring the Thread “back up” in view.
My Threads are necessarily long. I am trying to answer the question, ....”....Help in Understanding Class Instancing. Why can’t I Set ws = New Worksheet. ( Intellisense offers it ! )...
Thanks again
Alan
;)
Hmm ok .
Lets add new methodology to your understanding and development in learning VBA .. Debug
Your next step in effectively learning VBA is to use the Tools provided in the Visual Editor

Evaluating your syntax with the Debug tool will generate a error code 430.

The Office development Centre will give you an explanation of the known causes
https://msdn.microsoft.com/en-us/library/office/gg278829.aspx

"You can't write code to control an object's behaviour unless it has been exposed for Automation. Check the documentation of the application that created the object for limitations on the use of Automation with this class of object"

Use the Office Development Centre search to refine your search to Worksheet Class
There are many articles in the library

The Worksheet object is a member of the Worksheets collection
Basically the limitation of the worksheet class is you cannot create new (dependant on Worksheets) Worksheet host item at run time in a document-level project.
You can only use the Worksheet default method
expression .Add(Before, After, Count, Type)

The Worksheet object is a member of the Worksheets(index) collection.
@Pike
Hi Pike ....add new methodology to your understanding and development in learning VBA .. Debug
Your next step in effectively learning VBA is to use the Tools provided in the Visual Editor
Evaluating your syntax with the Debug tool will generate a error code 430.....
...Lol ;)
_..... I spend more time in Debug F8 than on anything else ( I really shouldn’t ! )
I know the Basic Worksheet stuff .
But I appreciate you adding the info, - It adds well to what is becoming a great learning Thread. :) Thanks
_..............
.. error code 430.....

Thanks for the reference there .. I was missing that because my Err.Decription does not give the error number, 430, - so I overlooked it ( It is in the actual message box that pops up without an error handler, so the Err.Desription is a bit lacking there ! ) so I could not take that one further.....
.. error code 430.....
...... That sort of sounds like it might mean something relevant and an advancement on.. ”The powers that be just decided you cannot do that”
if anyone could translate that into English I would be grateful.
_....On the other hand may be that is again just saying you are "not allowed to do it."

We have this as well so far..
. ...”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. “...
_.....This sounds like again just saying you cannot / are not allowed to, do it

You can't write code to control an object's behaviour unless it has been exposed for Automation. ...”
_......Just out of passing interest a translation of that into English might be useful




_...... - the bottom line, for now, is just it is not allowed, as maybe it would cause a bit of extra / different writing and not really worth the effort.

Thanks again for the "education"
Alan

[DocAElstein]

https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html
https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html#post4383895
https://www.excelfox.com/forum/showthread.php/2966-Class-related-Stuff-New-Instancing?p=24201&viewfull=1#post24201





Help Understanding Class Instancing. Why can’t I Set ws = New Worksheet.
( Intellisense offers it ! )

Edit: 15 May 2016 Thread problem and Answer Summary in Post # 46 excelforum (https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html#post4387191)

Hi
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
_....
I am just trying to get straight in my mind about Classes, Objects, Instances and the like. I think I almost get it. There is loads of info out there !. :)

Here’s my thought so far...

_1 ) First let me see if we are on the same level, ( then I will give my actual question in more detail, and a suggestion for an answer )

I think this a correct simple summary:
My Class could “be” ( or rather it’s important bits, or its “body” could be ) in a UserForm Module or a Class module.
What that is in Layman’s terms is a Blue Print, or a Form, or a Questionnaire not yet filled in etc.
It does not really exist in the terms of a Final product.
It is just describing how something or an “object” of that form or type would be.

For now I will give a particular “Blue Print” a name ( but I will not worry for now too much about exactly what that name is referring to ). Let me call that Blue Print for now “UsersClassForm”

Now say I want to make one of these Objects.

So first the convention in Code lines is to give a variable the type of the Class. ( This prepares memory etc... for something of that , and allows me to use intellisense through typing a period ( . ) to get a selection to choose from the available Methods and properties of that Object )

Dim VarA As UsersClassForm
_........
I need now a copy of that Blue Print form ( as I may want to make another object of this form later so I don’t want to mess up the original Blue Print by filling that in ! ). So this will copy me a form

Set VarA = New UsersClassForm

( This is often referred to as making a ( New ) instance of the class )
_........

Then I go off and fill that in .. in various ways.. for example if my object was a Worksheet, then something like this would be done

Let VarA.Name=”Sheet1”

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

_2 )Ok for anyone who did not give up reading this , now comes my question again.

Up until now I have found that VBA always Lets you type in the bits it lets you miss out. Take the classic example, this
Cells(1, 1) = 1
will mostly, ( not always ), be taken as implicit default of
ActiveSheet.Cells(1, 1).Value = 1

So it is troubling me that I cannot do the second line here, which I thought might be an implicit default

1 Dim ws As Worksheet ’ Prepare Memory for Variable of this form
2 Set ws As New Worksheet ‘ Should be necessary to copy the Blue Print
3 Set ws = ThisWorkbook.Worksheets.item(1) ’ Fill in a lot ( if not all ) of the copy of the Blue print in one go. ( In this case I fill in all details from the first tab counting from the left )

_3)
As often I may have answered my question by preparing this question. Is the answer that, when I open Excel it makes these instances, which I see? For Object types which have existing Instances, already filled in, line 2 simply is not allowed as “they” do not want you to make a sheet from scratch. They do not give you any way to do that. There is no WorksheetObject Folder in the VB Project Window. ( The Worksheets Object is an under Class, as it were, of the Class Microsoft Excel Object. It is confined there in. ***** )
So they give you one or more instances and let you copy them and change them a bit.

And Set ws = ThisWorkbook.Worksheets.item(1) and Set VarA = New UsersClassForm
Or
And Set ws = ___ and Set = New

are similar only. One Copies the Blue print. –You have to “fill it in” to make the Object ( And change it later if you wish )
The other returns an already completed ( filled in ) Blue Print Copy. ( Which you can change as you wish. Also you can copy such a “Final Product” But if you do that
ws.Copy ....
_.. no surprise may be - you get a new Workbook with that Worksheet in it.***** ( At least unless you specify different ) )
:rolleyes:

Thanks
Alan

[DocAElstein]

https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html#post4383957
https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html#post4383975
https://www.excelfox.com/forum/showthread.php/2966-Class-related-Stuff-New-Instancing?p=24202&viewfull=1#post24202





To be hones, not sure I understand your question
the code should be



Sub example()
Dim ws As Worksheet
Set ws = Worksheets.Add 'ActiveWorkbook is assumed by default, also the new ws is added at the end, but you can specify after or before specific sheet
MsgBox ws.Name 'Just an example for use of the ws variable/object
End Sub



Hi buran,
Thanks for the reply.
That is not really what I am talking about.

I understand what you have explained.
You are using the Worksheets Method .Add to make a new sheet..
I have no problem with either making a new, or copying an existing, Worksheet.

I am talking more generally about VBA Classes, Objects, and the different way one instances those. ( I am not trying to create or add a Worksheet with
Set ws = New Worksheet
- This is a code line recognised by VBA ( via intellisense ) which from Syntax appears correct to set a new instance from the VBA Class Worksheets. The line is not necerssary. But I am ( was ) puzzled that in errors. My point was that it I expected that VBA allowed it not to be written, but that it was an implied code line, an Implied Default. As such I should be alowed to enter it. Or so i thought ; ) )

So what I am talikng about is something quite different ;)

I am talikng about advanced ( slightly ) VBA Theory.

I am looking for "Help Understanding Class Instancing"

But thanks anyway for the Reply. Appreciate the effort. :)

Alan

[DocAElstein]

https://www.excelfox.com/forum/showthread.php/2966-Class-related-Stuff-New-Instancing?p=24213&viewfull=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.php?p=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:

Dim Obj As ClassX
Set Obj = New ClassX
, as really doing this pseudo coding

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.php?p=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

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

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

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

[DocAElstein]

https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html#post4385771 Kyle again repeated
Me https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html#post4386098

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

_.....Kyle continued / ( repeated the last )

Kyle:
I was thinking also if we could really do that we may be privy to info to make Excel as Excel is about Worksheets / Spreadsheets. –
We might see after the period ( . Dot ) new interesting things, if we could Instance the true Worksheets Object.
When Bill Gates instances it for our Copy, he may be chopped a few bits out of the Blue Print . So we do not see those when looking at one that is already there instanced by him originally.
No, you wouldn't. A worksheet is a worksheet, it only has worksheet properties and methods. Whether you get one or instance one some other way, it's still only a worksheet.

I think that part of your confusion is that you are misunderstanding what's going on:
Dim ws As Worksheet
Set ws = New Worksheet
Set ws = Worksheets(“Sheet1”)

And as i am allowed do the second then i am doing that instancing once... or sort of. I mean I can then also do
Dim wsSht_1 As Worksheet
Set wsSht_1 = Worksheets(“Sheet1”)
Do I not have now two instances of Worksheets(“Sheet1”) ? ( ws and wsSht_1 ) . - Or is there a subtle difference.
No, you have one instance of the worksheet, you just have 2 variables pointing at the same object. Think of it as a bit like a phone book, you may have 2 people living at the same address, but only one telephone number. Nothing is getting copied anywhere, objects are never copied (not usually anyway) the variables (ws and wsSht_1 in your case) are just lookups that point at the same object (worksheet) (hence the term pointer).

You are correct on the pseudo code, the worksheet is instanced, you just can't see it.

I couldn't follow your last paragraph. Think of it like this a class is a blue print for an object, calling new creates that object from the blueprint you have provided. I suppose you could call it copying, but the terminology is confusing since you don't as a rule copy objects, you can create new instances of them (with the new keyword) or create a variable to point at an existing instance.

Me: Excel Natters with Kyle on Class and Worksheets
@ Kyle
Hi Kyle, thanks very much for the reply.

_1)
I was thinking also if we could really do that we may be privy to info to make Excel as Excel is about Worksheets / Spreadsheets. –
We might see after the period ( . Dot ) new interesting things, if we could Instance the true Worksheets Object.
When Bill Gates instances it for our Copy, he may be chopped a few bits out of the Blue Print . So we do not see those when looking at one that is already there instanced by him originally.
No, you wouldn't. A worksheet is a worksheet, it only has worksheet properties and methods.......
Not sure how you would know that. But if I can’t take your word for it but then failing Bill saying something I will accept that one.
:)
_................................................
..... you have one instance of the worksheet, you just have 2 variables pointing at the same object. .......
Yes on second thought I agree totally with you . Missed that sorry.
In the case of a few “ Set New “ on the same Class Object you will have more instances.
But not for a few “ Set “ on the same existing Object

In my Excels, by default, I have three instances of the Worksheets Class Object.
I .Add a sheet to get another instance. Ties up with this …...... correct on the pseudo code, the worksheet is instanced, you just can't see it................ so I think we have that one.. a quick code to fart about a bit to demo all that... :)

Sub InstanceFarts() ' http://www.excelforum.com/showthread.php?t=1138804&p=4385771#post4385771 Just farting about with Kyle.
'Worksheet(s) - not a New thingy
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets.Item(1) 'Not instancing.
Dim JackSht As Worksheet
Set JackSht = ThisWorkbook.Worksheets.Item(1) 'Not instancing

ws.Cells(1, 1).Value = "fooarrrrphh"
MsgBox "First tab from the left " & JackSht.Cells(1, 1).Value & "s" ' So one instance ... The Set was not a "New" one.

'UserForms - a New thingy
Dim fm1 As UserForm1, fm2 As UserForm1
Set fm1 = New UserForm1
Let fm1.Caption = "UserForm Klone fm1 foorrph1"
Set fm2 = New UserForm1 ' Instancing
Set fm2 = New UserForm1 ' Instancing
Let fm2.Caption = "UserForm Klone fm2 foorrph2"

MsgBox "fm1 Caption is " & fm1.Caption & vbCr & "fm2 Caption is " & fm2.Caption ' So two instances ... The Set was a "New" one.

'Instance a New Worksheet
Call WorksheetsDottyAdd ' Instnicing ( WorksheetsDottyAdd is a routine to demo a Set = New Worksheet as you can and as it may actually be happening. http://www.excelforum.com/showthread.php?t=1138804&p=4385771#post4385771
Dim wsNew As Worksheet
Set wsNew = ThisWorkbook.Worksheets.Item(ThisWorkbook.Workshee ts.Count) ' back to not instancing-

Let wsNew.Cells(1, 1).Value = "Fresh farrrrph"
MsgBox wsNew.Cells(1, 1).Value & " in New Instance of a worksheet."

Application.DisplayAlerts = False
wsNew.Delete
Application.DisplayAlerts = True

Set ws = Nothing
Set JackSht = Nothing ' ;) ' Jack **** - get it Lol.... ;)
Set fm1 = Nothing
Set fm2 = Nothing
Set wsNew = Nothing
End Sub
'
Sub WorksheetsDottyAdd()
Dim wsAdd As Worksheet
'What to do
Set wsAdd = Worksheets.Add(after:=ThisWorkbook.Worksheets.Item (ThisWorkbook.Worksheets.Count), Count:=1, Type:=xlWorksheet)
wsAdd.Name = "Sht_" & (ThisWorkbook.Worksheets.Count - 1) + 1 ' -1 as I just increased it by 1 from original above'
' What actually happens
'Set wsAdd = 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. https://msdn.microsoft.com/en-us/library/office/gg278829.aspx
' Pike: Basically the limitation of the worksheet class is you cannot create new (dependant on Worksheets) Worksheet host item at run time in a document-level project
' Rory: they are dependent objects - i.e. they can't exist in isolation, only as a child of a workbook). It is made somewhat confusing by the fact that the various Excel.Sheet classes are in fact workbooks. (I suspect this is leftover from the old days when workbooks only had one sheet)
' Alan: 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. 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 )
'wsAdd.Visible = xlSheetVisible
'wsAdd.Name = "" & Name & ""
End Sub
_.....

_2) Pointers ! …..
….. ......Think of it as a bit like a phone book, you may have 2 people living at the same address, but only one telephone number. Nothing is getting copied anywhere, objects are never copied (not usually anyway) the variables (ws and wsSht_1 in your case) are just lookups that point at the same object (worksheet) (hence the term pointer)............
the second bit looks good.. the first bit is may be not quite right. I have two pointers, yes, but there is only one person living there. – one instance of the worksheet

As for Pointers......
You could do me a very big favour here as this word Pointer is used very loosely. Assume the “Pigeon Hole” with “code instructions line Paper” idea has some validity...
Then is the Pointer basically what the variable holds?. - A “pointer” to the Pigeon Hole. That being the case, some ideas knocking around that for an Object in a ByRef call takes the Pointer and a ByVal call takes a copy of the Pointer are Ok but slightly contradict another idea that a copy of the “code instructions line Paper” from in the Pigeon Hole is taken.
There seems a subtle point here that no one is understanding.

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.

??????????? Not sure if anyone really understands enough to really answer that last bit..

_........
………….......I couldn't follow your last paragraph. Think of it like this a class is a blue print for an object, calling new creates that object from the blueprint you have provided. I suppose you could call it copying, but the terminology is confusing since you don't as a rule copy objects, you can create new instances of them (with the new keyword) or create a variable to point at an existing instance………….
Hmm... Back again to that one.. 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....

Thanks Kyle

[DocAElstein]

öajfasfjfkj

[DocAElstein]

Rory
https://www.excelfox.com/forum/showthread.php/2966-Class-related-Stuff-New-Instancing?p=24206&viewfull=1#post24206



Rory 1
Rory 13 May 2016 https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html#post4385820
Me 13 may 2016 https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html#post4386105
Rory 14 May 2016 https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html#post4386321
Me 13 may 2016 https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html#post4386339
Rory 13 May https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html#post4386344
Me 13 May 2016 https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html#post4386352
Rory 13 May 2016 https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.htmlpost4386360 It's a pointer. It now points to a different object. There was no copy of the original object made.
Me 13 May Me https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html#post4386382
Rory 13 may 2016 https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html#post4386388


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

Rory If you look in the available classes in the registry, none of them are for Worksheets (since, as Pike mentioned, they are dependent objects - i.e. they can't exist in isolation, only as a child of a workbook). It is made somewhat confusing by the fact that the various Excel.Sheet classes are in fact workbooks. (I suspect this is left-over from the old days when workbooks only had one sheet)
Excel Natters on Dependency with Rory, QUOTE=Doc.AElstein;4386105
@ Rory
Hi Rory
If you look in the available classes in the registry, none of them are for ...
I do not know my way to well around the “Registry” ? , .. so I missed that.
I was thrown off a bit that.....
Set ws = New worksheet
_....did not error at compile and it was offered by Intellisense. So if it is not in that “Registry” then sounds like maybe intellisense is not doing its job correctly there ?
.....since, as Pike mentioned, they are dependent objects - i.e. they can't exist in isolation, only as a child of a workbook..
Ahh... maybe we are almost pulling our resources to get an answer finally to this one...

A dependant Object cannot exist in isolation. Hmm. I guess some how the Worksheets must be in a Workbook. Things like the Worksheets Collection Object would not work too well if they were independent. ( .Count might give interesting results ( On the other hand .Count often does reveal interesting . ... lol .. ;) ). ( Here I am talking about the Worksheets collection object of a Workbook )

So how about this.

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. This is likely because there will be some very complicated “Wiring” as Kyle put it. Think of a Range Object. Pictorially that is nice to demonstrate “Inserting” “adding” a Range, where things must be shifted one way or the other to make space for it.............
http://www.excelforum.com/tips-and-tutorials/1136702-vba-range-insert-method-excel-oop-syntax-error.html
_................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 )

My final Code Line comment on that for now:
‘ 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. 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 )




( For a Class module it seems reasonable that you can make new independent instances. What code lines you are allow to write in there will somehow limit you, dependency fashion. Not too sure if this ties up with something like a UserForm. I lack the experience there? Can you “count the instances of UserForms. If you can easily do that through .Count of some Collections Object, then it makes my latest Theory a bit dodgy. If you have to get at that count indirectly, then that would sort of allow for my dependency argument.


_.....................................
..... they can't exist in isolation, only as a child of a workbook). It is made somewhat confusing by the fact that the various Excel.Sheet classes are in fact workbooks. (I suspect this is leftover from the old days when workbooks only had one sheet)

Interesting. I sort of had this idea ( unknowingly ) when I said that the default
.Copy
Done on a worksheet gives you a new Workbook with a copy of that sheet in it. Makes sense then - .Copy on a Worksheet will go and copy what it “is” - stuck on its umbilical cord to its Mother Workbook.

Thanks
Alan

_.......

P.s.

I think I will pass on wanting to know why these do not work, unless on the off chance there is a simple explanation ;) Lol..



Sub WorkBookWonk()
On Error GoTo ForGoogleTranlator
Dim WB As Workbook
Set WB = New Workbook ' Error 429 Objekterstellung durch ActiveX-Komponente nicht möglich Can not create object ActiveX Component
Set WB = ThisWorkbook
Exit Sub
ForGoogleTranlator:
Debug.Print Err.Description 'Ctrl G to get something to copy to google transaltor, but NOTE: the number is missing
End Sub
'
Sub ExcelWonk()
'Dim XL As Excel ' Compile Error Benutzerdefinierte Typ anstelle eines Projekts erwartet Custom Type expected instead of a project
'Set XL = New Excel ' Compile Error Benutzerdefinierte Typ anstelle eines Projekts erwartet Custom Type expected instead of a project
Set XL = Windows(1) ' Errors as expected that variable XL is not Declared 'cos i coouldn't!
End Sub
_.........

_..And that the following gives a compile error of "un allowed use of the reserved word Range" instead of the error we have been discussing relating to Worksheets is weird.
But maybe as Intellisense gets it right and does not offer it, then the compile initially thinks you are trying to use it as a user defined Class name


Sub RAreaWonk()
Dim rng As Range
Set rng = New Range ' Complile error Un allowed use of reseved Code Name Range
End Sub

_.______________
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.
No, there is no copy for objects.
Hmm. I guess it depends what you are talking about by Copy there ?
Inside a Called routine for example ( ByVal Call ), The Variable used for the Object can have its Address changed, and further you can get at that Address from the .Address Property applied to that "changed" Variable. So in that case when referring to the Object itself, it is referring to something that is not the original Object, ( I think ) , and the original Object will not have its Address changed.
So where is it getting the info from ?
Alan
I'm afraid none of that made any sense to me.
I sympathise, it is a bit advanced ;)

If you look here for example
http://excelmatters.com/2016/03/10/byval-or-byref-whats-the-difference/
https://web.archive.org/web/20160402130728/http://excelmatters.com/2016/03/10/byval-or-byref-whats-the-difference/
at this code



Sub Change_object_ByVal(ByVal rInput As Range)
' change the range variable to one row down
Set rInput = rInput.Offset(1)
End Sub

Modify that a bit to this


Sub Change_object_ByVal(ByVal rInput As Range)
' change the range variable to one row down
MsgBox rInput.Address
Set rInput = rInput.Offset(1)
MsgBox rInput.Address
End Sub

Then you will see that in that called routine rInput , (in terms of an Object ) it is referring to “something”. I agree it points if you like to the original for changes in Properties, ( as well as too “something” , maybe )
But “Something” must be referred to get the modified address in the called Sub at the second Message box
Alan
It's a pointer. It now points to a different object. There was no copy of the original object made.
now we are getting somewhere you said .....
different object.

so which one is that then, ??
if it is not a copy, but then maybe a temporary local Variable, .....

_... well then you could call it a copy, why not.



How is this:
I rent a car at the airport I arrive at.

Say it is exactly the same type as the ( original ) one I drove to the airport I flew from in.
Someone frigged the Speedo and other things so the Mileage for example is "pointed" by a radio sender to the original cars Speedo at the airport i flew from

But i could still say the car i am in is a copy of the one back at the airport i flew from. In the case of VBA that second car was “made” I think when I arrived.
OK I did not “Copy” that original Car. But I am using one of the same Type.
It is just word play.
My Wife Phones and asks what sort of a car i have at my destination airport.
I might say it is a copy of the one she drove with me to the airport in.

With hindsight Copy may be inappropriate word here. I did suggest a few times along the way that it is a temporary one of the same type as the original.

That is better may be to say ?
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.

[DocAElstein]

shg
shg https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html#post4386387
Me https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html#post4386398
shg https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html#post4386400
Me https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html#post4386409
shg https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html#post4386454
Me https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html#post4386461


Me https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html#post4386623
shg https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html#post4386857

shg https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html#post4386901
Me https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html#post4386909




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

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.
That is what I was trying to say ( And did a lot of times )
That is the first time in 6 Months of saying it that someone else did
Thanks so much
Sorry if I may have used the word "Copy” a bit out of context once when I meant that “local Copy”
I use the local Copy of the car at the destination airport.
The Original Copy I left with the Wife, bless him
There is a fundamental difference between a copy of a pointer and a copy of an object. I don't think you've got your brain around that.
I think I have... At least for a Laymen I have a pretty good understanding of the difference. If you have time to read my previous post and others elsewhere you will see that.
But I confess that i do not understand it to the level of understanding that you may have.
I think for my uses i have a good enough and correct enough understanding.
( Post #20 from you was a “God Send”. I have put through exactly that argument / explanation more times than I can remember, and that is the first time I had a response to it or some suppot in it
Thanks once again )
But f you could give a better one, when you have the time, it could be a great contribution to the Thread.
Alan
And I would say again... you do make a local Copy of an Object..( and everything else ) .
No, Alan. The only thing there is a copy of is a four-byte pointer.
It is not possible ( for me )to follow these little tit bits of info, sorry.
Post # 20 was great. I will stick with that
Thanks again
Alan


_.___________
For example this might have been a typo...
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....
It is not a typo.
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 to 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
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.
Rory 15 May 2024 (https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html#post4387099) 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.

[DocAElstein]

SCNASSAN

[DocAElstein]

From 14 May , 2016 (https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html#post4386623), 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.php?t=1101544&page=9&p=4381274&highlight=#post4381274
http://www.excelforum.com/showthread.php?t=1101544&page=10&p=4381275&highlight=#post4381275
http://www.excelforum.com/showthread.php?t=1101544&page=10&p=4381420&highlight=#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]

Pike 2
13 may 2016
Pike 13 May 2016 https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html#post4386481





https://www.excelfox.com/forum/showthread.php/2966-Class-related-Stuff-New-Instancing?p=24210&viewfull=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
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]

ss,chsajch

[DocAElstein]

Rory 2
Rory 13 May 2016 https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html#post4386388


Rory 13 may 2016 https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html#post4386412
Me 13 May 2016 https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html#post4386421
Rory 13 May 2016 https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html#post4386463
Me 13 May 2016 https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html#post4386465

Rory 13 maqy 2016 https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html#post4386894
Me 14 may 2016 https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html#post4386898

Rory 15 may 2016 https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html#post4387099
Me 15 May 2016 https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html#post4387191

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


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 (https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html#post4386901) 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 (https://www.excelforum.com/excel-programming-vba-macros/1138804-help-understanding-class-instancing-cant-set-ws-new-worksheet-intellisense-offers-it.html#post4387099) 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.
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.php?t=1101544&page=9&p=4381274&highlight=#post4381274
http://www.excelforum.com/showthread.php?t=1101544&page=10&p=4381275&highlight=#post4381275
http://www.excelforum.com/showthread.php?t=1101544&page=10&p=4381420&highlight=#post4381420

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

Thanks again everyone
Alan

[DocAElstein]

https://www.excelfox.com/forum/showthread.php/2966-Class-related-Stuff-New-Instancing?p=24213&viewfull=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.php?p=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:

Dim Obj As ClassX
Set Obj = New ClassX
, as really doing this pseudo coding

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.php?p=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

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

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

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.php?p=317564#p317564
https://eileenslounge.com/viewtopic.php?p=317564#p317564

[DocAElstein]

https://www.excelfox.com/forum/showthread.php/2966-Class-related-Stuff-New-Instancing?p=24214&viewfull=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.php?p=317564#p317564
https://eileenslounge.com/viewtopic.php?p=317564#p317564

https://eileenslounge.com/viewtopic.php?p=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<x>, Sheet<x>, 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<x>, 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.php?p=317579#p317579
https://eileenslounge.com/viewtopic.php?p=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/showthread.php/2966-Class-related-Stuff-New-Instancing?p=24215&viewfull=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/Factory_%28object-oriented_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 (https://en.wikipedia.org/wiki/Rubber_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]

https://www.excelfox.com/forum/showthread.php/2966-Class-related-Stuff-New-Instancing?p=24215&viewfull=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 (https://i.postimg.cc/k5dKywmT/Too-Many-veggie-Meat-balls.jpg) 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 (https://www.excelfox.com/forum/showthread.php/2451-Class-Stuff-VBA-Custom-Classes-amp-Objects-Class-Modules/page3) , 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

5891 https://i.postimg.cc/nz67LYRK/my-custom-class-module-that-I-have-got.jpg (https://postimages.org/)
my custom class module that I have got.JPG (48.18 KiB) Viewed 301 times https://i.postimg.cc/nz67LYRK/my-custom-class-module-that-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).
5892 https://i.postimg.cc/DZWLFYgy/Do-an-instanciate-and-fill-a-property.jpg (https://postimages.org/)
Do an instanciate and fill a property.JPG (35.09 KiB) Viewed 301 times https://i.postimg.cc/DZWLFYgy/Do-an-instanciate-and-fill-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
5893 https://i.postimg.cc/YSCQDSLF/What-I-would-like-to-apppear-after-my-instantiating.jpg (https://postimages.org/)
What I would like to apppear after my instantiating.JPG (48.89 KiB) Viewed 301 times https://i.postimg.cc/YSCQDSLF/What-I-would-like-to-apppear-after-my-instantiating.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]

later

[DocAElstein]

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/showthread.php/2966-Class-related-Stuff-New-Instancing?p=24217&viewfull=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/showthread.php/2966-Class-related-Stuff-New-Instancing/page2#post24217
https://www.excelfox.com/forum/showthread.php/2966-Class-related-Stuff-New-Instancing/page2#post24217
This is frompost https://www.excelfox.com/forum/showthread.php/2965-Class-related-Stuff-Userforms/page2#post24185
https://www.excelfox.com/forum/showthread.php/2965-Class-related-Stuff-Userforms/page2#post24185
https://www.excelfox.com/forum/showthread.php/2965-Class-related-Stuff-Userforms?p=24185&viewfull=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-Click-a-tab-to-show-worksheet-coding.jpg
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-window-for-any-coding-you-might-want-to-add-associated-with-the-worksheet-who-s-tab-you-selected.jpg
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-bit-of-right-clicking-near-your-file-in-the-left-VBA-projct-explorer-and-find-the-User-Form-opti.jpg , https://i.postimg.cc/kgVfxmXY/Do-a-bit-of-right-clicking-near-your-file-in-the-left-VBA-projct-explorer-find-the-User-Form-option.jpg
5867 , 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-Form-Toolbox-Properties-in-VB-Editor.jpg , https://i.postimg.cc/852WFFXH/User-Form-Toolbox-Properties-in-VB-Editor.jpg
https://i.postimg.cc/N9t5dHWv/User-Form-Toolbox-Properties-in-VB-Editor.jpg (https://postimg.cc/N9t5dHWv)https://i.postimg.cc/Q9qVwM39/User-Form-Toolbox-Properties-in-VB-Editor.jpg (https://postimg.cc/Q9qVwM39)

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/Caption-name-default-User-Form1.jpg
https://i.postimg.cc/Mc0QJw2B/Caption-name-default-User-Form1.jpg (https://postimg.cc/Mc0QJw2B)
, 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 (https://www.excelfox.com/forum/showthread.php/2965-Class-related-Stuff-Userforms?p=24187&viewfull=1#post24187)

[DocAElstein]

dnfklakhcf

[DocAElstein]

,SCJsc

[DocAElstein]

gjhg

[DocAElstein]

later