CopyOf2DArray (Values Only)

Question

---

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

Answer 1

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.

Answer 2

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

Answer 3

Although the code isn't Excel-specific in any way, since this is tagged excel 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
    Set sheet = ThisWorkbook.Worksheets.Add

    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)
    Dim alerts As Boolean
    alerts = Application.DisplayAlerts
    Application.DisplayAlerts = False
    sheet.Delete
    Application.DisplayAlerts = alerts
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.