VBA Now Function

The VBA Now function is a built-in function that returns the current date and time in Microsoft Excel. This function is useful for time-sensitive tasks where the exact date and time are required. It can also be used for logging purposes or to perform calculations based on the current date and time. The Now function can be used in both Excel spreadsheets and VBA code.

VBA Now Function – Purpose, Syntax and Arguments

Syntax:

Now()

Arguments:

• (None): This function does not take any arguments.

Example:

Suppose you have a project in Excel where you need to track the current date and time whenever a certain value is entered in a specific cell. You can use the Now function to update the date and time automatically. For example, in a cell named “Time Stamp”, you can enter the following formula:

=IF(A1="Complete", Now(), "")

This formula will check the value in cell A1, and if it is “Complete”, it will enter the current date and time in the “Time Stamp” cell. Otherwise, it will leave the cell blank.

Remarks and Important Notes:

• The Now function returns the current date and time in the format of your computer’s regional settings. This means that the date format may be different for users in different regions.
• The value returned by the Now function will update automatically whenever the spreadsheet is recalculated or opened.
• If you only need to get the current date, you can use the ‘Date’ function instead of the Now function. The ‘Date’ function only returns the current date and does not include the time.
• The Now function is dependent on the system clock of your computer. If the system clock is incorrect, the date and time returned by this function will also be incorrect.

Understanding VBA Now Function with Examples

Example 1: Getting the Current Date and Time

Dim currentTime As Date
currentTime = Now
MsgBox "The current date and time is: " & currentTime

In this example, we have declared a variable named ‘currentTime’ of type Date. Then we have used the VBA Now function to assign the current date and time to this variable. Finally, the MsgBox function is used to display the current date and time in a message box.

Explanation:

• The VBA Now function does not require any arguments. It automatically gets the current date and time from the system and returns it as a Date data type.
• The Date data type represents a date and time value in VBA. The date portion of this value represents the number of days since December 31, 1899, and the time portion represents the fraction of the day for the current time.
• In the above code, the currentTime variable is assigned the value returned by the Now function, which is the current date and time in the Date data type format.
• The message box then displays the current date and time by concatenating the text string with the ‘currentTime’ variable.

Example 2: Extracting Date and Time Components from Now Function

Dim currentDate As Date, currentTime As Date
Dim justDate As Date, justTime As Variant
currentDate = Now 'gets the current date and time in the Date data type
currentTime = Time 'gets only the current time in the Variant data type
MsgBox "The current date is: " & currentDate & vbNewLine & "The current time is: " & currentTime 

This example demonstrates how to extract the date and time components from the Now function. In the above code, we have declared two variables, ‘currentDate’ and ‘currentTime’. Then we have used the Now function to get the current date and time, and the ‘Time’ function to get only the current time. Finally, the message box displays both the date and time components.

Explanation:

• In VBA, the Now function returns both the date and time components by default. However, sometimes we may need to work with only one of these components. In such cases, we can use the ‘Time’ function to get only the current time in Variant data type format.
• The Variant data type is used to represent any data type in VBA. It automatically adjusts the data type based on the value assigned to it. In this case, the justTime variable will hold only the time portion of the current date and time value returned by the Now function.
• We have used the vbNewLine constant to display the date and time components in separate lines in the message box.

Example 3: Adding or Subtracting Time from Now Function

Dim newDate As Date
newDate = Now + TimeValue("00:05:00") 'adds 5 minutes to the current date and time
MsgBox "The new date and time is: " & newDate
newDate = Now - TimeValue("00:02:30") 'subtracts 2 minutes and 30 seconds from the current date and time
MsgBox "The new date and time is: " & newDate

This example shows how the Now function can be used in combination with the ‘TimeValue’ function to add or subtract time from the current date and time. In the above code, we have declared a variable named ‘newDate’ of type Date. Then we have used the Now function to get the current date and time and added or subtracted a certain time value from it. Finally, the message box displays the new date and time.

Explanation:

• The ‘TimeValue’ function is used to convert a time value in string format to a Variant data type value. In this example, we have used the “hh:mm:ss” format to specify time values in hours, minutes, and seconds.
• When we add or subtract a time value from the current date and time, the Date data type automatically adjusts the value accordingly.
• This is useful when we need to do time-related calculations and manipulate date and time values in VBA.

Conclusion:

In this blog post, we have explored the VBA Now function with examples to understand its usage and applications. This function is a handy tool for working with date and time values in VBA and can be used in various scenarios. Understanding the Now function and its functionalities can greatly enhance our coding abilities and make date and time operations more efficient in VBA projects.