Reducing data size of module

I build a navagation-menu workbook which acts as a shell to other workbooks. The menu links to other workbooks which do unique things, like; one workbook which contains all the instructions, application information and help. Then another workbook which contains only data for the core operations of the application, which may be devided into additional workbooks depending on the type and extent of the data, such as one workbook for hard data on many sheets and another for inventory and item photos. Then another workbook for forms and reports.

Most of the code for the linking and operation of the linked application is contained in the menu. The overall size of the application is greatly increased, but the individual workbooks are reduced in size. Some operations are faster, but some are slower do to the linking. Also, linking errors make coding difficult.

The next way I speed-up large slow applications is to breakup large sheets into other sheets, based upon some sorting or filter field. If you have a reference table, which contains a full history of an item, but find that for the nuts and bolts of your application, you only need the: Name, last action date, current status and value then extract your Full-List sheet to the Short-List sheet and do your calculations and run your macros on the short list sheet, only referring to the Full-List as needed.

If your data sheet contains both sales and inventory information make two sheets from the data. One for inventory and another for sales info. You get the picture. Most of my applications are done this way. Even the largest work fast. Many of my macros use programming which is known to be slow, such as working on whole columns of data rather than selections, loops rather than filters, etc. Yet I find no difference in execution times, good code and slow code both happen as soon as I het the key.

If I find a code set that runs slow on an application I rebuild the code, you can do the same thing a multitude of ways, some faster some slower. If you check this board you will see that most respondents have different code for the same answer!

The best macro tip turn off anything you don't need such as screenupdating! JSW

I agree 100% with everything Joe just said. The only thing I would add is that you can put related and commonly used routines routines in the same module. e.g. I usually have one module that contains functions that contains only routines that manipulate strings. It takes processor time to instantiate code modules and the fewer times you have to do this, the better.