So I’ve been trying to write an Excel macro to select only the last trading day of the week from a list of Dates.  Typically, this is a Friday, but when Friday falls on a holiday, the last day of the week can be a Thursday.

I made a list of all non-Friday end-of-the-weeks going back to 2006, and wrote a simple little function to loop through this list, and return true if the current date matches any one of them.

The problem is, there is no Break keyword in VBA. And unlike functions in other languages that return a value, when the minute you return a value, the function exits, VBA just keeps on going on its merry way. So the minute I found my correct date, set the return value to True, and wanted to return to my main code, I had to explicitly exit the function. Here is the full function. The needed extra line is “Exit Function”:

Function IsEndOfWeek(CurrDate As Date)
Dim Row As Integer
Dim PreHolidayDate As Date
Row = 2
While Cells(Row, "N") <> ""
PreHolidayDate = Cells(Row, "N")
If (CurrDate = PreHolidayDate) Then
IsEndOfWeek = True
Exit Function
End If
Row = Row + 1
Wend
IsEndOfWeek = False
End Function

2 Responses to “Give me a Break! VBA Nonsense”

1. Minton McKarkquey says:

   May 5, 2009 at 4:18 pm

   True, but what’s the problem? This is actually quite useful if you want to explicitly set a value at the beginning of the function, guaranteeing a return value before exiting.
2. James Beswick says:

   May 5, 2009 at 5:30 pm

   VBA is the best programming language ev-ar. You can build all sorts of dangerous things like FIX engines, and force trading platforms to work in a single-threaded environment. Of course, major investment banks would never do this.

   I’m waiting for the VAMP stack to take over.