Class related Stuff New Instancing

[DocAElstein]

https://www.excelforum.com/excel-pro...offers-it.html
https://www.excelforum.com/excel-pro...ml#post4383895
https://www.excelfox.com/forum/showt...ll=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

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 ) )


Thanks
Alan