Reduce Workbook Size

poleary2000

Active Member
Joined
Apr 1, 2002
Messages
354
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.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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.
 
Upvote 0
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
 
Upvote 0
How do you load modules dynamically? I might not do it, but I would still like to know how. Please share.

Thanks again!
Patrick
 
Upvote 0
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
 
Upvote 0
Wow - interesting. Tom, thanks for taking the time to post the code. I'll try to use it in the future.

Thanks!
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top