Tips on speeding up excel

GrahamLawson

Board Regular
Joined
Nov 13, 2003
Messages
80
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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:
<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> MyMacro()
  Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
  Application.Calculation = xlCalculationManual
  
  ' Your code here...
  
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

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

Hope this helps!
 
Upvote 0
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/default.asp?url=/library/en-us/office97/html/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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top