+ Reply to Thread
Results 1 to 6 of 6

Excel Spreadsheet running VBA macros gets slower and slower over time

  1. #1
    Registered User
    Join Date
    06-04-2010
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    53

    Excel Spreadsheet running VBA macros gets slower and slower over time

    Hi, I wonder if anybody can help me on this annoying issue?

    I run quite large macros on a big Excel spreadsheet which monitors data every 5 seconds over an eight hour time window. When the macro is started it works perfectly and fast but then as time goes by the speed of the whole monitoring system gets worse and worse until at the end of the 8 hour process it is almost at what I would call stall speed and running super slow. I am not sure if it is caused by the macros or the actual workbooks.

    I've tried simplifying the code and making the actual spreadsheet itself as small as possible but to no avail.

    My computer has an i7 chip, runs 2.2GHz with 8 Gigs of RAM and uses Windows 10 so that shouldn't be the issue.

    Is there a procedure that can be used that can stop the whole thing for a few seconds that would then "refresh" the running process? As you can see from that suggestion I am at a loss (Lol) and so would be very grateful for any suggestions or ideas?

    Many thanks in advance,

    AJ

  2. #2
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    '97, 2016
    Posts
    2,523

    Re: Excel Spreadsheet running VBA macros gets slower and slower over time

    hi AliJay- I'm guessing you use Application.Ontime to schedule everything. Can you post your scheduling code? Anything to do with app.Ontime. If mishandled, you can end up responding to multiple call-backs.
    Last edited by leelnich; 06-15-2017 at 10:12 PM.

  3. #3
    Registered User
    Join Date
    06-04-2010
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    53

    Re: Excel Spreadsheet running VBA macros gets slower and slower over time

    Hi, Leelnich, Thanks so much for getting back to me. You're RIGHT! :-) Application.Ontime is what I use

    Please Login or Register  to view this content.
    This part is for shows the 3 seconds area of code but Its is coded for every 5 seconds throughout the 8 hour process.

    Is there a better way of scheduling the whole project?

  4. #4
    Registered User
    Join Date
    06-04-2010
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    53

    Re: Excel Spreadsheet running VBA macros gets slower and slower over time

    What (I think) I need is that once the process has been running for a while the running of zMiniTimerRelay from the Application Ontime functions seem to overlap and this causes the whole slow down. Is there a way of Cancelling the OnTime function once zMiniTimerRelay has run once so the process is ready for when it runs again in 5 seconds time rather than causing the overlap?

    Thanks again for any suggestions ...

  5. #5
    Forum Expert leelnich's Avatar
    Join Date
    03-20-2017
    Location
    Delaware, USA
    MS-Off Ver
    '97, 2016
    Posts
    2,523

    Re: Excel Spreadsheet running VBA macros gets slower and slower over time

    Those three examples are each a full minute apart, and unlikely to cause problems. If multiple/duplicate timers are the culprit, it's the 5 second code that we need to look at. A call-back has to wait until running code finishes. If that code sets ANOTHER callback, and then continues to run, say for another second, the actual interval between call-backs is reduced, not 5 seconds apart. If your code doesn't allow for this, the interval can shrink over time until your program bogs down, trying to respond to a whole stack of call-backs. So:
    1) Place your App.OnTime statements at the END of repeating code.
    2) Consider using Now + TimeValue ("00:00:05") to set timer instead of actually incrementing last setting. If necessary, reduce interval to allow for code run-time.

    Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee
    Last edited by leelnich; 06-16-2017 at 02:42 AM.

  6. #6
    Registered User
    Join Date
    06-04-2010
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    53

    Re: Excel Spreadsheet running VBA macros gets slower and slower over time

    Thanks so much for the reply - much appreciated!

+ 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] Macro running slower and slower
    By jj4jj in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-07-2016, 10:26 AM
  2. [SOLVED] Macros running much slower than they had been
    By nebb in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-20-2014, 06:56 PM
  3. Excel macro getting slower and slower.
    By swoop99 in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 06-24-2011, 06:41 AM
  4. Macros running much slower in 2007/Win7
    By surfengine in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-29-2010, 05:51 PM
  5. VBA routine gets slower and slower on each iteration of the main loop
    By whitespaces in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-15-2009, 03:29 AM
  6. [SOLVED] Excel gets slower and slower....
    By Romuald in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-07-2005, 10:05 AM
  7. Excel 97 macros run slower on a Pentium 4/Win Xp environment
    By Inver in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-14-2005, 03:08 AM

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

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.08812 seconds
  • Memory Usage 9,032KB
  • Queries Executed 15 (?)
More Information
Template Usage (34):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (1)bbcode_code
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (6)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (6)postbit_legacy
  • (6)postbit_onlinestatus
  • (6)postbit_wrapper
  • (4)showthread_bookmarksite
  • (7)showthread_similarthreadbit
  • (1)showthread_similarthreads
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (39):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./includes/functions_notice.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php 

Hooks Called (49):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • tag_fetchbit
  • tag_fetchbit_complete
  • showthread_similarthread_query
  • showthread_similarthreadbit
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1