Handle incorrect values passed to an Access VBA function

Question

I have a VBA function that is basically along these lines:

Public Function JoinDateTime(DateTime As String, Time As String) As Date
    Dim dtDate As Date
    dtDate = CDate(Format(DateTime, "dd/mm/yyyy"))
    dtDate = dtDate & " " & Format(Time, "hh:mm")
    JoinDateTime = dtDate
End Function

It glues a date and a time together into a datetime value. (The real function has some more logic behind it.)

The problem is, I'd like to add handling for annoying values being passed to it. This is mostly for empty/null values - if DateTime is empty, return empty. If it's a text string returning #Error so it doesn't just fail silently seems like a good idea.

Problem is, I'm not sure how to do so. I'd thought about doing an early return, maybe something like shoving this at the start of the function:

If DateTime = Null or DateTime = "" Then 
    JoinDateTime = Null
End If

but it doesn't seem to consider that as a return and still executes the rest of the function.

Is there a way to do this? A better way, ideally?

Answer 1

To prematurely return from a function in VBA, you need to use the Exit Function statement, similar to Exit Sub, Exit For, etc. So, this

If DateTime = Null or DateTime = "" Then 
    JoinDateTime = Null
    Exit Function 'Au revoir
End If

will prevent the rest of the code below from executing.

Answer 2

First off, you need to change your function declaration to use Variant instead of String. This is because in VBA, the String data type cannot hold the value Null. If you want your function to return Null for invalid dates, then you will need to change the return type to Variant as well. I would also take Mitch Wheat's advice and rename your arguments to something that doesn't conflict with built-in functions (such as dateSection and timeSection).

Second, the comparison DateTime = Null will never evaluate to true. Any comparison to a Null value will result in a Null value. Instead, you should use the IsNull() function (see Error 5).

Third, you might consider using the IsDate() function. This function will ensure that the argument is a valid date, which includes checking for nulls. It depends on whether you want poorly formatted dates to fail silently, too.

Putting it all together:

Public Function JoinDateTime(dateSection As Variant, timeSection As Variant) As Variant
    Dim dtDate As Date
    If IsNull(dateSection) Then
        JoinDateTime = Null
    Else
        dtDate = CDate(Format(dateSection, "dd/mm/yyyy"))
        dtDate = dtDate & " " & Format(timeSection, "hh:mm")
        JoinDateTime = dtDate
    End If
End Function

Answer 3

The cleanest and most error-proof way to do this is by raising an error.

Public Function JoinDateTime(DateTime As String, Time As String) As Date
    If DateTime = "" Then 
        Err.Raise _
            Number:=12345, _
            Source:="JoinDateTime", _
            Description:="Invalid input. DateTime cannot be empty string."
    End If
    'NB: could also check for IsNull(DateTime) if DateTime were type Variant.
    ' etc.

This will display an error with the following message: "Run-time error '12345': Invalid input. DateTime cannot be empty string." and execution will stop.

If you don't want execution to stop, you can handle the error in the procedure that calls JoinDateTime. For example:

Sub tester()

    On Error GoTo ErrorHandler ' Telling VBA where to go when an error is raised

    Dim d As String
    Dim j As Date        
    d = InputBox("Date please:")
    j = JoinDateTime(d) ' Raises an error upon invalid input
    MsgBox Prompt:=j

ErrorHandler:
    Select Case Err.Number
    Case 12345 ' "Invalid input" error was raised, therefore do the following...
        ' Whatever you want to happen when this error occurs, e.g.
        ' MsgBox Prompt:=Err.Description
        ' or 
        ' j = 0
        ' Resume Next
    End Select
    Resume ExitProcedure

ExitProcedure:
    'Cleanup code goes here
End Sub