Excel VBA cut and paste bug?

[Bob Flanagan]

The following code illustrates what looks like a bad bug to me:

Sub CutAndPasteBug()
Dim rangeToMove As Range
Dim destRange As Range
Dim destWS As Worksheet
Dim sourceWs As Worksheet

Set sourceWs = Sheets("sheet1")
Set destWS = Sheets("sheet2")

Set rangeToMove = sourceWs.Range("A1")
Set destRange = destWS.Range("F5")

rangeToMove.Cut
destWS.Paste destRange

MsgBox rangeToMove.Parent.Name & " " & rangeToMove.Address & _
Chr(13) & Chr(13) & "It should be sheet2!!!!"
End Sub

The variable rangeToMove should end up referring to sheet2 F5, not sheet1 F5
after the cut is done. The cell address changes from A1 to F5, but the
variable is still pointing to sheet1. The entry in sheet 1A1 is moved to
sheet2 F5. I've tested this in Excel 97, 2003, and 2007.

Am I correct this is a bug, or is it by design? Comments back appreciated!

Bob Flanagan

 

[Jim Thomlinson]

That is by design. The only way to move an object is with the key word Set.
Cut is a method of the range and ultimately it is just a copy and paste
followed by a clear contents.

 

[Jay]

Hi Bob -

I'm always hesitant to brand any apparent problem as a bug until a lot of
eyes review it, but I think you're on to something here because the range
component of the object's address changes independently of its sheet
component... I agree that with you that the independent nature of such
important location information is significantly inconsistent. It'll be
interesting to see additional input on this thread topic.

A common strategy to avoid such a problem (and other cascading problems due
to cutting/moving) is to copy-paste-delete. The fact that it's a common
strategy might be due in part to developers getting burned by the problem you
describe.

If feedback in this thread doesn't resolve the issue, you can contact
Microsoft with your information at:

http://support.microsoft.com/contactus/?WS=communities

This location contains links to "Send Questions", "Report a bug", and others.

 

[Peter T]

Hi Bob,

Just to add to Jim's comments, a Range object is always tied to its
worksheet. It can move around the sheet with cut, insert/delete rows etc,
but can't change its parent without re-adoption. Similar applies with other
objects.

I can't see the problem with your example as you already know destRange. But
if say you are setting a global range object that might be moved elsewhere
by user you could do something like this -

Set sourceWs = Sheets("sheet1")
Set destWS = Sheets("sheet2")

Set rangeToMove = sourceWs.Range("A1")
rangeToMove.Name = "abc"
Set destRange = destWS.Range("F5")

rangeToMove.Cut
destWS.Paste destRange

Set rangeToMove = Names("abc").RefersToRange
Names("abc").Delete

MsgBox rangeToMove.Parent.Name & " " & rangeToMove.Address & _
Chr(13) & Chr(13) & "It should be sheet2!!!! and indeed it is
Sheet2"

Regards,
Peter T