Contents
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
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
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.
Unlock Your Potential
Excel
Access
Power BI
