UDF Recalculating Prematurely

Guys

With the assistance of SHG, Parsnip, Dave & Daddylonglegs I have manage to put together a little app that assist our staff with scheduling dates in a calendar to avoid a specific trend.

Code provided by SHG has worked perfectly; see below:

Public DaysOff As Range


Function datNext(datLast As Date, DaysOff As Range) As Date
    Dim datBeg As Date, datEnd As Date
    Dim iLastPd As Integer
    Dim iDay As Long, nDay As Long
    Dim iYr As Integer, iMo As Integer
     
    iYr = Year(datLast)
    iMo = Month(datLast)
    datBeg = DateSerial(iYr, iMo + 1, 1)
    datEnd = DateSerial(iYr, iMo + 2, 0)
    iLastPd = Pd(datLast)
    nDay = DaysOff.Rows.Count
    Do
        Do
            datNext = Int((datEnd - datBeg + 1) * Rnd + datBeg)
         Loop Until Pd(datNext) <> iLastPd
         
        For iDay = 1 To nDay
            If DaysOff(iDay) = datNext Then Exit For
        Next
    Loop Until iDay > nDay
     
End Function
 
Function Pd(dat As Date) As Integer
    Select Case Day(dat)
    Case 1 To 10:   Pd = 1
    Case 11 To 20:  Pd = 2
    Case Else:      Pd = 3
    End Select
End Function

Though the built in DAY function on the calendar is affecting the dates that are calculated cos the function PD also references this. Whenever I change the year or month on the calendar; it changes the UDF, which is not supposed to be. If the recommended dates are going change all the time; as a result of this, it makes this app useless.

How do I get around this? Also is this what is causing the UDF to recalculate?

Re: Built In Function Affecting Udf

Dave

Thanks for the response. I have tried tracking the UDF by placing breakpoints but I can't seem to pinpoint the fault. The UDF references a specific cell as a date and the DaysOff named range. When I check these; the values are correct and this is what does not make sense to me.

The calendar that is part of this workbook and the control which control this calendar is no way (at least not what I am aware of) linked to the UDF. Thus I can't understand why if I change the calendar inputs it changes the UDF as well.

Regards

Ramiz

Re: Built In Function Affecting Udf

Quote from shg

The workbook is protected, Ramiz -- what's the password?

shg

Thanks you for looking at this. Apologies for that, I was under the impression that I unprotected the workbook. the password is the same for all the worksheets as well as the the function. password: funcDat

Regards

Re: Built In Function Affecting Udf

Quote from Dave Hawley

DaysOff is a Dynamic Named Range and as such Volatile.

Dave

That makes sense cos only when I made the DaysOff range dynamic did I have have these complications. In view of this, how do avoid the use of the dynamic range but still not affect the accuracy of the result of the UDF.

Basically, I understand that the UDF result is dependent on the correct DaysOff range. Will there be major inaccuracies if the range is static?

Regards

Ramiz

Re: UDF Recalculating Prematurely

Hi

SHG, thats a great suggestion. I will try that. thanks. Also, is it possible to dynamically change the range using VBA or would you not advise this.

Dave, when you refer to a standard named range with extra cells, does this mean that I just select the range where the names will be populated and define a name or is there a specific formula for this.

Regards

Ramiz

Re: UDF Recalculating Prematurely

Quote from shg

yes, of course, but I don't know what range you are referring to, or why you would want to change it.

If you're referring to a dynamic range that contains holidays in the coming month, I think that's overkill; just have a list of company holidays off into the middle distance (companies know them years in advance), and tuck it away someplace in a corner of the spreadsheet. The range can be dynmaically defined to cover all entries.

shg

Apologies for the lack of clarity. I was referring to the DaysOff range in your original UDF. As the number of names will change; I wanted to know if the result of the UDF will be greatly affected by the range not being dynamic.

Otherwise as Dave recommended in his earlier post; will a standard named range with extra space suffice for this UDF.

If the DaysOff range needs to be dynamic to get the best result, can I then use VBA to dynamically assign any changes to this range.

Regards

Ramiz

Re: UDF Recalculating Prematurely

Quote from shg

The UDF doesn't care if the range is dynamic.

This part of the UDF:

Code

For iDay = 1 To nDay 
            If DaysOff(iDay) = datNext Then Exit For 
        Next 
    Loop Until iDay > nDay

Could be made more efficient. Instead of traversing DaysOff, a Find could be used to see if the candidate day is a holiday. Roughly,

Code

set r = daysoff.find(datnext)
loop while not r  is nothing

shg

Thank you for the revision of that section of the UDF. If my understanding is correct; this section of the udf will assist the udf to identify "holidays" or non-work days. In view of this, it will select a date that does not correspond with this particular non-work day.

Dave
Your description of dynamic ranges and the offset function has clarified many issues I was having with previous formulas. Thank you for sharing; it has been a great help.

Regards

Ramiz

Re: UDF Recalculating Prematurely

Quote from shg

Yes.

shg
Thank you for that. As the DaysOff range is dependent on a list of non-work days; that I have specified; how do I make sure that the year of that date is correct.

If the year of this date is fixed; e.g. 01/09/2007, and the startdate is 01/02/2008, the function wil not identify the DaysOff range as the date will not comply.

Regards