File too big?

[davidam]

Hello All,
I am wondering if anyone can advise me on file size relative to RAM. I think I have reached the max. Still using XP with 4GB of RAM. The program in question is 130,000 KB with numerous charts, 11 worksheets, and many thousands of lines of VBA code. It all worked pretty well until I added one more sheet and some code that surely uses a lot of memory to clean up and parse some large imported files. It works OK, but this last operation now takes about 10 seconds to run. Occasionally, the file will fail to properly display a sheet, missing many elements etc. I am thinking it is time for a new machine with a large amount of RAM. Does anyone have any thoughts or experience on this?
Thank you,
David

 

[Asala42]

Do you suspect the file has any artificial bloat to it? It's not unusual to see 130MB files with performance issues, you may want to try a few things to see if you can bring the file-size down. Here are some suggestions:

- Do a quick Delete of Columns and Rows outside each sheet's used data range. Sometimes bloat hides there, especially with imported data ranges.
- Make sure all pivot tables share the same exact data source, if possible. This way you don't have similar but duplicated pivot caches.
- Simplify formatting. Especially applies to Conditional formatting (check to make sure rules aren't duplicated) and on data kept on hidden sheets.

Hope that helps

 

[davidam]

Hello Asala42,
Well, I have no pivot tables, no conditional formatting, and I have been pretty good about cleaning up unnecessary stuff. I do think you are correct about bloat, however. I imagine it is a pretty common practice to save iterations of a file as you build forward so I opened the most recent version, which is almost identical to the latest, and ran it without any of the issues that I described. The big parsing operation took about 300ms tops, which I can live with. I think it is a case of some kind of unseen bloat and I have no idea how to get rid of it...so I will just build forward with the last version. Thanks for your help.
David