Why a variable of type Range does not accept Nothing when cutting range of cells?

[Artik]

Hello everyone Excelholics and I wish all the best in the new year.

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]

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]

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

Artik

[Paul_Hossler]

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]

@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/1...-deletes-cells
http://www.thevbzone.com/secrets.htm
http://www.codeproject.com/Articles/...umented-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]

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:
[vba] Dim w1 As Worksheet
Dim w2 As Worksheet
Set w1 = ActiveSheet
Set w2 = ActiveSheet
MsgBox w2 Is w1

[/vba]

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

[Paul_Hossler]

@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 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/libr...(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]

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

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]

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

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]

@Paul,

I'm not sure I see your point there? Perhaps this will demonstrate the difference with Range:
[vba]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

[/vba]

[Paul_Hossler]

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]

@SamT -- maybe you had better add a little jargon.

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]

Paul,

If it were that simple, wouldn't this return False?
[vba]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[/vba]

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

[GTO]

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]

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.

[vba]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
[/vba]

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]

@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

Paul

[Aflatoon]

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]

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