I recently had to re-install an Excel macro on a new computer, that I had written a few years ago for a customer. The macro reads information from a database and updates cells in a spreadsheet and is therefore dependant on some external libraries most notably Microsoft DAO. However during the installation process I was sent on a wild goose chase, all because of some misleading error messages.
This macro, which had been running perfectly for the last 10 years on the old machine, now failed when trying to make database calls into Microsoft’s DAO 3.5 library, despite the fact that my investigation revealed the correct library files were installed and referenced by the macro.
The first DAO function to fail was OpenDatabase() which resulted in an “Out of memory” message. With nothing more to go on than this descriptive message, I went down the path of trying to figure out why the database engine was running out of memory. This led me astray through all sorts of Google search results — everything from verifying the amount of free ram available to checking buffer sizes in places I’d never heard of. After many frustrating hours, I finally discovered on some obscure website that in fact there was no memory problem. Instead, the website said that a new update pack was required since this new computer had a new service pack installed. Problem solved. Time to celebrate.
The celebration was short lived when I then ran into a second problem. A call to OpenRecordset() was now failing with a message telling me that the database was read-only. Again with nothing more than the “informative” error message, I began the long, painful journey into the world of Google searches.
Since the query being passed to OpenRecordset() was only selecting data and not modifying it, I thought that maybe the update now required that I specify a read-only query. No luck. However, hours later I just happened to stumble across an article on MSDN which said to double check the registry entry for the type of database I was trying to open (which was a text file); more specifically to ensure that the file extension I was trying to open was registered for the database type. One quick look into the registry and sure enough my custom file extension was missing. Problem solved. But I never had to do either of these steps when using this library 10 years ago.
So there are some lessons to be learned from Microsoft’s blunder. The most obvious is that Microsoft should have provided proper error messages. In the latter case for example, it should have been possible to pass a message to the user saying “Unknown file extension, check registry entry….” instead of leading the user astray with a “Database is read-only” message, regardless of how many layers and components were involved.
The lesson here: ensure errors are being handled properly in all cases and are propagated to the highest level (in this case the error message box presented to the user). If this is not possible, at the very least provide documentation explaining the possible causes and make that documentation easily accessible. I eventually found the solution, but Microsoft could have easily provided a help matrix of causes and solutions. And just as importantly they could have provided this in the VBA help file.
Another lesson is that if there are multiple components and updates required which could lead to the infamous “DLL hell”, ensure that these are well documented. In the first case cited above, I would never have gotten the macro working if I hadn’t stumbled across that obscure website. Again a help matrix of some sort listing versions, components etc, could have saved hours of time.
In closing, I hope this article and the two examples provided will inspire you to think about the user experience of your API in a new way.