CODEWITHSUNDEEP
arraysexcelvba
zealousteedo
zealousteedo

Reputation: 23

writing multi dimensional array to excel vba

I'm seeking to write a one dimensional array to an excel spreadsheet without using any looping. Essentially I'm trying to speed up my code so that I can transform a prices series into a return series.

I've copied the price series into one variable, then perform the transformation to returns, but I can't seem to write the entire array back to excel. I seem to be only able to do it one-by-one using a loop. This essentially defeats the purpose of trying to avoid reading/writing from excel multiple times. My code is below.

Ideally I'm trying to get the final loop done without actually looping. 

For j = 1 To num_sec
    len_ror = wks_acp.Range("A:A").Offset(0,j).Cells.SpecialCells(xlCellTypeConstants).Count - 2
    last_row = max_row - len_ror + 1 
    ReDim prices(len_ror + 1) As Variant 
    ReDim rors(len_ror - 1) As Variant 

    prices = wks_acp.Range("$a$" & last_row - 1 & ":" & "$a$" & max_row).Offset(0, j).Value

For i = 0 To len_ror - 1 
    On Error GoTo errhandler
    rors(i) = (prices(i + 2, 1) / prices(i + 1, 1)) - 1

Next i

For i = 0 To len_ror - 1
wks_test.Range("$a$" & last_row + i).Offset(0, j) = rors(i)
Next i

Upvotes: 1

Views: 766

Answers (2)

Gary's Student
Gary's Student

Reputation: 96753

As an alternative, you can ReDim rors as a two dimensional array and use it this way:

Sub demo()
    Dim x As Long
    x = 10
    ReDim rors(1 To x, 1 To 1)

    For i = 1 To 10
        rors(i, 1) = i
    Next i

    Range("B9:B18") = rors
End Sub

enter image description here

Upvotes: 2

Tim Williams
Tim Williams

Reputation: 166306

You can write a one-dimensional array to a row:

wks_test.Cells(1, 1).Resize(1, ubound(arr)+1).value = arr

or to a column:

wks_test.Cells(1, 1).Resize(ubound(arr)+1, 1).value = Application.Transpose(arr)

The above assumes your array arr is zero-based.

Note: Application.Transpose can't be used if your array size is > ~65k

Upvotes: 1

Related Questions