MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Max Number of Subs in a module?

Posted by Paul Magruder on May 04, 2001 11:45 AM

Is there a "suggested" max for number of subs in a Module? Modules in a workbook? How about the max number of UserForms in a Workbook?.

Thanks in Advance

Posted by Dave Hawley on May 04, 2001 1:52 PM

Hi Paul

This would only be determined by the available memory of the PC. I would imagine you would be looking at an awful lot of code before you encountered any problems. To give you an idea I have Workbooks that house hundreds or Procedures with no problems. If your questions is because of a blow out in file size then below may be of interest to you.

1. Save the workbook as one version only. Don't save as multiple versions unless needed.

2. Export ALL modules and Userforms to your Hard drive.

3. Open a new Workbook. Window back to the your Workbook and right click on a sheet name tab and select "Move or Copy" then Copy the sheet to the new Workbook. Then save your new Workbook.

4. Do the same for all Worsheets, but each time you copy a sheet to the new Workbook and save, go to File>Properties and make sure there is not an unusual increase in file size. If there is, then you have probaly got a corrupt Worksheet. If so delete the sheet and go back to the Workbook it came from. Select it and push Ctrl+A and copy it's content to a new sheet. Then try again.

5. After you have all Sheets moved open the VBE and Import all your Modules and UserForms.

6. Consider replacing any array formulas with either Pivot Tables or Database functions. Array formulas are notorious for slllloooowwwwing down Excels saving and recalculation.

Here are some intresting links. Some may apply ?

By following these steps I have succeeded in reducing a Workbook by 75%I hope this helps. Good Luck

OzGrid Business Applications

Posted by Paul Magruder on May 08, 2001 5:49 PM

Once again..... Thank You Very much.... Just what I was looking for...