Handle invalid values ​​passed to VBA access functions

Question

I have a VBA function that basically goes 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 binds the date and time together into a datetime value. (The real function has some more logic.)

The problem is that I would like to add processing for the annoying values ​​passed to it. This is mainly for null / null values ​​- if DateTime is empty, return empty. If this is a text string that returns #Error, so it doesn't just fail, it seems like a good idea.

The problem is that I'm not sure how to do this. I was thinking about making an early return, perhaps something like this at the beginning of the function:

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

but he does not seem to consider this a return and still does the rest of the function.

Is there any way to do this? Is it better, 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 is

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

prevent execution of the rest of the code.

Answer 2

The cleanest and safest way to do this is to make a mistake.

 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. 

You will receive an error message with the following message: "Runtime error" 12345 ": invalid input. DateTime cannot be an empty string." and execution will stop.

If you do not want the 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, eg ' MsgBox Prompt:=Err.Description ' or ' j = 0 ' Resume Next End Select Resume ExitProcedure ExitProcedure: 'Cleanup code goes here End Sub 

Answer 3

First, you need to modify the function declaration to use Variant instead of String . This is because in VBA, the String data type cannot contain a Null value. If you want your function to return Null for invalid dates, you will also need to change the return type to Variant . I would also take Mitch Wheat's advice and rename your arguments to something that does not contradict the built-in functions (e.g. dateSection and timeSection ).

Secondly, comparing DateTime = Null will never be true. Any comparison with a Null value will result in a Null value. Instead, you should use the IsNull () function (see Error 5).

Third, you can use the IsDate () function. This function ensures that the argument is a valid date, which includes checking for zeros. It depends on whether you want poorly formatted dates to fail, 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 4

 If Nz(DateTime, "") = "" Then JoinDateTime = Null End If 

Two points: it is better to return to the "special" date (for example, the beginning of an era), and not return zero; also ' DateTime ' is not a great name for a variable.