Optimizing code

hi Randell,

I'm willing to help, but not really to "take it on" especially because you seem willing to learn & you'll definitely learn more by doing rather than by reviewing what we do for you, so here are some suggestions which I hope you'll work through on copies of your files...
- call me lazy if you want ;-)

Read on for the novel!

1) Add "Option Explicit" at the top of all your (normal & form) modules (this forces all your variables to be declared) & set this as the default for new modules in the VBE under Tools - Options - Editor - tick "require variable declaration". You may also find it useful to tick a few of the other options here.

2) Rename all of your modules to identify their purpose. You can do this by selecting the module in the Project Explorer window & Pressing [F4] to bring up the Properties window. Note you can't/shouldn't give a module the same name as a macro already has.

3) Search using [ctrl + F] to identify & then remove as many ".select" sections as you can (where an object is selected before having something done to it), you may need to be careful where it is a worksheet.select, but where it is something like:

Please Login or Register  to view this content.

4) What causes the need for all the separate workbooks?
It will be easier to maintain with fewer workbooks.

5) Review all the code for blocks of code that are duplicated (I do this by printing the code sheets out & scribbling all over them), & move the duplicated blocks into a single macro which is called from where the blocks were originally. This shortens the amount of code that needs to be reviewed & makes it easier to maintain the code in the future as changes only need to be made in one place. An example of where you could do this is in the code for DayOfTheWeek form where a lot of the code is the same for each option button:

Please Login or Register  to view this content.

8) To decrease the written code when adding formulae to sheets you can write the formula to the whole range in one go without copying & pasting for example:

Please Login or Register  to view this content.

9) Are the "Delete_?_ColumnA" macros always run one after the other?
If so, merge them into a single macro, & include all checks within a single loop. This may speed it up a lot. Or, alternatively... don't use a loop at all as discussed in each of the below:
http://www.excelforum.com/excel-prog...ts-in-vba.html
see DeleteRowsWithSpecifiedData on http://www.ozgrid.com/VBA/VBACode.htm
http://www.databaseforum.info/16/8/6...0964c7cb0.html
General comments: http://www.excelforum.com/excel-prog...ting-rows.html

Note, that if you want to use any of the Specialcells approaches you should be aware of its limitiations - see Ron's page, http://www.rondebruin.nl/specialcells.htm

10) Use

Please Login or Register  to view this content.

because Integers have a size limit of approximately 32,000 & there are more rows than that in spreadsheets*, therefore you could end up with errors. Also, I have read (somewhere?) that all Integer variables are internally converted to Longs so there is no point in using Integers anymore.
*I recognise that in your particular files you may never reach this size, but it is good practice to declare rowcounter variables as Long & you never know when you'll use this code in bigger projects.

11) Once you're happy with all your changes & testing, try to compile the project which may identify more errors by pressing [alt + D + L] in the VBE.

Have fun & let us know if you have any particular questions :-)
I'll check the thread out over the next few days & may be able to find a printer/scanner to upload my scribbled comments on printouts of the code sheets.

Goodluck
Rob