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:

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