# CopyOf2DArray (Values Only)

Purpose:
Given a 2-Dimensional Array, return a copy of the Array. In VBA, if I just

Set newArray = oldArray


then newArray is simply a pointer to oldArray rather than an independent copy, hence this function.

Method:
Assign Array bounds. Create identically-sized Array. Iterate over Array and copy each element value.

Specific Areas of Interest:
The function feels very generic. The naming even more so. But then again it's supposed to be very generic. Is the naming alright or could it be better?

Function:

Private Function CopyOf2DArray(ByRef targetArray As Variant) As Variant
'/ Will error if the array contains objects

Dim LB1 As Long, UB1 As Long
Dim LB2 As Long, UB2 As Long
AssignArrayBounds targetArray, LB1, UB1, LB2, UB2

Dim newArray As Variant
ReDim newArray(LB1 To UB1, LB2 To UB2)

Dim i As Long, j As Long
For i = LB1 To UB1
For j = LB2 To UB2
newArray(i, j) = targetArray(i, j)
Next j
Next i

CopyOf2DArray = newArray

End Function

• I must be missing something. If you want a direct copy of oldArray then newArray = oldArray . No setting or redimming or looping required. What your CopyOf2DArray would be good for (with some minor modifications) is creating a transposed copy of oldArray that does not have the unsigned int limitations that the native Transpose does. See my_2D_Transpose(a1 As Variant, a2 As Variant).
– user66882
Jul 8, 2016 at 22:58
• @Jeeped beat me by a couple of minutes - I went the long route to verify a simple assignment is fine. Jul 8, 2016 at 23:04

I am going to challenge your premise

...then newArray is simply a pointer to oldArray rather than an independent copy.

The following code works fine ...

Sub myTest1()
Dim oldArray() As Double, newArray() As Double
Dim i As Long, j As Long

ReDim oldArray(0 To 1, 0 To 2)
For i = 0 To 1
For j = 0 To 2
oldArray(i, j) = (i * 10#) + (j * 1#)
Next j
Next i

newArray = oldArray

For i = 0 To 1
For j = 0 To 2
Debug.Print i, j, newArray(i, j)
Next j
Next i

End Sub


To verify that newArray is not just a pointer to oldArray, the following code ...

Public Declare PtrSafe Function VarPtrArray Lib "VBE7" Alias "VarPtr" (Var() As Any) As LongPtr

Sub myTest2()
Dim oldArray() As Double, newArray() As Double
Dim i As Long, j As Long

ReDim oldArray(0 To 1, 0 To 2)
For i = 0 To 1
For j = 0 To 2
oldArray(i, j) = (i * 10#) + (j * 1#)
Next j
Next i

newArray = oldArray

Debug.Print "oldArray", VarPtrArray(oldArray), VarPtr(oldArray(0, 0))
Debug.Print "newArray", VarPtrArray(newArray), VarPtr(newArray(0, 0))

End Sub


... generates this result ...

oldArray       1633360       350127888
newArray       1633356       350126600


All of which means that, for arrays that do not contain objects, a simple assignment (e.g. newArray = oldArray) is fine to copy an array.

• Ha! See my recent comment to the original question (not very old at all!) :)
– user66882
Jul 8, 2016 at 23:04
• Huh. TIL. Now I'm wondering where I got the idea in the first place. Probably a false extrapolation of object behaviour.
– Kaz
Jul 9, 2016 at 1:28
• Why does it matter if the arrays contain objects? The arrays actually contain pointers to the objects, so newarray = oldarray works regardless of whether the arrays are value-types of objects. Jul 9, 2016 at 2:53
• @Zak - I think early versions of VBA may not have allowed this simple assignment. Chip Pearson says as much. Not sure if/when this may have changed. Jul 9, 2016 at 3:26
• @ThunderFrame - I wasn't able to take the time to demonstrate to myself that this would work with an array of objects. So you may be (probably are) correct. Jul 9, 2016 at 3:31

Am I missing something? Why were you using Set newArray = oldArray? Arrays are not objects, so you can copy them using a normal assignment:

Sub TestLongArray()

Dim a(1, 1) As Long

a(0, 0) = 900
a(0, 1) = 901
a(1, 0) = 910
a(1, 1) = 911

Dim b() As Long
b = a

Erase a
Debug.Assert b(0, 0) <> a(0, 0)

End Sub

Sub TestVariantArray()
Dim c(1, 1) As Variant

c(0, 0) = 900
c(0, 1) = 901
c(1, 0) = 910
c(1, 1) = 911

Dim d() As Variant
d = c
Erase c
Debug.Assert d(0, 0) <> c(0, 0)

End Sub

– user66882
Jul 8, 2016 at 23:07
• Huh. TIL. Now I'm wondering where I got the idea in the first place. Probably a false extrapolation of object behaviour.
– Kaz
Jul 9, 2016 at 1:28

Although the code isn't Excel-specific in any way, since this is tagged there's a way to do this without even needing to iterate anything - this is probably far from perfect, but something like this anyway:

Private Function CopyArray2D(ByRef source As Variant) As Variant
On Error GoTo CleanFail

Dim redraw As Boolean
redraw = Application.ScreenUpdating
Application.ScreenUpdating = False

Dim sheet As Worksheet

sheet.Range("A1").Resize(UBound(source, 1), UBound(source, 2)).Value = source

CopyArray2D = sheet.UsedRange
CleanExit:
If Not sheet Is Nothing Then RemoveSheet sheet
Application.ScreenUpdating = redraw
Exit Function
CleanFail:
Debug.Assert False
Resume CleanExit
End Function

Private Sub RemoveSheet(ByVal sheet As Worksheet)
sheet.Delete
End Sub


Of course there's a cost associated with creating and deleting a worksheet, so for smaller 2D arrays it's possible the nested loops outperform it. But gut-feeling tells me the array dump would be $O(1)$, while the nested loops is certainly $O(n)$.

And as a bonus you don't have to worry about numbered, abbreviated variable names :)

'/ Will error if the array contains objects


Note, it will also error if the targetArray is not an array. I like that targetArray makes it pretty unambiguous what type you're expecting there, but "target" seems off, and makes it look like it's the return value - and the fact that the array must be passed ByRef reinforces that impression. Hence I'd go with source more than target. And since the function already says "I'm copying an array", then the unambiguous fooArray semi-Hungarian name (would you call a parameter fooString or fooInteger?) is actually redundant - so source would be sufficient and unambiguous to me.

CopyOf2DArray sounds a bit weird: you're using Copy as a noun, not a verb. Hence I'd put the 2D at the end and get rid of the Of, making CopyArray2D, which is more succinct.

• If you don't mind the new array being 1-based, use newarray = Excel.WorksheetFunction.Transpose(Excel.WorksheetFunction.Transpose(oldarray)) and it will work with 2D and 1D arrays, and it doesn't require any overhead. Jul 8, 2016 at 23:16