I never thought I would write hints for MS Excel VBA, nevertheless here is the first one.
It turns out, there is a scripting problem with identifying value as a time in a cell on a worksheet. I was curious to find out, there is no built-in IsTime() for the task in the wide range of IsDataType() functions: IsNumeric(), IsText(), IsLogical(), etc. And the most funny thing about it, the IsDate() function is exists!
A brief internet search showed me that people are facing the same issue while suggested solutions do not look well.
The problem is that “time” values in such cells are actually have “Double” type, which is actually an 8-byte floating point number. These numbers are just masked by one of the “time formats” to present them nicely to users. Obviously IsNumeric() function returns True for such values.
UPD. The similar situation with IsDate(), that’s why I had to put it before IsText(), otherwise it may be detected as text.
Below are two helper functions: IsTime() and TypeIs() I have wrote to handle cell data type identifying processes:
' TypeIs() and IsTime() Functions ' "THE BEER-WARE LICENSE" (Revision 42): ' <email@example.com> wrote this file. As long as you retain this notice you ' can do whatever you want with this stuff. If we meet some day, and you think ' this stuff is worth it, you can buy me a beer in return. Mikhail Zakharov Public Function IsTime(TargetCell As Variant) As Boolean IsTime = False If InStr(TargetCell.NumberFormat, "h:m") And TypeName(TargetCell.Value) = "Double" Then IsTime = True End Function Public Function TypeIs(TargetCell As Variant) As String Select Case True Case IsEmpty(TargetCell.Value) TypeIs = "Empty" Case IsTime(TargetCell) TypeIs = "Time" Case IsNumeric(TargetCell.Value) TypeIs = "Numeric" Case WorksheetFunction.IsLogical(TargetCell.Value) TypeIs = "Logical" Case WorksheetFunction.IsErr(TargetCell.Value) TypeIs = "Error" Case IsDate(TargetCell.Value) TypeIs = "Date" Case WorksheetFunction.IsText(TargetCell.Value) TypeIs = "Text" Case Else TypeIs = "Unknown" End Select End Function
Usage is simple:
Debug.Print TypeIs(Cells(507, 42)) Time Debug.Print IsTime(Cells(507, 42)) True