Solved

# Do Until Loop VBA Question

Posted on 2013-10-23

Hi Experts,

In the code below, the Public Function Work_Days works perfectly and calculates the number of days between two dates excluding weekends and holidays.

I am trying to write a function which essentially does this:

I give the function an end date and tell the function to subtract 10 work days to give me a start date. My attempt is the code below (Public Function Work_Days_Subtract()). It is getting stuck on 'Loop' with a compile error of 'Loop without Do'. This is my first attempt at writing this type of loop code by myself, but I thought the 'Do Until' would couple with the loop...any pointers would be appreciated.

Thanks!

Option Compare Database

Option Explicit

Public Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer

' "Calculating the workdays between Dates"

' Note that this function accounts for holidays.

Dim WholeWeeks As Variant

Dim DateCnt As Variant

Dim EndDays As Integer

BegDate = DateValue(BegDate)

EndDate = DateValue(EndDate)

DateCnt = BegDate

EndDays = 0

Do While DateCnt <= EndDate

If Not IsNull(DLookup("Holiday", "t_Holiday_Dates", "[Holiday]=#" & DateCnt & "#")) Then

EndDays = EndDays - 1

End If

If Format(DateCnt, "ddd") <> "Sun" And _

Format(DateCnt, "ddd") <> "Sat" Then

EndDays = EndDays + 1

End If

DateCnt = DateAdd("d", 1, DateCnt)

Loop

Work_Days = EndDays

End Function

Public Function Work_Days_Subtract() As Date

Dim INumDaysToSubtract As Integer

Dim INumCalendarDays As Integer

Dim StartDate As Date

Dim EndDate As Date

INumDaysToSubtract = 10

INumCalendarDays = -20

EndDate = DLookup("MaxOfDataDate", "q_DataDate_MAX")

StartDate = DateAdd("d", INumCalendarDays, EndDate)

Do Until Work_Days_Subtract = INumDaysToSubtract

If Work_Days(StartDate, EndDate) = INumDaysToSubtract Then

Work_Days_Subtract = StartDate

Exit Do

Else: INumCalendarDays = INumCalendarDays - 1

StartDate = DateAdd("d", INumCalendarDays, EndDate)

Loop

End If

End Function