Slow Response and Memory Problems
Speeding up Excel, Enhancing Performance

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

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

Excel 97 (and later versions)

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.

Outlook, journaling feature in MS Office 97 and 98 «

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

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).

File Extension is not .xls

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 lines is slow

Lastcell (last line) problems

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. 

Another indication is that your scrolling bar 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 lines 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

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. 

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
       Application.Calculation = xlAutomatic
       MsgBox Application.Calculation
    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

« 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.
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

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 lastline 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 lines with certain content,  inserting lines 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.  Turning off screenupdating saved about 12%, but turning off calculation saved an additional whopping 75%.

Dimensioning variables for use with Options Explicit.  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.

Speeding up VBA code

Declare variables with their datatype [see table]:  Byte, Boolean, Integer, 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.

Some pages strictly on speeding up VBA.

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.

Opening and Closing Excel

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

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

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

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 NT.  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$e35.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 [not found] 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

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

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 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.asp">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 navigate especially with those internal scrollbars]

Objects Not Being Released

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
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

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 activate 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

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 RtClicking on the taskbar.  (I have WinNT and am not familiar with the others, hope they look the same).

Check Memory Usage under Performance.   You can change your settings for page files under  desktop

START -->  Settings --> Control Panel --> System [System Properties] --> Performance --> [Change]
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.

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.

Memory consumption with VBE

The Visual Basic Editor (VBE) can consume lots of memory if you have several modules open.  Also close each module before exiting the VBE; otherwise, all modules that were open will still be open when you reinvoke the VBE.  Keep macros and formulas that are likely to be used in same session together so you don't bring in a lot of unneeded coding.  Don't put all your code into one module.  You can look at the Window menu item in the VBE to see if you have more than one module currently open for editing.

Disk files  (all systems)

    Eliminate unused files for  performance

    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  del  *.tmp  /s
    2. Eliminate c:\temp   files and all directories under  c:\temp
    3. 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) --
        C:\WINNT\PROFILES\userid\Temporary Internet Files [see Internet Temporary Files in next topic below]
        c:\documents and settings\userid\local settings\temp
      Such files may particularly slow down Excel when active-x components are involved.
    4. Compact your email.  In MS Outlook Express -- 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,
    5. 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 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)

    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.

    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:  

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".

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.

Checklist of things to check

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.

Items under consideration as to consequences, for inclusion

  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).
  9. Q199505 macro may take several minutes if page breaks are visible.

Things that can prevent response, keying in data

  1. AutoSave occurring, you will see that on the Status Bar at the bottom of the Excel Window.
  2. Automatic Calculation.
  3. 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 and sometimes (bug) CELL() is not being treated as volatile and requires F9 (calculate).
  4. Anti-Virus software scanning disk volumes.
  5. Defragmenter actively defragging.
  6. Getting email, newsgroup postings can take over machine for a period of time.
  7. 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)
  8. 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.
  9. 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
  10. 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

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\

Simple 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

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 for timing
  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 for timing
  myTime(2) = timeGetTime()
  MsgBox Format((myTime(2) - myTime(1)) / 1000, "#.000") _
     & " seconds,  " & Format(i, "#,##0 iterations")
End Sub

Reregistering Excel

"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.

Q211481 - XL2000: Startup Switches for Microsoft Excel 2000
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

Related Information

Hypertext entries have been included in the above article.

Additional pages

Also see information on backup.

Code Optimization

Other web sites

Warnings

Google Usenet Advanced Newsgroup Search

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

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

This page was composed with Netscape Composer in Communicator  and MS FrontPage.

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 mailto:DMcRitchie@msn.com