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