Slow Response, Memory Problems, and Speeding up Excel

Location:   http://www.mvps.org/dmcritchie/excel/slowresp.htm
Home page: http://www.mvps.org/dmcritchie/excel/excel.htm
[View without Frames]

 
[journaling], [speech], [ext], [insert], [lastcell], [addins], [slowmacros], [speedupVBA], [recalculation], [openclose], [calc], [1-2-3], [memory], [leakage], [GDI], [objects], [pagesetup], [swapfiles], [VBE], [files], [elimxused], [defrag], [formats], [likeformats], [volatile], [checklist], [slowxp], [moreitems], [grayed], [temp], [timer], [reg], [related], [startup], [warnings], [search], [mskb], [slow], [updatesites],

Many things can slow response in Excel.  One of the most common reasons for slowing down XL97 is logging (journaling).
 

You are probably visiting this page because you were referred to it by someone in a newsgroup.  Because of the nature of such problems, it is important to provide feedback when you solve your problem by replying back to the thread so that others can be helped and so that pages such as this can show relevant information.  Please also include your version of Excel and Operating system if you did not originally provide that information.

Excel 97 (and later versions) (#multiversion)

Storing in multiple Excel version formats -- XL95, XL97, XL2000, XL2002

Wastes space (takes twice as much space, 75-100% more) and takes much longer to process a save.  Example using XL2000, my 2.16MB file was saved as a 3.85MB file and took 45 seconds to save first time with Save As, and over one minute with subsequent Save.  The save time for this large file as a normal XL2000 file is 10 seconds.

Don’t use this feature if you don’t need it.

Results reported in XL2000, from XL97 would be similar

If you are saving as
    Microsoft Excel 97-2000 & 5.0/95 Workbook *.xls
you will see the following
    Microsoft Excel 5.0/7.0 saving XXXXXXX.xls
on your status bar where you normally see Ready toward the end of the save then you are saving in multiple formats.

If the file is accessed only in one version, you don’t want to use this feature.  Save As  a normal  *.xls  file to eliminate the feature from your file.

A bad HP print driver install caused intermitent print problems and following an uninstall that had mapped but not removed a z:  drive.  The non existant z:  drive left behind interferred with Save As process in ANY Office Application (like in Excel 2003) reported by Huvants Tuneau in email 2005-04-06.

The program stops responding when you try to open the "Save In" list in Office 2002 and 2003, and you attempt to open the Save In list in the Save As dialog box, the program appears to stop responding (hangs) for a long time.

If you click Cancel or attempt to close the Save As dialog box, the program still does not respond.

Outlook, journaling feature in MS Office 97 and 98 «     (#journaling)

The Journaling default  is the most frequent problem experienced by users who have just converted to XL97.

The journaling feature of Outlook logs many actions taking time from Excel, and eventually, the journal files become quite large, and performance is slowed.

The general recommendation in the Excel groups (and MS KB Q167081) is to turn journaling off.
In Outlook 97: Tools / Options / Journal / uncheck Microsoft Excel and all other MS Office Products (Select all, delete)
In Outlook 98: Tools/Options/Preferences/Journal Options

If you try to check Outlook options, you may then start the Outlook installation process, so check your system first with search to see if you have  outlook.exe  on your system, if you do then check for journalling turned on.

Outlook Express, an entirely different package and is mentioned only due to the similarity in it’s name, causes much grief to other Newsgroup users when it posts in MIME.  (see article on posting).

Problems commencing in Excel 2002 (Excel XP)   (#speech)

Speech Recognition can introduce garbage into a worksheet, and it can slow things down and other strange things.  Tools, Speech, Speech Recognition. (if it had a check mark, it was turned on) -- a problem, KB306537#27, frequently pointed out by Debra Dalgleish

Those with Excel XP (2002)may run into a situation where Excel thinks a workbook is bad and quarantines it.  Look in HELP (F1) for “Disabled items” (Dave Peterson, programming, 2005-04-28)   Startup Problems (Disabled files), Jan Karel Pieterse.

Unspecific version problems with standard macros, with Event macros.

Unwanted Task Pane comes up on right side when trying create a new file (#taskpane)
There is a Registry change that did not work for me (Ron de Bruin, misc, 2003-06-17).
Alternately Dave Peterson has four macros involved in adding another entry to the file menu to bring up the old menu (misc, 2004-07-14)

Problems with Excel 2003 (#excel2003)

Changes to later windows systems, and later Excel versions is invariably going to have much higher memory requirements, from others sounds like you should have 512MB RAM and turn off features.  There are two things that would be really annoying HELP file does not have index, doesn't work well for offline use.  The other is that Ctrl+A doesn't work properly see shorcut keys (foobar) for a macro solution.  Excel 2003 Review (John Walkenbach).  As for myself I'm still on Excel 2000 have 128MB RAM I have upgraded to 512MB RAM (max for my laptop) and it has eliminated all paging problems and a macro that had to go through menus would have taken many hours not rips right through it, and don't have to take down other applications to run Excel.

Some specific tweaks for Excel 2003 (spinner 2005-04-08), appear to be the speech recognition above, no improvement in zooming graphics and fonts (keep zoom at 100%).  Auto Recovery is improved but you have to respond when Excel is going down, and decide which file (old or recovered) to use later but no peeking.

Problems that appear on a new system or version (#newsystem)

Not all problems that appear when you upgrade your system or version of Excel are related to a new version.  I may just be that you had some things installed before and did not reinstall them.

File Extension is not .xls   (#ext)

Using a .TXT or .CSV file as an Excel file has been suggested to speed things up, but they are not Excel files and you lose all formatting, formulas etc when it not an Excel file.  I would think conversion would take some time and you would not have workbooks but single sheets. Not recommended.

Inserting rows is slow (#insert)

Lastcell problems -- last used row, last used column (#lastcell)

Cells with a single space somewhere on the sheet can make a sheet really large for no apparent reason and you can’t see the space by looking at the cell because it looks the same as a Blank cell.

Use Ctrl+End to find where Excel thinks your last used cell is.  Lastcell is the intersection of the last used row and the last used column.  If this is the problem you can change the last cell location to what you want using the MakeLastCell macro for an individual worksheet to force the lastcell to a specific cell eliminating problems caused by Excel or to discard additional rows or column that you no longer want.  To fix problems caused by Excel itself by retaining high water mark caused by inserting rows/columns and then deleting rows/columns you can fix them all at once with the Reset_all_lastcells macro.  Which may be interesting but if you just want to fix lastcells and not have to doublecheck to see if it works you might use Debra Dalgleish's macro instead even though it takes longer it is more likely to solve the problem – Why do my scrollbars go to row 500 -- my data ends in cell E50?, contextures.com, Debra Dalgleish

Another indication is that the scroller on the vertical scrollbar is small because it has to scroll through a lot of data but you don’t have a lot of data.

Excel 97 and 2000 are better at updating the location of the lastcell than XL95, but if it is not corrected immediately you should use SAVE and then recheck. 

The most frequent source of lastcell problems is using the spacebar or the Del key to clear out a cell, neither of which return the cell to it’s pristine ISBLANK (never used) condition.  To eliminate the content in a cell, use Delete rows, Delete columns, or under Edit use Clear content, and then SAVE.  XL95 has a bigger problem with lastcell and would recommend taking a look at lastcell be sure to read listed KB articles and updates, if any, besides.

If you are inserting/deleting hiding/unhiding rows or changing page layout in any way.  You might notice that slowing down only occurs after Print Preview for Instance.  Turn off PageBreaks.  (Tom Ogilvy, 2000-11-28) -- more detail on page breaks
    Turn off manually with Tools --> Options --> View --> (uncheck) Page Breaks, or in VBA with
    ActiveSheet.DisplayPageBreaks = False

Besides checking for lastcell problems and using of Build TOC to show how many cells are involved in the used range, you can run a macro to split up workbook into separate files strictly to see how big each worksheet really is -- Saveas, Save each worksheet as a separate workbook

Addins   (#addins)

Addins could change some of your settings, and invoke macros that you are not aware of.   You can turn them off selectively.  I had an addin that did not work for me, I wasn’t using but it was turning off calculation when I brought up Excel. Lots of addins can really slow things down.

If you are having problems particularly when starting up, try turning off all addins, closing and reopening Excel.  If that made a difference turn them back on one by one to find the culprit.  Write down the addins that were checked to help you turn them back on.

Tools -->addins     (uncheck all boxes to turn them all off)

Note, there may be something in your Auto_Open macro related to an addin, for instance there is an inclusion in the following for XL2000 users needed to turn off the prompt to accept the save even though user has AutoSave invoked, but would not be included after fixes.  You cannot test or change calculation in your personal.xls Auto_Open but you can do that in a separate workbook opened later.  There is nothing currently in the following Auto_Open that would be included in your personal.xls  You can use the calculation test in a different workbook.  Some additional notes posted by Gord Dibben point to inconsistency of these options to be for a workbook, sheet, or Excel use. 

Sub auto_open()
    If Application.Calculation <> -4105 Then
    '---- Calculation on/off/testing NOT for your personal.xls  Auto_Open
    '--    Run-time error '1004'.
    '--    Method 'Calculation' of object '_Application' failed
       '-4105 automatic, -4135 manual, 2 semi-automatic
       MsgBox Application.Calculation & " <calculation in> " & _
         ActiveWorkbook.FullName
       Application.Calculation = xlAutomatic
       MsgBox Application.Calculation
    End If
    If .FixedDecimal = True Then
       msgbox "Reset FixedDecimal back to False"
       Application.fixedDecimal = False 
    End If
     
    'The following line had been used to turn off prompting in AutoSave Addin
    ' in XL2000 before there was a complete fix for problem.
    'Result if addin is turned off: Run-time error '9'; subscript out of range
    'Workbooks("autosave.xla").Excel4IntlMacroSheets("Loc Table").Range("ud01b.Prompt").Value = False
End Sub
Private Sub Workbook_Open() installed in a workbook module was introduced in Excel 97 and will be invoked before Sub Auto_Open which resides in a normal module.

Macros run Slow (#slowmacros)

« Turn off calculation and screen refresh while macro is running.  Be sure to restore settings when the macro ends.  Some supposedly reset automatically but later releases tend to require you to reset.  Macros may terminate abnormally so don’t use without that thought in mind.  The examples below do not save current settings and restore them but assume normal usage is in effect.

Screen flickering is usually the result of bad coding, but turning off Screen Updating will eliminate the blinking, and speed up a macro.  Turning off calculation can have a more dramatic effect on improving performance.  Use of Special Cells and restriction to used range will greatly improve coding.

Turning off ScreenUpdating and Calculation are the easiest changes to implement.

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
  '    ooo   Your code here   ooo
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
 

Prior to XL97 (Excel 5 and Excel 97) use
Application.Calculation = xlManual          'prior to XL97
Application.Calculation = xlAutomatic     'prior to XL97

      alternative coding: restores previous settings:

  Dim savCalc As Long, savScrnUD As Boolean
  savCalc = Application.Calculation
  savScrnUD = Application.ScreenUpdating
  Application.Calculation = xlCalculationAutomatic
  Application.ScreenUpdating = True
  '    ooo   Your code here   ooo
done:
  Application.Calculation = savCalc
  Application.ScreenUpdating = savScrnUD

I always want calculation on and there are too many bad macros that fail and macro of my own that fail during testing so I use the version on the left above.  The code on the right above is more correct but prone to problems of leaving calculation off.

For most VBA actions, you don’t have to select the sheet or cell.  Look for such coding to be reworked/removed, you might even be able to remove the need to suppress screen updating if the appearance of the screen is not changed. (Brian Wilson Example: 2000-12-28)

Are there any Worksheet_Change events.

Related to last row problems, macros can take forever to run if they loop through all possible columns and rows instead of restricting activity to the used area.   Examples where such failures commonly occur:  deleting rows with certain content,  inserting rows or columns, selecting an entire column and macro processes entire column instead of cells in the active area.

In a test of one macro adding DIM statements appeared to increase time 30% contrary to good coding practices.  Nevertheless, by dimensioning variables you simplify code writing and maintenance and avoid some additional problems.  Turning off screenupdating saved about 12%, but turning off calculation saved an additional whopping 75%.

Use SpecialCells to reduce selection to cell types of interest (constants, formulas, comments, visible, etc.).  SpecialCells is automatically limited to the used range, which eliminates processing every single cell in a selected column, if a column is selected.  Examples range from 366 seconds without using SpecialCells, to .040 seconds by reducing selection with SpecialCells as seen on Proper, and other Text changes -- Use of Special Cells also see notations on same page.

Dimensioning variables for use with Options Explicit. (#dimensioning)  If you have simply used Dim xyz as variant you can find out the actual type that you used with  MsgBox typename(xyz)  so you can replace variant by its actual type.  (see vba.htm for more information).

Speeding up VBA code   (#speedupvba)

The biggest things were in the previous topic turning off calculation and display. Here are some additional tips.

Declare variables with their datatype [see table]:  Byte, Boolean, Long, Long, Currency, Decimal, Single, Double, Date, String, Object, Variant (default), and user-defined types, as well as specific types of objects.  A common pitfall is failing to include attributes for each variable on a DIM statement resulting in a data type of variant.

Use long in preference to integer and use double in preference to single as these are what the system actually has to use, and use String instead of Byte.  Also note that anything referring to rows should be immediately changed to long, since 65,536 exceeds the limit of the integer datatype.

Avoid changing the activecell or the active page, unless that is the desired result.  Example to create a new entry after last entry in Column A, not to be confused with last cell row (Ctrl+End), which can in itself be problematic.   (#samplesub) 

 Sub SampleSub()
   Cells(Rows.Count,Range("A1").Column).End(xlUp).Offset(1,0).Value _
     ="** New Entry**"
 End Sub
Some pages strictly on speeding up VBA.

Recalculation   (#recalculation)

Slow recalculation in Excel 5.  Does this have module references.  If the references point to a file on a network file system then Excel is known to behave this way. Check Google Usenet Archives or MS KB, if this looks likely.  Other references: calculation with F9 or macros, and volatile functions.

Opening and Closing Excel (#openclose)

Workbook_Open, Workbook_Close, Auto_Open, Auto_Close

Workbook_Open should be used in XL97 and up in a Workbook module in preference to and before Auto_Open in a normal module. (Workbook_close is performed after auto_close if both are present).

Try opening it with the shift key held down. If it now runs faster, you may have a macro that is being repeatedly run e.g.
Application.OnTime or Application.OnCalculate.

Tools --> Options --> Calculation   (#calc)

Haven’t checked into this but one of the options, Save External Link Values, has a tool tip which reads.
Saves copies of values contained in an external document linked to a Microsoft Excel worksheet.

If a worksheet with links to large ranges on an external document requires an unusually large amount of disk space or takes a very long time to open, clearing the Save external link values check box can reduce the disk space and time needed to open the worksheet.

Things to check investigate for better understanding: Help topic:  I can’t update the automatic links on my Microsoft Excel worksheet.
To ensure that automatic links to data in other programs are updated, click Options on the Tools menu, click the Calculation tab, and make sure the Update remote references check box is selected. If this check box is cleared, the Automatic option in the Links dialog box (Edit menu) is overridden for linked data sources from other programs, such as a Microsoft Word document or a Microsoft Access database.

Lotus 1-2-3, Conversions From   (#lotus-1-2-3)

My converted workbook opens very slowly and is too big. When you worked on the file in Lotus 1-2-3, you may have formatted entire columns. Microsoft Excel converts all formatted cells, even if they're blank, making the worksheet large and slow to open. You can use either of the following approaches to correct the problem. See HELP, Ans Wiz, save external links, Troubleshoot opening, saving, and using Lotus 1-2-3 files in Microsoft Excel.  Even if you never had 1-2-3 you should note that formatting is best done by columns and rows, not by individual cells.

Memory   (#memory)

Probably don’t want to use less than 32MB with XL97,  or less than 64MB with WinNT/Win 2000 or less than 128MB with Win XP.  That choice has already been made.  Check the performance on your taskbar.   Your memory or paging requirements may increase instantly when new software such as virus protection which runs all the time or  hardware such as a CD-R drive.

Charles Williams has posted a number of articles on hidden Excel limitations, beyond the specification limits found in your HELP. 
news:HoXD6.7738%24e35.136511@monolith.news.easynet.net 2001-04-20

A good summary of things to check out was posted by Charles Williams news:8kkh89$njn$2@lure.pipex.net including  broken link several memory problems, and a way of checking how much memory Excel is using with a worksheet formula.  =INFO("memused")  and though not mentioned taking a look at your task manager to see what Excel itself is using.
 

1,048,576 =INFO("memavail") Amount of memory available, in bytes.
3,591,860 =INFO("memused") Amount of memory being used for data.
4,640,436 =INFO("totmem") Total memory available, including memory
already in use, in bytes.
45 =INFO("numfile") Number of active worksheets.
Windows (32-bit)
NT 4.00 		=INFO("osversion") Current operating system version, as text.
9.0 =INFO("release") Version of Microsoft Excel, as text.

Memory Leakage   (#leakage)

If you are having severe problems that can be alleviated by maintaining the Zoom at 100%, you should make sure that the have the latest print drivers for your printer  The print drivers control the screen appearance.  Also check the MS KB for memory leakage.  Various problems have been reported against H-P printers with soft fonts, possibly after the introduction of the Euro character.

Q183503 -- XL: "Not Enough System Resources" with Controls on Zoomed Sheets

System Resources (as Windows calls them) are consumed by graphics images (such as charts, zoomed windows, drawing objects, fonts etc) and is limited.  General Resources (memory, stack space etc).  Windows operates a “Paging” system so that if the OS runs out of physical memory it just hijacks the disk to store the stuff it can’t keep in RAM.  The effect is that your spreadsheet runs more slowly, but it still runs.  [Peter Beach, programming, 2002-01-14]

Memory Leakage in GDI (graphics device interface) Resources   (#gdi)

Multiple Excel windows, window zoom not set at 100%, lots of graphics, colors, formats, charts, images, activex-objects.  A combination of these will eat excel’s graphical memory, which is allocated independent of available cards, ram, speed. -- as summarized by Harald Staff.  Also don’t forget to close your macro windows within the VBE when you are done with them.  Printers and fonts can also be involved.

Windows “resources” is a special segment of memory that is not related to how much RAM is installed.  That it is fixed by the operating system.  With Windows 3.1 it was small and used up rapidly. With Windows 95/98 it was increased or at least used more efficiently so it is less of a problem With NT it is a non-issue. (Jim Rech, misc 1999-12-16, and continues)

In Excel the thing to watch is graphical objects like pictures and drawing objects using up resources.  Also using a lot of different fonts.  MS believes that some printer drivers are at the root of some resource issues:

Q165985 -- OFF: "Out of Memory" Messages When Running Microsoft Office includes more information and a list of printers.  It is also possible that the latest version of the printer driver will correct the problem.  The latest versions of the printer drivers can be downloaded from the following Hewlett-Packard Internet web site:  http://www.hp.com/  «Some H-P printers in particular are identified with consuming memory and not releasing it.  This can result in memory errors and it can result in dramatic slowdowns.  A printer driver runs in the background and controls the display on the monitor as well as the printer.  So you might try changing print drivers.

Excessive use of MSGBOX about 100 times in XL2000 did not release GDI (Graphical Data Interface) resources.  Check out MS KB Q102438 -- Excel: Methods for Conserving GDI Resources [dead link] for a method to check -- could not get it to work failed on “user” library.  Bypass is to use Application.MsgBox in VBA code instead of MsgBox. (See Bill Manville 1in Programming 12/04/99, GDI)

The following relate to a resulting GPE in gdi.exe
Q192869 -- OFF97: PageSetup Object Causes Memory Leak and Hangs System

Q229670 -- Calling EnumObjects() Leaks a Selector

Ron Martell’s name was mentioned by Chip Pearson relating to some postings concerning memory problems.  Here are some of them, as always look at the entire thread.  Search: GDI Resources

Screen Savers and background constitute a GDI, will see if I can find a reference for this.  In the meantime if you see messages as seen in the MS KB articles about insufficient resources etc., you should try turning off the screen saver.

Also see Memory & Limits by Charles Williams.  you will have to look at (frames at top) topics separately: Types of Memory, Measuring Memory, Memory Limits, Memory Leaks.  [good information, but site is very difficult to work with because of internal scrollbars, use of Mozilla or Netscape works better on this site and eliminates those separate scrollbars]

Changing graphics to placeholders (Tools, Options, View, (show all/place holders/hide all) affects all of Excel may save time scrolling and refreshing.  Hide will hide graphical pictures and text pictures; while placeholders does not apply to text pictues.  [LockerGnome 2005-06-16]

Process Explorer (/\), Sysinternals Freeware, more information than provided by Task Manager .  The webpage has additional useful information such as MS KB articles. (#processmanager)

Objects Not Being Released   (#objects)

If you are declaring any objects such as workbooks, worksheets, etc . 
          (tip from Tom Collins, programming 2000-06-12)
    Dim MyWorkBook as WorkBook
    Set MyWorkBook = ActiveWorkBook
mk:@MSITStore:C:\WINNT\Help\sysdm.chm::/sysdm_advancd_perform_change_vmpagefile.htm Then you should explicitly release the variable when you're done.
    Set MyWorkBook = Nothing

Failure to release objects, may result in increasing delays later on, until the workbooks are closed or Excel is closed.

Page Setup and speed   (#pagesetup)

Print drivers particularly some HP printers cause GDI memory problems particularly on Win98 systems using XL97.  If running a macro be sure to simply select sheets rather than activating each sheet when processing all sheets in a book.

Switching my default printer from a HPLJ6L to a HP6si reduced pagesetup execution time to 50% on my machine.  -- Jeff McAhren 2000-04-11.

You may want to try the generic / text only printer driver.  To do this, add a new printer, select “Generic” for the manufacturer and “Generic / Text Only” for the printer.  This proved to be pretty fast for me.  -- Jake Marx 2000-04011

As already mentioned several times on this page -- turn off page breaks.
    Turn off manually with Tools --> Options --> View --> (uncheck) Page Breaks, or in VBA with
    ActiveSheet.DisplayPageBreaks = False

You can use XL4 macros instead which run much faster (John Green), but they could be withdrawn (look at entire thread).

Avoiding Page Setup

Dana DeLouis has some additional comments in a posting 2001-06-01, concerning use of styles but the main point he brought up was the use of templates for a single sheet on your own choice in VBA.

You can have a BOOK.XLT for the default template for new books, and a SHEET.XLT for new sheets added after the initial sheets.  These would normally reside in your default template library (XLStart folder) or in the alternate startup folder.  Customized
   C:\os\Profiles\user_name\Application Data\Microsoft\Excel\XLStart
where os is the operating system folder–for example, Windows.

To create a custom workbook template, select the Templates folder, which is usually
   C:\os\Profiles\user_name\Application Data\Microsoft\Templates
where os is the operating system folder, for example, Windows.

In VBA Help says that this is one way to add a sheet to a workbook..
Sheets.Add after:=ActiveSheet, Type:=xlWorksheet

What is not documented there is that “Type” can also be the name of a Template.
Sheets.Add after:=ActiveSheet, Type:="C:\MyTemplates\AllTheWorkIsDone.xlt"

Or presumably another existing sheet in the same workbook.
Sheets.Add after:=ActiveSheet, Type:="C:\MyTemplates\AllTheWorkIsDone.xlt"

A word on the side of caution, if it’s really not documented, and I don’t see it on that help page, it could lead to trouble. -- David.

Page datasets, SWAP files, Virtual Memory   (#swapfiles)

Your memory or paging requirements may increase instantly when you add new software such as virus protection which runs all the time or  hardware such as a CD-R drive or a new web browser.  Throwing more RAM onto you machine might help, but without any additional purchase you should be able to increase the size of your SWAP files.  Do not rely on Microsoft recommendations to do this best automatically for you, because when additional memory is required something else is happening and you want to reduce your exposure to problems not increase them.

Check your performance using Task Manager by Right-Clicking on the taskbar.  (I have WinNT and am not familiar with the others, hope they look the same).

Check Memory Usage under Performance.   Under the View menu choose “show kernel time” which adds a red line to the CPU Display.  If the red line is up with the CPU time then you are probably being swapped out, an indication that more RAM might help [ref].  You can change your settings for page files under  desktop.  For help with settins Windows Help: search on paging RAM.  – Settings for Paging (Page file)

START -->  Settings --> Control Panel --> System [System Properties] --> Performance --> [Change]

On my laptop Windows 2000 now with 512MB RAM (maximum capacity), the page file is set to 480MB providing 902MB available, the peak usage is around 505MB.  as both initial and maximum size, giving me an effective total of 992MB.  When I had 128MB RAM the peak usage was about 800MB and normal was over 500MB.  (current as of 2007-04-25)

Don’t have guidelines on how to set these, I have 64MB of RAM and probably initially doubled the settings, reduced it when disk space was real tight, and have since increased them again once I obtained a second hard drive.  Currently I am using:
My swap file is on a 1 gigabyte partition with no other files, so there is no interference with not being able to allocate needed space. [unfortuately everything is now on one a single drive partition]

Read about my second hard drive (HD) on my Office 2000, my experience in installing - Progress report and by all means do not forget to backup your data.  The second HD is a lot cheaper than memory and was absolutely necessary.  A memory boost on my machine would be not be justified versus purchase of a newer, faster machine.  [My newer faster machine is 600mHz]

Windows 2000 Experience

My Swap file is in 3 parts to get 400MB and half if it is very high compared to where the datasets actually reside. Wish I had made it 400MB or 500 MB to begin with.  I only have one hard drive and the one partition on it so can’t put swap file on a different drive. (see picture (78KB) after compression)

Memory consumption with VBE   (#vbe)

Disk files  (all systems)   (#files)

Eliminate unused files for  performance   (#elimxused)

Eliminate files that are wasting space on your system in preparation to running a Defrag.  Hopefully your cache and email is not on your c: drive nor on your data drive, especially if you are tight on space on your c: drive.  The main culprits on my system were. 
  1. Eliminate  *.tmp files with del *.tmp /s 
    will eliminate temporary files from many different applications, many of which store these files in their own application directories.
  2. Eliminate c:\temp   files and all directories under  c:\temp
      -- Suggest you do this manually in case you are still testing something "temporarily" in your temp directory.
    Also be sure to clear temporary internet files in your browser (see topic below)
  3. Clear out your Windows temporary files (contains .emf files (Enhanced Windows Metafile), Bill Manville, links, 2002-07-11 )
    win98:   C:\Windows\Temp\
    winxx;       C:/Winnt/Temp/ -- some usage in Win2k as well
    win2k:   C:\Documents and Settings\userid\Local Settings\Temp\   -- i.e. Administrator
    i.e. C:\Documents and Settings\Administrator\Local Settings\Temp
    Such files may particularly slow down Excel when active-x components are involved.
  4. Eliminate cache files for browsers, including those under old versions of browsers.    First use the facilities in your browser to purge cache, then search directories for cache folders and check the contents to make sure that you have cleaned up cache.  Also eliminate all files under something like (depends on your operating system and how you logged on) --
    winnt/win2k:   C:\WINNT\PROFILES\userid\Temporary Internet Files
    [see Internet Temporary Files in next topic below]
  5. Compact your email while offline.  In MS Outlook Express, and make sure Outlook Express is offline when doing this -- Options, Maintenance, Clean-up Now, Local Files Clean up, Compact Now -- hopefully these files are not on your c: drive.  In AOL -- Personal Filing Cabinet, Compact -- do this for each screenname.  In Netscape -- Edit, Options, Advanced,
  6. You could have *.dbx files that are no longer in use with Outlook Express, check out information in oe6.htm to open each file and then check the dates.  Make sure you are offline whenever you compact OE database files -- files, folder, compact all
  7. Empty the wastebasket.  Get in the habit of insuring that you have emptied the wastebaskets on each of your hard drives  before running defrag and before taking backups.

Windows 98 has a utility that will help with many of the above it is called Disk CleanUp and can be found in Programs, Accessories, Utilities from your Windows START button.

Like much of MS software it does not tell you what it is going to do before it starts.  Fortunately it just initially calculates how much space you would save by checking certain code lines to delete files and only deletes those types of files when you give it permission to delete all of them.

Internet Temporary Files (can hold some big surprises in wasted space (#intnettemp)

C:\WINNT\PROFILES\userid\Temporary Internet Files
From Internet Explorer, Tools, Internet Options, General (tab), Delete files.  I frequently delete the files, but the surprise was that I thought I only had abour four single pages marked in favorites as “store offline”.  So suggest also deleting the stored files occasionally to make sure that you are not storing documents you don’t want.  After clearing out everything imaginable and having been running 190-250MB for a month, I thought I'd clear out the “stored offline” files -- I suddenly had 900MB of free space.  Afterwards I went to Favorites, then organize, and used the ArrowDN key to check each exposed folder and single files.  Too much work to go into each folder, but I did find a lot more than I thought I had.  I kind of wonder what the details might have been on the MS KB favorite bookmark that I unchecked for “store offline”.  And to think I massacred a bunch of newsgroups to gain a few MB of storage here and there, but if I really needed them I suppose I wouldn’t have deleted those newsgroups.

With a lot of stored file content and even though I had over 20GB free on my harddrive and 7GB for internet file, I could not view the source, or get into Front Page from IE except by deleting all temporary internet files "each" time I wanted to view the source.  Don’t store the MS KB files you've read.

Defrag   (#defrag)

Defragging files may speed up your system considerably.

Defragmenter, with Win98 you get a defragmenter, with WinNT you will have to install one.  I think if you have AOL, you will have to protect it by directory in your defragmenter.  Since I lost AOL several times I think they have unmovable files. 

For WinNT 4.0 you can get DKLITE (Executive Software) disk defragmentation for WinNT 4.0 (build 1381) free [http://www.diskeeper.com/downloads/menu.asp](no charge for non-network use).  Documentation:http://www.diskeeper.com/products/documentation/documentation.asp

WinNT 4.0 does not come with a defragmenter.  Speed Disk provides defragmentation in the Norton Utilities package from Symantec.    A trial version is available for WinNT.     Win95 comes with a defragmenter.   Other defragmenters:  

Windows 2000.  Results of running Diskeeper Lite (Free Version) after running over 24 hours:
Diskeeper has completed a defragmentation run on this volume and there remain 22 fragmented files and/or directories and 99 excess fragments. (There were 1339 excess fragments before the defragmentation run, and now there are 92% less.) -- Picture of diskeeperlite result 78KB -- still looks like a mess.  Note the swap dataset not nicely situated either.

Formats   (#formats)

A message about too many cell formats.  A format in this case means each combination of font characteristics.  ie. Font type, font size, font color, interior color, bold, underline, pattern, number format, height, width, etc.  Reduce as much as you can.  Eliminating unused custom cell formats might also help.  The practical limit may be about 3K rather than 4K mentioned in Q213904 -- XL Err Msg: "Too Many Different Cell Formats".

The first problem is to open the file and people are simply not reporting back HOW they first opened the file.  See if anyone report back this time.

Leo Heuser has a macro that you can find in the 7th issue (eee007) of the Excel Experts E-letter to remove unused custom number formats.  The EEE newsletter is maintained by David Hager (MVP-Excel).  (link to revised code below)

Leo posted a macro 2001-05-06 in programming as a very major revision of the macro in the eee007 article.

Format entire columns instead of individual cells   (#likeformats)

Color, format, or conditionally format the entire column at one time, there will be NO change in file size but if you color the top cell and use the pattern tool and move the curser down increasing the number of cells being formatting as you go down you will greatly increase the filesize because each cell then carries it’s own format.  This is very simple for you to verify for yourself, save, and check file, properties after each test.  [my test] 

Macros and addins particularly Event Macros are frequently guilty of creating formats for individual cells instead of you formatting by an entire column before running a macro.

Volatile Functions   (#volatile)

Volatile Functions cause recalculation to take longer.  The worst would be User Defined Functions that have had the volatile option added.  But Excel itself has volatile functions:  CELL()  OFFSET()   TODAY()   INDIRECT()   NOW()   INFO()   RAND() as reported in MS KB Q274500 which indicates Excel prompts you to save changes to the workbook when you close it and sometimes (bug) CELL() is not being treated as volatile and requires F9 (calculate).

You can take a big hit on performance when using Volatile in your code to keep the results of a function current.  An example is that counting cell colors used with a function and other formatting changes do not force recalculation, so if you have other means of counting those you assigned a color to that might be more advisable. Another way would be to run your own recalc when you need correct numbers and leave out volatile.  Volatile keeps your function up to date based on the last recalculation, where something else changes -- like a value.  Some possible alternatives are Event macros that are triggered by a Worksheet or Workbook Event.  Conditional Formatting is a frequent alternative to event macros, but can’t change values of any cell.

The shortcut key Ctrl+Alt+F9 forces a recalculation of *everything* in all open workbooks whether or not Excel *thinks* recalculations are needed. Changing a format in itself does not trigger cell recalculation, so you will have to force this when you want the values to change if you don’t use volatile with some functions.
Application.CalculateFull ' in Excel 2000

You can avoid the use of volatile by providing another means of recalculating, either with Ctrl+Alt+F9 or by including the equivalent in another macro.  An example of such a macro in a worksheet activate event.

Checklist of things to check   (#checklist)

Some of these may have already been mentioned.  The two at the top are probably the most common causes of slow response. The above are things than can happen with Excel working properly.  It does not attempt to address problems related to network usage, or known Excel problems.

Slow after upgrading to Excel XP   (#slowxp)

Items under consideration as to consequences, for inclusion (#moreitems)

  1. Tools, Options, Calculation, update remote links
  2. Worksheet formulas and functions generally calculate faster than VBA functions.
  3. Small workbooks generally calculate faster than large ones; Peter Beach
  4. Best if dependent cells occur earlier (above and/or to the left). P.Beach
  5. Functions that find Workbook properties such as Author can force entire workbook to take a long time to calculate, save, etc.
  6. Conditional Formatting, with large specified ranges can take a long time to calculate, even if the change is to another column. 
    i.e. to find maximum quantity(B) for each product(A) item
        =AND(TRIM($B1)<:>"",$B1=MAX(IF($A$1:$A$1400=$A1,$B$1:$B$1400,"")))
  7. Macros available when file opened manually but not automatically.  Code to force opening provided by Pascal Robin 2000-08-31.  Excel.Application.Run("NameOfFile.xls!NameOfMacro")
  8. Calculation takes forever, MS KB Q243495 XL2000: Calculate Message Remains in Status Bar If 65,536 Formula References (Xl97 and XL2000), once there are more than 65,536 dependencies Excel no longer attempts to recalculate only changed cells.  Instead, it recalculates all cells after each change (by design).  Normally when you see “calculate” on the toolbar it means that you have Calculation set to manual instead of automatic.  Tools, Options, Calculation (tab), Automatic.  Also “calculate” may be seen for an intentional circular reference with Iterations checked, eg where one of the constituent compenents of a total price is dependent on the value of the total price.
  9. Q199505 macro may take several minutes if page breaks are visible.
  10. Links take a long time to update in Win2000.  Windows NT and Windows 2000 use a different version of ole32.dll.  The fix was to upgrade Windows 2000 to service pack 3 [Joe Buchanan ]. Q260943 Slow Performance Accessing Files May Slow Office Programs, This behavior involving hyperlinks occurs because Windows 2000 calls the system GetLongPathNameW function instead of the function built into Ole32.dll itself for Microsoft Windows NT 4.0

Things that can prevent response, keying in data   (#grayed)

  1. AutoSave occurring, you will see that on the Status Bar at the bottom of the Excel Window.
  2. Anti-Virus software scanning disk volumes.
  3. Defragmenter actively defragging.
  4. Getting email, newsgroup postings can take over machine for a period of time.
  5. MSGBOX or other dialog waiting for response, cell in process of being changed, or a cell error just found will prevent work in the VBE. (see note below this list)
  6. Macros in Break (Pause ||) in the VBE prevent use of macros (Alt+F8), but you will see a message “Can’t execute code in break mode”.  Awaiting a MSGBOX response will interfere with the Visual Basic Editor.
  7. Waiting for a response in system may interfere with Cut/Copy/Paste, FIND dialog boxes may interfere with working in VBE.  see note below this list and also Q231144 -- XL2000: Cut and Paste Operation in Visual Basic Procedure Hangs Excel
  8. Terminating a connection without going offline may bring up a dialog that mainly interferes with use of IE or OE.  (see note below this list).
Note:  Several of the above involve Dialog boxes waiting for a reply.  You can expose such dialog boxes by minimizing all of the applications with Windows Button + M

Temporary Files   (#temp)

Temporary files can make it impossible to open a file (file already open), or impossible to rename a file, or can simply slow things down.
delete everything from the temp folder...
win98:   C:\Windows\Temp\
win2k:   C:\Documents and Settings\your_user_name\Local Settings\Temp\
  ie.   C:\Documents and Settings\Administrator\Local Settings\Temp
  i.e.   C:\Documents and Settings\Administrator\Local Settings\Temporary Internet Files

Simple Timer (#timer)

Being able to time the execution of your code is an important in benchmarking performance.  Myrna Larson provided the following information in various postings:
The Timer in VBA only records seconds since midnight as a single which is not adequate to check timings for minimal data.  The NOW() function has an accuracy based on 18.2 ticks per second.  The Win API function winmm.dll is milliseconds since Windows was started.

If you use GetTickCount or timeGetTime from the WIN API you won’t have to worry about the midnight rollover.  They return the time in milliseconds since the system was started.  I believe the latter “rolls over” every 49 days.  Anyone have Macintosh considerations in this area because winmm.dll will not be on those machines.

Declare Function GetTickCount Lib "kernel32" () As Long
Declare Function timeGetTime Lib "winmm.dll" () As Long

Also check Jim Rech’s high performance timer http://www.j-walk.com/ss/excel/eee/eee005.txt, and also see MS KB Q172338 -- HOWTO: Use QueryPerformanceCounter to Time Code, because the counters are 10000 times smaller than the actual value you might also look at Q189862 -- HOWTO: Do 64-bit Arithmetic in VBA.

Don’t know specifically yet where the following code came from: (resolution 10ms) 


Private Declare Function timeGetTime Lib "winmm.dll" () As Long
Sub timings()
  Dim myTime(4) As Double
  myTime(1) = timeGetTime()
        '...  Your code here to be timed
  Dim i As Long
    For i = 1 To 10000000
      myTime(2) = timeGetTime()
      If myTime(2) - myTime(1) >= 1000 Then GoTo done
    Next i
done:
  '...  End of Your code here to be timed
  myTime(2) = timeGetTime()
  MsgBox Format((myTime(2) - myTime(1)) / 1000, "#.000") _
     & " seconds,  " & Format(i, "#,##0 iterations")
End Sub

Reregistering Excel, /Regserver, /Automation, /S {Safe mode) (#reg)

"C:\Program Files\Microsoft Office\Office\Excel.exe" /regserver

Components indicated as missing but they can be found in directories.

Clears up some problems with file showing up as Read-Only.  Also check that the read-only bit is not turned on such as from a file restored from a CD-ROM. Also try deleting temporary internet files in Internet Explorer.

Reregistering starts up Excel registers it settings and terminates.  You then restart Excel in the normal manner.

If the error message you’re getting is “cannot find xxx.xls or one of it’s components...” make sure Ignore other Applications under Tools, Options, General is unchecked. -- Jim Rech

Starting up Excel in “safe mode” so that you can turn off addins, macros that might interfere with starting up Excel.

291288 - Startup Switches for Microsoft Excel (2002: 291288, 2000: 211481; 97: 159474) (SAFE Mode, Re-register/Reregister Excel extensions)
Microsoft Excel accepts a number of optional switches that you can use to control how Excel starts.  This article lists the switches and provides a description of each switch. [for XL97 Q159474] see Tom Ogilvy's comments

Excel 2000 stops responding when start[ed], Jan Karel Pieterse, 2002-11-21

Just in case it comes up this is what I have under My Computer, Tools, Folder Options, File Types for .xls Microsoft Excel Worksheet
advanced button, Open, Edit button
Action: : Open
Application used to perform an action: : "C:\Program Files\Microsoft Office\Office\EXCEL.EXE" /e
Application: : Excel
DDE Application Not Running: : (blank)
Topic: : System

Continue in Startup topic below.

Additional things that cause read-only problems (#readonly)

Improvements to be seen in Office 12 (Vista) (#office12)

There are several Microsoft blogs following PDC and the MVP Summit that relate to improved features in Excel, notably by David Gainer (Microsoft).  Several things related to specification limits numbers where memory limits set in Excel to 1 GB have been removed, increase in columns and rows from 256 x 65536 to 16384 columns x 1048576 rows. 

Obviously virtual memory paging, segregation and reliability would have to have improved dramatically (like to at least mainframes in 1996).

I would expect font usage to improve considerably including use of upside down fonts which the lack of put font usage pre 1992 for True Type font usage in applications (compared to mainframes).

Hypertext entries have been included in the above article.

Additional pages

Also see information on Backup and Recovery, and on problems with save.

Code Optimization

Trouble Shooting and Startup Problems   (#startup)

Warnings (#warnings)

http://groups.google.com/advanced_group_search

Google currently has restored archives back to 1995-03-29 to about 48 hours of the present date and time.

Make due with the best you can, Google doesn’t allow Boolean Operators such as slow & (response | performance)

After seeing the responses, drop the word slow and retry your search.  Since only problems are posted, words like slow, poor, loss, problem, may not be needed to identify a problem when searching.  But you will also now pick up things like your previous response meaning something entirely different.

Also see my page on Newsgroups for additional information on the Microsoft Excel newsgroups and searching the archives.  Also look at my Search page for links to various Search engines.

Microsoft Knowledge Data Base -- MS KB   (#mskb)

You must register to use the MS KB and must turn on all things you don’t want to like cookies and Java scripts.  Hardly things that make for universal access.
  1. Microsoft for Windows
  2. Keywords
  3. slow response
Additional search entry points for the MS KB and fax back service information is included on my newsgroup page.

 Check if you have the latest maintenance.

 Also the following articles may be of interest

Slowed down system (#slow)

Multiple files being opened later

Windows and Office Update sites   (#updatesites)

Keeping your existing software up to date can solve problems or prevent you from experiencing problems that others have had.  Updates are more likely to address reliability and usage than optimization but it is best to keep your system up to date. Since there are Microsoft sites the URL’s might (WILL) change.  You will be automatically redirected with several of the following links to more specific links for your system.  Note service packs can be very large you may want to run these from a high speed connection if you have at least occassional access to same, or order a service pack on a CD.

This page was originally composed with Netscape Composer in Communicator  and MS FrontPage, but like almost all of my pages is maintained manually.

This page was introduced on October 15, 1998. 
 

[My Excel Pages -- home]    [INDEX to my site and the off-site pages I reference] 
[Site Search -- Excel]     [Go Back]    [Return to TOP] 

Please send your comments concerning this web page to: David McRitchie send email comments

Copyright © 1997 - 2009,  F. David McRitchie,  All Rights Reserved