Reduce Workbook Size

[poleary2000]

What is the best way to reduce workbook size? The workbook has 16 modules and approximately 20 ActiveX drop-down-boxes.

Also, is it possible to rename the modules. I hate calling them Module 1, etc. Plus, if you get over 10, they are out of order.

[poleary2000]

Figured out how to rename the modules. Duh. I am an idiot. LOL.

[pilot]

Please tell us other idiots.

[poleary2000]

In the VBA editor, open properties [either F4 or from the view menu. Click on the module in the project explorer. In the properties window, you can change the name of the module [just like a text box or combo box]. Just be careful not to name it with the same name as a sub in the module.
Otherwise, you will receive an error message.

[Tom Schreiner]

Hi
A module with about 500 lines of code adds about 20 - 25 kb.
Not really too significant...
If you really are concerned about size, you can load the vba modules dynamically as you need them...
Just learned how to do it yesterday.
Not too sticky
Also cell formatting instructions...
From what I understand, formatting a whole column of 65536 rows, by selecting the column and not the rows, uses one instruction.
Formatting an individual cell uses one instruction as well.
Tom

[ This Message was edited by: TsTom on 2002-04-23 20:35 ]

[poleary2000]

How do you load modules dynamically? I might not do it, but I would still like to know how. Please share.

Thanks again!
Patrick

[Tom Schreiner]

Give me a couple of minutes to drum up a decent example Patrick...

[poleary2000]

Okay - thanks again Tom.

[Tom Schreiner]

Create a folder:
"C:/DynaLoad"

Open a new workbook, add a module,
rename the module "DynaMod"

Place the following procedure into
the module:

Sub LoadDynaTest()
Sheet1.Range("A1") = "Tis' Loaded!"
End Sub

Export the module into folder
"C:/DynaLoad"

Now remove the module from your
workbook all together.

Insert another module into this
workbook(name does not matter)

Place this sub in the module:

Sub CallDynaLoadedProcedure()
LoadDynaTest
End Sub

In whatever way you want to call it, call it:
The example here loads the module when the workbook opens.

Private Sub Workbook_Open()
Application.VBE.ActiveVBProject.VBComponents.Import "C:/DynaLoad/DynaMod.bas"
CallDynaLoadedProcedure
End Sub

You cannot call the procedure directly
until the bas file is loaded or you will get a compile error. That is why
we have the middleman procedure.
Save the workbook. Close it.
Open it. Check sheet1.Range("A1")
Your module is loaded into the project if the range has the data...

Tom

[ This Message was edited by: TsTom on 2002-04-23 21:13 ]

[poleary2000]

Wow - interesting. Tom, thanks for taking the time to post the code. I'll try to use it in the future.

Thanks!