arraysexcelvba

Pasting an Array to a Column Range in MS Excel Using VBA

Problem: Develop a sub-procedure to paste an array of data into a range of cells (without looping) in an MS excel worksheet.

Note: the functions build_date_array & populate_date_array work together to create and store data in an array. The sub-procedure paste_array calls on these functions before trying to paste data into the worksheet "test". The sub-procedure as-is is incorrectly pasting the first element of the array in the example coded below. See the two images attached.


build timeline data storage array
Function build_date_array(length As Integer) As Variant

' declare array variable
Dim eNumStorage() As String ' initial storage array to take values

' dimension the array
ReDim eNumStorage(1 To length)

' set empty array to variable to pass back to module
build_date_array = eNumStorage

End Function

' Populate a date array
Function populate_date_array(dimed_array As Variant, start As Variant) As Variant

Dim i As Long

' iterate through dimension empty array to populate it with data
For i = LBound(dimed_array) To UBound(dimed_array)
   
    If i = LBound(dimed_array) Then
        ' first element of array needs to be the desired start
        dimed_array(i) = Format(WorksheetFunction.EDate(start, 0), "MM/DD/YYYY")
    Else
        ' kick in function for 2nd element to end of array
        dimed_array(i) = Format(WorksheetFunction.EDate(start, i - 1), "MM/DD/YYYY") ' https://learn.microsoft.com/en-us/office/vba/api/excel.worksheetfunction.edate
    End If
   
Next i

populate_date_array = dimed_array

End Function

Sub paste_array()

Dim wb As Workbook, sh As Worksheet, date_array As Variant, horizon As Integer

Set wb = ActiveWorkbook '  instance of workbook
Set sh = wb.Sheets("test") ' instance of sheet

horizon = 5 ' time increment in months

date_array = build_date_array(horizon) ' build empty array

date_array = populate_date_array(date_array, "10/01/2024")  ' populate array

LastRow = sh.Cells(sh.Rows.Count, 1).End(xlUp).Row + 1 ' get last row

'sh.Range("A2").Resize(UBound(date_array), 1).Value = date_array

sh.Range(sh.Cells(LastRow, 1), sh.Cells(UBound(date_array), 1)).Value = date_array

End Sub

Split Screen View of Code and Output:

enter image description here

Solution

  • Two issues were identified in the code:

    sh.Range(sh.Cells(LastRow, 1), sh.Cells(UBound(date_array), 1)).Value = date_array
    1. sh.Cells(UBound(date_array), 1) does not point to the last cell in the intended target (populated) range.
    2. date_array is a one-dimensional array. When populating cells in a column with this array, only the first item is written to all cells. To fix this, you need to use the Excel worksheet function Transpose.

    Pls try

    sh.Cells(LastRow, 1).Resize(UBound(date_array), 1).Value = Application.Transpose(date_array)

    Microsoft documentation:

    Range.Resize property (Excel)