VBA TimeValue Function

What is TimeValue Function in VBA?

In VBA (Visual Basic for Applications), the “TimeValue” function is used to convert a valid time expression represented as a string into a time value that Excel can recognize and manipulate. It’s often used when working with dates and times in Excel macros.

See the example below.

TimeValue Function in VBA - Example 1

Using the VBA TimeValue Function, we can view the time in 24-hour format by adding AM/PM after adding the date.

TimeValue Function in VBA - Example 1-1

The default type of VBA TimeValue format is “hh:mm:ss” as seen above.

Key Takeaways
  • When extracting times from cells, ensure the cell format is compatible with TimeValue.
  • To work with AM/PM notation, you can convert it into either 12-hour (provided you mention AM/PM explicitly) or 24-hour format before using TimeValue.
  • Keep in mind that VBA’s TimeValue does not account for time zone differences.
  • Always validate input data to avoid unexpected results or errors. Consider creating user-friendly error messages for users when dealing with invalid inputs.
  • Test your VBA code with various time formats and edge cases to ensure accuracy.

Syntax of VBA TimeValue Function

The syntax of VBA TimeValue Variable is as shown below:

TimeValue(time_string)

Where,

  • ‘time_string’ is a required parameter that represents the time expression you want to convert into a time value. It should be a valid time string in the format “hh:mm:ss” (hours, minutes, and seconds), where “hh” is the hour (00 to 23), “mm” is the minute (00 to 59), and “ss” is the second (00 to 59).

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

How To Use Time Value Function in VBA?

See how to use the VBA TimeValue function by following the steps mentioned below.

Step 1: Open the Excel WorkBook you’ll be working on. In the title bar, choose “Developer.”

How To Use TimeValue Function - Step 1

In the Developer section, choose the “Visual Basic” option to open the Excel VBA Editor.

How To Use TimeValue Function - Step 2

Once the Editor opens, in the toolbar, click “Insert” and select “Module.”

How To Use TimeValue Function - Step 3

Now that the prerequisites are done let us see how to convert a given date to depict time accurately in different formats.

Step 2: Start by declaring a sub-procedure’s name.

How To Use TimeValue Function - Step 4

Step 3: Declare a variable of String VBA data type and assign it a string in the valid time format.

How To Use TimeValue Function - Step 5

Step 4: Declare a Date variable to convert the string variable to a Date variable.

How To Use TimeValue Function - Step 6

Step 5: Print the time using a Message Box.

How To Use TimeValue Function - Step 7

Print it in different formats using the VBA TimeValue Format function to print it in “hh:mm” and “hh:mm:ss.”

Here,

“vb” is, in short, Visual Basic.

“CrLf” is the Carriage Return and Line Feed, characters used in text files and communication protocols to indicate the end of a line and move the cursor to the beginning of the next line.

“vbInformation” is used to print an icon in the message box.

We used vbCrLf to print values in the same box but different lines.

Code:

Sub ExampleTimeValue()

    Dim timeString As String

    timeString = “15:30:45”

    Dim myTime As Date

    myTime = TimeValue(timeString)

    MsgBox “Time Value: ” & vbCrLf & _

    Format(myTime, “hh:mm AM/PM”) & vbCrLf & _

    Format(myTime, “hh:mm:ss AM/PM”), vbInformation

End Sub

Step 6: Run the program by clicking the green arrow button or the “F5” button on your keyboard.

How To Use TimeValue Function - Step 8

The output is printed in a message box as shown below.

How To Use TimeValue Function - Step 9

Examples of Time Value Function with Excel VBA

Let’s see how you can use VBA TimeValue for your benefit by referring to the examples below.

Example #1

Given a date acquired using the VBA Now function in an Excel worksheet, print the different VBA TimeValue formats available in the adjacent cells.

VBA TimeValue Example 1 - Step 1

Step 1: Create a subroutine to print VBA TimeValue milliseconds format.

VBA TimeValue Example 1 - Step 2

Step 2: Assign the value of cell “A1” to a Variant data type.

VBA TimeValue Example 1 - Step 3

Step 3: Assign the time value of the given date in a Date variable. The Date data type is the ideal VBA TimeValue variable data type.

VBA TimeValue Example 1 - Step 4

Step 4: Print the formatted value of the VBA TimeValue in cell “B1”

VBA TimeValue Example 1 - Step 5

Step 5: Print VBA TimeValue milliseconds format in cell “C1.”

VBA TimeValue Example 1 - Step 6

The format to print time with milliseconds in Excel or Excel VBA is “hh:mm:ss.000.”

Where the points after the seconds depict a decimal point, depicting the number of milliseconds that have passed.

Code:

