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 () function, 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.

Let me show you an example of a simple TIME in excel function. Then, follow the below steps to create code to use the TIME function.

Step 1: Create a Macro.

Code:

Sub Time_Example1()
End Sub

Step 2: Declare a variable as String.

Code:

Sub Time_Example1()

  Dim CurrentTime As String

End Sub

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

Code:

Sub Time_Example1()

  Dim CurrentTime As String

  CurrentTime = Time

End Sub

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

Code:

Sub Time_Example1()

  Dim CurrentTime As String

  CurrentTime = Time

  MsgBox CurrentTime

End Sub

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

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

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 DATE & 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

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

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

Now, the result of this code is as follows.

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.”

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

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

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

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

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