VBA Time

Excel VBA Time

TIME function in VBA inserts the current time and using it as input to other lines of code. 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

TIME ()

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 Excel VBA?

Let me show you the example of simple TIME function. 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 VBA message box.

Code:

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.

Code:

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. Below code will format the date & 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.

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 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 workbook opening date & time.

I have already written code and 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

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

Recommended Articles

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 –

• VBA Set with Range Objects Variables
• VBA Left Function
• VBA AutoFilter
• VBA Format
• VBA Paste Special