We are often pulled in by small businesses (and sometimes large businesses) to help with automating or otherwise optimizing their usage of Microsoft Office applications, for example Word, Excel, and Access. Many times, there are existing Visual Basic for Applications (VBA) scripts and macros that need updating or tweaking, so we found this post on the Daily WTF to be quite humorous.
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 StringIf xlsMonth = 1 Then RepMONTH = “Jan”
If xlsMonth = 2 Then RepMONTH = “Feb”
***Snip – we removed the rest of visual basic script code above, which was about another 30 or so lines. The Daily WTF post has it all.***
Anyway… today, I replaced that entire procedure with this one:
Public Sub set_xlsFilename()
xlsFilename = Format(Date – 1, “yyyymmdd”) & “.xls”
End Sub
VBA can be good for automating tasks in Microsoft Office applications, and it is easy enough to use that it facilitates use by non-programmers, which is good, but the end results of that can often be unpredictable/unmaintainable, which is bad. Also, Visual Basic (VB) in general can be useful for a quick GUI wrapper to commandline applications.
via The Daily WTF