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
Bookmarks