PDA

View Full Version : Why a variable of type Range does not accept Nothing when cutting range of cells?



Artik
01-01-2014, 04:40 PM
Hello everyone Excelholics and I wish all the best in the new year. :bggift:

Why in the procedure Test2 variable R is not Nothing, but points to the wrong range?

Sub Test1()
Dim R As Range

Set R = Sheet1.[A1:A2]

Sheet1.[A1].Cut Sheet2.[X1]
MsgBox R.Address(0, 0, , True)
End Sub


Sub Test2()
Dim R As Range

Set R = Sheet1.[A1:A2]

Sheet1.[A1:A2].Cut Sheet2.[X1]
MsgBox R.Address(0, 0, , True)
End Sub

Is it to be regarded as a bug of Excel?

Artik

Paul_Hossler
01-01-2014, 05:43 PM
1. Excel doesn't have 'bugs' -- it has undocumented features

2. That's the way .Cut works. You're taking the range R which starts out on Sheet1 and 'cutting' it (putting it on the clipboard) it and then putting it on Sheet2 (from the clipboard) as range R

3. The TestCopy sub does not change the address of R



Sub TestCut()
Dim R As Range

Set R = Sheet1.[A1:A2]
MsgBox R.Address(0, 0, , True)

Sheet1.[A1:A2].Cut Sheet2.[X1]
MsgBox R.Address(0, 0, , True)
End Sub



Sub TestCopy()
Dim R As Range

Set R = Sheet1.[A1:A2]
MsgBox R.Address(0, 0, , True)

Sheet1.[A1:A2].Copy Sheet2.[X1]
MsgBox R.Address(0, 0, , True)
End Sub



What are you trying to do?

Paul

Artik
01-01-2014, 06:25 PM
Paul, thank you for your reply, but I'm more interested the essence of the problem. Why variable R is not Nothing. But the range A1:A2 is gone. Why R takes the address of the destination (and it also inaccurate)? I am a rather advanced user VBA.

Sorry for my english. I use a translator.



1. Excel doesn't have 'bugs' -- it has undocumented features
:thumb :rotlaugh:

Artik

Paul_Hossler
01-01-2014, 08:43 PM
My understanding ...

'Range' is an object

'Dim' allocates but does not create an instance of the object

'Set' creates an instance of the object

Each object has a pointer (handle) with it's allocated address in memory which ObjPtr will return as a Long

Test1 -- R1's address is 0 until it's instanticated and then it has a address

Test2 -- R2 is Set to R1 so it's address is the same and R1 'Is' R2 (same ObjPtr)

Test3 -- R1 and R2 are created separately but assigned the same group of cells. Even though they both contain the same block of cells, they are NOT the same object (different ObjPtr)

Test4 -- R1 is assigned a block of cells, and then with .Cut it is assigned a different block of cells, but it is the same object (ObjPtr did not change) and still exists

Test5 -- Same as Test4 with the commands spread out

Test6 -- The only way to 'release' an Object (on purpose at least) is to Set it to Nothing



Option Explicit


Sub test1()
Dim r1 As Range, r2 As Range
MsgBox ObjPtr(r1)
Set r1 = Worksheets("Sheet1").Range("A1:B2")
MsgBox ObjPtr(r1)
End Sub

Sub test2()
Dim r1 As Range, r2 As Range
Set r1 = Worksheets("Sheet1").Range("A1:B2")
MsgBox ObjPtr(r1)
Set r2 = r1
MsgBox ObjPtr(r2)
MsgBox r1 Is r2
End Sub
Sub test3()
Dim r1 As Range, r2 As Range
Set r1 = Worksheets("Sheet1").Range("A1:B2")
MsgBox ObjPtr(r1)
Set r2 = ActiveShworksheets("Sheet1").Range("A1:B2")
MsgBox ObjPtr(r2)
MsgBox r1 Is r2
End Sub


Sub test4()
Dim r1 As Range, r2 As Range
Worksheets("Sheet1").Range("A1").Value = 100
Worksheets("Sheet1").Range("A2").Value = 200
Worksheets("Sheet1").Range("B1").Value = 300
Worksheets("Sheet1").Range("B2").Value = 400

