Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
The expanding XLS spreadsheet
I've been working on an Application (in Excel 2002) for awhile. Lately, it's
been growing out of control. To give you an idea, when I save the thing (in Excel 2002/XP format, we won't even TALK about what happens if I save it in Excel 97 format), it's pushing 150MB. At this point, the application consists of the following: - About 110 worksheets - each with two TabStrip controls (with anywhere from 5 to 30 tabs), and approximately 4-5 buttons, as well as two small images - Each worksheet has about 400 lines of VBA code (although about 150 lines of that is temporary code, commented out, and will be gone when this goes to production). - Two main (shared/common) VBA modules that have about 4000 and 1200 lines (respectively) of VBA code And that's about it... But somehow it's ballooned up to 150MB, and is starting to give my computer fits as I work on it. To add insult to injury, realistically, there will probably (when all is said and done) be over 200 worksheets (each with probably around 200-300 lines of code each) Now, it's way too late to rewrite this in a better way (and for that matter, although the code is similar on each of the worksheets, it's unique enough that I cannot just call a shared function for most of it). Is there any way I can get this thing to shrink back down to a managable size? I wouldn't mind if it was as much as 50-60MB, once I've got all 200 worksheets. But that it's above 150MB already, and I'm barely half-way there, has me quite concerned. Can anyone offer ideas/suggestions? Thanks! -Scott |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
The expanding XLS spreadsheet
"Scott Lyon" <scott.lyonNOSPAM_at__NOSPAMrapistan.comNOSPAM wrote in message ... I've been working on an Application (in Excel 2002) for awhile. Lately, it's been growing out of control. To give you an idea, when I save the thing (in Excel 2002/XP format, we won't even TALK about what happens if I save it in Excel 97 format), it's pushing 150MB. At this point, the application consists of the following: - About 110 worksheets - each with two TabStrip controls (with anywhere from 5 to 30 tabs), and approximately 4-5 buttons, as well as two small images - Each worksheet has about 400 lines of VBA code (although about 150 lines of that is temporary code, commented out, and will be gone when this goes to production). - Two main (shared/common) VBA modules that have about 4000 and 1200 lines (respectively) of VBA code And that's about it... But somehow it's ballooned up to 150MB, and is starting to give my computer fits as I work on it. To add insult to injury, realistically, there will probably (when all is said and done) be over 200 worksheets (each with probably around 200-300 lines of code each) Now, it's way too late to rewrite this in a better way (and for that matter, although the code is similar on each of the worksheets, it's unique enough that I cannot just call a shared function for most of it). Is there any way I can get this thing to shrink back down to a managable size? I wouldn't mind if it was as much as 50-60MB, once I've got all 200 worksheets. But that it's above 150MB already, and I'm barely half-way there, has me quite concerned. Can anyone offer ideas/suggestions? Dowload and run a VBA Codecleaner such as that on the appspro site. Each time you edit a VBA file it gets bigger as VBA cant seem to recover used space. This utility will recover that space and lets you strip comments and blank lines from code (useful for shipping externallu but keep a copy) http://www.appspro.com/utilities/Cleaner.asp Keith |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
The expanding XLS spreadsheet
Scott
"And that's about it" 100 sheets x 400 lines of code = 40,000 lines of code + 5,200 lines = 45,200 lines 200 sheets x 400 lines of code = 80,000 lines of code + 5,200 lines = 85,200 lines PLUS all the other stuff I don't know how the size of the workbook relates to the amount of code but that sounds a lot to me Is the code in all those sheets really that different? Is every sheet unique or are there groups of sheets ? First thought would be to move all the (duplicated) code to standard modules, perhaps one per sheet type, and then have one line calls from the worksheet. For example: ' Sheet1 - Type "one" Private Sub Worksheet_Change(ByVal Target As Range) TypeOneSheet_Worksheet_Change(....) End Sub TypeOneSheet_Worksheet_Change(....) ' original worksheet change code End Sub * Any code that IS common, move to a standard module. * Remove Select/Selection pairs * get rid of the redundant code and so on * Use Rob Bovey's Code Cleaner I'm sure you'll get lots more suggestions Regards Trevor "Scott Lyon" <scott.lyonNOSPAM_at__NOSPAMrapistan.comNOSPAM wrote in message ... I've been working on an Application (in Excel 2002) for awhile. Lately, it's been growing out of control. To give you an idea, when I save the thing (in Excel 2002/XP format, we won't even TALK about what happens if I save it in Excel 97 format), it's pushing 150MB. At this point, the application consists of the following: - About 110 worksheets - each with two TabStrip controls (with anywhere from 5 to 30 tabs), and approximately 4-5 buttons, as well as two small images - Each worksheet has about 400 lines of VBA code (although about 150 lines of that is temporary code, commented out, and will be gone when this goes to production). - Two main (shared/common) VBA modules that have about 4000 and 1200 lines (respectively) of VBA code And that's about it... But somehow it's ballooned up to 150MB, and is starting to give my computer fits as I work on it. To add insult to injury, realistically, there will probably (when all is said and done) be over 200 worksheets (each with probably around 200-300 lines of code each) Now, it's way too late to rewrite this in a better way (and for that matter, although the code is similar on each of the worksheets, it's unique enough that I cannot just call a shared function for most of it). Is there any way I can get this thing to shrink back down to a managable size? I wouldn't mind if it was as much as 50-60MB, once I've got all 200 worksheets. But that it's above 150MB already, and I'm barely half-way there, has me quite concerned. Can anyone offer ideas/suggestions? Thanks! -Scott |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
The expanding XLS spreadsheet
I downloaded that "cleaner" program, and tried it. Unfortunately, it seems
that it only "cleans" the VBA code that's actually in modules. The problem is that the vast majority of my code is actually in the code behind individual sheets. In fact, running that "cleaner" program only saved me a few megs off of a 150MB file. Which isn't enough to even make a difference, unfortunately. Is there a way (manual or automatic, although with over 200 worksheets I'd prefer automatic) that I can "clean" up the garbage associated with all of the worksheets' VBA code? Thanks! -Scott "Keith Willshaw" wrote in message ... "Scott Lyon" <scott.lyonNOSPAM_at__NOSPAMrapistan.comNOSPAM wrote in message ... I've been working on an Application (in Excel 2002) for awhile. Lately, it's been growing out of control. To give you an idea, when I save the thing (in Excel 2002/XP format, we won't even TALK about what happens if I save it in Excel 97 format), it's pushing 150MB. At this point, the application consists of the following: - About 110 worksheets - each with two TabStrip controls (with anywhere from 5 to 30 tabs), and approximately 4-5 buttons, as well as two small images - Each worksheet has about 400 lines of VBA code (although about 150 lines of that is temporary code, commented out, and will be gone when this goes to production). - Two main (shared/common) VBA modules that have about 4000 and 1200 lines (respectively) of VBA code And that's about it... But somehow it's ballooned up to 150MB, and is starting to give my computer fits as I work on it. To add insult to injury, realistically, there will probably (when all is said and done) be over 200 worksheets (each with probably around 200-300 lines of code each) Now, it's way too late to rewrite this in a better way (and for that matter, although the code is similar on each of the worksheets, it's unique enough that I cannot just call a shared function for most of it). Is there any way I can get this thing to shrink back down to a managable size? I wouldn't mind if it was as much as 50-60MB, once I've got all 200 worksheets. But that it's above 150MB already, and I'm barely half-way there, has me quite concerned. Can anyone offer ideas/suggestions? Dowload and run a VBA Codecleaner such as that on the appspro site. Each time you edit a VBA file it gets bigger as VBA cant seem to recover used space. This utility will recover that space and lets you strip comments and blank lines from code (useful for shipping externallu but keep a copy) http://www.appspro.com/utilities/Cleaner.asp Keith |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
The expanding XLS spreadsheet
Typically code is not what adds significantly to file size. It's the
worksheets themselves. Recreation of the worksheets is the best and only way to get a reasonable file size reduction (at least have the potential of). However with that many worksheets this may not be practical. I'm no longer surprised by the gains that can be achieved by doing this purely because over time even a clean-ish looking sheet can contain lots of invisible formatting. Try ensuring your file is saved in Excel 97 only format. Saving in other formats can double the size due to compatibility constraints. Although reading the rest of the post suggests you've already experimented with this. If your confident that the worksheets are reasonably cleanly designed (and there's no stray random formatting etc. everywhere) then it's clear you have to target the code use. Perhaps consider using a COM component if your architecture allows for it. This way you can centralise a lot of your code base and save repetition. However public functions would, to a large degree, do the same thing in your case. -- Regards, Bill Lunney www.billlunney.com "Scott Lyon" <scott.lyonNOSPAM_at__NOSPAMrapistan.comNOSPAM wrote in message ... I downloaded that "cleaner" program, and tried it. Unfortunately, it seems that it only "cleans" the VBA code that's actually in modules. The problem is that the vast majority of my code is actually in the code behind individual sheets. In fact, running that "cleaner" program only saved me a few megs off of a 150MB file. Which isn't enough to even make a difference, unfortunately. Is there a way (manual or automatic, although with over 200 worksheets I'd prefer automatic) that I can "clean" up the garbage associated with all of the worksheets' VBA code? Thanks! -Scott "Keith Willshaw" wrote in message ... "Scott Lyon" <scott.lyonNOSPAM_at__NOSPAMrapistan.comNOSPAM wrote in message ... I've been working on an Application (in Excel 2002) for awhile. Lately, it's been growing out of control. To give you an idea, when I save the thing (in Excel 2002/XP format, we won't even TALK about what happens if I save it in Excel 97 format), it's pushing 150MB. At this point, the application consists of the following: - About 110 worksheets - each with two TabStrip controls (with anywhere from 5 to 30 tabs), and approximately 4-5 buttons, as well as two small images - Each worksheet has about 400 lines of VBA code (although about 150 lines of that is temporary code, commented out, and will be gone when this goes to production). - Two main (shared/common) VBA modules that have about 4000 and 1200 lines (respectively) of VBA code And that's about it... But somehow it's ballooned up to 150MB, and is starting to give my computer fits as I work on it. To add insult to injury, realistically, there will probably (when all is said and done) be over 200 worksheets (each with probably around 200-300 lines of code each) Now, it's way too late to rewrite this in a better way (and for that matter, although the code is similar on each of the worksheets, it's unique enough that I cannot just call a shared function for most of it). Is there any way I can get this thing to shrink back down to a managable size? I wouldn't mind if it was as much as 50-60MB, once I've got all 200 worksheets. But that it's above 150MB already, and I'm barely half-way there, has me quite concerned. Can anyone offer ideas/suggestions? Dowload and run a VBA Codecleaner such as that on the appspro site. Each time you edit a VBA file it gets bigger as VBA cant seem to recover used space. This utility will recover that space and lets you strip comments and blank lines from code (useful for shipping externallu but keep a copy) http://www.appspro.com/utilities/Cleaner.asp Keith |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
The expanding XLS spreadsheet
Excel 97 format? Really? I've been noticing exactly the opposite... I found
that saving in Excel 2002/XP format was a fraction of the size of saving it in Excel 97 format. I'm trying it now again, to see if it's any different this time. As for recreating the worksheets, yes, you're correct, with that number, it would take an IMMENSE amount of time to do all of that, and I could still wind up with the same (or similar) problem. Unfortunately, one of the requirements for this application is that it would be standalone (an Excel spreadsheet - no install required other than copying the XLS to the user's hard drive), so using a COM component isn't an option. Besides, if as you say the problem is more the worksheets than the code, I'm not sure it would save much. Thanks! -Scott "Bill Lunney" wrote in message ... Typically code is not what adds significantly to file size. It's the worksheets themselves. Recreation of the worksheets is the best and only way to get a reasonable file size reduction (at least have the potential of). However with that many worksheets this may not be practical. I'm no longer surprised by the gains that can be achieved by doing this purely because over time even a clean-ish looking sheet can contain lots of invisible formatting. Try ensuring your file is saved in Excel 97 only format. Saving in other formats can double the size due to compatibility constraints. Although reading the rest of the post suggests you've already experimented with this. If your confident that the worksheets are reasonably cleanly designed (and there's no stray random formatting etc. everywhere) then it's clear you have to target the code use. Perhaps consider using a COM component if your architecture allows for it. This way you can centralise a lot of your code base and save repetition. However public functions would, to a large degree, do the same thing in your case. -- Regards, Bill Lunney www.billlunney.com "Scott Lyon" <scott.lyonNOSPAM_at__NOSPAMrapistan.comNOSPAM wrote in message ... I downloaded that "cleaner" program, and tried it. Unfortunately, it seems that it only "cleans" the VBA code that's actually in modules. The problem is that the vast majority of my code is actually in the code behind individual sheets. In fact, running that "cleaner" program only saved me a few megs off of a 150MB file. Which isn't enough to even make a difference, unfortunately. Is there a way (manual or automatic, although with over 200 worksheets I'd prefer automatic) that I can "clean" up the garbage associated with all of the worksheets' VBA code? Thanks! -Scott "Keith Willshaw" wrote in message ... "Scott Lyon" <scott.lyonNOSPAM_at__NOSPAMrapistan.comNOSPAM wrote in message ... I've been working on an Application (in Excel 2002) for awhile. Lately, it's been growing out of control. To give you an idea, when I save the thing (in Excel 2002/XP format, we won't even TALK about what happens if I save it in Excel 97 format), it's pushing 150MB. At this point, the application consists of the following: - About 110 worksheets - each with two TabStrip controls (with anywhere from 5 to 30 tabs), and approximately 4-5 buttons, as well as two small images - Each worksheet has about 400 lines of VBA code (although about 150 lines of that is temporary code, commented out, and will be gone when this goes to production). - Two main (shared/common) VBA modules that have about 4000 and 1200 lines (respectively) of VBA code And that's about it... But somehow it's ballooned up to 150MB, and is starting to give my computer fits as I work on it. To add insult to injury, realistically, there will probably (when all is said and done) be over 200 worksheets (each with probably around 200-300 lines of code each) Now, it's way too late to rewrite this in a better way (and for that matter, although the code is similar on each of the worksheets, it's unique enough that I cannot just call a shared function for most of it). Is there any way I can get this thing to shrink back down to a managable size? I wouldn't mind if it was as much as 50-60MB, once I've got all 200 worksheets. But that it's above 150MB already, and I'm barely half-way there, has me quite concerned. Can anyone offer ideas/suggestions? Dowload and run a VBA Codecleaner such as that on the appspro site. Each time you edit a VBA file it gets bigger as VBA cant seem to recover used space. This utility will recover that space and lets you strip comments and blank lines from code (useful for shipping externallu but keep a copy) http://www.appspro.com/utilities/Cleaner.asp Keith |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
The expanding XLS spreadsheet
I do a lot of work for clients where I can't deploy ANY files to a PC except
the XLS or perhaps an XLA. Remember it is possible to encapsulate the majority of your code base into an XLS which can then be called by other workbooks. This is only really relevent if you're going to have multiple copies of the same structure but execute exactly the same code. It just saves having this big lump of code attached to each workbook and also greatly reduces updating the code as you do it in one place and all the clients refer to the one 'core' file. Yep from what you say I'd concentrate on the worksheets. Should be simple enough to do a test. I'd just take any worksheet with it's code recreate it by hand in another workbook, copy this 10 times or however many worksheets you have in the source, save it and compare the file sizes. This way you get a quick idea if it's worthwhile looking at sheet recreation. -- Regards, Bill Lunney www.billlunney.com "Scott Lyon" <scott.lyonNOSPAM_at__NOSPAMrapistan.comNOSPAM wrote in message ... Excel 97 format? Really? I've been noticing exactly the opposite... I found that saving in Excel 2002/XP format was a fraction of the size of saving it in Excel 97 format. I'm trying it now again, to see if it's any different this time. As for recreating the worksheets, yes, you're correct, with that number, it would take an IMMENSE amount of time to do all of that, and I could still wind up with the same (or similar) problem. Unfortunately, one of the requirements for this application is that it would be standalone (an Excel spreadsheet - no install required other than copying the XLS to the user's hard drive), so using a COM component isn't an option. Besides, if as you say the problem is more the worksheets than the code, I'm not sure it would save much. Thanks! -Scott "Bill Lunney" wrote in message ... Typically code is not what adds significantly to file size. It's the worksheets themselves. Recreation of the worksheets is the best and only way to get a reasonable file size reduction (at least have the potential of). However with that many worksheets this may not be practical. I'm no longer surprised by the gains that can be achieved by doing this purely because over time even a clean-ish looking sheet can contain lots of invisible formatting. Try ensuring your file is saved in Excel 97 only format. Saving in other formats can double the size due to compatibility constraints. Although reading the rest of the post suggests you've already experimented with this. If your confident that the worksheets are reasonably cleanly designed (and there's no stray random formatting etc. everywhere) then it's clear you have to target the code use. Perhaps consider using a COM component if your architecture allows for it. This way you can centralise a lot of your code base and save repetition. However public functions would, to a large degree, do the same thing in your case. -- Regards, Bill Lunney www.billlunney.com "Scott Lyon" <scott.lyonNOSPAM_at__NOSPAMrapistan.comNOSPAM wrote in message ... I downloaded that "cleaner" program, and tried it. Unfortunately, it seems that it only "cleans" the VBA code that's actually in modules. The problem is that the vast majority of my code is actually in the code behind individual sheets. In fact, running that "cleaner" program only saved me a few megs off of a 150MB file. Which isn't enough to even make a difference, unfortunately. Is there a way (manual or automatic, although with over 200 worksheets I'd prefer automatic) that I can "clean" up the garbage associated with all of the worksheets' VBA code? Thanks! -Scott "Keith Willshaw" wrote in message ... "Scott Lyon" <scott.lyonNOSPAM_at__NOSPAMrapistan.comNOSPAM wrote in message ... I've been working on an Application (in Excel 2002) for awhile. Lately, it's been growing out of control. To give you an idea, when I save the thing (in Excel 2002/XP format, we won't even TALK about what happens if I save it in Excel 97 format), it's pushing 150MB. At this point, the application consists of the following: - About 110 worksheets - each with two TabStrip controls (with anywhere from 5 to 30 tabs), and approximately 4-5 buttons, as well as two small images - Each worksheet has about 400 lines of VBA code (although about 150 lines of that is temporary code, commented out, and will be gone when this goes to production). - Two main (shared/common) VBA modules that have about 4000 and 1200 lines (respectively) of VBA code And that's about it... But somehow it's ballooned up to 150MB, and is starting to give my computer fits as I work on it. To add insult to injury, realistically, there will probably (when all is said and done) be over 200 worksheets (each with probably around 200-300 lines of code each) Now, it's way too late to rewrite this in a better way (and for that matter, although the code is similar on each of the worksheets, it's unique enough that I cannot just call a shared function for most of it). Is there any way I can get this thing to shrink back down to a managable size? I wouldn't mind if it was as much as 50-60MB, once I've got all 200 worksheets. But that it's above 150MB already, and I'm barely half-way there, has me quite concerned. Can anyone offer ideas/suggestions? Dowload and run a VBA Codecleaner such as that on the appspro site. Each time you edit a VBA file it gets bigger as VBA cant seem to recover used space. This utility will recover that space and lets you strip comments and blank lines from code (useful for shipping externallu but keep a copy) http://www.appspro.com/utilities/Cleaner.asp Keith |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Expanding spreadsheet with formulas | Excel Discussion (Misc queries) | |||
expanding numbers? | Excel Worksheet Functions | |||
Expanding capacity of a spreadsheet / workbook | New Users to Excel | |||
Expanding Selection | Excel Discussion (Misc queries) | |||
Expanding data | Excel Worksheet Functions |