Creating a UDF from a Worksheet Function

Hi Folks.

I have the following worksheet function that I created:

 =IF(U2="Complete", IF(ISNUMBER(SEARCH("Critical",$N2)), IF((NETWORKDAYS(C2,S2,P_Holidays)-1)>1,"No","Yes"),IF(ISNUMBER(SEARCH("High",$N2)), IF((NETWORKDAYS(C2,S2,P_Holidays)-1)>2,"No","Yes"),IF(ISNUMBER(SEARCH("Moderate",$N2)), IF((NETWORKDAYS(C2,S2,P_Holidays)-1)>3,"No","Yes"),IF(ISNUMBER(SEARCH("Low",$N2)), IF((NETWORKDAYS(C2,S2,P_Holidays)-1)>100,"No","Yes"))))),"N/A")

It works just fine - providing the user has the Analysis ToolPak add-in loaded.

I would like to create a UDF in Visual Basic for this function, but I'm having difficulty (most likely because I don't have much experience yet writing UDFs:)

To explain what the function does:  It's purpose is to determine if the due date was met or not  Our request queue has "submit date", "completion date", "status" and "priority" fields to fill out.  The function first determines if the job is complete (if U2 -- Status --  is "Complete" - if not, it gets an N/A).  Then it starts with the "Critical" status - Critical jobs have a one-day turnaround.  So it uses the NetworkDays function to determine the length of time between the submit date and the completion date, then subtracts any company holidays (they are in a named range "P_Holidays").   If the networkdays is greater than one day, then due date was not met and you get a "No."  If it was within the 24 hour time period, then you get a "Yes" for Due date Met.

Then it would continue checking "High" priority (which is a 2 day turnaround), then "Moderate" (3-day turnaround), then "Low"  which actually has an open-ended turnaround time, but I limited it to 100 days.

I could write a macro to do formular1c1 to insert this into cell, but I'm concerned that the end user will not have the analysis tool pack loaded, hence my desire to create a UDF which will work without the need for an add-in.

'I started with this:

Function DueDateMet(SubmitDate As Date, ComplDate As Date, ReqPrior As String, _
ReqStatus As String) As String

Dim PHol As Range
Set PHol = Range("June.xls!P_Holidays")
Dim NetWorkD As Integer
NetWorkD = Application.NETWORKDAYS(SubmitDate, ComplDate, PHol)

'then this:

If ReqStatus = "Complete" And Left(ReqPrior, 8) = "Critical" And (NetWorkD - 1) > 1 Then

DueDateMet = "No"

Else
DueDateMet = "Yes"

End If

'I thought I would try to get just the "critical" status working, then add the rest, but so far, I'm just getting #VALUE!

Anyone who would like to direct some brain cells toward solving this, I would be greatly appreciated!

Thank you,

Gary

Gary

Caveats (This is to get you started. NOT optimised or tested thoroughly. Certainly not Internationalised for dates, should work with US mm/dd/yyyy format). Comments to help. I have to iterate the range of holidays as NETWORKDAYS will not work in the code if not available on the machine as the VBA equivelant of Analysis Toolpak. I have done nothing with weekends, but you could do a test in the array iteration to test for the 'day number'

Option Explicit

Public Function ProcessQueue(ByVal sStatus As String, ByVal dSubmitDate As Date, _
                                 ByVal dCompletionDate As Date, ByVal rHols As Range) As String

'Dimension Variable to hold sub calcs
Dim vDates() As Variant
Dim lActualDays As Long
Dim lHolidayDays As Long
Dim lNettDays As Long
Dim x As Long

'Calculate each time XL does
Application.Volatile True

'Assign the holiday range to an array
vDates() = rHols

'iterate the array to check if holiday dates are between start and end dates
For x = 1 To UBound(vDates)
    If vDates(x, 1) <= dCompletionDate And vDates(x, 1) >= dSubmitDate Then
        lHolidayDays = lHolidayDays + 1
    End If
Next x

'get actual dates between submit and completion
lActualDays = DateDiff("d", dSubmitDate, dCompletionDate)

'Take holidays from total to get nett days to calc on
lNettDays = lActualDays - lHolidayDays

'use select statement to check the status (Convert to upper case to make case insensitive)
'Pass value back to function when found
Select Case UCase(sStatus)
    Case Is = "COMPLETE"
        ProcessQueue = CVErr(xlErrNA)
    Case Is = "CRITICAL"
        If lNettDays <= 1 Then ProcessQueue = "Yes" Else ProcessQueue = "No"
    Case Is = "HIGH"
        If lNettDays <= 2 Then ProcessQueue = "Yes" Else ProcessQueue = "No"
    Case Is = "MODERATE"
        If lNettDays <= 3 Then ProcessQueue = "Yes" Else ProcessQueue = "No"
    Case Is = "LOW"
        If lNettDays <= 100 Then ProcessQueue = "Yes" Else ProcessQueue = "No"
End Select
        
End Function

Public Function DueDateMet(Status As Variant, Priority As Variant, _
                           FromDate As Variant, ToDate As Variant, _
                           Holidays As Variant)
    If Status <> "Complete" Then
        DueDateMet = "N/A"
        Exit Function
    End If
    If (InStr(Priority, "Critical") > 0 And NumDays(FromDate, ToDate, Holidays) - 1 > 1) Or _
        (InStr(Priority, "High") > 0 And NumDays(FromDate, ToDate, Holidays) - 1 > 2) Or _
        (InStr(Priority, "Moderate") > 0 And NumDays(FromDate, ToDate, Holidays) - 1 > 3) Or _
        (InStr(Priority, "Low") > 0 And NumDays(FromDate, ToDate, Holidays) - 1 > 100) Then
       
        DueDateMet = "No"
    Else
   
        DueDateMet = "Yes"
    End If
End Function

Private Function NumDays(StartDate, EndDate, Holidays)
Dim i As Long

    For i = StartDate To EndDate
        If Weekday(i, vbMonday) < 6 Then
            If IsError(Application.Match(i, Holidays, 0)) Then
                NumDays = NumDays + 1
            End If
        End If
    Next i
End Function

Bob, your UDF works perfectly.   Nick, yours is not yet working, but I think it's because the Status ("Complete") and Priority ("Critical", "High", "Moderate" ,"Low" ) are 2 separate fields on the spreadsheet.  I think I can probably play around with it and get it to work. 

It is a learning experience to see both of your approaches.  Nick, I noticed your use of lowercase letters at the beginning of the variable names, to signify the data types.  I am taking a online VB course and they are starting to teach use about proper naming conventions for variables.  And Bob, I liked your use of variants, and splitting it up into 2 separate functions, which I would not have thought of.

Thank you both for helping me out - much appreciated!

Gary

I found a better of calculating the number of days after I posted this, there was no loop.

I will see if I can dig it out and post it.