This tutorial will demonstrate how to get today’s date in VBA.
There are a couple of ways to obtain today’s date in VBA code, namely using the VBA Date() function or the VBA Now() functions.
Dim dtToday as Date dtToday = Date() Debug.Print dtToday
Alternatively, we can display the date in a message box.
Sub TestDate Dim dtToday as Date dtToday = Date() Msgbox "Today's date is " & dtToday End Sub
The Now() Function works in the same way as the date function, but it includes the time.
Sub TestDate() Dim dtToday As Date dtToday = Now() MsgBox "Today's date is " & dtToday End Sub
Formatting Dates with VBA
In both the Date() and the Now() functions, the date is formatted in a default style as determined by the settings on our PC. We can customize this formatting using the VBA Format function. As the format function will return a string, we need to declare a STRING variable rather than a DATE variable.
Sub TestDate() Dim dtToday As String dtToday = Format (Date, "dd mmmm yyyy") MsgBox "Today's date is " & dtToday End Sub
We can also format the Now() function to include the time portion in a customized format.
Sub FormatNow() Dim dtToday As String dtToday = Format(Now(), "dd mmmm yy hh:mm:ss am/pm") MsgBox dtToday End Sub
Comparing 2 Dates with VBA
We can also use the Date function to compare today’s date with a different date – we might want to calculate how many days there are until an event! We can do this using the VBA DateDiff() function which will return a number. We can therefore declare an INTEGER variable to store the returned value in.
Sub TestDateDiff() Dim dtToday As Date Dim dtSomeDay As Date Dim iDays As Integer dtToday = Date dtSomeDay = "05/06/2021" iDays = DateDiff("d", dtToday, dtSomeDay) MsgBox "There are " & iDays & " days between the 2 dates" End Sub
As Dates are stored as numbers, we could also minus the second date from the first to obtain the same answer.
iDays = dtToday - dtSomeDay
VBA Coding Made EasyStop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!