+ Reply to Thread
Results 1 to 9 of 9

VBA routine gets slower and slower on each iteration of the main loop

  1. #1
    Registered User
    Join Date
    12-12-2006
    Posts
    17

    VBA routine gets slower and slower on each iteration of the main loop

    Hello all,

    I have a VBA routine that on each loop updates data from queries, prepares some formatting and then prints to pdf. This all works fine, the problem is that it starts off and produces each pdf in around 30 seconds but on each iteration this gets larger and larger (and there are 700 schools/iterations we need to produce the report for) in the end it slows Excel right down practically to a halt and I have to close down and reopen Excel - then everything is fast again.

    Anyway I've narrowed the problem down to this function that changes a chart series and adjusts the scale depending on certain criteria. This function seems to be the one that causes the loop to take longer and longer. Has anyone any ideas what it could be??

    Note that I've declared some worksheet types as module level public vars now to try and speed things up so these vars are declared at top of module (and so get declared only once during cycle instead of getting declared on every iteration of the loop)

    Many thanks for any help:

    Please Login or Register  to view this content.
    For info this is an excerpt of what is declared at the top of the module

    Please Login or Register  to view this content.

  2. #2
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,286

    Re: VBA routine gets slower and slower on each iteration of the main loop

    There are a couple of things that you could do, but nothing that would make a significant difference, and ther is no ob vious bottleneck in that code.

  3. #3
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: VBA routine gets slower and slower on each iteration of the main loop

    As a matter of interest, does it help if you change the activeprinter to a printer that is not busy whilst formatting the charts, and then change it back to the PDF printer when you're done and ready to print? (I suspect that Excel is trying to communicate with the printer driver when formatting the charts and the delay is due to the printer being busy, but that is only a suspicion)
    Remember what the dormouse said
    Feed your head

  4. #4
    Registered User
    Join Date
    12-12-2006
    Posts
    17

    Re: VBA routine gets slower and slower on each iteration of the main loop

    Ahhh that's a really good point romperstomper I never thought of that. I'll add some code in to change printer and see if that makes a difference. I'm finishing now but will try tomorrow and let you know how I get on.

    I have to say that I've found Excel goes really slow whenever I've been on print/page setup view so there's obviously some sloppy coding going on around printing in Excel - so it's quite likely this is the cause of the problem.

    Cheers for any further ideas/help people.

  5. #5
    Forum Contributor
    Join Date
    02-23-2006
    Location
    Near London, England
    MS-Off Ver
    Office 2003
    Posts
    770

    Re: VBA routine gets slower and slower on each iteration of the main loop

    I go with Rompers suggestion.
    In fact this was one reason why on my travelling laptop I used to have the default printer set to a PDF printer. If I had it set to a network printer then whenever I used to 'Print Preview' or 'Page Setup' both Word and Excel would go horrendously until they realised they couldn't contact the network printer and defaulted to something else.
    If you find the response helpful please click the scales in the blue bar above and rate it
    If you don't like the response, don't bother with the scales, they are not for you

  6. #6
    Registered User
    Join Date
    12-12-2006
    Posts
    17

    Re: VBA routine gets slower and slower on each iteration of the main loop

    Actually thinking about, I had disabled the part that prints to pdf in order to single out the function that was causing the problem and so maybe the printing isn't the issue - because I have commented out the bit that prints to pdf. The code to find the pdf network path is still in at the beginning though but this shouldn't affect it as it only occurs once and is not in the main loop.

    Has anyone any other suggestions?

    By the way I appreciate that I could enclose the second Select sType statement in with the first but I've just not got around to it yet and I'm pretty confident this won't be causing the issue.

    Cheers for any help

  7. #7
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: VBA routine gets slower and slower on each iteration of the main loop

    If you access the Page Setup in Excel, manually or in code, it communicates with the print drivers. That's why the old XL4 macro PAGE.SETUP is so much faster in VBA.

    Do you have screen updating turned off?

  8. #8
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: VBA routine gets slower and slower on each iteration of the main loop

    Perhaps try commenting out the chart mods, and (for testing) change print to print preview (to save a tree).

    If that makes it go faster, consider changing the charts to plot dynamic ranges instead of changing the data series, and then have the code just modify the sheet variables that control the ranges.
    Entia non sunt multiplicanda sine necessitate

  9. #9
    Registered User
    Join Date
    12-12-2006
    Posts
    17

    Re: VBA routine gets slower and slower on each iteration of the main loop

    Yes I have screen updating turned off and I'm not currently printing the document - but I'll change the charts to look at dynamic ranges instead and see if that affects it.

    Cheers for all your help.

+ 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