Copy an array question

[Mr930]

I have this:

dim buf(1500) as double

' do something here

Range("Q6:Q1505").Value = buf <-- will this work?


I want to copy the buf array into a range in one swoop, is this possible, or do I need to loop thru each array element and place it in a cell?

Also, as a general rule, is it quicker to do calculations in VBA and put them back in cells, or is it better to have individual cells have their own formulas? I have a big spreadsheet and I am trying to reduce recalc time.

thanks
Fred

 

[Andrew Poulsom]

This works:

Sub Test()
    Dim buf As Variant
    buf = Range("Q6:Q1505").Value
    Range("P6").Resize(UBound(buf), 1) = buf
End Sub

 

[Mr930]

I saw something like this in another example, and I was wondering if this line:

buf = Range("Q6:Q1505").Value

would add extra overhead? I don't need to "get" any data from the cells, but I do want to put my results to cells. The results are in the buf array. My array would be 1500 elements, copied 15 times across, so I am trying to maximize code speed.

thanks
Fred

 

[Andrew Poulsom]

Try this:

Sub Test()
    Dim buf(1 To 1500) As Variant
    Dim i As Integer
    For i = 1 To UBound(buf)
        buf(i) = i + 20
    Next i
    Range("Q6").Resize(UBound(buf), 1) = WorksheetFunction.Transpose(buf)
End Sub

 

[Mr930]

I will give it a whirl.

What is the difference between:

dim buf(1 to 1500) as variant

and

dim buf(1500) as variant


thanks
Fred

 

[Andrew Poulsom]

This will tell you:

Sub Test()
    Dim Msg As String
    Dim Buf1(1 To 1500) As Variant
    Dim Buf2(1500) As Variant
    Msg = "Buf1 " & vbCrLf & "Lower bound " & LBound(Buf1) & vbCrLf & "Upper bound " & UBound(Buf1) & vbCrLf & "Elements " & UBound(Buf1) - LBound(Buf1) + 1
    Msg = Msg & vbCrLf & vbCrLf & "Buf2 " & vbCrLf & "Lower bound " & LBound(Buf2) & vbCrLf & "Upper bound " & UBound(Buf2) & vbCrLf & "Elements " & UBound(Buf2) - LBound(Buf2) + 1
    MsgBox Msg
End Sub

If Option Base 1 is put at the top of the module there is no difference.