Time Value in VBA is an inbuilt function which is also categorized under the date and time function in VBA, this function as the name suggests gives us the numerical value of the date provided as an argument, it takes a single argument which is the date and returns the numerical value from the argument.
What Does Time Value Function Do in VBA?
The time Value function in VBA Excel returns the time value portion from the full date and time. Date and time are stored as serial numbers in excel. The serial number represents the DATE, and the decimal represents the time. 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 as follows.
As we said, the TimeValue function returns the serial number part of the given date, which is stored as a text value. Time is nothing but the actual time we are looking to get the serial number. Remember, Time Value 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 the 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 thing, I have declared the variable “MyTime” as Date.
Dim MyTime As Date
Then I have assigned the value to the variable by applying TimeValue.
MyTime = TimeValue("28-05-2019 16:50:45")
Then in the message box, I have assigned the variable result.
MsgBox "Supplied Time is: " & MyTime, vbInformation, "TIMEVALUE Function".
If I run the code using the F5 key or manually, then we will get the result as follows.
VBA TimeValue Example #2
For the same code, I will declare the VBA variable as “Double.”
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 I 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, I 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 I will apply the time format to check the exact result.
When you convert it into a time format, you can see the exact time.
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 set of tasks for all the cells.
We have data from 1st cell to 14th cell, so our loop should run for 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 limit and upper limit. The Below code is the already written code to extract time value from the date & 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 like below.
If you wish to see the time, then apply TIME format to it.
So, this is how the TIME VALUE function works in VBA & Excel.
This has been a guide to VBA TimeValue. Here we learn how to use VBA TimeValue function to return the serial number from the stored text value along with practical examples and a downloadable template. Below you can find some useful excel VBA articles –