VBA Time Function

Updated on January 2, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Excel VBA Time Function

VBA Time function returns the current time. Also, the important thing to note is that this function has no arguments whatsoever. Another important factor is that this function returns the current system time. Using this function, we can find the time taken by the line of codes to complete the process.

TIME is a kind of volatile function. It does not have any syntax to it.

We also have a similar function in Excel – NOW () functionNOW () FunctionIn an excel worksheet, the NOW function is used to display the current system date and time. The syntax for using this function is quite simple =NOW ().read more, which inserts both current times and the current date in the spreadsheet.

TIME ()

We need to enter the function. No need for parenthesis to enclose. Just the TIME function is enough to insert the current time. The result given by the TIME function is in the String.

VBA Time Function

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: VBA Time Function (wallstreetmojo.com)

How to Use TIME Function in VBA?

Let me show you an example of a simple TIME in excelTIME In ExcelTime is a time worksheet function in Excel that is used to calculate time based on the inputs provided by the user. The arguments can take the following formats: hours, minutes, and seconds.read more function. Then, follow the below steps to create code to use the TIME function.

You can download this VBA Time Function Template here – VBA Time Function Template

Step 1: Create a Macro.

Code:

Sub Time_Example1()
End Sub
VBA Time Example 1

Step 2: Declare a variable as String.

Code:

Sub Time_Example1()

  Dim CurrentTime As String

End Sub
VBA Time Example 1-1

Step 3: Assign a value to this variable through the TIME function.

Code:

Sub Time_Example1()

  Dim CurrentTime As String

  CurrentTime = Time

End Sub
Visual Basic Application Example 1-2

Step 4: Now, show the result in the message box.

Code:

Sub Time_Example1()

  Dim CurrentTime As String

  CurrentTime = Time

  MsgBox CurrentTime

End Sub
Visual Basic Application Example 1-3

Run this code using the F5 key or manually. We will get the current time.

Visual Basic Application Example 1-4

So, when we ran this code, the time was 11.51.54 AM.

–>> If you want to learn Excel VBA professionally, then our VBA Basic Course (16+ hours) is the perfect solution. In our Basic Excel VBA course you learn the skill of automating tasks using Variables, Data Types, Ranges, and Cells in VBA. Master Control Structures, including Conditional Statements and Loops, and discover techniques for manipulating data through sorting, filtering, and formatting. By the end of the course, you will be able to apply your acquired skills to real projects, culminating in an Excel VBA project which will solidify your ability to create efficient, automated solutions.

Alternative of Now() Function

Combination of Date & Time as an Alternative to NOW Function

As we said at the beginning of the article, the NOW function can insert the current date and time. However, we can use two other functions as an alternative to the NOW function. Those two functions are VBA DATEVBA DATEVBA Date is a date and time function. It returns only the current date as per the system date you are using and has no arguments whatsoever. This function returns the current system date.read more & VBA TIME functions.

VBA Date will return the current date, and the Time function will return the current time, making the NOW function. Below is a set of codes that will insert the current date and time in cell A1.

Code:

Sub Time_Example2()

  Range("A1").Value = Date & " " & Time

End Sub
VBA Time Example 2

This code will insert the current date and time in cell A1.

VBA Time Example 2-1

We can also apply a format to these values using the FORMAT function. For example, the below code will format the date and time.

Code:

Sub Time_Example2()

  Range("A1").Value = Date & " " & Time

  Range("A1").NumberFormat = "dd-mmm-yyyy hh:mm:ss AM/PM"

End Sub
VBA Time Example 2-2

Now, the result of this code is as follows.

VBA Time Example 2-2

Track Your Workbook Open Records using Time Function in VBA

Often, we need to know our workbook opening time frequency. For example, there is a situation where we open the workbook quite often, and we make some changes. Then, we can track the workbook opening time and date by tracking the workbook opening time.

Create a new sheet and rename it “Track Sheet.”

VBA Time Example 3

Step 1: Double-click on ThisWorkbook from VBE Editor.

Visual Basic Application Time Example 3-1

Step 2: Select the workbook from the object drop-down list.

Visual Basic Application Time Example 3-2

Step 3: As soon as you select this option, you can see a new Macro automatically created by itself in the name “Workbook_Open().”

VBA Time Example 3-3

Step 4: Inside this Macro, we will need to write a code to track the workbook opening date and time.

We have already written the code. Below is the code for you.

Code:

Private Sub  Workbook_Open()

Dim LR As Long

LR = Sheets("Track Sheet").Cells(Rows.Count, 1).End(xlUp).Row + 1

Sheets("Track Sheet").Cells(LR, 1).Value = Date & " " & Time()
Sheets("Track Sheet").Cells(LR, 1).NumberFormat = "dd-mmm-yyyy hh:mm:ss AM/PM"

End Sub
Visual Basic Application Example 3-4

It will record your workbook opening times like the below one.

Visual Basic Application Example 3-5

Recommended Articles

This article has been a guide to VBA Time Function. Here, we learned how to use the VBA Time function and the combination of date and time as an alternative to the NOW() function and track the open workbook records along with examples and a downloadable Excel sheet. Below are some useful Excel articles related to VBA: –