VBA Now

Excel VBA Now Function

VBA NOW function is similar to the one in excel worksheet function. Like DATE function in VBA “NOW” too doesn’t have parameters to pass in, we just need to pass the function with closed parenthesis or no need of parenthesis as well. By using DATE function in VBA we can generate the current date as showing the system we are working on. However, I have seen situations where we need current time along with the date as well. In excel we can do much variety of things, similarly, we can generate current date and time with a simple function called NOW.

The formula of the VBA NOW is simple.

NOW ()

Example of NOW Function in VBA Excel

Look at the simple example of the NOW function in VBA. Follow the below steps to write the VBA code and have a fair bit of knowledge on NOW as well as writing the code.

Step 1: Start the sub procedure by giving a macro name.

Code:

Sub Now_Example1()

End Sub

Step 2: Declare the variable as “Date”. The reason why we need to declare the variable as “Date” because our end result is in Date and Time format.

Code:

Sub Now_Example1()

Dim k As Date

End Sub

Step 3: Assign the value to the variable “k” by applying VBA NOW function.

Code:

Sub Now_Example1()

Dim k As Date

k = Now

End Sub

Step 4: Now show the value of the function NOW which we have assigned to the variable “k” in the message box in VBA.

Code:

Sub Now_Example1()

Dim k As Date

k = Now

MsgBox k

End Sub

Ok, we are done.

Run the code using F5 key or manually and see what the result is.

The result shows 4/15/2019 5:03:35.

My computer date format is “mm-dd-yyyy”.

We can also change the date format by using the FORMAT function. Below is the code to change the date format.

Code:

Sub Now_Example1()

Dim k As Date

k = Now

MsgBox Format(k, "DD-MMM-YYYY HH:MM:SS")

End Sub

Run the code and see the difference.

Now we have a proper date and time format. With this format, anybody can understand the date and time format.

Volatile in Nature:

As you can see in the first example we got the time result as 5:03:35 and in the second example we go the result as 17:19:02. So this shows that NOW function is a volatile function which changes every second.

Alternative to Timer Function in VBA

As an alternative to VBA TIMER, we can use “VBA NOW” function to calculate the total time taken by the macro to complete the task.

Use below code to calculate the time taken by your code.

C0de:

Sub TotalDuration()

Dim k As Date

k = Now

'

'

'Enter your code here
'

'

'
    MsgBox "Total Time Taken by the  macro to complete the task is : " & _
Format((Now - k), "HH:MM:SS")
End Sub

In the green colored area copy and paste your code.

Execute the code by pressing F5 key or press the run button. As soon as it completes the execution we will get the time taken by the macro to complete the task message in the message box. Below is an example of the same.

Like this, we can use NOW function in many ways in VBA.

You can download this Excel VBA Now Function template here – VBA Now Function Template

Recommended Articles

This has been a guide to VBA Now. Here we learn how to use Now function in Excel VBA and its alternative to timer Function along with simple to advanced examples. Below are some useful excel articles related to VBA –

• VBA Change Font Color
• VBA StrComp with If Condition
• What does CSTR Function do in Excel?
• What is WorkBook in Excel VBA?
• Sleep in VBA
• VBA Long
• VBA For Each Loop Example
• Declare Data Type in VBA
• Set Borders in VBA