Sub GetTimeFromSheets()

    Dim ipString As Variant

    ipString = Range(“A1”).Value

    Dim t As Date

    t = TimeValue(ipString)

    Range(“B1”).Value = Format(t, “hh:mm:ss”)

    Range(“C1”).Value = Format(t, “h:mm:ss.000”)

End Sub

Step 6: Run the code and go to the worksheet to view the output.

VBA TimeValue Example 1 - Step 7

Note: To view the hours and milliseconds format properly, you will need to format cells to do so manually.

VBA TimeValue Example 1 - Step 8

Right-click on the cell and select “Format Cells.” It will open a new box.

VBA TimeValue Example 1 - Step 9

Here, you can customize and enter in your preferred format.

Example #2

Given time values (in the string) in a given range, we want to calculate the total time by adding the time duration of all the values in the table and displaying the sum. This can be done by adding the values of the VBA TimeValue function.

The table is given below.

VBA TimeValue Example 2

Step 1: Start with declaring a sub-procedure to find the sum of the time of all values combined.

VBA TimeValue Example 2 - Step 1

Step 2: Declare 2 Date variables and a range variable to iterate through the table.

VBA TimeValue Example 2 - Step 2

Step 3: Initialize a sub-procedure that calls the above function to perform VBA Today plus 7 days.

VBA TimeValue Example 2 - Step 3

Step 4: Assign the sum variable as 0 or “midnight”.

VBA TimeValue Example 2 - Step 4

We will add the time values to this variable.

Step 5: Initialize a for loop through the range of the table.

VBA TimeValue Example 2 - Step 5

Check if the given values in Excel aren’t in Date format. If so, we can convert them into the Date format using the CDate function in Excel VBA.

Step 6: Add the sum value to the newly converted dates and iterate continuously.

VBA TimeValue Example 2 - Step 6

Step 7: Print the sum value in a message box, formatted to print the hours and minutes only.

VBA TimeValue Example 2 - Step 7

Code:

Sub CalculateTotalTime()

    Dim totalDuration As Date

    Dim cell As Range

    Dim dte As Date

    totalDuration = TimeValue(“00:00:00”)

    For Each cell In Range(“A1:A10”)

        If Not IsDate(cell.Value) Then

            dte = CDate(cell.Value)

            totalDuration = totalDuration + TimeValue(Format(dte, “hh:mm:ss”))

        End If

    Next cell

    MsgBox “Total Duration: ” & Format(totalDuration, “hh:mm”)

End Sub

Step 9: Run the code and view the output below.

VBA TimeValue Example 2 - Step 8

Important Things To Note

  • Ensure that the input time string adheres to the “hh:mm:ss” format, with hours ranging from 00 to 23 and minutes and seconds ranging from 00 to 59.
  • It’s essential to validate the input data for a valid time to prevent potential runtime errors. This validation helps ensure that the time string you provide to the TimeValue function accurately represents a time, minimizing the risk of unexpected issues.
  • TimeValue exclusively focuses on the time component within a date-time value. When you use the TimeValue function, it disregards any date information that might be included in the input string, concentrating solely on the time of day.
  • After using the TimeValue function to convert a time string, you can store the resulting time value in either Date or Variant variables.
  • To present time values in a specific custom format when displaying them to users or within your VBA applications, you can use the Format function in conjunction with TimeValue.
  • Regional variations may affect the format and syntax of time strings, so being aware of these settings helps ensure consistent and accurate results when using TimeValue.

Frequently Asked Questions (FAQs)

1. Can I use the VBA TimeValue function to convert a time with AM/PM notation?

Yes. You can use the VBA TimeValue to convert a time with AM/PM using the VBA TimeValue variable. The format can either be written in a 12-hour or 24-hour format.
 
Sub ShowTime()
     Debug.Print TimeValue(“12:30 PM”)
End Sub
 
Substitute the date for any date. It returns the value in a 24-hour format.
 
time with AM-PM notation FAQs

2. What happens if I provide an invalid time string to the VBA TimeValue function?

If you provide unrecognizable values for the VBA TimeValue format, you will experience a VBA TimeValue Type Mismatch error. VBA TimeValue function variable accepts either a String or Variant data type and returns a Date or Variant data type.

For example, TimeValue(12:30:00) is an invalid format.

3. Is the VBA TimeValue function case-sensitive for the time string?

No, the VBA TimeValue function is not case-sensitive for the time string; it can handle both uppercase and lowercase letters.

4. Are there any regional settings or formatting considerations when using TimeValue in VBA?

Yes, regional settings can affect TimeValue in VBA, particularly when interpreting time strings with different date and time formats.

This has been a guide to VBA TimeValue in Excel. Here we explain the How To Use Time Value Function in VBA using syntax along with examples & downloadable excel template. You can learn more from the following articles –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *