How to get the current date and time in Excel VBA?

Coding in VBA

In Excel VBA, there are various scenarios where you might need to work with date and time values. One common task is obtaining the current date and time. In this blog post, we’ll explore a simple VBA code snippet to achieve this and discuss how you can customize it for your specific needs.

The Basics: Using the Now Function

To get the current date and time in Excel VBA, you can use the Now function. Here’s a simple example:

Sub GetCurrentDateTime()
    Dim currentDateTime As Date
    currentDateTime = Now
    MsgBox "Current Date and Time: " & currentDateTime
End Sub

When this subroutine is executed, it retrieves the current date and time, and then displays a message box.

Customizing Output: Writing to a Cell

However, if you want to print to a1 instead of the message box, you can use the following code:

Sub GetCurrentDateTime()
    Dim currentDateTime As Date
    currentDateTime = Now
    Range("A1").Value = currentDateTime
End Sub

Writing to a Specific Sheet

If you want to paste the current date or time into a specific sheet, you can do so by specifying the sheet as shown below:

Sub GetCurrentDateTime()
    Sheets("Total").Range("A1").Value = Now
End Sub

Feel free to adapt and integrate these code snippets into your Excel VBA projects. Whether you need a quick display in a message box or want to record the information in a specific cell or sheet, these examples serve as a foundation for handling date and time in your VBA macros.

Happy coding!