HELP! User Defined Function returning #value

[helpimanewb]

Trying to write my first user defined function, but not having much success. Have a light programming background (mostly Python).

The function takes a date and returns the most appropriate meeting date from a list of all the first Fridays of the month during the year. I'm not sure exactly where I went wrong. Code for the two functions used is listed below.

Thank you for your help in advance!

Public Function TeamDecisionDate(dateExpiration As Date) As Date
    'Function takes expiration date of contract and returns best date for team to decide on action.
    
    'Define local variables
    Dim dateAnalytics As Date
    Dim dateIP As Date
    'Dim dateSP As Date
    'Dim arraystringTeamSchedule() As String
    Dim arraydatesTeamSchedule(11) As Date
    Dim dateTeamDecision As Date
    Dim y As Variant
        
        'Analytics to begin 161 days before
        dateAnalytics = dateExpiration - 161
        dateIP = dateAnalytics + 5
        'dateSP = dateExpiration - 5
    
    'Assigns date of every first Friday of the month for the year 2014 to arraydatesTeamSchedule -- utilizes dhNthWeekday function provided by MSDN
    For x = 1 To 12
        arraydatesTeamSchedule(x - 1) = dhNthWeekday(DateSerial(2014, x, 1), 1, vbFriday)
    Next x
    
    'Assigns initial value of dateTeamDecision to first Friday of the year (first value in team meeting date array).
    dateTeamDecision = arraydatesTeamSchedule(0)
    
    'Evaluates each team meeting date against current value of dateTeamDecision to find the value nearest to exactly 60 days from dateIP
    For Each y In arraydatesTeamSchedule
        If Abs(dateIP - y - 60) < Abs(dateIP - dateTeamDecision - 60) Then
            dateTeamDecision = y
        End If
    Next y
    
    'Assigns best team decision date to function variable for return
    TeamDecisionDate = dateTeamDecision
    
    
End Function

Public Function dhNthWeekday(dtmDate As Date, intN As Integer, _
 intDOW As Integer) As Date
    ' Find the date of the specified day within the month. For
    ' example, retrieve the 3rd Tuesday's date.
    Dim dtmTemp As Date
    If (intDOW < vbSunday Or intDOW > vbSaturday) _
    Or (intN < 1) Then
        ' Invalid parameter values. Just
        ' return the passed-in date.
        dhNthWeekday = dtmDate
        Exit Function
    End If
    ' Get the first of the month.
    dtmTemp = DateSerial(Year(dtmDate), Month(dtmDate), 1)
    ' Get to the first intDOW in the month.
    Do While Weekday(dtmTemp) <> intDOW
        dtmTemp = dtmTemp + 1
    Loop
    ' Now you've found the first intDOW in the month.
    ' Just add 7 for each intN after that.
    dhNthWeekday = dtmTemp + ((intN - 1) * 7)
End Function

 

[Norie]

Can you post an example of the UDF in use?

 

[helpimanewb]

Not sure what exactly you mean by "in use" -- I am calling it with something like =TeamDecisionDate(12/1/2014)

 

[Norie]

That's what I meant, and I think I can see a problem straightaway.

12/1/2014 is not really a date, it's 12 divided by 1 divided by 2014.

Not sure if that's the cause of the #VALUE! error but if you want to pass the date 12/1/2014 to the function try this.

=TeamDecisionDate(DATEVALUE("12/1/2014"))

When I do that I get a result of 03 January 2014, not sure if that's right.

 

[helpimanewb]

I'm getting 5/2/2014, which would be a perfect date, but it's 60 days before, not after, the analysis date. Looks like I need to reevaluate how the absolute value is being used.

Thanks for your helpful input.

 

[Norie]

I get that as well when I switch day and month.

 

[helpimanewb]

Solved!

When calling function using =TeamDecisionDate(DATEVALUE("12/1/2014")) I now get 9/5/2014, which is 69 days after the dateIP (6/28/2014).

Changed the code for the main function to screen out team meeting dates before the analysis date and changed formula from -60 to +60.

Public Function TeamDecisionDate(dateExpiration As Date) As Date
    'Function takes expiration date of contract and returns best date for team to decide on action.
    
    'Define local variables
    Dim dateAnalytics As Date
    Dim dateIP As Date
    'Dim dateSP As Date
    'Dim arraystringTeamSchedule() As String
    Dim arraydatesTeamSchedule(11) As Date
    Dim dateTeamDecision As Date
    Dim y As Variant
        
        'Analytics to begin 161 days before
        dateAnalytics = dateExpiration - 161
        dateIP = dateAnalytics + 5
        'dateSP = dateExpiration - 5
    
    'Assigns date of every first Friday of the month for the year 2014 to arraydatesTeamSchedule -- utilizes dhNthWeekday function provided by MSDN
    For x = 1 To 12
        arraydatesTeamSchedule(x - 1) = dhNthWeekday(DateSerial(2014, x, 1), 1, vbFriday)
    Next x
    
    'Assigns initial value of dateTeamDecision to first Friday of the year (first value in team meeting date array).
    dateTeamDecision = arraydatesTeamSchedule(0)
    
    'Evaluates each team meeting date against current value of dateTeamDecision to find the value nearest to exactly 60 days from dateIP
    For Each y In arraydatesTeamSchedule
        If (dateIP - y) < 0 Then
            If Abs(dateIP - y + 60) < Abs(dateIP - dateTeamDecision + 60) Then
                dateTeamDecision = y
            End If
        End If
    Next y
    
    'Assigns best team decision date to function variable for return
    TeamDecisionDate = dateTeamDecision
    
    
End Function

Now just need to implement this for multiple team meeting schedules, which should be achievable using a second string argument and some if statements.

Thanks so much for your help! This forum is awesome.