#type Error on VBA function when input left blank

Question

I am using the following code in Access 2010. I use it in an unbound text box to return weekdays between two schedule dates (start/Finish) on various scheduled tasks on a form. Code is working properly when dates are entered, however on this particular form not every task will have start / end dates. I would like the code to just return "" or 0 if inputs are blank.

I should note I did not write this code myself, I am very very new to VBA and found this code online and manipulated it slightly to work for my application. How can I modify this to fit my needs?

Public Function Weekdays(   ByRef startDate As Date, _
                            ByRef endDate As Date _
                        ) As Integer
' Returns the number of weekdays in the period from startDate
' to endDate inclusive. Returns -1 if an error occurs.
' If your weekend days do not include Saturday and Sunday and
' do not total two per week in number, this function will
' require modification.

On Error GoTo Weekdays_Error

    ' The number of weekend days per week.
    Const ncNumberOfWeekendDays As Integer = 2

    ' The number of days inclusive.
    Dim varDays As Variant

    ' The number of weekend days.
    Dim varWeekendDays As Variant

    ' Temporary storage for datetime.
    Dim dtmX As Date


    ' Calculate the number of days inclusive (+ 1 is to add back startDate).
    varDays = DateDiff(Interval:="d", _
                        date1:=startDate, _
                        date2:=endDate) + 1

    ' Calculate the number of weekend days.
    varWeekendDays = (DateDiff(Interval:="ww", _
                                date1:=startDate, _
                                date2:=endDate) _
                                * ncNumberOfWeekendDays) _
                                + IIf(DatePart(Interval:="w", _
                                Date:=startDate) = vbSunday, 1, 0) _
                                + IIf(DatePart(Interval:="w", _
                                Date:=endDate) = vbSaturday, 1, 0)

    ' Calculate the number of weekdays.
    Weekdays = (varDays - varWeekendDays)

Weekdays_Exit:
    Exit Function

Weekdays_Error:
    Weekdays = -1
    Resume Weekdays_Exit
End Function

Change ByRef to ByVal, if that does not help, also change Date to Variant. It will return -1 then. — GSerg
An appropriate place to deal with the one missing value is before this function is called. If you will post the code that CALLS this function -- presumably found in an event of the text box -- that will let us say more about that approach. — Smandoli
Alternatively, you can take advantage of the error-handling already built into this function. I have reasons for preferring the other approach. — Smandoli
PaulFrancis solution worked. Thanks! Returns 0 days which is fine by me! — CBraun

PaulFrancis PaulFrancis · Accepted Answer · 2015-02-04T16:32:40

Your code will have to accept a Null value, since Date is a Data type that will not tolerate Null, you have two methods, change the declaration of the function from.

Public Function Weekdays(   ByRef startDate As Date, _
                            ByRef endDate As Date _
                        ) As Integer

To,

Public Function Weekdays(startDate, endDate) As Integer

This way the code can have Null values, so a few more additions could be made as,

Public Function Weekdays(startDate, endDate) As Integer
' Returns the number of weekdays in the period from startDate
' to endDate inclusive. Returns -1 if an error occurs.
' If your weekend days do not include Saturday and Sunday and
' do not total two per week in number, this function will
' require modification.

On Error GoTo Weekdays_Error
    If IsNull(startDate) Or IsNull(endDate) Then
        Weekdays = 0
        Exit Function
    End If

    Const ncNumberOfWeekendDays As Integer = 2

    'so on....

Or the other way is to make sure you pass dates by employing Nz() or even prevent the function to be called if you have Null values.

#type Error on VBA function when input left blank

1 Answers