Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reason for increased size of workbook
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 __________________________ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reason for increased size of workbook
That sounds so big, just one sheet??
Richard Buttrey wrote: 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 __________________________ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reason for increased size of workbook
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" wrote: 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 __________________________ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reason for increased size of workbook
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 On Sat, 21 Oct 2006 19:45:02 -0700, JLatham <HelpFrom @ Jlathamsite.com.(removethis) wrote: 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" wrote: 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 __________________________ __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reason for increased size of workbook
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 Richard Buttrey wrote: 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 On Sat, 21 Oct 2006 19:45:02 -0700, JLatham <HelpFrom @ Jlathamsite.com.(removethis) wrote: 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" wrote: 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 __________________________ __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
File Size With Macros Has Increased From Its Origina Size | Excel Discussion (Misc queries) | |||
What increased my file size from 2mb to 5mb in a week? | Excel Discussion (Misc queries) | |||
File size increased 7-fold! | Excel Discussion (Misc queries) | |||
Can actual size of CHECK BOX be increased? | Excel Worksheet Functions | |||
Excel file size increased | Excel Discussion (Misc queries) |