Type 1,2,and 3 errors in Office 2001

[Nathan]

I use Excel 2001 on Mac OS 9.2 to run a particularly problematic
workbook file. This file contains about 5000 rows and 20 columns
filled with formatted text. Each cell contains no more than 70
characters and an average of 10 characters. There are no formulas or
hyperlinks. Some cells contain validations. Each column is in
autofilter mode. The workbook contains 2 simple macros:
"Before_Close" and "Open". The total size of the file is 2.2 MB.
This does not seem too large, since I work with many files double this
size without problems. Note that these other files contain formulas,
internal links, and charts. The problem is as follows. Occasionally,
the workbook will refuse to open, citing errors of type 1,2, or, most
commonly 3. The fact that I work with much larger files without
problem suggests the crashes are fundamentally due to memory
allocation. Crashes occur regardless of whether the "Open" macro is
enabled. After a crash, the file will never open again, and is not
accessible by any kind of linking. When I try to link to the crashed
file, excel quits citing a type 11 error. I restore this file using
several backups created periodically by internal macros. I have found
that reducing the size of the file causes these crashes to happen much
less frequently. No crash occurred when the file was less than 1.9
MB. Often when many cells are updated by an external macro and the
file is closed, it will crash the next time I attempt to open it.

Tech specs for two computers that typically run this file:
First: 320 MB built in memory + 400 MB virtual memory – Microsoft
Excel memory allocation: suggested = 6144K, minimum = 33,072K,
preferred size = 122,880K.
Second:1024MB built in memory with disabled virtual memory -- Excel
memory allocation: suggested =11910K, minimum =159662, preferred size
=197476K.

 

[JE McGimpsey]

I use Excel 2001 on Mac OS 9.2 to run a particularly problematic
workbook file. This file contains about 5000 rows and 20 columns
filled with formatted text. Each cell contains no more than 70
characters and an average of 10 characters. There are no formulas or
hyperlinks. Some cells contain validations. Each column is in
autofilter mode. The workbook contains 2 simple macros:
"Before_Close" and "Open". The total size of the file is 2.2 MB.
This does not seem too large, since I work with many files double this
size without problems. Note that these other files contain formulas,
internal links, and charts. The problem is as follows. Occasionally,
the workbook will refuse to open, citing errors of type 1,2, or, most
commonly 3. The fact that I work with much larger files without
problem suggests the crashes are fundamentally due to memory
allocation. Crashes occur regardless of whether the "Open" macro is
enabled. After a crash, the file will never open again, and is not
accessible by any kind of linking. When I try to link to the crashed
file, excel quits citing a type 11 error. I restore this file using
several backups created periodically by internal macros. I have found
that reducing the size of the file causes these crashes to happen much
less frequently. No crash occurred when the file was less than 1.9
MB. Often when many cells are updated by an external macro and the
file is closed, it will crash the next time I attempt to open it.

Tech specs for two computers that typically run this file:
First: 320 MB built in memory + 400 MB virtual memory – Microsoft
Excel memory allocation: suggested = 6144K, minimum = 33,072K,
preferred size = 122,880K.
Second:1024MB built in memory with disabled virtual memory -- Excel
memory allocation: suggested =11910K, minimum =159662, preferred size
=197476K.

Sounds like your workbook may be corrupted. Try copying the data to a
new workbook (don't copy the sheet, rather Select All and Copy, then
Paste into a new workbook. Likewise copy the macros by cut and paste).

Alternatively, try saving your workbook as a Text file, then import it
into a new workbook (again, macros will need to be copied separately).

BTW - your minimum memory allocations are too high - it will limit what
other applications you can run while Excel is open. I recommend you set
it back to the factory setting - XL will still get all the memory in the
preferred allocation, if it's available, but the OS will be able to swap
out the memory if another application needs it.

 

[Nathan]

Thank you for your quick response. However, I would like, if
possible, a more detailed explanation of what is going on behind the
scenes, about why my file is continually becoming corrupted. I
restore the file quite often using the first method that you mentioned
– taking backed-up information in the cells and copying it into a
template (a very small file) which was never filled with information
and I assume is not corrupted. This technique solves our problems in
the short term. Eventually, my file becomes corrupted again. Any
insight or hypotheses would be greatly appreciated.
I had a feeling that my minimum memory allocations were too high, but
unfortunately, I don't know what the factory settings are.

Thanks again

 

[Nathan]

Thank you for your quick response. However, I would like, if
possible, a more detailed explanation of what is going on behind the
scenes, about why my file is continually becoming corrupted. I
restore the file quite often using the first method that you mentioned
– taking backed-up information in the cells and copying it into a
template (a very small file) which was never filled with information
and I assume is not corrupted. This technique solves our problems in
the short term. Eventually, my file becomes corrupted again. Any
insight or hypotheses would be greatly appreciated.
I had a feeling that my minimum memory allocations were too high, but
unfortunately, I don't know what the factory settings are.

Thanks again

 

[JE McGimpsey]

Thank you for your quick response. However, I would like, if
possible, a more detailed explanation of what is going on behind the
scenes, about why my file is continually becoming corrupted. I
restore the file quite often using the first method that you mentioned
– taking backed-up information in the cells and copying it into a
template (a very small file) which was never filled with information
and I assume is not corrupted. This technique solves our problems in
the short term. Eventually, my file becomes corrupted again. Any
insight or hypotheses would be greatly appreciated.

If this is happening frequently, I would start with a complete disk
repair, using Norton or TechTool - in my experience OS errors are more
common sources of recurring Type 1-3 errors than application errors.

Unfortunately, I don't have much info on how/why the corruption is
occurring. Over the years, I've had only a very few production workbooks
get corrupted, but a couple of those corrupted frequently.

Are you pasting into the template itself or into a fresh copy? Have you
tried pasting it into a completely new workbook? One of the more
frequent sources of corruption, in my experience, is from frequent
editing of macros - the VBA modules get corrupted.

If repairing your HD doesn't change things, I'd strongly consider a
complete reinstall of Office. First, run the Remove Office application
from MacTopia downloads (don't use the one on the install disk - it's
hopelessly out of date). Make sure you apply all of the relevant updates
you can find at MacTopia.

I had a feeling that my minimum memory allocations were too high, but
unfortunately, I don't know what the factory settings are.

I don't recall the exact figure, but 8-10 MB should suffice.