HOUR Function works as a formula but not as VBA code for 24:00
<p>So I have a procedure that I thought was working last time I checked it but apparently it is not.  The problem I am having is with 24:00 and the hour check.  Yes a classic problem apparently.  When entering 24:00 and I use =hour(A1) where A1 has 24:00 in it, I get the answer of 0, and that part I am ok with.  I even have some other code that says if timehour = 0 then timehour = 24 and I carry on from there and all is peachy.</p>
<p>Today when I went to enter my 24:00 and used the hour function in VBA it returned run-time error '13': Type mismatch instead of 0.</p>
<h1>SET UP</h1>
<p>Cell E4 and E5 in excel has validated data list for the user to pick from in a drop down that ranges from 01:00, 02:00, 03:00, ..., 24:00. All formatted as text.  If the user has selected 12 hour clock in set up then the times are listed 1:00 AM, 2:00 AM, 3:00 AM, ...,  11:00 PM, 12:00 AM.</p>
<h1>CODE</h1>
<pre><code>Private Sub Set_Work_Hours()
    Dim setStartHour As Integer
    Dim setEndHour As Integer
    Dim sht As Worksheet
    
    'setting the worksheet to be working with
    Set sht = ThisWorkbook.Worksheets(&quot;Daily&quot;)
    sht.Activate
    
    'hardcode location of start and end times from daily sheet
    setStartHour = Hour(Cells(4, 5).Value)
    setEndHour = Hour(Cells(5, 5).Value)
        
    Call Display_Work_Hours(setStartHour, setEndHour)
    
End Sub
</code></pre>
<p>I get the error at setStartHour or setEndHour when I select 24:00 from the list.
In another cell in the spreadsheet, I had the formula =HOUR(E4) and =HOUR(E5) and they both return 0 when 24:00 is selected.</p>
<p>Why is the VBA version of HOUR not returning zero?</p>
<p>Clarification on the question.
Why am I getting two different results from excel formula to VBA function?</p>
<h1>My work around</h1>
<pre><code>Private Sub Set_Work_Hours()
    Dim setStartHour As Integer
    Dim setEndHour As Integer
    Dim sht As Worksheet
    
    'setting the worksheet to be working with
    Set sht = ThisWorkbook.Worksheets(&quot;Daily&quot;)
    sht.Activate
    
    'hardcode location of start and end times from daily sheet
    If Cells(4, 5).Value = &quot;24:00&quot; Then
        setStartHour = 0
    Else
        setStartHour = Hour(Cells(4, 5).Value)
    End If
    If Cells(5, 5).Value = &quot;24:00&quot; Then
        setEndHour = 0
    Else
        setEndHour = Hour(Cells(5, 5).Value)
    End If
    
    Call Display_Work_Hours(setStartHour, setEndHour)
    
End Sub
</code></pre>