+ Reply to Thread
Results 1 to 6 of 6

Excel VBA - Fast Loop

  1. #1
    Registered User
    Join Date
    09-22-2009
    Location
    Yep
    MS-Off Ver
    Excel 365 Pro Plus
    Posts
    30

    Excel VBA - Fast Loop

    Hi Everyone
    I have a huge database with Products, Quantities and Dates. The macro goes through this database (sheet "Data") and distribute them into the "Summary" sheet.
    The loop is already created and it's running fine but it's taking too long (more than a hour) to cover the whole file. There are several loops in the same macro checking different data. What I'm trying to find another method to speed it up. Attached is a sample file with the loop and a sample data.
    Any help will be very appreciated.

    I will describe below a sample of a loop I'm using today.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by Richard Buttrey; 01-16-2021 at 02:06 PM.

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,336

    Re: Excel VBA - Fast Loop

    Hi

    Please remember our rule about code tags see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    I'll add them for you this time.

    Secondly

    Whenever you find yourself creating looping code stop and think is this really necessary? Most of the time it isn't. Loops are inherently slow when they have to jump back and forth between VBA and the Excel application - there's a big time overhead at each pass through the loop.

    In your case you should read the data into a VBA array and process the array with the loops. That way you only read and write to Excel once rather than hundreds if not thousands,

    However why are you using VBA at all? Adding an extra column to your Data and using a Pivot Table will produce the results immediately. If you really want the dates in columns then just drag the date2 field to the columns area of the PT field list.

    See attached
    Attached Files Attached Files
    Last edited by Richard Buttrey; 01-16-2021 at 02:09 PM.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    09-22-2009
    Location
    Yep
    MS-Off Ver
    Excel 365 Pro Plus
    Posts
    30

    Re: Excel VBA - Fast Loop

    Thanks for your advice Richard.
    The PivotTable was considered but, unfortunately, it will not work well in this model. As I reported, this file is just a sample. The original template uses multiple source data. I believe the loop is inevitable. I run a separate loop for each source data. The number of lines for each of this source files is around 10K lines.

    For example:

    Information come from Product Name Past Due 1/16/2021 1/17/2021 1/18/2021
    Source Data 1 Product A 55 35 20
    Source Data 2 Product A 14 34
    Source Data 3 Product A 2 7 8
    Source Data 4 Product A 87
    Source Data 1 Product B 18 63 98
    Source Data 2 Product B 4 23
    Source Data 3 Product B 34
    Source Data 4 Product B 3 65

    Thanks

  4. #4
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,336

    Re: Excel VBA - Fast Loop

    Excel's Power Pivot Add in can of course handle many separate tables in one PT

    If you're going to stick with VBA then the only way to speed up your looping macro is as I said to write all the data into a VBA array, then process the array with your loops, finally writing the result back to the Excel sheet.

    That's one read/write process, not the thousands you are currently performing.

  5. #5
    Registered User
    Join Date
    09-22-2009
    Location
    Yep
    MS-Off Ver
    Excel 365 Pro Plus
    Posts
    30

    Re: Excel VBA - Fast Loop

    Is that feasible to create a vba array model for each loop? Any idea to replace the loop I provided? The loops are running separately because the source data is provided at different time. This is a shared macro and run by different users.
    Thanks

  6. #6
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,336

    Re: Excel VBA - Fast Loop

    Maybe you are missing the point.

    I'm saying that rather than processing/reading the data FROM the Excel sheet on every iteration of the loop(s) first read the whole data into a VBA array.
    Then change the loops to process the data in the VBA array NOT the Excel sheet.

    The point is that with all the data safely inside VBA you can process it at lightening speed since you're not jumping back and forth to Excels. There is a significant time overhead each time you do that.

    Where you are now taking over an hour, by using an array it will probably take a minute or so.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Fast CopyPaste from web to excel
    By HelpExc in forum Excel General
    Replies: 1
    Last Post: 01-06-2016, 11:37 PM
  2. Fast Loop for Forecolor change in Listview
    By BigDaddy01 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-01-2015, 06:02 PM
  3. How to import big txt file into excel fast!!!
    By wilo45 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-04-2015, 02:12 AM
  4. [SOLVED] Excel code running too fast?
    By etacv in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-20-2013, 01:14 PM
  5. Fast Summation in Excel 2007
    By ExcelTip in forum Tips and Tutorials
    Replies: 0
    Last Post: 11-08-2007, 09:34 AM
  6. A fast way to fill cells in Excel
    By Tas in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-18-2006, 01:45 PM
  7. OH, I need help fast.(Excel-Adobe)
    By DKR in forum Excel General
    Replies: 3
    Last Post: 11-04-2005, 04:53 PM

Tags for this Thread

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