How to use TimeValue function in VBA?

The TimeValue function in VBA (Visual Basic for Applications) is used to convert a string that represents a time into a Date data type that VBA can recognize and work with.

Basic syntax

VBA


TimeValue(time)

time is a string expression that represents a time from 0:00:00 (12:00:00 AM) through 23:59:59 (11:59:59 P.M.).

Example

VBA


Sub ConvertTime()

    Dim strTime As String
    Dim vbaTime As Date
    
    ' This is a string representing the time "10:30 PM"
    strTime = "22:30:00"
    
    ' Convert the string to a time value
    vbaTime = TimeValue(strTime)
    
    ' Output the converted time
    MsgBox "The time is: " & vbaTime
    
    ' You can now use vbaTime as a Date/Time value in VBA
    ' For example, you can format it as a string in a specific way:
    MsgBox "Formatted Time: " & Format(vbaTime, "hh:mm:ss AM/PM")
    
End Sub

In this example, we’ve declared a string that represents a time in the 24-hour format. We then used the TimeValue function to convert this into a Date datatype. Once converted, we can use the result in any date and time-related operation in VBA. Remember that while the TimeValue function uses a Date data type, it effectively only stores time information; the date part would be set to the “zero” date (30-Dec-1899) in VBA when you use a TimeValue function without an associated date. If the TimeValue can’t convert the string because it’s not a valid time format, it will raise a runtime error, so you should ensure that the strings being converted are in a valid time format or use error handling to manage this situation.

facebooklinkedin