+ Reply to Thread
Results 1 to 8 of 8

VBA - Large Arrays

  1. #1
    Forum Contributor
    Join Date
    12-22-2004
    Location
    Kokomo, Indiana
    Posts
    233

    Question 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

  2. #2
    Tom Ogilvy
    Guest

    Re: VBA - Large Arrays

    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" <ajocius.1t6tas_1123070737.3586@excelforum-nospam.com> wrote in
    message news:ajocius.1t6tas_1123070737.3586@excelforum-nospam.com...
    >
    > 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
    >




  3. #3
    Tushar Mehta
    Guest

    Re: VBA - Large Arrays

    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 <ajocius.1t6tas_1123070737.3586@excelforum-nospam.com>,
    ajocius.1t6tas_1123070737.3586@excelforum-nospam.com 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
    >
    >


  4. #4
    Forum Moderator dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Smile

    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

  5. #5
    Tushar Mehta
    Guest

    Re: VBA - Large Arrays

    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 <dominicb.1t6w2q_1123074849.4104@excelforum-nospam.com>,
    dominicb.1t6w2q_1123074849.4104@excelforum-nospam.com 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
    >
    >


  6. #6
    hanjohn@netspace.net.au
    Guest

    Re: VBA - Large Arrays

    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


  7. #7
    Forum Moderator dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Smile

    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

  8. #8
    Tom Ogilvy
    Guest

    Re: VBA - Large Arrays

    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" <tmUnderscore200310@tushar-mehta.SeeOhEm> wrote in message
    news:MPG.1d5a92797ffd8d0198b0f0@msnews.microsoft.com...
    > 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 <dominicb.1t6w2q_1123074849.4104@excelforum-nospam.com>,
    > dominicb.1t6w2q_1123074849.4104@excelforum-nospam.com 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
    > >
    > >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1