writing multi dimensional array to excel vba

Question

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

Gary's Student · Accepted Answer · 2017-06-30 00:25:58Z

2

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

answered Jun 30, 2017 at 0:25

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tim Williams · Accepted Answer · 2017-06-29 23:33:53Z

1

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

answered Jun 29, 2017 at 23:33

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

1 Comment

zealousteedo Over a year ago

Perfect, that's it exactly!

Collectives™ on Stack Overflow

writing multi dimensional array to excel vba

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related