I am missing something here. Could somebody please look at the
following code an explain me why this behavior? ( I modified an
example posted by Chip Pearson one year ago Subject " vatPtr &
objPtr", address:
http://www.google.com.au/groups?q=ob...ftngp09&rnum=1
)

Sub AAA()
Dim BaseVar As Long
Dim Rng As Range
Set Rng = Range("A1")
Debug.Print "AAA 1: BaseVar: " & VarPtr(BaseVar)
Debug.Print "AAA 1: VarPtr: " & VarPtr(Rng), "ObjPtr: " &
ObjPtr(Rng)
BBB Rng
Debug.Print "AAA 2: VarPtr: " & VarPtr(Rng), "ObjPtr: " &
ObjPtr(Rng)
Dim rng2 As Range

'new variable pointing to the same range than variable rng"
Set rng2 = Range("A1")
Debug.Print "AAA 3: VarPtr: " & VarPtr(rng2), "ObjPtr: " &
ObjPtr(rng2) & " <--why this one does not return the same address?"

Dim rng3 As Range

Set rng3 = Rng
Debug.Print "AAA 4: VarPtr: " & VarPtr(rng3), "ObjPtr: " &
ObjPtr(rng3) & " <--this one does as expected"

End Sub

Sub BBB(ByVal r As Range)
Debug.Print "BBB 1: VarPtr: " & VarPtr(r), "ObjPtr: " & ObjPtr(r)
Set r = Nothing
Debug.Print "BBB 2: VarPtr: " & VarPtr(r), "ObjPtr: " & ObjPtr(r)
End Sub

Enrique

I was hoping that someone would answer this post, but since they haven't
I'll take a stab. Mind you, this is guessing on my part, but maybe someone
will correct me if I'm off.

The result of objPtr is the address of the COM object created internally,
not the address of the particular cell in Excel. Take this example

Sub test()

'Allocate a memory location to variable rng1
Dim rng1 As Range
'Allocate a memory location to variable rng2
Dim rng2 As Range

'Create a COM object that contains Excel range A1
Set rng1 = Range("a1")
'Create a different COM object that contains Excel range A1
Set rng2 = Range("a1")

'Print the address of the first COM object
Debug.Print "rng1: " & ObjPtr(rng1)
'Print the address of the second COM object
Debug.Print "rng2: " & ObjPtr(rng2)

End Sub

So the different "values" that you are seeing are the different address of
the two COM objects that you created.

When you use a statement like this

Set rng1 = rng2

you are storing the same COM object address in two different memory
locations (variables). If you then show the objPtr for those variables, the
"values" will be the same because they point to the same COM object.

I hope that's accurate. And if it's accurate, I hope it helps you.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Enrique Bustamante" wrote in message
om...
I am missing something here. Could somebody please look at the
following code an explain me why this behavior? ( I modified an
example posted by Chip Pearson one year ago Subject " vatPtr &
objPtr", address:

http://www.google.com.au/groups?q=ob...ftngp09&rnum=1
)

Sub AAA()
Dim BaseVar As Long
Dim Rng As Range
Set Rng = Range("A1")
Debug.Print "AAA 1: BaseVar: " & VarPtr(BaseVar)
Debug.Print "AAA 1: VarPtr: " & VarPtr(Rng), "ObjPtr: " &
ObjPtr(Rng)
BBB Rng
Debug.Print "AAA 2: VarPtr: " & VarPtr(Rng), "ObjPtr: " &
ObjPtr(Rng)
Dim rng2 As Range

'new variable pointing to the same range than variable rng"
Set rng2 = Range("A1")
Debug.Print "AAA 3: VarPtr: " & VarPtr(rng2), "ObjPtr: " &
ObjPtr(rng2) & " <--why this one does not return the same address?"

Dim rng3 As Range

Set rng3 = Rng
Debug.Print "AAA 4: VarPtr: " & VarPtr(rng3), "ObjPtr: " &
ObjPtr(rng3) & " <--this one does as expected"

