Variant Array Copy, Array row contains Object

[Neal Zimm]

Hi All,
For the first time I had a need to put an object inside a two dimension
variant array.

The proc below was for some initial testing.
I had to code an object test at the line marked with 1.

I was surprised that the 'copy' at line 2. worked.
Can you tell me why?

Thanks.
Neal Z.



Sub Test()
Dim vArray, vArrayB
Dim Row As Long, Col As Long

ReDim vArray(1 To 2, 1 To 2)

Set vArray(1, 1) = ActiveWorkbook
vArray(1, 2) = "abc"
vArray(2, 1) = 24
Set vArray(2, 2) = ActiveSheet

ReDim vArrayB(LBound(vArray, 1) To UBound(vArray, 1), _
LBound(vArray, 2) To UBound(vArray, 2))

For Row = LBound(vArray, 1) To UBound(vArray, 1)
For Col = LBound(vArray, 2) To UBound(vArray, 2)

If Not IsObject(vArray(Row, Col)) Then
vArrayB(Row, Col) = vArray(Row, Col)
Else
'1. for individual items, Set is needed, expected.
Set vArrayB(Row, Col) = vArray(Row, Col)
End If
Next Col
Next Row

' 2. Why does this not 'error out' ?
vArrayB = vArray
End Sub

 

[Peter T]

A shorter example

Sub test2()
Dim a(1), b()
Set a(1) = ThisWorkbook
b = a
Debug.Print b(1).Name
Debug.Print ObjPtr(ThisWorkbook), ObjPtr(a(1)), ObjPtr(b(1))
End Sub

Behind the scenes the object pointer stored in a(1) is copied to b(1)

Regards,
Peter T

 

[Neal Zimm]

Thanks Joel,
Sure is nice to know Excel VBA knows how to copy an entire array but
hiccoughs on one item in the array. LOL.

I know I'm dating myself here, but I know from first hand experience,
because I was there, that Basic was invented at Dartmouth College in the
early 1960's.
The head of the Math Department, John Kemeny, and another math professor
John Kurtz, along with some graduate students wrote it. (Interesting to note
that Kemeny later became president of Dartmouth, he was an absolutely
brilliant man.)
They gave it to General Electric, in exchange for which, GE gave
Dartmouth a couple of rooms full of computers. (They were a maker of big
mainframes back in the day...)
I don't know if you're old enough to remember the power plant accident
at Three Mile Island, but it you Google it, you may run into Kemeny's name
again as he headed up the Federal Commission which investigated the accident.

Thanks again,
Neal

 

[Neal Zimm]

Thanks again Peter,
It's the behind the scenes stuff that drives me a little bit crazy from
time to time.