PDA

View Full Version : Solved: .xls File Size Diet



thomaspatton
03-04-2008, 01:54 PM
I have this code :

Public Sub ClsInfoPrnt()
Select Case Cells(8, 14).Value
Case Is <= 0
swtchUpdateOn
Exit Sub
Case 1 To 20
Rows("38:75").Hidden = True
Range("a1:q76").PrintOut
Rows("18:75").Hidden = False
Case 21 To 40
Rows("38:75").Hidden = True
Range("a1:q76").PrintOut
Rows("38:75").Hidden = False
Rows("18:37").Hidden = True
Rows("56:75").Hidden = True
Range("a1:q76").PrintOut
Rows("18:75").Hidden = False
Case 41 To 58
Rows("38:75").Hidden = True
Range("a1:q76").PrintOut
Rows("18:75").Hidden = False
Rows("18:37").Hidden = True
Rows("56:75").Hidden = True
Range("a1:q76").PrintOut
Rows("18:75").Hidden = False
Rows("18:55").Hidden = True
Range("a1:q76").PrintOut
Rows("18:75").Hidden = False
Case Else
MsgBox "Error with VBA. Find a smart person.", vbCritical, "Error"
End Select
swtchUpdateOn
End Sub



It only runs twice on one worksheet and right now I have it in a module.

My question is this, would I save more .xls filesize leaving it in the module, or pasting the code twice within the actual userform?

Reason I'm asking is because so far my .xls is a tad over .5mb and I'm trying to trim it down a bit to open faster on these "top 'o the line" laptops. I'd like to know what takes up more file space, VBA Code in a Sheet or VBA Code in a Module.

Bob Phillips
03-04-2008, 02:14 PM
That code will make very VERY little difference whichever way you go (althoug I would leave it where it is), as it is just a bit of text, it is not that making your workbook .5Mb (which is small nowadays by the way).

thomaspatton
03-04-2008, 02:22 PM
as it is just a bit of text, it is not that making your workbook .5Mb (which is small nowadays by the way).

Well then how do you propose I fit that ginormous workbook on my 8 inch floppies?!

Thanks XLD. I figured as much, but wasn't sure about the correlation between using individual modules, userforms, and worksheet coding. I'll keep on keepin on.

Bob Phillips
03-04-2008, 02:40 PM
You're lucky mate, my floppies are only 51/2".

Bob Phillips
03-04-2008, 02:43 PM
Thomas,

You could try Rob Bovey's code cleaner on it, that ofetn reduces the size substantially

http://www.appspro.com/Utilities/CodeCleaner.htm