Case Study Using a Hidden Code Workbook to Hold All Macros and Forms

Access developers routinely use a second database to hold macros and forms.They place all forms and programs in one database and all data in a separate database.These database files are linked through the Link Tables function in Access.

For large projects in Excel, I recommend the same method.You use a little bit of VBA code in the Data workbook to open the Code workbook.

The advantage to this method is that when it is time to enhance the application, you can mail a new code file without affecting the client's data file.

I once encountered a single-file application rolled out by another developer that the client had sent the file out to 50 sales reps.The reps replicated the application for each of their 10 largest customers.Within a week,there were 500 copies of this file floating around the country.When they discovered a critical flaw in the program, patching 500 files was a nightmare.

We designed a replacement application that used two workbooks.The data workbook ended up with about 20 lines of code. This code was responsible for opening the code workbook and passing control to the code workbook. As the files were being closed,the data workbook would close the code workbook.

There were many advantages to this method. First,the customer data files were kept to a very small size. Each sales rep now has one workbook with program code and 10 or more data files for each customer.As enhancements are completed, we distribute new program code workbooks.The sales rep opens his or her existing customer data workbook,which automatically grabs the new code workbook.

Because the previous developer had been stuck with the job of trying to patch 500 workbooks,we were extremely careful to have as few lines of code in the customer workbook as possible.There are maybe 10 lines of code,and they were tested extremely thoroughly before being sent out. By contrast, the code workbook contains 3,000+ lines of code. So, if something goes wrong, I have a 99% chance that the bad code will be in the easy-to-replace code workbook.

0 0

Post a comment