Reason for increased size of workbook

[Richard Buttrey]

We have a multi sheet workbook of circa 37 Mb which we've been using
succesfully for a couple of years or so. The main sheet in the WB
covers almost all the 256 columns and about 1800 rows, with a mixture
of formulae and hard coded values.

Recently, and because we needed to extend the data to cover more
business periods, and not having sufficient room on the main sheet, I
decided to rethink the organisation of the data.

Many of the columns were intermediate working columns supporting their
particular period's summary values, so I decided to write a UDF to
eliminate the working columns and freeing up room for more periods.

I now find the workbook is around 47 Mb, which is not a particular
problem, but I'd like to know why. Is it normal for columns of UDFs to
take more room than columns of standard Excel functions (like
If(etc..etc)?

It also takes slightly longer to calculate, but I sort of expected
that since presumably there is an overhead when the UDF calls the VBE.

Can anyone offer a comment on why the Wb takes up about 30% more
space?

Rgds


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[CurlyDave]

That sounds so big, just one sheet??

 

[JLatham]

37MB does seem large, 47MB obviously significantly larger. But 256 x 1800 =
460,800 cells with formulas and data in them that we don't know the length
of, not unbelievable. And for those asking - that's not the only sheet in
the workbook, was mentioned it is the "main sheet", so, on with it... And if
there are graphics stored within the workbook...

But since it is characters stored and number of cells used/sheets used that
is what is stored on the drive, the space shouldn't have increased that much
just from complexity of operations. Not unless you were even more verbose
than I am when writing code <g>

The growth over time may be coming from Excel not accurately updating what
is known as the Used Range on worksheets. It often leaves that marked as the
right-most column, farthest down row ever used, even though you may only have
something in cell A1 at the moment! Easiest way to tell how to possibly
reset this Used Range value to remove reference to now unused space is to
refer you to this page:
http://www.contextures.com/xlfaqApp.html#Unused
Start reading there, it goes on down to provide some code to do the task
starting at a heading that reads "To programatically reset the used range" -
code is in that area.

Another habit that Excel has that can increase file size is that of putting
macros recorded into separate/new code modules during each session when they
are recorded. I've seen workbooks with 30 or more code modules, each only
holding a single macro Sub. Each of those modules takes a little overhead
just to exist - in cases like that you can usually copy the Subs from many
modules and put them into a single one, then delete the no longer needed
modules, reducing the overhead for all those individual modules.

Hope this helps some.

 

[Richard Buttrey]

Hi,

Thanks for the comprehensive post. Much appreciated.

I'll check out some of the ideas. I thought I'd eliminated unpopulated
cells in the Used Range, but I'll take another look.

You are quite correct in your interpretation, there are another 30 or
so sheets, some with a few hundred rows on them and one with about
8000, although this latter one comprises values only.

Kind regards

37MB does seem large, 47MB obviously significantly larger. But 256 x 1800 =
460,800 cells with formulas and data in them that we don't know the length
of, not unbelievable. And for those asking - that's not the only sheet in
the workbook, was mentioned it is the "main sheet", so, on with it... And if
there are graphics stored within the workbook...

But since it is characters stored and number of cells used/sheets used that
is what is stored on the drive, the space shouldn't have increased that much
just from complexity of operations. Not unless you were even more verbose
than I am when writing code <g>

The growth over time may be coming from Excel not accurately updating what
is known as the Used Range on worksheets. It often leaves that marked as the
right-most column, farthest down row ever used, even though you may only have
something in cell A1 at the moment! Easiest way to tell how to possibly
reset this Used Range value to remove reference to now unused space is to
refer you to this page:
http://www.contextures.com/xlfaqApp.html#Unused
Start reading there, it goes on down to provide some code to do the task
starting at a heading that reads "To programatically reset the used range" -
code is in that area.

Another habit that Excel has that can increase file size is that of putting
macros recorded into separate/new code modules during each session when they
are recorded. I've seen workbooks with 30 or more code modules, each only
holding a single macro Sub. Each of those modules takes a little overhead
just to exist - in cases like that you can usually copy the Subs from many
modules and put them into a single one, then delete the no longer needed
modules, reducing the overhead for all those individual modules.

Hope this helps some.

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Art H]

Hi Richard,

Most likely not the case, but might be worth your while. For Excel
2000, Format-> Style-> and pull down the Style name list. There might
be lots of formats not needed.

It might be a useful experiment to create a new greatly pared-down
workbook pre-UDF, determine the size, then replace the Excel formulas
with your UDF just to see the impact. This might indicate that several
calls to a UDF comes with the expense of a lot of overhead. Who knows?

Art