'Microsoft Office has been the bane of my life, while simultaneously keeping me employed'

Monthly report scripting special

Line Break Welcome back to Line Break, our weekly roundup of terrible code you've seen in the wild. Over the past six weeks, we've featured all sorts of broken or ugly source – from insecure web apps to write-once-read-never-again scientific programs.

Now we turn to ... the monthly report. The sort of thing you automate ASAP with a handy script. Unfortunately, even these little programming ditties can leave you sobbing with your head in your hands.

High on your own supply

Reg reader Andrew hit us up with this workplace tale of an automatically generated regular report with mysterious spurious numbers:

I once found that an end-of-month report for our supply department had a value at the end of the document that had no meaning from a programming perspective. It used a table column that was long obsolete, and even though the data no longer meant anything, the program still reported the value.

I removed the report entry, and explained to the supply manager the reasons for doing so. His response was, “I have always reported this figure to senior management, I must continue to do so.”

All that was needed was a simple change to the code:

Seed = today;
ReportValue = random(Seed)*1000;

Happy manager. Senior management none the wiser.

Inspiring, Andrew. I am actually slow clapping at my desk. That's not the code your colleague needs, it's the code he deserves.

You've Excel'd yourself, Microsoft

Here's an amusing confession of sorts from Mark about his battle with running the same script on different versions of Microsoft Excel for OS X.

Today has been a lesson in why Microsoft Office has been the bane of my life, while simultaneously keeping me employed.

A few months ago I built a weekly reporting package that generates 36 Excel reports. It's a relatively simple piece of VBA code that opens a template file, copies in the relevant data from a master file, and then saves it with the correct name. This has worked without fail for the past three months, and still works on my machine, using Office 2011 for Mac.

Yes, you read that right, it runs on a Mac, which is partly the issue I have below.

Due to the length of time it takes to complete, I've started running the report code on multiple machines at once: a second machine runs the reports in order Z-A while mine runs them A-Z. Today the only spare machine to hand is running Office 2016, rather than Office 2011. And cue the inevitable broken code.

Thankfully, the first bit of code to break is relatively straightforward. In Office 2011, file paths are coded with a ":" as a folder separator. In Office 2016, they've reverted to standard Unix format, so a quick change to /Users/[username]/Documents/[Folder name]/ and the code's seeing the folders again.

The next code break: now it's no longer switching between the open spreadsheets. Again a relatively simple fix, replacing Windows.([Workbook]).Activate with Application.Workbooks.([Workbook]).Activate and again this bit of the code is now working again.

Except... every time the code is run, Excel crashes with no explanation. If I step through the script, it works fine and so I can't find where it's crashing.

Next change: I add a simple test mode to the code with a variable that counts up to indicate how far we've got. It's very simple:

varTest = varTest + 1
If strTest = "yes" then
msgbox("Test no " & varTest)
Else
End If

My thinking is this should at least allow me to narrow down which bit of code is breaking. Unfortunately, it runs through an entire iteration of code with no crashes. So I change the declaration strTest = "Yes" to strTest = "No" and run it again. Almost immediately it crashes. Somehow just breaking up the code fixes it. In a flash of inspiration, I change the test to the following and run the code outside of test mode, and now it works.

varTest = varTest + 1
If strTest = "yes" then
msgbox("Test no " & varTest)
Else
Application.Wait(Now + TimeValue("0:00:01"))
End If

The test is in place everywhere it tries to switch between open workbooks, so in several places, as a result of this extra time delay, each iteration now takes an extra 30 seconds or so, but it works, and the extra 30 seconds is acceptable to myself if it means I don't have to spend the next half a day (remember, this is now a Friday afternoon with beer o'clock lurking in the near future) removing the test code one piece at a time until I find the exact spot (or as I suspect spots) where the code crashes. With the exception of the time delay, the code is identical.

Congratulations Microsoft, a new version of Office and yet again new VBA code to make it work.

When the pints are calling, sometimes you gotta do what you gotta do to workaround heisenbugs like the one above. But you know this technical debt has to be paid off at some point. I can imagine a coworker coming for someone's kneecaps after seeing a brave fudge like that.

Rewriting your future

Finally, for now, Damon writes in with this exchange regarding – wait, one sec, I think I was just a little sick in my mouth – this exchange regarding self-modifying shell scripts:

What about my boss asking me, for some mission-critical batch code:

“How far ahead do sh and csh read their script files so I can have them edit themselves safely while they are running?”

The answer was 32 and 256 bytes as I recall, but the real answer was: “Stop doing that!”

Amen to that. Please do keep your anecdotes and terrible code coming in via email or Twitter so we can feature them in our weekly group therapy sessions for working and recovering programmers. ®

Click here to see all Line Break columns