Class related Stuff New Instancing

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

This is post #1 in Thread 2966 #post 24199
https://www.excelfox.com/forum/showt...New-Instancing
https://www.excelfox.com/forum/showthread.php/2966-Class-related-Stuff-New-Instancing
https://www.excelfox.com/forum/showt...ll=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-pro...offers-it.html

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

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

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

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

https://www.excelforum.com/excel-pro...ml#post4383957
https://www.excelforum.com/excel-pro...ml#post4383975
https://www.excelfox.com/forum/showt...ll=1#post24202

Originally Posted by buran

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

Code:

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

Originally Posted by Doc.AElstein

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** · 12-26-2018, 04:28 PM

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

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

Kyle
https://www.excelforum.com/excel-pro...ml#post4384945 Kyle
https://www.excelforum.com/excel-pro...ml#post4384945 Me
https://www.excelforum.com/excel-pro...ml#post4385771 Kyle
https://www.excelforum.com/excel-pro...ml#post4386098 Me
https://www.excelforum.com/excel-pro...ml#post4385771 Kyle
https://www.excelforum.com/excel-pro...ml#post4386098 Me

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

QUOTE=Kyle123;4384945 You can't set instance it because the powers that be say you can't. It really is as simple as that, likely that adding a worksheet does no small amount of complex wiring up that you can't do yourself to keep everything working. For example it adds a Class in the form of a Worksheet module, it handles the default naming of the worksheet etc.

Is it easier to visualise if it's written like this for you?

Worksheet Collection Class

Code:

Public Function Add() as Worksheet
    Set Add = New Worksheet
    Add.Name = "Next Available Name"
    AddWorksheetClass Add
End Function

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.

P.S Your analogy isn't quite correct, if a Class is a blueprint, using New returns the building.
Does that help any?
QUOTE=Doc.AElstein;4385018 Hi Kyle
Yep..

_............................... .... because the powers that be say you can't. It really is as simple as that,...
That almost ties up with my ….. ...it has simply been decided that in the VBA Software available to us Mortals that we should not be allowed to do such things. ....... 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.

_.¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬¬__ ___________________

A couple of things
_1 ) ……… You can't set instance it ....

?? are not both these “Set Instancing” it ( Or would both if both were allowed )
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.

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

_2 ) ..Is it easier to visualise if it's written like this for you?
Worksheet Collection Class

Code:

Public Function Add() as Worksheet
    Set Add = New Worksheet
    Add.Name = "Next Available Name"
    AddWorksheetClass Add
End Function

Sorry i cannot follow that......It is a pseudo code, correct ? But it makes no sense to me
Is this Pseudo code also along the lines of what you mean. I think I know what you are getting at. It could be that in the actual Worksheets.Add function there may be a
Set ws = New Worksheet
Which works, but we cannot see it ??

This would be my Pseudo code, .....

Code:

Sub KylesTests_1WorksheetsDotAddWonk()
' Add a Worksheet and name it
Call WorksheetsDotAdd("NewSh_t")
End Sub
Sub WorksheetsDotAdd(ByVal Name As String)  ' Kyle  http://www.excelforum.com/showthread.php?t=1138804&p=4384945#post4384945    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.
On Error GoTo GetMessageOutIcanCopyToGoogleTranslator
Dim wsAdd As Worksheet
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.
wsAdd.Name = "" & Name & ""
wsAdd.Visible = xlSheetVisible
Exit Sub
GetMessageOutIcanCopyToGoogleTranslator:
Debug.Print Err.Description
End Sub

Alan

P.s. If you have the correct translation for that error can you give it please :- I still have space in the Code Window to the right !

( I mean the error you get at a line like
Set wsAdd = New Worksheet
_....................................

Edit
_3 )
.....P.S Your analogy isn't quite correct, if a Class is a blueprint, using New returns the building....
_ Are we not saying the same, almost. It depends if I have all that Pigeon Hole Paper stuff right maybe.

Dim does whatever is necessary to let what it gets by Set “fit”
Set then puts something there. If you like rather than a Blue print you can say a Template. Or if you like an Empty Building. Whatever you say , Class is the oRefiginal. But you only do calls By Value on it
( To change it by Referring to it you go in the VB Editor etc, or use a Designer thing Rory was on about )
A Set New copies the original , whatever it is.
A Set without new is going a copy that wass already made and filled in a bit.
This
if a Class is a blueprint, using New returns the building
is lost on me.. – my way of thinking was that a copValy is made of the Class Blue print oRefiginal. It gets filled in / built , painted , name shield stuck / painted on it later as we go along

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.

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

https://www.excelforum.com/excel-pro...ml#post4385771 Kyle again repeated
Me https://www.excelforum.com/excel-pro...ml#post4386098

https://www.excelfox.com/forum/showt...ll=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...

Code:

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.Worksheets.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** · 12-26-2018, 04:32 PM

öajfasfjfkj

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

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-t...tax-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..

Code:

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

Code:

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/20160402...he-difference/
at this code

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

Code:

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** · 12-26-2018, 04:33 PM

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