Copy an array question

Mr930

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

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

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
585
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
585
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.
 

Forum statistics

Threads
1,141,913
Messages
5,709,292
Members
421,625
Latest member
Natalie1107

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top