Class related Stuff New Instancing

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

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...t=#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...t=#post4384440
Rem Ref http://www.excelforum.com/showthread...t=#post4381274
Rem Ref http://www.excelforum.com/showthread...t=#post4381275
Rem Ref http://www.excelforum.com/showthread...t=#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/lib.../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