Size matters, or Brother can you spare a Mega-Bite

Posted by Tom Urtis on June 23, 2001 1:42 AM

Sometimes I wonder if VBA stands for Very Big Application.

My concern is inexplicable file size, which I know has been discussed here before but I still don't understand how some files manage to get so huge.

Tonite I finished a VBA-filled template that is 700 KB in size, to automate a here-to-fore manually generated report that was typically 1 MG. Instead of being a 1.7 MG end product, the thing is almost 6 MGs.

Is there something in the VBA water that causes this? Any tips on keeping file size down? I did everything I could think of already (no formulas in unused ranges, limited formatting, etc).

Thanks for any ideas.

Tom Urtis

Posted by Ivan F Moala on June 23, 2001 5:51 AM

Tom
Have a look @ www.baarns.com, I believe they have
a white paper on this somewhere.
Plus if you have a lot of Blank or supposedly blank cells this will take up space.
To reset this range (even though they are blank)
you just run this code;

Sub ResetRg()
ActiveSheet.UsedRange
End Sub

Just to test it run this to see what you last
row is, then run above and check it out.

Sub lastrow()
Dim iLastRow
iLastRow = Cells(1, 1).SpecialCells(xlCellTypeLastCell).Row
End Sub

HTH

Ivan

Posted by Tom Urtis on June 23, 2001 9:05 AM

Re: Size matters, etc., Baarns observations, comments welcome

Thanks Ivan, I downloaded the VBA code cleaner add-in from Baarns.

I'd welcome comments from others who've tried that cleaner; it reduced my file size from 5.34 MG to 5.24 without stripping the modules, and down to 5.13 with the stripping, which I am not in favor of because it eliminates all the comments that are valuable to the end user(s). But 0.2 MG reduction is better than nothing; however maybe I'm still stuck with this apparently common problem.

Thanks again for your code suggestion Ivan, I'll give it a go.

Tom