- 0 0
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
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