End Sub

Sub BBB(ByVal r As Range)
Debug.Print "BBB 1: VarPtr: " & VarPtr(r), "ObjPtr: " & ObjPtr(r)
Set r = Nothing
Debug.Print "BBB 2: VarPtr: " & VarPtr(r), "ObjPtr: " & ObjPtr(r)
End Sub

Dick: thank you very much for your answer. I got into this because of
the following:

In Chip's answer to the post where I took the example, he stated: "This
line shows that the local variable R in BBB was allocated at memory
location 1308176, and this address contains the value 46151360, which
is the memory location in which the actual Range("A1") object exists.
Note that ObjPtr returns the same location in both of the preceding
statements, 46151360, that is, the location or Range("A1")."

I understood that it was possible to get the address location for the
range("a1") object ( a cell). This would help me with something I am
doing that is the actual issue I am interested in.
Assume we have a group of cells in a workbook and I want to know if the
user changed one of the cells of the group. I had used the following
technics:

. changedcell.address = groupcell.address ( the "Is Operator" does not
work I guess because of the same reason the ObjPtr does not give a
consistent address for a cell)

. intercept(ChangedCells, UnionGroupCells) is not nothing

. and the third one I wanted to use was to create a dictionary with the
memory address of every cell in the group as keys and only ask if the
key ( memory address) of a changed cell exists in the dictionary. Off
course this does not work because I can not get a consistent memory
address for a cell object.

So the question is: do you know any other technics or a better way to
find if a specific cell or group of cells have been changed or selected?

Or do you know how to identify a cell consistently with a value that the
user can not change (like internal tag)?

(It could be tens of thousands of cells, in any worksheet in any
location, containing any value. They are dynamic, this means that a cell
can get in or out of the group under certain conditions. Dictionaries
would be great to keep the group cells under control. )

Thank for your time.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Enrique


changedcell.address = groupcell.address ( the "Is Operator" does not
work I guess because of the same reason the ObjPtr does not give a
consistent address for a cell)

intercept(ChangedCells, UnionGroupCells) is not nothing


What's wrong with those two methods? Do they not work for you?

and the third one I wanted to use was to create a dictionary with the
memory address of every cell in the group as keys and only ask if the
key ( memory address) of a changed cell exists in the dictionary. Off
course this does not work because I can not get a consistent memory
address for a cell object.

So the question is: do you know any other technics or a better way to
find if a specific cell or group of cells have been changed or selected?

Or do you know how to identify a cell consistently with a value that the
user can not change (like internal tag)?

(It could be tens of thousands of cells, in any worksheet in any
location, containing any value. They are dynamic, this means that a cell
can get in or out of the group under certain conditions. Dictionaries
would be great to keep the group cells under control. )


Unless I misunderstand you, there is a constant cell identifier that the
user can't change - its cell reference. Sheet1!A1 can only refer to one
cell, so why not store you ranges by their sheet name and cell address?

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

Dick: Both methods work but they are slow. Users percive a significant
delay when working with these workbooks. Your suggestion of creating a
dictionary with the full address of cells as key has an issue I has not
been able to deal with. Because there is not event to detect insertion
of rows or columns (directly or easly), the user can make the keys
invalid at any moment (I understant you are refering to save the address
as strings in the dictionary key unless there is another way to do it).

I am going to try something that could work. I found that Range object
has a property named ID. It can be used to store "an identifier or tag"
that the user can not change by any action he/she takes if I do not
allow them to save the workbook as a web page. The drawback would be
that ID property values are lost when the workbook is saved normaly. I
plan to dump the dictionary in a veryhidden worksheet before the
workbook is saved and load it with the workbook open event.

I mention on the above parragraph the key issue: "a secure way to
identify or tag cells irrespective of ANY action taken by the user".

Thanks for your time and answers. I will inform you if I get it working.
If you find something that can help me, please let me know.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!