VBA - Large Arrays

Group,
Recently several of you enlightened me on the ways I can optomize my macro's and wow what a difference. One writer suggested the use of arrays. This actually I find to be very interesting. Assume, I have a spreadsheet that has up to 2000 rows, the spreadsheet changes weekly with rows being deleted and others inserted. Assume also that the spreadsheet can have up to 52 columns, but only the first dozen or so are somewhat constant, values changing. When you have a spreadsheet this large, is it truly more efficient to put into array? And if so, how would a typical array look with 2000 rows and 52 columns (worst-case). Your thoughts.


A budding VBA programmer..........

Tony

Depends on what you are doing. If you are treating the data as a database
(which is about the only thing that appears immediately logical from the
quantity of data), then using an array wouldn't necessarily be very
productive. Filters (advanced and autofilter) and so forth don't work on
arrays. For the person providing the advice, there may be great benefit for
a specialized requirement. In general, I don't start using arrays just
because I am working with a lot of data.

What would the array look like?
it would be a 2 dimensional array with up to 2000 rows and 52 columns.

v = Range("A1:AZ2000").Value
for i = 1 to 2000
for j = 1 to 52
debug.print i, j, v(i,j)
next
Next

--
Regards,
Tom Ogilvy

"ajocius" <[email protected]> wrote in
message news:[email protected]...
>
> Group,
> Recently several of you enlightened me on the ways I can optomize
> my macro's and wow what a difference. One writer suggested the use of
> arrays. This actually I find to be very interesting. Assume, I have a
> spreadsheet that has up to 2000 rows, the spreadsheet changes weekly
> with rows being deleted and others inserted. Assume also that the
> spreadsheet can have up to 52 columns, but only the first dozen or so
> are somewhat constant, values changing. When you have a spreadsheet
> this large, is it truly more efficient to put into array? And if so,
> how would a typical array look with 2000 rows and 52 columns
> (worst-case). Your thoughts.
>
>
> A budding VBA programmer..........
>
> Tony
>
>
> --
> ajocius
> ------------------------------------------------------------------------
> ajocius's Profile:
http://www.excelforum.com/member.php...o&userid=17695
> View this thread: http://www.excelforum.com/showthread...hreadid=392468
>

The answer to whether an array would be more efficient is "it depends."

The answer to how would the array look with 2000 rows by 52 columns is
"2000 rows by 52 columns."

Don't know what else to write at this abstract level of analysis.

--
Regards,

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

In article <[email protected]>,
[email protected] says...
>
> Group,
> Recently several of you enlightened me on the ways I can optomize
> my macro's and wow what a difference. One writer suggested the use of
> arrays. This actually I find to be very interesting. Assume, I have a
> spreadsheet that has up to 2000 rows, the spreadsheet changes weekly
> with rows being deleted and others inserted. Assume also that the
> spreadsheet can have up to 52 columns, but only the first dozen or so
> are somewhat constant, values changing. When you have a spreadsheet
> this large, is it truly more efficient to put into array? And if so,
> how would a typical array look with 2000 rows and 52 columns
> (worst-case). Your thoughts.
>
>
> A budding VBA programmer..........
>
> Tony
>
>
> --
> ajocius
> ------------------------------------------------------------------------
> ajocius's Profile: http://www.excelforum.com/member.php...o&userid=17695
> View this thread: http://www.excelforum.com/showthread...hreadid=392468
>
>

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

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 <[email protected]>,
[email protected] 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
>
>

Hi ajocius,
If you can get your hands on a copy of "Excel 2000 VBA Programmer's
Reference" by John Green published by Wrox, and have a look at page 114
it states "If you want to process all the data values in a range, it is
much more efficient to assign the values to a VBA array and process the
array rather than process the Range object itself. You can then assign
the array back to the range."
I would like to emphasise that looping code is not used when loading
the range into the array nor when placing the results of the processing
into a range on the worksheet, which is part of the reason for the
increase in speed (can be fifty times faster). If you want more
information and you can't get your hands on a copy of the book let me
know and I will send you the three relevant pages.
Ken Johnson

Tushar

Yes they were thrown together, and no, they don't exploit the power of VBA (perhaps you can tell me where I stated that they did). Read my post again - JUST FOR FUN it seems to say to me. Something very simple that does exactly what I said it would.

DominicB

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" <[email protected]> wrote in message
news:[email protected]...
> 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 <[email protected]>,
> [email protected] 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
> >
> >