WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » VBA Time Function

VBA Time Function

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj 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 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 times as well as the current date in the spreadsheet.

TIME ()

We just need to enter the function. In fact, no need for parenthesis to enclose just TIME is enough to insert the current time. The result given by the TIME function is in the string.

VBA Time

How to Use TIME Function in VBA?

Let me show you the example of a simple TIME in excel function. 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.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Visual Basic Application Example 1-4

So, when I run this code, the time was 11.51.54 AM.

Alternative of Now() Function

Combination of Date & Time as an Alternative to NOW Function

As I told at the beginning of the article, NOW can insert the current date & time. However, we can use two other functions as an alternative function to the NOW function; those two functions are VBA DATE & VBA TIME functions.

VBA Date will return the current date & Time will return the 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

VBA Time Example 2

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

VBA Time Example 2-1

We can also apply a format to these values using the FORMAT function. The 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

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

VBA Time Example 3

Step 1: Double click on This workbook 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 & 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

Visual Basic Application Example 3-4

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

Visual Basic Application Example 3-5

Recommended Articles

This has been a guide to VBA Time Function. Here we learned how to use VBA Time Function, and also we learned the Combination of date and time as an alternative to Now() Function and tracks the workbook open records along with examples and downloadable excel sheet. Below are some useful excel articles related to VBA –

  • VBA Paste Method
  • Time Card Template
  • Excel Convert Function
  • CDATE Function in VBA
0 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA Time Function Template

Special Offer - VBA Training Course (6 courses, 35+ hours video) View More