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
