VBA - Large Arrays

[ajocius]

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

 

[Tom Ogilvy]

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

 

[Tushar Mehta]

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

 

[dominicb]

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 - 'co
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 ru
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 int
your spreadsheet. Time taken, on a 3.0Gb machine running Windows X
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

Dominic

 

[Tushar Mehta]

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

 

[hanjohn]

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

 

[dominicb]

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

 

[Tom Ogilvy]

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.