Copy an array question

Mr930

Well-known Member
Joined
Aug 31, 2006
Messages
583
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
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
This works:

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

Mr930

Well-known Member
Joined
Aug 31, 2006
Messages
583
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try this:

Code:
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

Well-known Member
Joined
Aug 31, 2006
Messages
583
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
This will tell you:

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,051
Messages
5,545,724
Members
410,702
Latest member
clizama18
Top