When I have two arrays, Ar1 and Ar2, whenever I set Ar2=Ar1, it copies
Ar1 to another location and sets Ar2 equal to that clone... so that they
are not pointing to the same memory. Is there anyway to change this
behavior so that both Ar1 and Ar2 point to the same location in memory?

For example, how would I modify the following code to do print out the
same value. Perhaps the CopyMemory or other API function?



Sub ArrayTest()
Dim Ar1(2) As Long, Ar2() As Long

Ar1(0) = 190
Ar1(1) = 190
Ar1(2) = 190

Ar2 = Ar1

Ar2(1) = 222

Debug.Print Ar2(1), Ar1(1)
End Sub

As far as I know, there is no way to do this.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"R Avery" wrote in message
...
When I have two arrays, Ar1 and Ar2, whenever I set Ar2=Ar1, it
copies
Ar1 to another location and sets Ar2 equal to that clone... so
that they
are not pointing to the same memory. Is there anyway to change
this
behavior so that both Ar1 and Ar2 point to the same location in
memory?

For example, how would I modify the following code to do print
out the
same value. Perhaps the CopyMemory or other API function?



Sub ArrayTest()
Dim Ar1(2) As Long, Ar2() As Long

Ar1(0) = 190
Ar1(1) = 190
Ar1(2) = 190

Ar2 = Ar1

Ar2(1) = 222

Debug.Print Ar2(1), Ar1(1)
End Sub

I think that it is possible based on the following article:

www.ftponline.com/archives/premier/ mgznarch/vbpj/1997/10oct97/mc1097.pdf


But, I still don't know exactly how because playing with pointers in VBA
is dangerous -- I can't test methods of accomplishing this because
(since I don't know what to do), I encounter fatal errors fairly often,
crashing Excel.

However, I don't see any reason why we can't just set one of the array
variables equal to the other, change the original array so that it
refers to the null pointer, Erase the original array variable, and
continue working with the new array variable as if no funny business was
going on. That way, when VBA releases the memory allocated to the
variables, the original array won't release any memory since it refers
to the null pointer, and the 2nd array will release exactly what the 1st
array would have.

This may not work, I just see no reason (at this time) that it wouldn't
work.

I guess if I really want it as fast as I do, I should just learn a
language that natively supports pointers. =\