Quick MS Excel IsTime() and TypeIs() functions solution

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):
' <zmey20000@yahoo.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
Advertisements

About mezzantrop

10 years of experience in large SAN and storage environments: mainly Hitachi, HP and Brocade. Now I am a proud SAN/storage IBMer. Empty – expect-like tool author. FreeBSD enthusiast.
This entry was posted in Tips & tricks and tagged , , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s