Option Explicit

Function sbOptimalVacationDays(lNumberOfVacationDays As Long, _
    dtStartDate As Date, _
    dtEndDate As Date, _
    vBankHolidays As Variant) As Variant
'This function informs you for all days from
'1..lNumberOfVacationDays which exact days would result in the
'longest (as well as second longest) series of subsequent free
'days with given vBankHolidays and assuming that weekends
'(Saturdays and Sundays) are also free.
'Source (EN): http://www.sulprobil.de/sboptimalvacationdays_en/
'Source (DE): http://www.berndplumhoff.de/sboptimalvacationdays_de/
'(C) (P) Bernd Plumhoff v0.2 07-Jan-2024
Dim dt      As Date
Dim dtMax   As Date
Dim i       As Long
Dim j       As Long
ReDim v(1 To lNumberOfVacationDays, 1 To 7) As Variant
With Application.WorksheetFunction
If lNumberOfVacationDays < 1 Then
    sbOptimalVacationDays = CVErr(xlErrValue)
    Exit Function
End If
If dtStartDate > dtEndDate Then
    sbOptimalVacationDays = CVErr(xlErrNum)
    Exit Function
End If
For i = 1 To lNumberOfVacationDays: v(i, 1) = i: Next i
For dt = dtStartDate To dtEndDate
    For i = 1 To lNumberOfVacationDays
        dtMax = .WorkDay(.WorkDay(dt - 1, 1, _
                vBankHolidays), i, vBankHolidays) - 1
        If dtMax > dtEndDate Then Exit For
        j = dtMax - dt + 1
        If j >= v(i, 2) Then
            v(i, 7) = v(i, 4)
            v(i, 6) = v(i, 3)
            v(i, 5) = v(i, 2)
            v(i, 2) = j
            v(i, 3) = dt
            v(i, 4) = dtMax
        End If
    Next i
Next dt
sbOptimalVacationDays = v
End With
End Function