Profile: Craig Hatmaker
Practically no one uses Excel to update databases directly. I think the reason is because we think of Excel as a tool that end users can do whatever they want to with and updating databases requires discipline. Just using Excel to shotgun records into a database can be disastrous because putting bad data into a database is often far worse than putting no data in at all. That’s not to say Excel can’t be an excellent tool for updating databases, it just means we have to approach the subject with the same discipline as we would with ‘normal’ or ‘traditional’ programming methods.
At the heart of that discipline is good data validation, also known as ‘edit checks’.
Today’s post won’t share any code. I want to provide an overview of the process before we start coding. Here is a simple data flow diagram (DFD) to help the discussion.
As always, I use lots of little functions instead of one monolithic program. In some cases, my purpose is to build a routine that can be used for other circumstances. In other cases, my purpose is to isolate where changes should occur from places where, in all likelihood, they never should occur. And in all cases, my purpose is to have small bits of code that perform specific functions that can be thoroughly tested before being placed into production. That’s no assurance that something won’t go bump in the night (thus every routine has error handling). Nor does it mean that experience won’t lead us to better methods and thus, changes to underlying code.
The first routine is the worksheet’s specific validation controller. Though it is expected that this routine will require modifications, much of it should not need any changes (and in many cases, I have used it without any modifications). The first function it performs is one such piece that should not need modification. It handles some housekeeping such as making sure all global variables have been initialized (I normally don’t like global variables. I made an exception for speed here) and clearing any previous error indications from the data to be validated.
Below the housekeeping piece is a loop that processes all rows, and all cells in those rows. Within the inner loop is a place where, if you need unique data validation logic, you can put it. Below the section set asside for your modifications is a routine that handles all the normal data validation stuff, such as checking dates, making sure numeric fields contain numbers, making sure text fields aren’t longer than they should be, converting to upper case when necessary, handling Yes or No flags, simple Excel Table lookups, and database lookups.
Database lookups require specific information and so the generic routine really doesn’t handle them. It passes control back to the worksheet where a template routine awaits your modifications.
Note: As you can see, things that need your attention, are isolated in the worksheet code. Things that you should never have to modify are placed in modules.
The last function of the Check_Entry routine is to highlight any cells in error with red, provide a brief description of the problem and/or it’s resolution, and communicate back to the calling routine if any errors were found.
That’s the overview. Next post – we dive into code.