Help Understanding Class Instancing. Can't Set ws = New Worksheet. Intellisense offers it!

[Doc.AElstein]

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

[buran]

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

Please Login or Register  to view this content.

[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

[pike]

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.

[Doc.AElstein]

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 )

_3b) .....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 ___ = COLOR="#FFFFFF"] ___ [/COLOR] 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

[Kyle123]

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

Please Login or Register  to view this content.

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?

[Doc.AElstein]

Hi Kyle

......Does that help any?

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

Please Login or Register  to view this content.

...

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

Please Login or Register  to view this content.

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

[pike]

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

[Kyle123]

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

[rorya]

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 leftover from the old days when workbooks only had one sheet)

[Doc.AElstein]

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

Please Login or Register  to view this content.

_.....

_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

[Doc.AElstein]

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

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

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

[Doc.AElstein]

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

Please Login or Register  to view this content.

_.........

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

Please Login or Register  to view this content.

[rorya]

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.

[Doc.AElstein]

Hi

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 excample ( 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

[rorya]

I'm afraid none of that made any sense to me.

[Doc.AElstein]

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

Please Login or Register  to view this content.

Modify that a bit to this

Please Login or Register  to view this content.

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

[rorya]

It's a pointer. It now points to a different object. There was no copy of the original object made.

[Doc.AElstein]

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 ?

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

[rorya]

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.

[Doc.AElstein]

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

[shg]

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.

[Doc.AElstein]

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

[rorya]

But you were talking about a copy of an object. That is not at all the same thing as a copy of a pointer.

[Doc.AElstein]

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

[shg]

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.

[Doc.AElstein]

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

[rorya]

As long as you understand the "local copy" did not refer to a copy of the original object.

[Doc.AElstein]

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.

[pike]

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

Please Login or Register  to view this content.

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

[Doc.AElstein]

Hi Pike

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

I did not know that thanks
I Know it does not always give everything that you can use, but I thought what it did give was valid
_ - useful to know

As for the New stuff.- Yeah, it is getting well covered here now.

Alan

[pike]

Hi Doc,
Asking questions is a real skill so remember forum rule one.

1. Use concise, accurate thread titles. Your post title should describe your problem, not your anticipated solution.

and

Want to get your question answered quickly - Be descriptive and concise. Short, direct, and to-the-point question

Some indirect information from the Development library about Visual Basic-Specific IntelliSense https://msdn.microsoft.com/en-us/library/cxy240ac.aspx it is basically the same for VBA

[Doc.AElstein]

Hi Pike

..... Your post title should describe your problem, not your anticipated solution.....

Ha Ha .. Lol
changed the title for you
http://www.excelfox.com/forum/showth...=9716#post9716
http://www.excelfox.com/forum/showth...=9767#post9767

_....................................
( Thanks for the intellisense info. )
Alan

[pike]

Hey Doc,

No problem.

Don't forget to mark the thread solved

[Doc.AElstein]

....Don't forget to mark the thread solved

Somehow I guessed you’d say that , not sure why
There have been some great enlightenments here. Clearly some things are not fully understood by anyone, or those that know need a bit of time to explain clearly in terms Non experts like me can understand.***

Post #20 was great, I had waited 6 Months for that!!
I will leave the Thread unsolved for a few days in case any other Experts make a contribution. Then in a few Days I will mark it as solved.

Alan

Edit P.s.

***

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

So i will leave it, and see if any more clarifications come in.
I have more than done my bit too straighten it out.

[pike]

I needed to have the last post .. Mark solved was all i could think of

[shg]

For example this might have been a typo...

It is not a typo.

[rorya]

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

[Doc.AElstein]

Hi shg, Rory
You upset Pike now,, Lol...

_........

It is not a typo.

A pointer is a variable that contains a memory address.

Thanks for that.
No offence, I am very grateful for your help.
But, as I mentioned before, it makes things very difficult when you give such short answers like this.
( maybe you are just amusing yourself ?- why not – your well earned privilege )

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

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

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

And if not, thanks anyway again for coming back here.
I myself ( in my profession ) had many frustrating hours trying to explain things that were obvious to me to others who had no idea.
I feel your pain

Alan

[shg]

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

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

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

[Doc.AElstein]

yeah, we got that bit. Thanks.

[Kyle123]

So which bit haven't you got? Or have you got it now?

[Doc.AElstein]

never mind
thanks for all your help

[rorya]

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.

[Doc.AElstein]

Hi Rory,
Thanks for the reply.

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

A pointer is a Long (or LongPtr on 64bit Office). The number it contains is a memory address .... So passing it ByRef or ByVal is the same as passing a Long.

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

Thread Summary.

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

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

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

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


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

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

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


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

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

Thanks again everyone
Alan

[pike]

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

[Doc.AElstein]

Hi Pike
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



Edit.. wot, you mean it is virtual, like not real life stuff
like a computer program... Hmm there's an idea

[pike]

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.