TimeValue in VBA is an inbuilt function, also categorized under the Date and Time function in VBA. As the name suggests, this function gives us the numerical value of the date provided as an argument. It takes a single argument, the date, and returns the numerical value from the argument.
Table of contents
What Does Time Value Function Do in VBA?
The TimeValue function in VBA Excel returns the time value portion from the full date and time. It stores the date and time as serial numbers in Excel. Moreover, the serial number represents the DATE. The decimal represents the time. Therefore, using the TimeValue function, we can get only the time serial number, i.e., decimal number.
Syntax of VBA TimeValue Function
The syntax of the VBA TimeValue function is as follows.
The TimeValue function returns the serial number part of the given date and stores it as a text value. Time is nothing but the actual time we are looking to get the serial number. Remember, the TimeValue function can only get the serial number from the time, which is stored as text, not as TIME.
Examples of TimeValue Function with Excel VBA
Below are examples of the VBA TimeValue function.
VBA TimeValue Example #1
Now, look at the simple example of the VBA TimeValue function.
Sub TIMEVALUE_Function_Example1() 'Convert the given input string to a valid time and display on the screen 'Variable declaration Dim MyTime As Date 'Assign time to a variable MyTime = TimeValue("28-05-2019 16:50:45") 'Display output on the screen MsgBox "Current Time is: " & MyTime, vbInformation, "VBA TIMEVALUE Function" End Sub
First, we have declared the variable “MyTime” as a date.
Dim MyTime As Date
Then, we assign the value to the variable by applying TimeValue.
MyTime = TimeValue("28-05-2019 16:50:45")
Then in the message box, we have assigned the variable result.
MsgBox "Supplied Time is: " & MyTime, vbInformation, "TIMEVALUE Function".
If we run the code using the F5 key or manually, we will get the result as follows.
VBA TimeValue Example #2
We will declare the VBA variable as “Double” for the same code.
Sub TIMEVALUE_Function_Example1() 'Convert the given input string to a valid time and display on the screen 'Variable declaration Dim MyTime As Double 'Assign time to a variable MyTime = TimeValue("28-05-2019 16:50:45") 'Display output on the screen MsgBox "Current Time is: " & MyTime, vbInformation, "VBA TIMEVALUE Function" End Sub
Now, if we run the VBA codeRun The VBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task. manually or by pressing the F5 key, it will display the serial number part of the time, 16:50:45.
For your better understanding, we will first enter the numbers given by the VBA message boxVBA Message BoxVBA MsgBox function is an output function which displays the generalized message provided by the developer. This statement has no arguments and the personalized messages in this function are written under the double quotes while for the values the variable reference is provided. to one of the cells.
Now, we will apply the time format to check the exact result.
You can see when you convert it into a time format.
VBA TimeValue Example #3
Now, look at the below data.
We have data and time together from A1 to A14 cells. For the second column, we need to extract only the time value. Since we have more than one cell to deal with, we need to employ loops to perform the same tasks for all the cells.
We have data from the 1st cell to the 14th cell, so our loop should run 14 times. We need to use FOR NEXT loop in VBAFOR NEXT Loop In VBAAll programming languages make use of the VBA For Next loop. After the FOR statement, there is a criterion in this loop, and the code loops until the criteria are reached. to mention the lower and upper limits. The Below code is the already written code to extract the time value from the date and time combination.
Sub TimeValue_Example3() Dim k As Integer For k = 1 To 14 Cells(k, 2).Value = TimeValue(Cells(k, 1).Value) Next k End Sub
When we run the code, we will get the values below.
If you wish to see the time, then apply TIME format to it.
So, this is how the TIMEVALUE function works in VBA and Excel.
This article has been a guide to VBA TimeValue. Here, we learn how to use the VBA TimeValue function to return the serial number from the stored text value, with practical examples and a downloadable template. Below you can find some useful Excel VBA articles: –