5
\$\begingroup\$

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
\$\endgroup\$
2
  • 2
    \$\begingroup\$ 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). \$\endgroup\$
    – user66882
    Jul 8, 2016 at 22:58
  • \$\begingroup\$ @Jeeped beat me by a couple of minutes - I went the long route to verify a simple assignment is fine. \$\endgroup\$
    – OldUgly
    Jul 8, 2016 at 23:04

3 Answers 3

6
\$\begingroup\$

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.

\$\endgroup\$
5
  • \$\begingroup\$ Ha! See my recent comment to the original question (not very old at all!) :) \$\endgroup\$
    – user66882
    Jul 8, 2016 at 23:04
  • \$\begingroup\$ Huh. TIL. Now I'm wondering where I got the idea in the first place. Probably a false extrapolation of object behaviour. \$\endgroup\$
    – Kaz
    Jul 9, 2016 at 1:28
  • \$\begingroup\$ 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. \$\endgroup\$ Jul 9, 2016 at 2:53
  • 1
    \$\begingroup\$ @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. \$\endgroup\$
    – OldUgly
    Jul 9, 2016 at 3:26
  • \$\begingroup\$ @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. \$\endgroup\$
    – OldUgly
    Jul 9, 2016 at 3:31
4
\$\begingroup\$

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
\$\endgroup\$
2
  • \$\begingroup\$ Sorry ThunderFrame - you spent about 8 minutes too long creating your excellent answer. :) \$\endgroup\$
    – user66882
    Jul 8, 2016 at 23:07
  • \$\begingroup\$ Huh. TIL. Now I'm wondering where I got the idea in the first place. Probably a false extrapolation of object behaviour. \$\endgroup\$
    – Kaz
    Jul 9, 2016 at 1:28
3
\$\begingroup\$

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
    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.

\$\endgroup\$
1
  • 2
    \$\begingroup\$ 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. \$\endgroup\$ Jul 8, 2016 at 23:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.