Set r1 = Worksheets("Sheet1").Range("A1:B2")
MsgBox ObjPtr(r1)
MsgBox r1.Address(1, 1, xlA1, True)
r1.Cut Worksheets("Sheet2").Range("X4:Y5")
MsgBox ObjPtr(r1)
MsgBox r1.Address(1, 1, xlA1, True)
End Sub


Sub test5()
Dim r1 As Range, r2 As Range
Worksheets("Sheet1").Range("A1").Value = 100
Worksheets("Sheet1").Range("A2").Value = 200
Worksheets("Sheet1").Range("B1").Value = 300
Worksheets("Sheet1").Range("B2").Value = 400

Set r1 = Worksheets("Sheet1").Range("A1:B2")
MsgBox ObjPtr(r1)
MsgBox r1.Address(1, 1, xlA1, True)
r1.Cut
Worksheets("Sheet2").Select
Worksheets("Sheet2").Range("X4:Y5").Select
Worksheets("Sheet2").Paste

MsgBox ObjPtr(r1)
MsgBox r1.Address(1, 1, xlA1, True)
End Sub



Sub test6()
Dim r1 As Range, r2 As Range
MsgBox ObjPtr(r1)
Set r1 = Worksheets("Sheet1").Range("A1:B2")
MsgBox ObjPtr(r1)
Set r1 = Nothing
MsgBox ObjPtr(r1)
MsgBox r1 Is Nothing
End Sub







My understanding at least.

Paul

GTO
01-02-2014, 04:59 AM
@Artik:

Greetings,

As you will note, my comments are rather general and may not help your specific question. For a really short answer and for my pea-sized brain at least, there isn't really an error in your second sub. In my less-than-articulate thinking at least, the object referenced is still a range. The range/object referenced therefore still has the same properties, and indeed, none of these properties have changed their values (such as the value of a cell, or a border, etc...), save that the range/object has been moved "address-wise" in the same parent object - the workbook.

@Paul Hossler:

Greetings Paul :-)

Bob doesn't appear to have seen my PM yet, and I need to wake up in about 6+ hours, so here goes...

"'Set' creates an instance of the object"

I believe that you have more knowledge of vba/Excel than I, so of course, please correct me if I am in error. That said, I believe that Set, w/o the New keyword, sets a reference to an already existing object; vs. creates a new instance of the object. For instance, we cannot set a reference to a newly created 'Sheet1', as Excel will not allow it. On the other hand, if we have an Object defined such as UserForm1, then we can create multiple instances of it. To wit: Set myForm = New UserForm1

Thus, the New keyword, or CreateObject, is what creates the objects, and Set, sets a reference to it.

Again, I naturally hope that I am correct, but if not, please take me to task.

"Test2 -- R2 is Set to R1 so it's address is the same and R1 'Is' R2 (same ObjPtr)

Test3 -- R1 and R2 are created separately but assigned the same group of cells. Even though they both contain the same block of cells, they are NOT the same object (different ObjPtr)"

From: http://support.microsoft.com/kb/199824/en-us

"ObjPtr takes an object variable name as a parameter and obtains the address of the interface referenced by this object variable." (italics added - mws)

So far, that description seems as clear as mud to me, but I think (believe) that the address of the pointer to the object is being returned, not the address of the object itself (if that makes any sense at all). Thus - when R2 is set to R1, the address of the pointer is the same. But if a second reference is created, the second variable is not holding the same pointer (I wish I could word that better!), but, both pointers are "aimed" if-you-will, at the same actual object. Am I making any sense at all?

Mark

PS - here is stuff I found; you might find interesting :-)

http://support.microsoft.com/kb/199824/en-us
http://support.microsoft.com/kb/q182598
http://www.xtremevbtalk.com/showthread.php?t=230453
http://stackoverflow.com/questions/12127311/vba-what-happens-to-range-objects-if-user-deletes-cells
http://www.thevbzone.com/secrets.htm
http://www.codeproject.com/Articles/5685/Pointers-in-Visual-Basic-using-Undocumented-Functi
http://support.microsoft.com/kb/199824

And while this seemed the most confusing to me (as if the rest was not already!), it seems valuable. Whilst included in Karl E. Peterson's site, this article appears to have been authored by a Matthew Curland (given the signature at the bottom).

http://vb.mvps.org/tips/varptr.asp

At least for me, it appears to work if one substitutes "msvbvm60.dll" for the older .dll referenced in the article.

