J
Jim Luedke
Excel 2002 seems to retain tons of hidden stuff you thought you
deleted from your workbook or VBA project.
Two symptoms:
1. I had a 5-megabyte workbook. I:
- deleted all worksheets but one
- deleted all data and formatting on that remaining sheet
- removed all hundreds of range names (manually, due to Excel 2002’s
notorious lack of defined-name management--which I understand has been
improved in Excel 2007)
- deleted all VBA modules and code
- killed the workbook password, etc.
My workbook is now a single empty spreadsheet. The VBA editor has no
content. (The Project Explorer shows only funcres and my project,
under which are only the Sheet and Workbook objects, both codeless.)
Yet my .xls file size is 2/3 MB.
By contrast, create a new workbook and save it. File size is a couple
dozen KB.
2. The VBA editor wrongly remembers var, const, and perhaps sub names
I have deleted or renamed.
That is, it does its auto-re-casing on obsolete spellings, signaling
that it still recognizes them.
I have a hunch that my 2/3 MB black hole is mostly deleted VBA stuff,
not deleted sheet data.
***
For those old enough to remember, dBASE II on release in 1982
laughably never removed data from your file. Oh, it had an .erase
command, which made a record no longer accessible. But deleted records
stayed there forever. Your .DBF files grew like topsy and never
shrank.
That behavior was pathetic in the 1980’s. It is pathetic today.
At the above ratio, is Microsoft saying a 100 MB workbook is 87% beef,
13% corn meal and sawdust?
Am I all wet, and missing something obvious here?
What is the solution? Surely there's a third-party app that
expunges .xls files?
And how do you tell Excel to call off its recognition of obsolete VBA
objects?
To you Knights of the Net, thanks much as usual.
***
deleted from your workbook or VBA project.
Two symptoms:
1. I had a 5-megabyte workbook. I:
- deleted all worksheets but one
- deleted all data and formatting on that remaining sheet
- removed all hundreds of range names (manually, due to Excel 2002’s
notorious lack of defined-name management--which I understand has been
improved in Excel 2007)
- deleted all VBA modules and code
- killed the workbook password, etc.
My workbook is now a single empty spreadsheet. The VBA editor has no
content. (The Project Explorer shows only funcres and my project,
under which are only the Sheet and Workbook objects, both codeless.)
Yet my .xls file size is 2/3 MB.
By contrast, create a new workbook and save it. File size is a couple
dozen KB.
2. The VBA editor wrongly remembers var, const, and perhaps sub names
I have deleted or renamed.
That is, it does its auto-re-casing on obsolete spellings, signaling
that it still recognizes them.
I have a hunch that my 2/3 MB black hole is mostly deleted VBA stuff,
not deleted sheet data.
***
For those old enough to remember, dBASE II on release in 1982
laughably never removed data from your file. Oh, it had an .erase
command, which made a record no longer accessible. But deleted records
stayed there forever. Your .DBF files grew like topsy and never
shrank.
That behavior was pathetic in the 1980’s. It is pathetic today.
At the above ratio, is Microsoft saying a 100 MB workbook is 87% beef,
13% corn meal and sawdust?
Am I all wet, and missing something obvious here?
What is the solution? Surely there's a third-party app that
expunges .xls files?
And how do you tell Excel to call off its recognition of obsolete VBA
objects?
To you Knights of the Net, thanks much as usual.
***