Why is my empty Excel workbook 2/3 MB in size?

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

***

 

[Charles Williams]

Try mine & Jan Karel's freebie Name Manager as a better approach to Name
Management
http://www.decisionmodels.com/downloads.htm

& check out Rob Bovey's Code Cleaner if you are not already using it

http://www.appspro.com/Utilities/CodeCleaner.htm

& presumably you have already removed all the hidden and very hidden stuff &
fixed the used ranges etc

Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

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.

***

 

[Dave Peterson]

600kb still sounds like a lot to me.

If you hit ctrl-end (when you're on the only worksheet), do you go to A1 or a
different cell?

Are you sure you didn't have any hidden objects (Pictures???) on that sheet?

 

[Jim Luedke]

Charles, Dave:

Thanks much for replies.

Charles: Yes, I've been meaning to check out your range-name mgr. for
some time.

Dave: I've done this test twice now, on large WBs, and I don't think
I've overlooked hidden stuff. Also, I never hide things anyway.

Since I suspect my 2/3 MB of dark matter could be deleted VBA stuff,
is there such a capability as hiding things in the VBA Project
Explorer's tree? I'll certainly check that out.

Thanks again.

***

 

[Dave Peterson]

Not that I've seen.

In fact, in large workbooks that I've used--with what I'd consider a lot of
code, the size of the data/formulas (excel stuff) just dwarfs the size of the
code -- in all the modules/userforms (project stuff).

 

[Charles Williams]

Excel/VBA is not wonderfully clever at removing P-code etc, maybe you have
residual stuff: have you tried Rob Bovey's code cleaner?
(might have to Clean before deleting the source VBA modules or add a dummy
module).

Or if you have access to XL2007 you could saveas .xlsm and look in the
resulting zip file to see what it contains
(If you don't have access to XL 2007 you could send a mystery workbook to me
& I will have a look)

regards
Charles
___________________________________
The Excel Calculation Site
http://www.decisionmodels.com

 

[PolymerTim]

Same problem here. Still no solution.

I've been having the same trouble with my Excel sheet. I've created a table in Excel 2007 and had a few hundred entries and a small Pivot Table in it. One day I noticed that the vertical scroll bar had gotten tiny as if I was using the full range of rows in the sheet. Sure enough, CTRL+End brings me to row 1048576 (the last row) and my worksheet size is about 2.5 MB regardless of what I do. As an experiment, I made a copy of my workbook and tried just about everything. I have saved it as a xlsx (original document was xlsm), deleted all rows and columns, tried several VBA snippets including Dave McRitchie's "MakeLastCell" code that makes use of the UsedRange trick and programmatically deleting extraneous rows and columns.

The problem is tied to this one worksheet. I can export any other sheet by copying to another workbook and the filesize is fine. If I copy this problem worksheet to another workbook, the 2.5 MB goes with it. Once in its own book, I can delete everything in the entire sheet and still have 2.5 MBs. Print preview verifies that there is nothing found to print, but all the time, the vertical scroll bar hints that all the rows are considered in use. If I save to html, I get a 4.9 MB, supertall, blank page. If I open the xlsx file with notepad, I get several dozen pages of fairly repetitive gobbledegook.

So do you guys have any suggestions? Have I missed something, or is this just some crazy glitch that has left useless data permanently stuck in this sheet? The only solution I can find is to completely recreate the worksheet from scratch and then manually edit all the sheets within the book that reference the data table (I'd really rather not have to do that).

Thanks for all your help,
Tim