Aflatoon
01-02-2014, 06:51 AM
Test3 is actually due to the fact that the Range property returns a new object every time it is called - even with the same parameters - rather than the fact of not using Set r1 = r2. Contrast it with the result of:
Dim w1 As Worksheet
Dim w2 As Worksheet
Set w1 = ActiveSheet
Set w2 = ActiveSheet
MsgBox w2 Is w1



which is the same principle but returns True at the end.

Paul_Hossler
01-02-2014, 07:35 AM
@Mark



I believe that you have more knowledge of vba/Excel than I, so of course, please correct me if I am in error.


I took an OOP class a very long time ago, but that's be it. Entirely possible I used less than accurate terms in VBA environment, or I could be completely out to lunch back here in the cold



Online help -- Dim



"Declares variables (http://www.vbaexpress.com/forum/HV10383569.htm) and allocates storage space."



Online help -- Set

Set Statement

Assigns an object reference to a variable or property.

Syntax
Set objectvar = {[New] objectexpression | Nothing}

The Set statement syntax has these parts:

objectvar Required.

Name of the variable or property; follows standard variable naming conventions.

New Optional.

New is usually used during declaration to enable implicit object creation. When New is used with Set, it creates a new instance of the class. If objectvar contained a reference to an object, that reference is released when the new one is assigned. 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.



