At some point in your career as a programmer, you will be tricked into maintaining a VBA (Visual Basic for Applications, aka Word/Excel macros) application. I'd tell you how to get out of it, but I really don't think it's possible. All I know is that at some point between admitting to have knowledge of Excel and cursing under your breath while typing VBA, you will be coaxed into giving some "quick help" on a spreadsheet.

I suppose one positive in this unfortunate reality is that we all seem to know that VBA maintenance rates slightly higher than dental work on the fun scale. In fact, the only time a VBA "application" elicited a "WTF" reaction from me was when the code was well structured and maintainable. No, seriously, there actually was VBA code like that.

No less, today's example from Brian Peterson isn't too far from the VBA norm. However, I consider it a public service announcement to prepare the VBA virgins among us for what they will eventually work on. Oh, and don't think of trying to go be an accountant or something; once you've been tagged a programmer, there's no escaping the VBA.

At my office, we have a reporting tool that was written almost entirely in VBA for Excel. Due to employee turnover, I am now the one responsible for this reporting tool. It was designed to run automatically each day, but I soon discovered that it broke every Saturday morning, which meant the weekend reports never went out as they should.

When I started to dig into the actual code to fix this problem, I discovered the most devilishly complex, insanely backward, and horribly convoluted code I have ever had the displeasure of trying to understand. At this point, I've wiped out most of the WTFable code, but I decided to replace the following code today...

This procedure's sole purpose is to determine the string to use when setting the tail of each of the reports' filenames to "yyyymmdd.xls", which is supposed to reflect the prior day's date: 

Public Sub set_xlsFilename()
  ZeroMonth = ""
  ZeroDay = ""
  Range("T1").Select
  ActiveCell.FormulaR1C1 = "=MONTH(TODAY())"
  Range("U1").Select
  ActiveCell.FormulaR1C1 = "=DAY(TODAY())-1"
  Range("V1").Select
  ActiveCell.FormulaR1C1 = "=YEAR(TODAY())"
  xlsMonth = Range("T1")
  xlsDay = Range("U1")
  xlsYear = Range("V1")

  Dim RepMONTH As String
  Dim RepYEAR As String
  Dim RepMODAY As String

  If xlsMonth = 1 Then RepMONTH = "Jan"
  If xlsMonth = 2 Then RepMONTH = "Feb"
  If xlsMonth = 3 Then RepMONTH = "Mar"
  If xlsMonth = 4 Then RepMONTH = "Apr"
  If xlsMonth = 5 Then RepMONTH = "May"
  If xlsMonth = 6 Then RepMONTH = "Jun"
  If xlsMonth = 7 Then RepMONTH = "Jul"
  If xlsMonth = 8 Then RepMONTH = "Aug"
  If xlsMonth = 9 Then RepMONTH = "Sep"
  If xlsMonth = 10 Then RepMONTH = "Oct"
  If xlsMonth = 11 Then RepMONTH = "Nov"
  If xlsMonth = 12 Then RepMONTH = "Dec"

  If xlsDay = 0 And xlsMonth = 1 Then xlsDay = 31: xlsMonth = 12: xlsYear = xlsYear - 1: RepMONTH = "Dec"
  If xlsDay = 0 And xlsMonth = 2 Then xlsDay = 31: xlsMonth = 1: RepMONTH = "Jan"
  If xlsDay = 0 And xlsMonth = 3 Then xlsDay = 28: xlsMonth = 2: RepMONTH = "Feb"
  If xlsDay = 0 And xlsMonth = 4 Then xlsDay = 31: xlsMonth = 3: RepMONTH = "Mar"
  If xlsDay = 0 And xlsMonth = 5 Then xlsDay = 30: xlsMonth = 4: RepMONTH = "Apr"
  If xlsDay = 0 And xlsMonth = 6 Then xlsDay = 31: xlsMonth = 5: RepMONTH = "May"
  If xlsDay = 0 And xlsMonth = 7 Then xlsDay = 30: xlsMonth = 6: RepMONTH = "Jun"
  If xlsDay = 0 And xlsMonth = 8 Then xlsDay = 31: xlsMonth = 7: RepMONTH = "Jul"
  If xlsDay = 0 And xlsMonth = 9 Then xlsDay = 31: xlsMonth = 8: RepMONTH = "Aug"
  If xlsDay = 0 And xlsMonth = 10 Then xlsDay = 30: xlsMonth = 9: RepMONTH = "Sep"
  If xlsDay = 0 And xlsMonth = 11 Then xlsDay = 31: xlsMonth = 10: RepMONTH = "Oct"
  If xlsDay = 0 And xlsMonth = 12 Then xlsDay = 30: xlsMonth = 11: RepMONTH = "Nov"

  If xlsDay <= 9 Then ZeroDay = 0
  If xlsMonth <= 9 Then ZeroMonth = 0

  xlsFilename = xlsYear & ZeroMonth & xlsMonth & ZeroDay & xlsDay & ".xls"

  RepTimeStamp = xlsMonth & "/" & xlsDay & "/" & xlsYear & " 23:59"
  RepTIMETAG = xlsYear & ZeroMonth & xlsMonth & ZeroDay & xlsDay
  RepDATE = "=TODAY() - 1"
  RepMODAY = ZeroMonth & xlsMonth & ZeroDay & xlsDay

  Range("T4") = RepTimeStamp
  Range("T5") = RepDATE
  Range("T6") = RepTIMETAG
  Range("T7") = RepMONTH
  Range("T8") = 5
  Range("T9") = RepMODAY
End Sub

The original procedure starts by writing Excel formulae to cells in the spreadsheet and then reading the resulting values of those cells to set variables. It then does all sorts of If ... Then things to handle the (not so) fringe case of being run on the first of the month or the first of the year. It then pads the filename for single-digit days and single-digit months. Next, it sets the xlsFilename variable. And finally, it outputs a number of unused variables to cells in the spreadsheet that are never read again.

Much of this made me say, "WTF?!", but my favorite is the 'Range("T8") = 5' statement. The rest of the cells could arguably be used for debugging, but what the heck is '5' going to tell anyone? Anyway... today, I replaced that entire procedure with this one: 

Public Sub set_xlsFilename()
  xlsFilename = Format(Date - 1, "yyyymmdd") & ".xls"
End Sub

In the original coder's defense, he was not very experienced writing VBA, but then again neither am I.

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