Custom Function to Check Time Entries

How to create a custom function to check time entries in an Excel worksheet and use the user defined function in VBA. Watch the video tutorial below:

Custom or User Defined Function

In the last Excel VBA tutorial we learnt how to use a worksheet change event to allow only entries of numerical values in an Excel worksheet. But one user came up with this interesting query:

‘This is brilliant I would like my spreadsheet to only accept integers. At the moment you can type in Time Values (hh:mm) and receive no error message. Is there a way for MyRange to only accept whole numbers?’

So the first thing we’ll need to do is to check whether a user has entered a time value like so 12:30. Well, we’ll have to create a custom or user defined function (UDF). Our user defined function looks like this:

Function IsTime(Cell) As Boolean
IsTime = (Left(Cell.NumberFormat, 4) = “h:mm” And VarType(Cell) = vbDouble)
End Function

This custom function checks for the format of the time entry using a text function and also uses the VarType function:

VarType function
Returns an Integer indicating the subtype of a variable, or the type of an object’s default property.

So if you check the VarType(Cell) in our case using a variable checkMyValue, it will return the value 5 indicating that the entered type of object’s default property is vbDouble – Double-precision floating-point number

checkMyValue=VarType(Cell)

Now we can the IsTime function using standard methods or in our new VBA code as given below:

Private Sub Worksheet_Change(ByVal Target As Range)

Const MyCellAddress = “$B$2:$B$100” ‘we can always change this range

If InStr(Target.Address, “:”) > 0 Or InStr(Target.Address, “;”) > 0 Or InStr(Target.Address, “,”) > 0 Then

Exit Sub
Dim isect As Range
Set isect = Application.Intersect(Target, Range(MyCellAddress))

If Not isect Is Nothing Then

   If Not IsNumeric(isect) Or InStr(isect, ",") > 0 Or IsTime(isect) Then

        MsgBox "Please enter a numerical value only." & vbCrLf & _
        "No commas please." & vbCrLf & _
        "No time values in hh:ss format allowed!", vbCritical, "Excel Training Videos"

        isect = vbNullString
        isect.ClearFormats
        isect.Activate


    Else
        isect.NumberFormat = "0"
    End If

End If

End Sub

user defined function to check time entries
User Defined Function to Check Time Entries

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.