So my understanding is that for 'built in' objects at least (can't use keyword New for intrinisic objects like Range)

Dim r1 as Range creates a pointer to a pointer (handle is the term I think). Since there is Nothing (pun intended) at the memory location the handle points to yet (no Set yet), ObjPtr (r1) = 0

Set r1 = ..... allocates (heap space to actually hold the range object. Now there is something on the heap so ObjPtr (r1) <> 0 but a real memory location
(http://msdn.microsoft.com/en-us/library/windows/desktop/aa366711(v=vs.85).aspx)


Set r1 = Nothing releases the heap space holding the range object and now ObjPtr(r1) = 0 again. But r1 still exists as an existing variable


Hopefully Bob, or Ken or some of the other VBA/Windows gurus will weigh in, but that was my understanding

Paul

Paul_Hossler
01-02-2014, 07:39 AM
@Aflatoon



Test3 is actually due to the fact that the Range property returns a new object every time it is called - even with the same parameters - rather than the fact of not using Set r1 = r2


Possibly just semantics, but I think it's not so much the Range property being called, but the fact that Set creates 2 different objects, that just happen to have the same contents (range, WS, etc.)





Test2 -- R2 is Set to R1 so it's address is the same and R1 'Is' R2 (same ObjPtr)
Test3 -- R1 and R2 are created separately but assigned the same group of cells. Even though they both contain the same block of cells, they are NOT the same object (different ObjPtr)



Paul

Aflatoon
01-02-2014, 07:48 AM
Paul,
No - in the sample I posted, the message says True, not False as it does with Range.

Re Set assignment, the VBA language spec states:

5.4.3.9 Set Statement

A Set statement performs Set-assignment of an object reference. The Set keyword is not optional and must always be specified to avoid ambiguity with Let statements.
set-statement = “Set” l-expression “=” expression
Static Semantics. This statement is invalid if any of the following is true:
- <expression> cannot be evaluated to a data value(5.6.2.1).
- <l-expression> is classified as something other than a variable, property or unbound member.
- Set-coercion from the declared type of <expression> to the declared type of <l-expression> is invalid.
- <l-expression> is classified as a property, does not refer to the enclosing procedure, and <l-expression> has no accessible Property Set.

Runtime Semantics. The runtime semantics of Set-assignment are as follows:
- Evaluate <expression> as a data value to get a value.
- Set-coerce this value from its value type to an object reference with the declared type of <l-expression>.
- If <l-expression> is classified as an unbound member, resolve it first as a variable, property, function or subroutine.
- If <l-expression> is classified as a variable:
- If the variable is declared with the WithEvents modifier and currently holds an object reference other than Nothing, the variable’s event handlers are detached from the current object reference and no longer handle this object’s events.
- Assign the coerced object reference to the variable.
- If the variable is declared with the WithEvents modifier and the coerced object reference is not Nothing, the variable’s event handling procedures are attached to the coerced object reference and now handle this object’s events.
- If <l-expression> is classified as a property with an accessible Property Let, and does not refer to an enclosing Property Get, invoke the Property Let, passing it the coerced object reference as the value parameter.
- If <l-expression> is classified as a property or function and refers to an enclosing Property Get or function, assign the coerced expression value to the enclosing procedure’s return value.
- If <l-expression> is not classified as a variable or property, runtime error 450 (Wrong number of arguments or invalid property assignment) is raised.

Paul_Hossler
01-02-2014, 09:06 AM
@Aflatoon

I think the difference is that since ActiveSheet is instantiated by Excel/VBA, it already has a pointer. I suspect that Setting an object to a created object just copies the pointer




Option Explicit
Sub test8()
Dim w1 As Worksheet
Dim w2 As Worksheet

MsgBox ObjPtr(ActiveSheet)

Set w1 = ActiveSheet
Set w2 = ActiveSheet

MsgBox ObjPtr(w1)
MsgBox ObjPtr(w2)

MsgBox w1 Is ActiveSheet
MsgBox w2 Is ActiveSheet
MsgBox w2 Is w1
'----------------
Set w1 = Nothing

MsgBox ObjPtr(ActiveSheet)
MsgBox ObjPtr(w1)
MsgBox ObjPtr(w2)

MsgBox w1 Is ActiveSheet
MsgBox w2 Is ActiveSheet
End Sub





Paul

SamT
01-02-2014, 09:11 AM
Very simplified with minimal jargon


Dim w1 As Worksheet 'Compiler assigns memory pointer(hFFFFA0) and configures memory block(hFFFFB0:hFFFFB15) to hold worksheet object.
Dim w2 As Worksheet ' Ditto to different point and block
Set w1 = ActiveSheet 'Compiler fills Mem block with object
Set w2 = ActiveSheet 'Ditto again
MsgBox w2 Is w1 ' "IS" compares two Objects (Mem Blocks,) not two pointers.

That information is actually worth less than you paid me for it.

Aflatoon
01-02-2014, 09:17 AM
@Paul,

I'm not sure I see your point there? Perhaps this will demonstrate the difference with Range:
Sub test()
Dim w1 As Object
Dim w2 As Object
Set w1 = Sheets(1)
Set w2 = Sheets(1)
MsgBox w2 Is w1

Set w1 = Workbooks(1)
Set w2 = Workbooks(1)
MsgBox w2 Is w1

Set w1 = Range("A1")
Set w2 = Range("A1")
MsgBox w2 Is w1
End Sub

Paul_Hossler
01-02-2014, 11:13 AM
All I'm saying that the both Sheets(1) and Workbooks(1) are existing objects, where as there is no allocated (instantiated) object in the VBA project for Range("A1")

When you Set w1 to an existing object, it 'Is' that object

When you Set w1 = Range("A1"), since there is no existing object, VBA instantiates a New object
When you Set w2 = Range("A1"), since there is no existing object, VBA instantiates a New (and different) object, it just happens to have the same properties as w1


If you add this to the bottom to your sample since w1 is now an existing object, then w2 'Is' w1



Set w1 = Range("A1")
Set w2 = w1
MsgBox w2 Is w1



Paul

Paul_Hossler
01-02-2014, 11:32 AM
@SamT -- maybe you had better add a little jargon. :hi:

I'm confused by ....



"IS" compares two Objects (Mem Blocks,) not two pointers



Would not the 2 'Mem Blocks' below be the same, since they're data-wise the same?




Option Explicit

Sub Paul_002()
Dim r1 As Range, r2 As Range
Set r1 = Worksheets("Sheet1").Cells(1, 1)
Set r2 = Worksheets("Sheet1").Cells(1, 1)
MsgBox r1 Is r2
End Sub



But I still think that 'Is' compares pointers

Paul

Aflatoon
01-03-2014, 01:31 AM
Paul,

If it were that simple, wouldn't this return False?
Sub Foo()
Dim ch1 As Object
Dim ch2 As Object

Set ch1 = ActiveSheet.Shapes(1)
Set ch2 = ActiveSheet.Shapes(1)

MsgBox ch1 Is ch2
end sub

I agree with your description of what is happening (since that's what I was saying ;)) but not why.

GTO
01-03-2014, 04:15 AM
Greetings All:

Firstly, thank you for the responses; I find this interesting while admittedly frustrating (learning-wise). Sorry for my slow response as well - I stared at this awfully late last night, and as I could today (which was not much; pesky working for a living kind of stuff...), but am barely getting to where I think I might be comprehending in at least a rudimentary manner.

Anyways, given:


Test3 is actually due to the fact that the Range property returns a new object every time it is called - even with the same parameters - rather ... which is the same principle but returns True at the end.

This becomes wayyy wrong, at least when discussing a Range.


@Paul Hossler:

...That said, I believe that Set, w/o the New keyword, sets a reference to an already existing object; vs. creates a new instance of the object...

Aflatoon, if you have the patience, let me see if I am grasping this a bit better. Excusing the laymen's verbiage, would this be true:

You state that a new Object is returned every time it is called. At my level of understanding, might this mean that rather than a truly new instance of the object, a new reference is created, this being aimed at the defined Range? Thus - rather than a new Object, we have defined a "temporary object" (for my utter lack of better description) that would be passed similar to a "regular" variable, By Reference???

Also - as Paul said, some of it (discussion/communication) can be more at verbiage (perception vs. intended message), and I want to understand this bit:

"ObjPtr takes an object variable name as a parameter and obtains the address of the interface referenced by this object variable." (italics added - mws)

vs.

"If the function succeeds, the return value is a pointer to the object." (In this case, from: http://allapi.mentalis.org/apilist/VarPtr.shtml)

If I am "getting" this at all, these basically say the same thing. That is, the 'address of the interface referenced', is saying the same thing as 'pointer'; as 'the interface' is the object. Do I have that bit right?


@Mark

I took an OOP class a very long time ago, but that's be it. Entirely possible I used less than accurate terms in VBA environment, or I could be completely out to lunch back here in the cold...

Shucks Paul, I have lost sight of your taillights you are so far ahead. When I started reading about allocation, pain in head and drooling occurred... :-)

Thank you all :-)

Mark

Aflatoon
01-03-2014, 05:50 AM
Hi, Mark,



This becomes wayyy wrong, at least when discussing a Range.

Actually no. Set is still assigning a pointer to an existing object - the Range property has just created said object ('Set' did not create it)



You state that a new Object is returned every time it is called. At my level of understanding, might this mean that rather than a truly new instance of the object, a new reference is created, this being aimed at the defined Range? Thus - rather than a new Object, we have defined a "temporary object" (for my utter lack of better description) that would be passed similar to a "regular" variable, By Reference???

This, I think, is where MS put the range into strange. ;)

Yes, a new object reference is created. You can view it as a new Object because it points to a separate memory location each time (ObjPtr will return a different address) rather than simply being a pointer to the same memory. Even though you may have assigned the same range to multiple variables, and they may all affect the same cell, they are, at least in theory, separate objects.




Also - as Paul said, some of it (discussion/communication) can be more at verbiage (perception vs. intended message), and I want to understand this bit:

"ObjPtr takes an object variable name as a parameter and obtains the address of the interface referenced by this object variable." (italics added - mws)

vs.

"If the function succeeds, the return value is a pointer to the object." (In this case, from: http://allapi.mentalis.org/apilist/VarPtr.shtml)

If I am "getting" this at all, these basically say the same thing. That is, the 'address of the interface referenced', is saying the same thing as 'pointer'; as 'the interface' is the object. Do I have that bit right?


ObjPtr and VarPtr are not the same. VarPtr returns the memory address of the variable; ObjPtr returns the memory address of the object to which the variable refers.

Sub foo()
Dim w1
Dim w2

Set w1 = Sheets(1)
' doesn't matter which of these you use
' Set w2 = Sheets(1)
Set w2 = w1

' these will be the same because they are pointers to the same Object
Debug.Print ObjPtr(w1)
Debug.Print ObjPtr(w2)


' these will be different because the two pointers are stored in separate memory locations
Debug.Print VarPtr(w1)
Debug.Print VarPtr(w2)
End Sub


Does that make any sense?

(One of these days I'd love to sit down with one of the Excel devs and get him/her to explain it fully!)

Paul_Hossler
01-03-2014, 07:08 AM
@Aflatoon --



If it were that simple, wouldn't this return False?


Shapes(1) is a pre-existing object and so it has a pointer

So Set-ing an user Dim-ed variable to an existing object appears to assign the pointer, and if I remember Bob's explanation from a long time ago, increments as reference counter as to how many variables are using it so that garbage collection can do its thing (but that's really go back a long way, so might not be accurate)

And ...Yes... I'm glad we're saying the thing since that increases my chances of not being too wrong




Option Explicit
Sub Foo()
Dim ch1 As Object
Dim ch2 As Object

Set ch1 = ActiveSheet.Shapes(1)
MsgBox ch1 Is ActiveSheet.Shapes(1)


Set ch2 = ActiveSheet.Shapes(1)
MsgBox ch2
Is ActiveSheet.Shapes(1)

MsgBox ch1 Is ch2
End Sub



Shapes(1) is a pre-existing object and so it has a pointer



(One of these days I'd love to sit down with one of the Excel devs and get him/her to explain it fully!)


Invite me please :rofl:

Paul

Aflatoon
01-03-2014, 07:16 AM
How is Shapes(1) a pre-existing object in any way that Range("A1") is not?

We are possibly veering off my original point which was that it was not Set creating different objects, but Range.

Paul_Hossler
01-03-2014, 07:45 AM
I was trying to use the CopyMemory API to copy an object's data to another object

Set O2 = O1 just copies the pointer, so that O1 and O2

Apparently better heads than me have determined that there is no way:

http://www.bigresource.com/VB-How-to-determine-size-of-Object--K3pUEUwQea.html


The closed I ever got was this, but I didn't rely on it. I think it expands on the earlier description of the differences between VarPtr and ObjPtr



Sub foo2()
Dim w1 As Worksheet, w2 As Worksheet

MsgBox VarPtr(w1)
MsgBox VarPtr(w2)

'length of 32 bit pointer is 4 bytes and
'w1 and w2 are variables containing pointers to objects
MsgBox VarPtr(w1) - VarPtr(w2)

Set w1 = Worksheets("Sheet1")
Set w2 = Worksheets("Sheet2")

MsgBox ObjPtr(w1)
MsgBox ObjPtr(w2)

'GUESSING HERE -- length of worksheet object is 3432 bytes
MsgBox ObjPtr(w1) - ObjPtr(w2)
End Sub



Paul

Paul_Hossler
01-03-2014, 08:03 AM
Maybe Excel handles Range objects differently. After all, it is Excel.



which was that it was not Set creating different objects, but Range.


I would have thought the pointer for the 2 lines in red would be different then, but I could be misunderstanding



Option Explicit
Sub foo3()
Dim r1 As Range, r2 As Range


MsgBox ObjPtr(r1)
MsgBox ObjPtr(r2)
MsgBox r1 Is Nothing
MsgBox r2 Is Nothing

'these are the same
MsgBox ObjPtr(Range("A1"))
MsgBox ObjPtr(Range("A1"))

'but after using Set ...
Set r1 = Range("A1")
Set r2 = Range("A1")

'this is the same as the original
MsgBox ObjPtr(r1)


'this is different
MsgBox ObjPtr(r2)
'all different
MsgBox ObjPtr(Range("A1"))
MsgBox ObjPtr(r1)
MsgBox ObjPtr(r2)

'all false
MsgBox r1 Is r2
MsgBox r1 Is Range("A1")
MsgBox r2 Is Range("A1")
End Sub



In any event, this has been an interesting discussion

I hope some of the Excel / VBA / Windows gurus will weigh in

Paul

Aflatoon
01-03-2014, 08:27 AM
It appears that there is some sort of internal collection of Range object references that get re-used:
Sub foo()
Dim w1 As Object
Dim w2 As Object
Set w1 = Range("A1")
Set w2 = Range("A1")
Debug.Print "w1=" & ObjPtr(w1)
Debug.Print "w2=" & ObjPtr(w2)

Set w1 = Nothing
Set w2 = Nothing
Debug.Print "w1=" & ObjPtr(w1)
Debug.Print "w2=" & ObjPtr(w2)

Set w1 = Range("A1")
Set w2 = Range("A1")
Debug.Print "w1=" & ObjPtr(w1)
Debug.Print "w2=" & ObjPtr(w2)

End Sub



returns consistently for me (at the moment):
w1=36338688
w2=28578944
w1=0
w2=0
w1=28578944
w2=36338688
Note the switch over at the end.

I think I'll pose the question to the Excel team and see what they say (assuming I can publish it).

GTO
01-03-2014, 10:57 PM
Hi, Mark,

Actually no. Set is still assigning a pointer to an existing object - the Range property has just created said object ('Set' did not create it)

This, I think, is where MS put the range into strange. ;)

Yes, a new object reference is created. You can view it as a new Object because it points to a separate memory location each time (ObjPtr will return a different address) rather than simply being a pointer to the same memory. Even though you may have assigned the same range to multiple variables, and they may all affect the same cell, they are, at least in theory, separate objects.


A friendly Hi back-atchya Aflatoon :hi:

Gosh, if there are patience awards being handed out, you get a prize for sure. Sorry that took so many efforts on your part; I think I was closing in on it so-to-speak, and your last explanation did the trick! (Or maybe more succinctly, I think it's finally hammered into my thick noggin.) Thank you so much :bow:.



ObjPtr and VarPtr are not the same. VarPtr returns the memory address of the variable; ObjPtr returns the memory address of the object to which the variable refers.

Sub foo()
Dim w1
Dim w2

Set w1 = Sheets(1)
' doesn't matter which of these you use
' Set w2 = Sheets(1)
Set w2 = w1

' these will be the same because they are pointers to the same Object
Debug.Print ObjPtr(w1)
Debug.Print ObjPtr(w2)


' these will be different because the two pointers are stored in separate memory locations
Debug.Print VarPtr(w1)
Debug.Print VarPtr(w2)
End Sub


Does that make any sense?

Uhm... Well, yes sir, it does make sense, but I don't believe I was referring to VBA's VarPtr. Might you have skipped looking at the link and assumed that the 'VarPtr' in the link's address referred to VBA._HiddenModule's VarPtr? If not, I am way off somewhere, so allow me to restate with (he says hopefully) better clarity. For brevity's sake, I'll take some leaps, hoping I am not in never-never land.

Okay, I believe that in http://allapi.mentalis.org/apilist/VarPtr.shtml , we are returning the memory address of the object, not of the variable. If I am correct in that part, then you will already see where I was going, as to asking if the two descriptions really mean the same thing, just different verbiage.

Oh and here's the leaps: Between the allapi link and http://vb.mvps.org/tips/varptr.asp , I think that:
what was once-> Declare Function VarPtr Lib "vbrun100.Dll" (Var As Any) As Long
at some point became-> Declare Function VarPtrAny Lib "vb40032.dll" Alias "VarPtr" (lpObject As Any) As Long
and eventually-> Declare Function VBObjPtr Lib "msvbvm50.dll" Alias "VarPtr" (ByVal pObj As IUnknown) As Long (Now "msvbvm60.dll"), as they all have the same alias.
Well, I think I was on track, I'd better stop there until I find out how hard I might be landing:ack:



(One of these days I'd love to sit down with one of the Excel devs and get him/her to explain it fully!)

I'm certainly with Paul on this. I'll just sit in the back quietly.

In closing, I certainly agree that it has been a very interesting discussion! Thank you both very much.

@Artik:

Yikes! I imagine that when you check back in, you will wonder, "What in God's green earth happened to my thread?!". Sorry about that. Did the back and forth discussion answer your question?

Mark

Aflatoon
01-04-2014, 03:09 AM
Hi again, Mark,

Yep - you got me. I saw VarPtr but overlooked which site the link was to (or I'd have realised it would be an API document rather than a VBA one).

Basically yes, you are correct that they say the same thing. A pointer to an object will always be a pointer to its interface (or one of them, anyway, since an object can have many).

I'll post back with any official response I get, but in the meantime, consider the apparent weirdness of this:
debug.print Range("A1") Is Range("A1")

More weight to the theory of an internal heap of range object references.

Paul_Hossler
01-04-2014, 07:36 AM
Or this



Sub MoreWierdness()
Dim r1 As Range, r2 As Range
Set r1 = Range("A1")
Set r2 = Range("A1")
Debug.Print Range("A1") Is Range("A1")
Debug.Print r1 Is r1 ' better be :-)
Debug.Print r1 Is r2
Debug.Print r1 Is Range("A1")

End Sub


Paul