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....

Could you please state more clearly what you experience and what is the intended behaviour? Does it stop at the first condition evaluating to 'False' and you do not expect that or is it the other way around? — M.Doerner, Jun 06 '20 at 17:42
It stops at the first condition and I do not expect this behaviour... I thought was going head since the condition is not matching. — Nik, Jun 07 '20 at 07:31

M.Doerner · Answer 1 · 2020-06-06T18:05:39.443

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.

edited Jun 06 '20 at 18:05

answered Jun 06 '20 at 18:00

M.Doerner

712
3
7

Thanks for your explanation, still try to understand something... TIMETOCOMPARE maybe is not the best function name, it should be like GetTime. the expected behaviour should be to evaluate as false Hour(TOut) < Hour(TCheckIn), and then to pass at the following case without giving this result -1/24 (10:00 – 11:00) Tin and Tout if in the time range of TCheckIn and TCheckOut should give me the difference between Tin and Tout and so on if not in the time range the result should be 0. Trying to explain as better as I can... I´m a beginner. – Nik Jun 07 '20 at 06:41
What do you mean saying so : Accordingly, the return value of your function is the date corresponding to the Double 0.? I have Tin = 07:00 P.M. Tout=11:00 P.M. TCheckIn= 10:00 P.M, TCheckOut=12:00 A.M (that I wrote as 24:00) When I wrote TIMECOMPARE = TimeSerial(Hours, 0, 0) I am expecting the function to return the difference between 11:00 and 10:00 when the case is matched (the value thes should be the double 1/24). So many questions that I have…. – Nik Jun 07 '20 at 08:24
I don´t understand neither this sentence: "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." In my opinion is -1 if the condition is false, and then the case is not matching.... – Nik Jun 07 '20 at 10:16
You seem to fundamentally misunderstand how the Select-Case statement works. Please have a look at its [documentation](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/select-case-statement). – M.Doerner Jun 07 '20 at 13:42
Note that inside the function `TIMECOMPARE`, the identifier `TIMECOMPARE` without argument refers to the variable holding the value returned when the function exits. At the first point you use it, no value has been assigned to it and, accordingly, it has the default value. – M.Doerner Jun 07 '20 at 13:46
Your case conditions are comparisons, which return a Boolean. When converted to a numeric type, `False` is converted to 0 and `True` to -1. – M.Doerner Jun 07 '20 at 13:49
Got it, I am not able to think this solution with select case, I´ll go with an "if then, elseif" in another post. still not understanding true -1 since excel I´ve thought it was 1, while false 0.... – Nik Jun 07 '20 at 16:21

select case stop at the first false condition

1 Answers1

Select Case

Variable Initialization

Comparison and Implicit Conversion

Putting it Together