Sep 12 2019 09:51 AM
Good Day,
I am searching for a way to track cells so that I know where they have been moved to if a user editing the workbook adds columns/rows, cuts / pastes cells.
I have found that I can use a Range variable in a module to keep track of movements of cells within a sheet, but if those cells are cut and pasted onto another sheet I find that the fact that it has moved sheets is lost.
Consider the following VBA in a module:
Dim MyRange As Range
Public Sub GetReference()
Public Sub PrintReference() |
If we put a string lets say "testing" in A1 if sheet1 then:
It seems that the address tracking part still works, yet the Range.Parent does not get updated when the paste occurs, and the Value2 parameter is somehow is reinitialized as another instance.
My questions are how can I identify:
Any other points would be helpful in understanding this automation challenge.
Thank you.
Sep 12 2019 11:07 AM
The point is you set the variable MyRange in the code GetReference.
And in the code PrintReference, you are printing it's properties only without setting it again and since you declared the MyRange variable is the module level variable, it is still have a reference set in GetReference code. And you are not getting it's value in the Immediate Window because you cut that cell and pasted at another location and the range MyRange which was previously set to refer to the Sheet1!A1 is still referring that cell which is now empty and therefore you are not getting its' value.
If you have one such case where you want to track a cell and all it's properties even if it is cut and paste into another location, you may do something like this...
Dim MyRange As Range
Public Sub PrintReference()
Set MyRange = Range("MyRange")
Debug.Print MyRange.Parent.Name & "->" & MyRange.Address & "->"; MyRange.Value2
End Sub
Sep 12 2019 02:08 PM
Thank you for your response.
The named range approach you suggest could work for needing to track small numbers of cells <30K, the reason I was interested in using a reference in VBA was to make the tracking lighter (ultimately I'll be using C#) however the object model is shared so I hoped to learn something here. Using named ranges to track the number of cells (~600K) I need would have performance impacts on the workbooks, not to mention with Named Ranges you will run into memory problems after about 64K.
What struck me with the VBA approach was that as long as I am cutting and pasting on the same sheet it works as I expect:
MyRange.Parent.Name = "Sheet1"
MyRange.Address = "$B$5" (or whatever address you paste to)
MyRange.Value2 = "Testing" (or whatever you type in the cell before or after moving it)
however if I paste on another sheet I get:
MyRange.Parent.Name = "Sheet1" (no mater what sheet you pasted on
MyRange.Address = "$B$5" (or whatever address you paste to)
MyRange.Value2 = Empty
It's almost like the object is reinitialized then some properties are cloned from the previous object.
Even if I could identify the ranges where this occurred in a trustworthy way I could deal with those cases, the problem I have is that the MyRange gives no indication of failure, just ambiguous wrong information.
Any additional thoughts would be most appreciated.