select case stop at the first false condition

Question

the problem as follows:

TOut = 11:00:00 PM
TCheckIn = 10:00:00 PM

in my code, select case, stop at the first condition and the result is -1:00:00 (I figured out with ABS function).

here the code:

Public Function TIMECOMPARE(TIn As Date, TOut As Date, TCheckIn As Date, TCheckOut As Date) As Double
    Dim Hours As Integer
    Select Case TIMECOMPARE
        Case Hour(TOut) < Hour(TCheckIn)
            Hours = Hour(TCheckIn) - Hour(TOut)
            TIMECOMPARE = TimeSerial(Hours, 0, 0)
        Case Hour(TIn) > Hour(TCheckOut)
            Hours = Hour(TIn) - Hour(TCheckOut)
            TIMECOMPARE = TimeSerial(Hours, 0, 0)
        Case Hour(TIn) < Hour(TCheckIn) And Hour(TOut) < Hour(TCheckOut)
            Hours = Hour(TOut) - Hour(TCheckIn)
            TIMECOMPARE = TimeSerial(Hours, 0, 0)
    End Select
End Function

Someone can help me to understand what I am not understanding? the case where should stop is the third....

Answer 1

To understand the behaviour, you have to understand three things: how the select case statement works, how variable initialization works and how implicit conversions in comparisons work.

Select Case

The Select Case <value> statement compares the value provided as value with the result of evaluating the each Case condition in order and executing the first one with a matching value.

Variable Initialization

All variable types in VBA have a default value. For object types, it is Nothing, for Boolean, it is False, for numeric types, it is 0, and and for Date, it is whatever corresponds the backing value 0. (Date is stored as an offset in days to a base date, I think 1900-01-01, encoded as a Double.)

Accordingly, the return value of your function is the date corresponding to the Double 0.

Comparison and Implicit Conversion

When VBA has to compare two values of different types, it uses an implicit conversion scheme specified in its specification. In the case of a Date and Boolean, it will convert both to Double and compare the values.

Putting it Together

The value to compare to you have specified is TIMECOMPARE, to which no value has been assigned to, yet. So you compare to the Date corresponding to the Double 0. All your Case conditions are Booleans, so you compare to 0, if the condition evaluates to False and to -1 if the condition evaluates to True. Accordingly, exactly the code in the first Case block with a condition evaluating to False will be executed.