Home > Software engineering >  Index notation to paste an array on a range
Index notation to paste an array on a range

Time:11-05

The following code is within a module

Option Explicit

Private sub paste_arr()
    Dim arr(2)
    
    arr(0) = 2
    arr(1) = 3
    arr(2) = 5

    with sh_array
   
        .Range(.Cells(2,5), .Cells(4,5)) = arr

    end with

Results in the following array

 2
 2
 2

It seems that only the first item in the list is pasted but I want to paste the entire array.

Other solutions don't seem to use the index notation. For example the following enter image description here

Option Explicit

Private Sub ZeroBased()
    
'    ' 1. Either
'    'Dim arr(2) As Variant
'    ' More correct is (Option Base related):
'    Dim arr(0 To 2) As Variant ' ensure zero-based

'    arr(0) = 2
'    arr(1) = 3
'    arr(2) = 5

    ' 2. Or
    'Dim arr As Variant: arr = Array(2, 3, 5)
    ' More correct is (Option Base related):
    Dim arr As Variant: arr = VBA.Array(2, 3, 5) ' ensure zero-based

    Dim cCount As Long
    'cCount = UBound(arr) - LBound(arr)   1 ' 2 - 0   1 = 3
    ' Since in this case LBound(arr) is 0, you can simplify:
    cCount = UBound(arr)   1 ' 2   1 = 3
    
    ' A simple way to write the values to the Immediate window (Ctrl G).
    Debug.Print Join(arr, ",") ' delimited
    Debug.Print Join(arr, vbLf) ' one below the other
    
    ' Write to a range
    With Sheet1
        ' Write to a one-row range:
        .Range("A1").Resize(, cCount).Value = arr ' A1:C1 ' note the comma
        ' Write to a one-column range:
        .Range("A3").Resize(cCount).Value = Application.Transpose(arr) ' A3:A5
    End With
    
    ' .Resize(, cCount) is short for .Resize(1, cCount)
    ' .Resize(cCount) is short for .Resize(cCount, 1)
    
    ' Interestingly, you could easily write only the first 2 items
    ' (think first 100 out of 1000 items):
    With Sheet1
        ' Write to a one-row range:
        .Range("E1").Resize(, 2).Value = arr ' E1:F1 ' note the comma
        ' Write to a one-column range:
        .Range("A7").Resize(2).Value = Application.Transpose(arr) ' A7:A8
    End With
    
End Sub
  • Related