The phone rang. Jason stared at it for a long moment before answering. He held the handset away from his ear, as if it might leak something vile on him.

"DocGen is crashing," the caller complained. "And I've got 1,500 mailings that have to get out before the 6PM post."

Something vile indeed.

When you install a development suite on every user's desktop, eventually one of them decides they're qualified to make use of it. MS Office, of course, is one such tool. Slowly but surely, they start churning out various macros and forms and piling up layers of kludge. There's nothing unusual about this, but after a certain point, this code ceases to be "just crappy VBA macros" and becomes "MISSION CRITICAL SOFTWARE OMG!" That was DocGen. Tens of thousands of lines of VBA that handled every single document that passed through his organization. Nothing could get to a printer without passing through some leg of this code.

Fortunately for Jason, his job was to replace this blob with real document generation applications. Unfortunately, he had to support the existing software. No instructions, no documentation, and no sign of logic anywhere in the code.

He got a rough idea of where the user's problem lived in the code, and started trying to trace through it. The first comment in that block announced: 'this will not work on sundays. Jason checked the calendar, just to be sure, but no- Friday. Good, in a few hours he could have a stiff drink to erase what he was about to see from his memory.

A few lines below that, it tried to load a template called lnternationalRefTemplate. He wasted twenty minutes looking through InternationalRefTemplate before he realized that he really should be looking at lnternationRefTemplate. None of which should be confused with InternatinoalRefTemplate, which was an intentional typo used because someone had once locked IntertanionalRefTemplate for an entire weekend and a previous developer needed to make some changes.

Hopefully, that previous developer was locked away in a padded room for the safety of everyone else on the planet, Jason mused. "This counts as toxic waste, right?" Jason muttered. "Shouldn't I get a superpower out of this?"

Jason hacked away at the code. While he could reproduce the error, it was difficult to get the application to give him a meaningful error message. Of course, that's only to be expected when your error handler looks like this:

'sets all g variables to 0, makes all hidden word wndows visible and exist. put this in all error modules
    gintGenericListFormOutputs = 0
    gintGenericListFormOutputs1 = 0
    gstrGenericListFormLabel1 = ""
'...etc. etc.
    gstrDayOfTheYear As String

Err.Clear
MsgBox "The macro encountered the following error:" & vbCr & vbCr & vbTab & _
 Err.Number & ": " & Err.Description & vbCr & vbCr & "Please contact a macro administrator!"

He kept tracing through. For a bit, he was convinced that the problem lurked in this procedure, which was apparently written before parameters were invented:

Sub ConvertShortFormatDateToFullDate()
'Takes an IPOL or user-entered date and transfers it from whatever format it's entered in to a full text date:
  'Requires gstrDateToCheck to be filled in from passing procedure - the reformatted date is passed back _
'    in the same variable: _
'  gstrDateToCheck = the date that needs re-formatting:
'returns _
'  gstrDateToCheck = The Date : and _
'  gboDateCleanUpErrorOccurred  =  True/False notation of whether there was an error
'Call A_Generic_Macro_Code.ConvertShortFormatDateToFullDate

After all, a function that used global variables and took 183 lines of VBA code to convert one date format into another had to have at least a few problems. Well, it did contain a few potential infinite loops, but that wasn't the current problem. Probably good, because he didn't want to figure out what this If statement was for:

If strTempString = "0" Or strTempString = "1" 
Or strTempString = "2" Or strTempString = "3" Or strTempString = "4" 
Or strTempString = "5" Or strTempString = "6" Or strTempString = "7" 
Or strTempString = "8" Or strTempString = "9" Or UCase(strTempString) = "A" 
Or UCase(strTempString) = "B" Or UCase(strTempString) = "C" 
Or UCase(strTempString) = "D" Or UCase(strTempString) = "E" 
Or UCase(strTempString) = "F" Or UCase(strTempString) = "G" 
Or UCase(strTempString) = "H" Or UCase(strTempString) = "I" 
Or UCase(strTempString) = "J" Or UCase(strTempString) = "K" 
Or UCase(strTempString) = "L" Or UCase(strTempString) = "M" 
Or UCase(strTempString) = "N" Or UCase(strTempString) = "O" 
Or UCase(strTempString) = "P" Or UCase(strTempString) = "Q" 
Or UCase(strTempString) = "R" Or UCase(strTempString) = "S" 
Or UCase(strTempString) = "T" Or UCase(strTempString) = "U" 
Or UCase(strTempString) = "V" Or UCase(strTempString) = "W" 
Or UCase(strTempString) = "X" Or UCase(strTempString) = "Y" 
Or UCase(strTempString) = "Z" Then

Obviously, testing all those letters in case they needed to handle dates in Zebruary. It was at this point that Jason decided Skynet wasn't a rogue military AI, but a mail merge macro trying to recover from a badly formatted postal code.

'think this will cause problems if the user enters more than six numbers into textboexs

And so it went. Jason wrestled with all variety of strange beasts in the code, like the 700 line procedure that wrote data to two files for no particular reason. Or the 7 pages of code that generated a two page letter, most of which was pre-written in a template. Or this lovely error message:

"Under normal circumstances you should report this problem to a macro administrator ... but you are a macro administrator!
You may wish to investigate the problem yourself or WRITE DOWN THE ERROR NUMBER AND DESCRIPTION and report it to a macro administrator for further assistance".

Then the moment of insight happened. Perhaps the madness was setting in, or perhaps he was starting to understand the code, or even worse- both. Giddy, he snatched up the phone and called the user back. "There are dots!" he cried into the phone.

"Huh?"

"Dots!"

A moment of confused silence ended with, "Oh! Yes. I've been deleting them."

"Stop it! Do not delete the dots. Do not look at the dots! Do not touch the dots!"

"But they're ugly! They're right in the middle of the page."

"DO NOT TOUCH THEM," Jason repeated.

You see, the dots weren't dots. The original author wanted a place to store some variables, and couldn't think of a better place than the body of the document, "hidden" in a 1pt font. And then, in the four places those variables were used, a 22-line version of "Selection.Find" was used to retrieve them.

Jason checked the clock. Still Friday, and 5-O'clock- on the dot.

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