Speed of outputting an Array from VBA into Excel

petroguy

New Member
Joined
Jan 11, 2018
Messages
4
So I'm currently wrote a sub which will performs some calculations then output the variables back into excel. For the most part performing the calculations take no time at all for VBA to perform. But sending the array from VBA to excel will take 1-2 mins which is far too long of a wait. I'm currently using the code below to output the dataset.

Sheets("Inputs").Select
Range("q4").Select
For i = 1 To Lenght
With ActiveCell
.Offset(i - 1, 0) = Solution(i, 1)
.Offset(i - 1, 1) = Solution(i, 2)
.Offset(i - 1, 2) = Solution(i, 3)
.Offset(i - 1, 3) = Solution(i, 4)
.Offset(i - 1, 4) = Solution(i, 5)
End With
Next I

Is there a quick way to perform this?
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Assuming that Solution is defined like:

Code:
Dim Solution(1 To 10, 1 To 5)

then this line will write everything in one instruction, which should be much faster:

Code:
Sheets("Inputs").Range("Q4").Resize(Length, 5) = Solution


Also, using the

Code:
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
. . .

Application.ScreenUdating = True
Application.Calculation = xlCalculationAutomatic

lines can help a lot.
 
Last edited:
Upvote 0
With the new code I keep getting

Run-time error '1004':
Application-defined or object-defined error

No idea how to correct
 
Upvote 0
With the new code I keep getting

Run-time error '1004':
Application-defined or object-defined error
Eric spelled the variable name "Length" (correct spelling for the word) whereas you (apparently) spelled it "Lenght"... could that be the problem? If you actually spelled the word "Lenght, then make that change in the code Eric gave you and see if that fixes the problem.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

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
Back
Top