Don’t make it harder than it is.

A couple of weeks back I was working on a ETL project for EF Education First, which allowed me to delve into the world of Excel VBA. Over the course of the project, I’ve been looking for ways to translate the program logic I had in my mind (which I can implement in other languages) to VBA, a language I’m still trying to get familiar with. Because of my random walk, I came across this post on Stack Overflow where I thought of this dilemma–just how elegant should a code be for it to be useful?

To guide the user on the thought process, here’s an attempt to break it down in simple terms:

Case: How do you use Excel VBA on a specific data set to remove columns with headers containing specific text?

Alternative # 1: Dynamically (iteratively) locate the columns and delete them

Sub deleteCol()

On Error Resume Next

Dim wbCurrent As Workbook
Dim wsCurrent As Worksheet
Dim nLastCol, i As Integer

Set wbCurrent = ActiveWorkbook
Set wsCurrent = wbCurrent.ActiveSheet
‘This next variable will get the column number of the very last column that has data in it, so we can use it in a loop later
nLastCol = wsCurrent.Cells.Find(“*”, LookIn:=xlValues, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

‘This loop will go through each column header and delete the column if the header contains “Percent Margin of Error”
For i = nLastCol To 1 Step -1
If InStr(1, wsCurrent.Cells(1, i).Value, “Percent Margin of Error”, vbTextCompare) > 0 Then
wsCurrent.Columns(i).Delete Shift:=xlShiftToLeft
End If
Next i

End Sub

Alternative # 2: Hard code column deletion

Range(“C:C,F:F,I:I,L:L,O:O,R:R”).Delete

Alternative # 3: Manually select the columns in Excel. Right-click > Delete.

My analysis: I believe like most managers, developers have the bias towards action. They figure out WHAT needs to be done, and HOW they’re going to do it, but they don’t really think about WHY they’re doing it. And because of failing to think about the latter, the entire endeavor results to an attempt to deliver a one-size-fits-all solution, unsustainable code that breaks after the second or third change release, and increased costs due to rework.

That said, my proposal would be to take a step back and look at the reasons behind the implementation and ask the following set of questions:

  1. Why are we automating this–is this a one-time thing or something we would use periodically?
  2. If it’s periodic, how often do we have to do this?
  3. What is the time/money cost of doing the manual process versus the time/money cost of developing the automated solution?
  4. Does the time/money cost saved from the manual process justify the time/money cost to develop the solution and how long can we benefit?
  5. Are the inputs constant or are they bound to change?

The question posted on Stack Overflow doesn’t really give us much of the answers to these questions, but by starting with this sort of framework we can come up with better recommendations on which alternative to take:

  • Q1 : One-time thing
    –> Alternative #3
  • Q1: Periodic | Q2: Yearly | Q3: Cost of Manual (CoM) < Cost of Development (CoD)
    –> Alternative #3
  • Q1: Periodic | Q2: Quarterly | Q3: CoM < CoD
    –> Alternative #3
  • Q1: Periodic | Q2: Monthly | Q3: CoM > CoD | Q4: Within 1 year
    –> Alternative #3
  • Q1: Periodic | Q2: Weekly/Daily | Q3: CoM > CoD | Q4: 2-3 years | Q5: Constant
    –> Alternative # 2
  • Q1: Periodic | Q2: Weekly/Daily | Q3: CoM > CoD | Q4: 2-3 years | Q5: Changing
    –> Alternative # 1

Bottom line: life will be a lot simpler, if we have a framework or a simple heuristic to organize our thinking when it comes to making these decisions. We shouldn’t want to invest in things that are not fit for their purpose, or things where we’d end up spending more than what we can actually save.

All of these alternatives would yield the same results, but they come at different costs. You’d find that by doing this quick acid test, more often than not, Alternative #3 (the manual process) will give you the most value.

So beat that action bias, take a step back, and think… it won’t cost you much.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s