Check two dates using Excel VBA code IFERROR, but still it's yielding a Error 13

Question

My code is reporting:

Run Time Error 13:
Type mismatch.

How can that be if I use an iferror function?

Please note, the value in payroll_Start or payroll_Last should be a date but it could be blank or some idiot user could input a string. Thus, I need to do a check with iferror. However, the check produces the above error. What does this mean? Thank you in advance.

Private Sub CheckStartLast()

     '''''<<<<< Check for error: Employee's last day cannot be before start day.

     payroll_Start = UserForm9.ComboBox21.Value
     payroll_Last = UserForm9.ComboBox22.Value
     payroll_Diff = WorksheetFunction.IfError(payroll_Last - payroll_Start, "-Check-")

     Select Case payroll_Diff
        Case Is = "-Check-"
           Exit Sub
        Case Is >= 0
           Exit Sub
        Case Is < 0
           MsgBox "Employee's start date cannot be after their last day. Doesn't make sense. Does it to you?"
        Case Else
           Exit Sub
     End Select
End Sub

Answer 1

Use the IFERROR function to trap and handle errors in a formula. (from Help press F1 while cursor on code) and payroll_Last - payroll_Start causes an error if at least one is a string as you can't subtract a string from a number. String is wrong type, what causes the type mismatch.

Example:

Sub ForceTypeMismatch()
  Dim v As Variant
  v = "a" - "b" ' Error 13 Type mismatch raised here
End Sub

In VBA you can use On Error Resume Next to resume the next line of code if an error occurs and then check the Err.Object if an error was raised.

This code implements something similar

...
On Error Resume Next ' turn off errors
payroll_Diff = payroll_Last - payroll_Start
if Err.Number > 0 then ' if error set to check
    payroll_Diff = "-Check-"
End If
On Error Goto 0 ' turn on regular errors

Usually you check the values of the TextBoxes on input and complain if they aren't dates! For the sake of simplicity we do this in your sub, but usually you do this in an event of the TextBox.

Option Explicit ' put this code on top of every code module(window), it checks for undeclared variable to avoid typos
Private Sub CheckStartLast()
 '''''<<<<< Check for error: Employee's last day cannot be before start day.

    If IsDate(DateForm.DateStart.Value) And IsDate(DateForm.DateLast.Value) Then ' check only if dates are entered
        Select Case DateDiff("s", DateForm.DateLast.Value, DateForm.DateStart.Value) 'calculates diff in seconds
            Case Is >= 0
                MsgBox ">"
                Exit Sub
            Case Is < 0
                MsgBox "Employee's start date cannot be after their last day. Doesn't make sense. Does it to you?"
            Case Else
                Exit Sub
        End Select
    End If
End Sub

Attach importance to proper naming, where the names show what they are e.g. DateForm instead of Userform9. Adapt your names to my code or improve mine, but never use that auto-generated one like ComboBox21.

Get more help from Rubberduck, an open source add-in with hints on bad coding (inspections) and many other useful stuff.