Results 1 to 6 of 6

Tips on speeding up excel

This is a discussion on Tips on speeding up excel within the Excel Questions forums, part of the Question Forums category; Hi, I have a sheet that has several macros on it. When I run some of these it can take ...

  1. #1
    Board Regular
    Join Date
    Nov 2003
    Posts
    80

    Default Tips on speeding up excel

    Hi,

    I have a sheet that has several macros on it. When I run some of these it can take a long time (2 minutes) to run. Whilst i have a relitivly old and slow machine, I was wondering if there were any tips on how to speed up a workbook?

    Cheers

  2. #2
    Board Regular LTunnicliffe's Avatar
    Join Date
    May 2003
    Location
    Round Rock, TX
    Posts
    767

    Default

    Not much to work with but, in general if you are running macros that the recorder has written for you then you may want to try a couple of things.

    1. Get rid of all select statements that select ranges or sheets. Example:
    The recorder will give you:
    Range("A1").Select
    Selection.Copy
    this can be changed to:
    Range("A1").Copy

    2. Turn off screenupdating and calculation during macro. Example:
    Sub MyMacro()
    **Application.ScreenUpdating = False
    **Application.Calculation = xlCalculationManual
    **
    **' Your code here...
    **
    **Application.Calculation = xlCalculationAutomatic
    **Application.ScreenUpdating = True
    End Sub


    3. Lastly the one thing that admittedly I'm not very good at is to avoid loops in your macro.

    Hope this helps!
    Loren

    The numbers don't lie, but sometimes they don't tell the whole truth.

  3. #3
    Board Regular
    Join Date
    Nov 2003
    Posts
    80

    Default Re: Tips on speeding up excel

    Thanks for those, I'll give them a go!

  4. #4
    Board Regular
    Join Date
    Jun 2002
    Location
    Perth, Australia
    Posts
    1,416

    Default Re: Tips on speeding up excel

    Graham,

    Presumably, your macros are too large to post here? Someone may suggest ways to improve performance and/or a different way to write the code e.g. See Lorenís comments

    See:
    http://cpearson.com/excel/optimize.htm

    Some tips on overcoming slow response (David McRitchie)
    http://www.mvps.org/dmcritchie/excel/slowresp.htm

    Opening/Saving Bottlenecks (Charles Williams)
    http://www.decisionmodels.com/optspeedd.htm#OpSave
    http://www.decisionmodels.com/optspeed.htm

    Optimizing for Size and Speed
    http://msdn.microsoft.com/library/de...ml/web/013.asp

    Random collection of various statements/comments from people on the Net (I collect these from time to time):

    Charles Williams
    www.DecisionModels.com

    - make the range referred to in Vlookup/Index as small as possible
    - make sure all your named ranges and all other ranges refer to as small a range of cells as possible
    - make sure your used range is not excessive ( check with edit -->goto-->Special-->Lastcell)
    - eliminate as much sparseness as possible (ie as few blank cells embedded in the used range as possible)
    - use dynamic ranges if appropriate
    - store frequently occurring "formula snippets" in named formulas
    - use multi-cell array formulae to replace blocks of formulae where possible
    - remove zero-sized objects
    - switch off Change tracking for shared workbooks
    - reduce the number of worksheets by merging several sheets into one
    - Avoid references to large ranges on other worksheets, particularly references to blank or unused cells
    - simplify formatting/reduce fonts etc
    - avoid embedded graphics
    - small savings are possible with short wide sheets rather than tall narrow sheets ( but less useable).

    Chip Pearson

    Set the calculation mode to manual unless you really need it set to automatic while your code executes.

    Set Application.ScreenUpdating to False to prevent Excel from updating the screen while your code runs.

    Make use of the With / End With construct whenever possible.

    Avoid Variant and Object type variables whenever possible - declare variables with a specific type.

    Don 't use the Select method and Selection object -- access a range
    directly. E.g., instead of
    Range("A1").Select
    Selection.Value = 123
    use
    Range("A1").Value = 123

    Sundry

    You could put your macro's in a separate file and reduce file size that way.

    Make sure that with conditional formatting you format only the range you want and not the whole row, column or sheet.

    Donít copy and paste images from Word into Excel - use Insert/Picture

    Try reducing the amount of formatting you apply with the macro.

    Try commenting out all the VBA code, save.

    Try separating out the macro from the workbook you are actually having the macro work on (make them two separate workbooks).

    Over time if you use the same workbook over and over, applying values, formats, copying worksheets, garbage will collect bloating the file size.

    Macro Modules

    "You should keep macro modules at less than 64K in size. How do you tell the size - export the module and then look at the .bas file to see what the file size is".

    Tom Ogilvy (Microsoft MVP)

    "The module size limitation isn't hard and fast, nor is it limited to any specific version of Excel. In fact I don't think it's ever been officially documented.However , it 's pretty well recognized among professional Excel developers that if you create code modules larger than 64KB you will very likely run into problems".

    Rob Bovey, MCSE, MCSD, Excel MVP


    HTH

    Mike

  5. #5
    Board Regular
    Join Date
    Sep 2003
    Posts
    123

    Default Re: Tips on speeding up excel

    One other thing to mention about recorded macros is that the code can contain a great deal of scrolling. You can delete all of this to speed up your Macro.

    Regards

    Zoot

  6. #6
    New Member
    Join Date
    Oct 2011
    Posts
    1

    Default Re: Tips on speeding up excel

    So, it might be better to copy the data into a new workbook from time to time to speed up the spreadsheet, but this might mean re-inserting formulas in the new sheet as well.

    W
    Excel Specialist

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com