May 12, 2021

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


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.NumberFormat = "0"
    End If

End If

End Sub

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

One thought on “Custom Function to Check Time Entries

Comments are closed.