Cleaner Code Part 1

By John — in clean code, efficiency, messy code · 2 min read
Cleaner Code Part 1

When revisiting old functions, you'll often find some truly brute force methods used, usually due to time restrictions and trying to push together a solution as quick as you can.

Something I recently came across was a quick UDF ( User defined function )  to return an integer month from a text string.  This was used to select the correct month/year from a filename with an embedded date, partially as text ( 'DD-MMM-YY' format) 

Previously , the code looked like this :


Function DateMonTextNum_OLD(InputSt As String) As Integer

InputSt = Left(Trim(UCase(InputSt)), 3)


Select Case Left(UCase(InputSt), 3)
Case "JAN": DateMonTextNum = 1
Case "FEB": DateMonTextNum = 2
Case "MAR": DateMonTextNum = 3
Case "APR": DateMonTextNum = 4
Case "MAY": DateMonTextNum = 5
Case "JUN": DateMonTextNum = 6
Case "JUL": DateMonTextNum = 7
Case "AUG": DateMonTextNum = 8
Case "SEP": DateMonTextNum = 9
Case "OCT": DateMonTextNum = 10
Case "NOV": DateMonTextNum = 11
Case "DEC": DateMonTextNum = 12
Case Else: DateMonTextNum = -1 ' ERROR FINDING DATE VALUE
End Select


End Function
Vb

In this case, it's a simple range of integers, which could be turned into a loop instead of a case statement.

The second version is much more elegant , and doesn't require having to type comparison clauses.

Wherever there's an existing function/method to save time and code like this, use it. :)


Function DateMonTextNum(InputSt As String) As Integer

InputSt = Left(Trim(UCase(InputSt)), 3)
DateMonTextNum = -1

For AA = 1 To 12
If InputSt = Left(UCase(Format(DateValue("01/" & AA & "/2001"), "MMMM")), 3) Then DateMonTextNum = AA
Next AA

End Function
Vb

 

 

 

Tagged as clean code, efficiency and messy code

John —

Started out in media/graphic design then moved to Financial Services / MI data / Business intelligence. ~15 years experience of Excel / VBA , and more recently Qliksense , SQL

  • [CONTACT]
  • [LOCATION]
    London, UK