Excel VBA Time Function
VBA Time function returns the current time, also the important thing to note is that this function has no arguments in it whatsoever, another important factor to remember is that this function returns the current system time. Using this function we can actually find the actual 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 time as well as the current date in the spreadsheet
We just need to enter the function, in fact, no need to parenthesis to enclose just TIME is enough to insert the current time. The result given by the TIME function is in the string.
How to Use TIME Function in VBA?
Let me show you the example of a simple TIME function. Follow the below steps to create code to use the TIME function.
Step 1: Create a macro.
Sub Time_Example1() End Sub
Step 2: Declare a variable as String.
Sub Time_Example1() Dim CurrentTime As String End Sub
Step 3: Assign a value to this variable through the TIME function.
Sub Time_Example1() Dim CurrentTime As String CurrentTime = Time End Sub
Step 4: Now show the result in the VBA message box.
Sub Time_Example1() Dim CurrentTime As String CurrentTime = Time MsgBox CurrentTime End Sub
Run this code using F5 key or manually we will get the current time.
So, when I run this code time was 11.51.54 AM.
Combination of Date & Time as an Alternative to NOW Function
As I told at the beginning of the article NOW can insert current date & time. However we can use two other functions as an alternative function to NOW function, those two functions are VBA DATE & VBA TIME functions.
VBA Date will return current date & VBA Time will return current time, so this makes the NOW function. Below is a set of code which will insert the current date & time in cell A1.
Sub Time_Example2() Range("A1").Value = Date & " " & Time End Sub
This code will insert the current date & time in cell A1.
We can also apply to format to these values using FORMAT function. The below code will format the date & time.
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.
Track Your Workbook Open Records using Time Function in VBA
Often times we need to know our workbook opening time-frequency. There is a situation where we open the workbook quite often and we make some changes. By tracking the workbook opening time & date we can track workbook opening time. Create a new sheet and rename it as “Track Sheet”.
Step 1: Double click on This workbook.
Step 2: Select the workbook from the object drop-down list.
Step 3: As soon as you select this option you can see 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 & time.
I have already written code and below is the code for you.
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
This will record your workbook opening times like the below one.
This has been a guide to Excel VBA Time. Here we learned how to use Time Functions in excel VBA and also we learned the Combination of date and time as an alternative to Now() Function and tracks the workbook open records along with some simple to advanced examples. Below are some useful excel articles related to VBA –