What is Excel VBA Now Function?
In Excel VBA, the Now function is a built-in date and time function that returns the current date and time according to your computer’s system clock. It’s handy when recording or manipulating date and time values within your VBA code.
See the example below:
Declare a sub-routine to print the current time with VBA Now. It is printed in the Immediate tab. The VBA Now Format is printed by default as “dd-mm-yyyy hh:mm:ss”.
Table of contents
Key Takeaways
- The VBA Now function obtains the current date and time according to your computer’s system clock. It returns a Date data value that includes the current date and time down to the second.
- The result of Now can be assigned to a variable, displayed in a cell, or used for calculations within VBA code.
- You can format the result of the Now function to display the date and time in a specific format using the Format function.
- Now() is commonly used when working with date and time components, such as recording logs, measuring time intervals, or time-sensitive calculations.
Syntax
The syntax of VBA Now is shown below:
Now()
Where,
Now() is the VBA function assigned to a variable of “Date” datatype, or it can be converted from VBA Now to String. Hence, “String” and “Date” datatypes can be used interchangeably. There are no arguments for this function.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
How to Use Excel VBA Now Function?
To create an example where VBA Now can be implemented, follow the steps below:
- Open an Excel Workbook. Go to the Developer section in the Toolbar.
Click the “Visual Basic” icon in the Developer section. It opens the VBA code editor.
In the Editor, click “Insert” and select “Module” to create a new Module to create and edit VBA Subroutines/ functions.
- Create a sub-procedure to create a digital clock in Excel VBA by printing the time.
- Initialize a Do Loop in VBA.
- Inside the Do Loop, print the current time into cell “A1.”
- Delay the loop by 1 second using the Wait function. Then, loop it continuously.
Code:
Sub UpdateDigitalClock()
Do
ThisWorkbook.Sheets(“NOW_Use”).Range(“A1”).Value = Now
Application.Wait Now + TimeValue(“00:00:01”)
Loop
End Sub - Run the above subroutine. Go to “NOW_Use” and view the output.
After one minute,
After one minute,
This simple example shows us how and where to use the VBA Now function.
Examples of NOW Function in Excel VBA
Let us view some interesting examples where Now() can be used practically in real-life applications.
Example #1
Consider an example where you’ll have to log in the changes at the specific time it’s done to keep track of the changes. It can be done using the Now function in Excel VBA. Let’s see how we can do this by following the steps below:
- Step 1: Right-click on the sheet you want to record the changes.
Click “View Code” in the drop-down. It will open the VBA Editor specifically for that particular sheet.
- Step 2: Declare a private subroutine for a Range variable.
- Step 3: Declare a range to store the cell value in. If the cell value changes, it will print a new Now function.
- Step 4: Declare your preferred Cell Range.
- Step 5: Initialize an If statement to check if the cell values aren’t or the current value isn’t empty; the Now function is printed onto cell “C1.”
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim TargetCell As Range
Set TargetCell = Me.Range(“B1”)
If Not Intersect(Target, TargetCell) Is Nothing And TargetCell.Value <> “” Then
Me.Range(“C1”).Value = Now
End If
End Sub
- Step 6: Go back to the worksheet and view the output.
If the values are changed after some time,
With this, we’ve implemented a record recording the changes made at a specific time.
Example #2
Here, we will to measure the time between two clicks of a button in a Userform.
- Step 1: Create a Userform in Excel VBA by selecting “Userform.”
It creates a new blank Userform, which you can start customizing with.
Edit this UserForm to your liking.
- Step 2: Create a Label and customize its properties.
Now the UserForm will be seen below:
- Step 3: Initialize a sub-procedure that calls the above function to perform VBA Today plus 7 days.
After editing and customizing the Command Button, the UserForm currently looks like this:
Double-click the Button to open its VBA editor, where you can define the button’s functionalities.
- Step 4: Declare a “Date” variable outside the function. It is so that it retains its value between many VBA button clicks.
Step 5: Check if any value for the above variable is declared. If not, assign it with VBA Now time.
Step 6: Add a message to the user that the timer has been started.
Step 7: In the Else block, declare another Date variable to store the current time after clicking the button a second time.
- Step 8: Assign the variable declared above to find the difference between the current and earlier starting times.
- Step 9: Print the elapsed time in a message box function and set the starting time as 0. Then, end the If-Else statement block.
Code:
In the UserForm
Dim StartTime As Date
Private Sub CommandButton1_Click()
If StartTime = 0 Then
StartTime = Now
MsgBox “Timer started!”
Else
Dim ElapsedTime As Date
ElapsedTime = Now – StartTime
MsgBox “Time elapsed: ” & Format(ElapsedTime, “hh:mm:ss”)
StartTime = 0
End If
End Sub
- Step 10: Press “F5” to run the program. The UserForm you created will show and you can view it.
Clicking on the button once.
Click on it another time.
We can see that about 46 seconds have passed between both clicks.
Alternative to Timer Function in VBA
The Timer function in VBA returns the number of seconds elapsed since midnight. However, if you need a higher-resolution timer or a timer that measures milliseconds or microseconds, you can use the Windows API function called GetTickCount or other methods like using the QueryPerformanceCounter function for even higher precision timing.
Some of the other well-known alternatives are:
- Now function: The Now function returns the current date and time. It can be used as an alternative to the Timer function to track the time consumed by the code to complete the process specified in the sub-procedure.
- SetTimer and KillTimer functions: These are Win32 API functions that can be used as an alternative to the Application.OnTime method without using .NET. The SetTimer function sets a timer for the specified number of milliseconds, while the KillTimer function destroys the specified timer.
- Wait and Sleep functions: These are VBA functions that can be used to pause or delay VBA code execution. The Wait function remains the code execution for a specified number of seconds, while the Sleep function in VBA suspends the execution of the code for a specified number of milliseconds. These functions can be useful for creating a delay in the code execution.
- Custom functions: You can create custom functions in VBA to track the time consumed by the code to complete the process specified in the sub-procedure. For example, you can create a function that returns the difference between the start time and end time of the code execution.
Important Things to Note
- Make sure your computer’s date and time settings are accurate because Now() relies on these settings.
- Ensure that your variables and cells where you store the result of Now are formatted as date/time data types to display the information correctly.
- The Now function captures the date and time when it is called. If you store the result in a variable, it won’t update automatically.
- The Now function provides second-level precision.
- In Excel, if you place Now in a cell, it will update whenever the worksheet recalculates.
- The function provides a way to record event timestamps, track real-time progress, and perform time-based logic.
Frequently Asked Questions (FAQs)
• Ensure that your computer’s date and time settings are accurate, as Now relies on these settings.
• Check your VBA code for syntax errors or logical issues that might prevent the Now function from executing correctly.
• Make sure that your VBA macro, which contains the Now function, is being executed. Check for any macro-triggering conditions or events.
• Confirm that you have correctly declared any variables used with the Now function.
• In Excel, if you use Now in a Cell Formula, it may not update in real-time unless the worksheet recalculates. Ensure that your worksheet is set to auto-calculate or recalculate as needed.
• Ensure that your VBA environment is enabled and functioning correctly. Check for any security settings that might block VBA macros.
In VBA, Time is not a function by itself; it’s a data type used to represent time values (hours, minutes, seconds) without a date component. The Time data type works with time values in VBA, but it doesn’t provide a way to obtain the current time like the Now function for both date and time.
Download Template
This article must be helpful to understand the VBA Now, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to VBA Now. We learn the NOW function in Excel VBA, its syntax, how to use it & as alternative to Timer function, along with examples. You can learn more from the following articles –
Leave a Reply