1.5. Designing Applications

If you are writing code simply to make your own projects easier, thinking of them as applications might not be especially relevant. However, if you are building Microsoft Office applications that will be used by others, it is important to think about how the applications might change and how those changes can be dealt with. If you build an application that creates a set of reports and emails them to users, you could hardcode all kinds of information into the code. If you do that, though, any time the reports or recipients change, you will need to change the code.

I try to put elements that might change into tables that can be easily changed. To manage outgoing email, you could have a table that lists the reports and email addresses of the recipients. The code would open that table and send the reports based on the information in the table. This would allow the end user to make the changes necessary to email new or existing reports to new recipients.

While this might not seem very important, if you do not consider factors like this, you will spend more time modifying and maintaining applications than developing them. The same thoughts apply to connection strings to data sources, report formats, and other items where information can change over time. I once helped change an application that was written to produce a report of general ledger accounts with transactions over $1,000. Over time, the company grew and wanted to look at accounts over $50,000. As it turned out, the $1,000 parameter was hardcoded in the application code. Instead of just changing that code, I added a table that held parameters for the general ledger accounts to be queried, the dollar amount to review, and the tables holding the information (each type of transaction had a different table). Once I did this, changes to these criteria could be made without programming.

Some developers seem to build applications that always require developer assistance to make changes. This isn't a strategy I recommend, as it is dangerous for the end user. There are countless requests on the project boards online to modify applications that say that they cannot locate the original developer, or the original developer does not have time to work on it, or other similar reasons. It is also a good idea to document what each procedure does so that if you look at something you made two years ago, you can still follow what you were thinking.

Designing a graphical user interface (GUI) is not covered until much later in the book. As you try to solve a business problem, consider what the information flow will be and the best way to get that data from the user. In addition, it is also useful to consider the best way to display information when that is the purpose of the GUI. For example, is it better to have a large input screen with scrollbars, or is it better to use a tabbed dialog (like many Windows applications)? The other question that comes up when you integrate Access and Excel is which application is best suited for each task. Sometimes the answer is very clear, and at other times there is no clear-cut best product. As you work, the GUI should be in the back of your mind.