It was Seth's first day on the job. He had just completed the usual paperwork and orientation stuff, when his new boss Gabe said: "Why don't you take care of Issue #88743?"

Seth loaded up their issue tracker and eagerly navigated to #88743. The ticket read as follows:

NETWORK OPS BUILT A NEW EMAIL SERVER FOR DEVELOPMENT. 
PLEASE UPDATE THE TEST VERSION'S CONFIG FILE TO 
USE 10.1.8.22 AS THE SMTP SERVER.

Clearly, the purpose of the assignment was to get Seth acquainted with the codebase. The application he was hired to maintain was used to manage commercial real estate properties, and was sold to management companies of all sizes. And because it was built in .NET, Seth figured he'd start by hunting for the app.config file.

The Spreadsheet Begins

When Seth opened up the codebase's root folder, a file named config.xls immediately caught his attention. The file had several different Excel worksheets, each named for a different module in the application. The worksheets each contained two columns -- KEY and VALUE -- and each had a handful of rows. The key he needed to change -- "SMTP Server" -- was staring right at him.

"Just type in the new IP address and hit save," Gabe said with a big grin on his face. "Pretty elegant, eh?"

Seth wasn't quite sure how to respond to all this, so he just smiled and figured he'd make the small configuration change. When he saved config.xls, though, a custom dialog box popped up: "WARNING: About to Save Changes to Configuration! Are you sure everything is OK?" Heeding the warning, he clicked "Cancel" and verified that he had, in fact, typed in 10.1.8.22 correctly. Curious as to why that message had appeared, Seth hit ALT-F11 to bring up the VBA Macro Editor.

What came up was 1,000-plus lines of Excel VBA code that seemed to validate and tidy up the variety of different configuration fields. There was even a macro named "Upgrade()" that, apparently, was responsible for loading the values out of an older version of the configuration spreadsheet into the current file. The first line of code in that macro rendered the entire macro moot as it just popped up a dialog box that said "Not Supported Anymore."

On the application side of things, the VB.NET configuration code wasn't any prettier. There were three distinct ways to read values from the configuration file, but only two seemed to work. The "COM Interop" method was great if you wanted to find a value in a specific cell (such as sheet 5, column 2, row 23), while the ADO.NET method was used to find a value by name (such as "SMTP Server").

Problem Update

Seth wasn't alone in thinking there were much better solutions. None of the developers, however, could convince Gabe. Until that one day, when a customer needed to update the mailing address that the application used and followed the instructions provided by tech support: open config.xls, navigate to the "Company" workbook, and edit the appropriate address fields.

She was an experienced Excel user and had no problem updating the data. By force of habit, she made sure to type in the apostrophe (') character before entering their new ZIP code so that Excel would recognize it as text instead of a number.

And therein lay the problem: the field was already defined as text and their data-validation routine never took into consideration the ZIP code apostrophe and allowed the file to save as normal. A perfect storm of bugs allowed the apostrophe'd ZIP code to be used in a SQL statement, which ended up wreaking quite a bit of havoc on their data. And thanks to their "gotta-catch-'em-all" error-handling philosophy, it took nearly 12 hours to figure all this out.

After Word

"This is completely unacceptable," Gabe yelled in the developer meeting. "Can't you guys write a simple Excel macro!? Or at least test for stuff like this?"

"Well," one of the developers defended, "we did, but the customer never updated their macro code. This is probably why no one uses Excel for configuration files."

"Huh?" Gabe frowned, "Excel is a proven technology! Besides, easy-to-edit is a priority, people!"

Another developer sarcastically remarked, "Then why not just use Microsoft Word?"

At that moment, Gabe's expression went from angry to intrigued. "Actually, we could do that! No apostrophes to worry about and it's even easier to use," he said as his mind started to wander toward the technical implementation. "It'd be pretty elegant, eh?"

Waiting to Excel was originally published in Alex's DevDisasters column in the Oct 1, 2008 issue of Redmond Developer News. RDN is a free magazine for influential readers and provides insight into Microsoft's plans, and news on the latest happenings and products in the Windows development marketplace.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!