+ Reply to Thread
Results 1 to 4 of 4

Processing speed

  1. #1
    Valued Forum Contributor
    Join Date
    11-20-2003
    MS-Off Ver
    2010, 2016
    Posts
    1,166

    Processing speed

    I have just incorporated the following VBA code into a spreadsheet. The users are complaining that now it is very slow to save the spreadsheet. Prior to adding the code it took less than one second to save the sheet. Now it takes about 10 seconds to save. Is this normal? Is there anything thing I can do with the code to speed it up? (fyi - there is about 40 sheets in the workbook). Thanks
    Please Login or Register  to view this content.

  2. #2
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988
    You should try to avoid selecting where possible.

    For example you could change these lines into one line.

    Please Login or Register  to view this content.
    to

    Please Login or Register  to view this content.
    Also if the sheet has lots of formulas you could change

    Please Login or Register  to view this content.
    and

    Please Login or Register  to view this content.
    to

    Start of code
    Please Login or Register  to view this content.
    End of Code
    Please Login or Register  to view this content.
    See link for more tips
    http://www.decisionmodels.com/optspeedb.htm

    HTH

    VBA Noob
    _________________________________________


    Credo Elvem ipsum etian vivere
    _________________________________________
    A message for cross posters

    Please remember to wrap code.

    Forum Rules

    Please add to your signature if you found this link helpful. Excel links !!!

  3. #3
    Forum Moderator - RIP 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
    29,464
    Quote Originally Posted by maacmaac
    I have just incorporated the following VBA code into a spreadsheet. The users are complaining that now it is very slow to save the spreadsheet. Prior to adding the code it took less than one second to save the sheet. Now it takes about 10 seconds to save. Is this normal? Is there anything thing I can do with the code to speed it up? (fyi - there is about 40 sheets in the workbook). Thanks
    Please Login or Register  to view this content.

    I'd echo all VBA Noob says about cutting out all the .Selecting. This is a definite no-no for me. You can work with all objects, like sheets and workbooks without selecting or activating them. Which as VBA Noob says slows things down since Excel has to perform extra steps, and work out what sort of object you're selecting or activating. The problem is that the Macro recorder automatically records all the .selects by default, and most people simply accept that and don't change it.

    I'd be careful though with switching off the Automatic Calculation. It is a good idea provided that before it's switched on again, you haven't changed cell values and copied data to other workbooks. In that event you could end up with inaccurate data.

    The other thing to consider is that every time you jump from the VBE to the Application there is a time overhead involved. If you have loops with large numbers of iterations which do this, you might find it more efficient to read the whole of any relevant ranges into a VBA array, and work on the array entirely within the VBE, before writing the final results of your process back to the Application. It's a question of balancing things. It obviously takes longer to read a large Excel range than a single cell, but then you only have to do it once rather than lots of times.

    HTH

  4. #4
    Forum Contributor VBA Noob's Avatar
    Join Date
    04-25-2006
    Location
    London, England
    MS-Off Ver
    xl03 & xl 07(Jan 09)
    Posts
    11,988

+ 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