Forum Discussion
Looking for a way to track and reference cells in VBA
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...
- Select the cell A1 on Sheet1.
- Type MyRange in the Name Box to create a Named Range which refers to the cell A1 on Sheet1.
- Enter any value in A1 on Sheet1, say 'Testing'.
- Now run the code PrintReference given below.
- Now cut the cell and paste it into any available sheet in the file.
- Run the code PrintReference again and you will get it's latest properties.
Dim MyRange As Range
Public Sub PrintReference()
Set MyRange = Range("MyRange")
Debug.Print MyRange.Parent.Name & "->" & MyRange.Address & "->"; MyRange.Value2
End Sub
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.