If you've ever been asked the question "whaddya mean it's VBA-based?", you're likely familiar with the moment of sadistic glee that follows. After all, before you stands a naïve programmer who believes that VBA (i.e. Word/Excel macros) is a simple tool for increasing the usability of forms and similar documents. Not only do you get to crush his untainted worldview by divulging that entire information systems are built on VBA, but you get to tell him that he will be responsible for maintaining such a system.

A few years back, John was that naïve programmer. When his company merged two locations, his group inherited an in-house "application" developed by the programmer at the other location. Note that the word application is in quotes; I'm not quite sure if one can define thirty-four interconnected spreadsheets and six separate databases as an application.

Another depressing tidbit that John learned is that this ... thing ... requires frequent 24x7 support. His group would get called in the middle of the night when batch jobs (yes, you can do those in VBA) would fail, requiring them to often delve into the horrific code.

To keep things interesting, the original programmer was as bad of a coder as he was a speller. One late-nite call resulted from the Calculate_Intergrated() function working slightly different from the Calculate_Integrated() one. Following is a compendium of some of the favorite misspellings from the application ... 

'Clears data that may exsist from provious quarys
' Check and make sure file exsist
' Loads invoice from invoiceComponint into memeory
' To more closely match XXXX Estaments this multliplier is used
' If csv file had allready been processed then deleat file
Public Function PeroidEnding_1(

Surprisingly the original coder's database design skills were strong. Many of the tables amongst the six databases looked like this ... 

CREATE TABLE dbo.TRACKING (
  TRACKING_ID nvarchar (255) NOT NULL ,
  CustomerID nvarchar (255) NOT NULL ,
  Date smalldatetime NULL ,
  Hour int NULL ,
  Field5 nvarchar (255) NULL ,
  TimeZoneOffset int NULL ,
  Field7 nvarchar (255) NULL ,
  Field8 nvarchar (255) NULL ,
  Field9 nvarchar (255) NULL ,
  Field10 nvarchar (255) NULL ,
  Field11 nvarchar (255) NULL ,
  Field12 nvarchar (255) NULL ,
  Field13 nvarchar (255) NULL ,
  Field14 nvarchar (255) NULL ,
  Field15 nvarchar (255) NULL ,
  Field16 nvarchar (255) NULL ,
  Field17 nvarchar (255) NULL ,
  Field18 nvarchar (255) NULL ,
  Field19 nvarchar (255) NULL ,
  Field20 nvarchar (255) NULL ,
  ItemName_Type nvarchar (255) NOT NULL ,
  Field22 nvarchar (255) NULL ,
  Field23 nvarchar (50) NULL ,
  Field24 nvarchar (255) NULL ,
  Field25 int NULL ,
  Field26 nvarchar (255) NULL ,
  Field27 nvarchar (255) NULL ,
  Field28 nvarchar (255) NULL ,
  Field29 nvarchar (255) NULL ,
  Field30 nvarchar (255) NULL ,
  Field31 nvarchar (255) NULL ,
  Field32 nvarchar (255) NULL ,
  Field33 nvarchar (255) NULL ,
  Field34 nvarchar (255) NULL ,
  Field35 nvarchar (255) NULL ,
  Field36 nvarchar (255) NULL ,
  Field37 nvarchar (255) NULL ,
  Field38 nvarchar (255) NULL ,
  Field39 nvarchar (255) NULL ,
  Field40 nvarchar (255) NULL ,
  Field41 nvarchar (255) NULL ,
  Field42 nvarchar (255) NULL ,
  Field43 nvarchar (255) NULL ,
  PriceModel_Election nvarchar (255) NULL 
)

Wait, you didn't believe me when I said "good", right? In case you haven't heard enough, John had a few other fun facts to share ...

  • Spreadsheets reprotected themselves whenever any code ran making it very difficult to work on them
  • Development always had to be on the "production" system since spreadsheet paths were hardcoded
  • Common code used the "copy/paste" method of reuse
  • Hidden sheets were rampant as were references to thousands of hidden columns and rows
  • Flat shape objects were used instead of buttons so you could never be sure if clicking a cell would activate some code or not
  • Shapes were stacked on top of each other and then hidden and shown at run time

But all's well that ends well. The original programmer had been promoted as a manager and the John's team has finally gotten the go-ahead to estimate costs of rewriting the system in .NET.

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