Just to build on Tushar's excellent example to reinforce the comment I made,
if I use the built in capabilities of Excel:

Sub testIt3a()
Dim StartTimer As Double, n As Long
StartTimer = Timer()
Range("A1").Value = 0
Range("A1:A50001").DataSeries Rowcol:=xlColumns, Type:=xlLinear,
Date:=xlDay, _
Step:=1, Stop:=50000, Trend:=False
Debug.Print Timer() - StartTimer
End Sub

it takes about .03 seconds or 1/3 of the array approach as compared on my
machine.

True, this is just a simple, probably non-productive task, but nonetheless
reinforcing the contention of "it depends" -- arrays are far from a
universal solution.

--
Regards,
Tom Ogilvy

"Tushar Mehta" wrote in message
om...
The examples fail to exploit the power of XL. And, are somewhat
sloppily written, but we will leave that alone.

The following takes between 0.09 and 0.12 seconds to fill 50001 cells
with the numbers 0,1,2...,50000

Sub testIt3()
Dim StartTimer As Double, n As Long
StartTimer = Timer()
Dim a(50000)
For n = LBound(a) To UBound(a)
a(n) = n
Next n
ActiveSheet.Cells(1, 1).Resize( _
UBound(a) - LBound(a) + 1, 1).Value = _
Application.WorksheetFunction.Transpose(a)
MsgBox Timer() - StartTimer
End Sub

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...

Good afternoon Ajocius

If you are running a macro which is copying information into a sheet,
it is not really efficient to put the info into an array first - 'cos
they would have to be copied to your spreadsheet at some point.

Just for fun, and as a little introduction to the world of arrays run
these two macros.

The first will load all the numbers from 1 to 50,000 into an array,
a(1) to a(50000). Time taken instantly.

The second will write all the number from 1 to 50,000 directly into
your spreadsheet. Time taken, on a 3.0Gb machine running Windows XP
Pro and Excel 2000, expect between 35 and 45 seconds.

Sub Module1()
startnow = Now()
Dim a(50000)
For n = 1 To 50000
a(n) = n
Next n
endnow = Now()
MsgBox Format(endnow - startnow, "hh:mm:ss")
End Sub

Sub Module2()
startnow = Now()
Range("A1").Select
For n = 1 To 50000
ActiveCell.Offset(n).Value = n
Next n
endnow = Now()
MsgBox Format(endnow - startnow, "hh:mm:ss")
End Sub

Draw your own conclusions...

HTH

DominicB


--
dominicb
------------------------------------------------------------------------
dominicb's Profile:
http://www.excelforum.com/member.php...o&userid=18932
View this thread:
http://www.excelforum.com/showthread...hreadid=392468