+ Reply to Thread
Results 1 to 5 of 5

Super slow VBA code on Intel Core i7 with Max RAM

  1. #1
    Registered User
    Join Date
    07-03-2013
    Location
    Monterey, CA
    MS-Off Ver
    Excel 2013
    Posts
    13

    Super slow VBA code on Intel Core i7 with Max RAM

    I'm self taught Excel vba coder writer, and still learning.

    I wrote a macro to import data that is in a new format, into separate tabs in a spreadsheet, reassigning the fields where necessary. The good news is that it works, the bad news is that importing a mere 300 records takes over 24 hours. It starts out by taking about a minute per record, which is excruciatingly slow, but as the processing continues, about 75% the way through it takes over 12 minutes to import a single record.

    I've obviously bungled this somehow, as I'm amazed with the power of computing that such a simple task is more than Excel can handle (with my code, below). Any help appreciated, but in general, why is this so stinking slow? Is it because I'm using worksheet.function functions? Offset references? I'm stumped.

    -- Thanks, Mark

    Please Login or Register  to view this content.

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: Super slow VBA code on Intel Core i7 with Max RAM

    Hard to say without seeing a sample of the workbook, there are a lot of loops being looped.

  3. #3
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Super slow VBA code on Intel Core i7 with Max RAM

    1. Reduce the number of times VBA reads from or writes to excel.

    2. Reduce the number of actions in a loop

    eg:

    Please Login or Register  to view this content.
    In this scenario If your cell contains 1, you go through 5 if statements to return "Bad"

    Try This
    Enter this line outside your loop

    Please Login or Register  to view this content.
    Inside your loop enter

    Please Login or Register  to view this content.

    3. Use Excel to do what excel does best and VBA to do what it does best.

    In your code you do something like this twice:-

    Please Login or Register  to view this content.
    If h_rec_count = 1000

    Then you are reading 1000 excel cells into VBA one at a time

    Looking for some text in each cell

    If you find the text you are modifying a cell in excel.

    It is a lot faster to use a helper column.

    a. Insert a formula in an empty column to do the hard work.
    b. Copy Paste your Values from your helper column into your Ammended Column
    c. Clear your Helper column.

    So three lines of code and no loop as opposed to your 1000 loops and six lines of code.
    Last edited by mehmetcik; 10-18-2017 at 01:45 PM.
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  4. #4
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Super slow VBA code on Intel Core i7 with Max RAM

    Hi,

    You should definitely turn Screenupdating off at the start of the code. Also avoid selecting things. And using Worksheetfunction.Index here is unnecessary
    Please Login or Register  to view this content.
    when you can just use
    Please Login or Register  to view this content.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  5. #5
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Super slow VBA code on Intel Core i7 with Max RAM

    What's the purpose of this section of the code?
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

+ 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. [SOLVED] VBA countifs super slow
    By Gekko42 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-03-2016, 04:35 AM
  2. Super Slow Excel
    By graemearthur in forum Excel General
    Replies: 12
    Last Post: 12-23-2015, 03:38 PM
  3. General help simplifying my super slow VB code PLEASE :-D
    By blackcat_78uk in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-30-2015, 05:22 PM
  4. General help simplifying my super slow VB code PLEASE :-D
    By blackcat_78uk in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-30-2015, 04:02 PM
  5. 2003 code runs super slow in 2010
    By emanresu65 in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 01-04-2013, 05:25 PM
  6. speeding up code that is super slow
    By cabinetguy in forum Excel Programming / VBA / Macros
    Replies: 28
    Last Post: 05-19-2011, 12:24 PM
  7. Excel CPU Usage - Single Core/Multi Core (balanced/unbalanced?)
    By winstontj in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-03-2010, 07:22 PM

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