VB(A) : looping to find previous sheet

Question

Hi

In Excel, i have sheets named after the working day. So, I don't have weekends nor special days (holidays and so). I've created a function : previous(x), with x the number of days worked. I want my function to give me the date (mm-dd format) of n-x day, n is the current sheet's day edit: sorry day1 and month1 because day and month are used by VBA

Function Previous(x As Integer) As String
currentSheet= ActiveSheet.Name
day1 = Right(currentSheet, 2)   'sheets' name convention is mm-dd
month1 = Left(currentSheet, 2)
nbLoop = 0
dayFound= 0
While dayFound < x
    day1= Format(day1 + 1, "dd")   'Call to 2 other functions that correctly decrement'
    day1= DecrementJ(day1)         'day and month'
    month1 = DecrementM(day1, month1)
    fPrec = month1 & "-" & day1
    On Error Resume Next
    Set wsSheet= Sheets(fPrec)
    If wsSheet Is Nothing Then
    Else                          'dont ask me why If Not wsS Is Nothing doesnt work'
        dayFound= dayFound+ 1
    End If
    nbLoop = nbLoop +1
    if nbLoop > 30 then goto Err:  'avoid infinite loop'
Wend
Previous= fPrec
Err:
End Function

This function correctly work once (it find the day before the weekend) but fail to jump over two holes (like a weekend + a holiday)

Thanks for your help

Answer 1

Why so much effort? How about:

calcdate = CDate(Year(Date) & "-" & ActiveSheet.Name) - x
calcname = Format(calcdate, "mm-dd")
For Each ws In Worksheets
    If ws.Name = calcname Then
        Previous = calcname
    End If
Next