Tuesday, March 3, 2009

A lesson in error trapping

~ Spring Thing ~Image by ViaMoi via Flickr

One would think that this is something that doesn't require much. If you don't handle errors in your code it will turn into a nightmare. Sure I do know this but today I discovered that there's still the odd lesson out there.

The situation... quite simple really. Have a call to a function that gets a value from an Excel spreadsheet using the ADO interface. If things go wrong the function returns the error as reported by ADO. So everything is just peachy. Even more so when you notice that there is a check on an error state to the function. All handled - or so it would seem.

The "where the heck is Kansas" question arose out of the fact that the call on a specific spreadsheet was always had a null value. Pointing to a different spreadsheet and a value is suddenly there again. Very surreal situation. File name changes, different values being queried, copying the values into a new file as well as changing the search key values refused to change the situation. Believing that the file itself was whacked didn't help when a copy of the content to a new workbook also returned the same lovely blank entry.

Eventually went back to the function call and added code to display the function return value. The light in the tunnel. There is an error on the query and it quite cryptically claims that there is too much data. Back in the spreadsheet and one Ctrl-End later we find that there is a white space character in column IV (the limit in Excel 2003). Few hundred deleted columns later the script is working as it should.

With a little "else" added to the function code and suddenly tracking this error would not have been such a surreal journey. The error is now being nicely reported in the execution report and this likely solves some of the frequent mysterious errors (like "Object required in appDict(...)") that pop up.

If's really can not divorce the catch all else.

Pity that VBScript doesn't have cool tools like a unit test module as well as some form of mock objects (for dealing with QTP GUI interaction results).