As the function name we can understand that it is a Date and Time functions, the use of this function is very simple, this function returns the current date, also the important thing to note is that this function has no arguments in it whatsoever, another important factor to remember is that this function returns the current system date.
VBA DATE Function in Excel
In excel we cannot live without some of the functions and “VBA Date” is one of those function. If you are a frequent user of excel worksheet then you must be aware of a function called “TODAY ()” which will return current date as per the system date.
Similarly in VBA Date function will return the current date as per your system date settings. This is unlike worksheet function TODAY because it always returns static values.
What is DATE Function in Excel VBA?
The date is a very simple function and it returns only the current date as per the system date you are using. This works very similar to our worksheet function “TODAY” but not volatile in nature.
The syntax of DATE function in VBA is very simple because it has no argument to supply and includes only empty parenthesis.
Parenthesis are there just to explain the function when you use the function no need of entering parenthesis.
How to Use DATE Function in VBA Excel?
We will show you a simple example of using DATE function in VBA.
VBA DATE – Example #1
Assume you want to insert the current date in cell A1 then follow below steps to write the code to insert the current date in cell A1.
Step 1: Create a macro name.
Step 2: Since we need to store the current date in cell A1 our code will be Range (“A1”).Value.
Sub Date_Example1() Range("A1").Value End Sub
Step 3: In cell A1 we need the current date, so use DATE function.
Sub Date_Example1() Range("A1").Value = Date End Sub
Step 4: Ok, we are done. Let’s run this code now by pressing the F5 key or you can also run the code manually as shown in the below screenshot. We will get the current date in cell A1.
So, when I am writing this code current date in my system is “15th March 2019”.
VBA DATE – Example #2
We have seen a simple example of using DATE function in VBA. Now we will see one of the projects using DATE function in VBA.
Assume you are an LIC agent and you have several customers to deal with. One of the key objects is to know whose payment due is on today so that you can call them and collect the payment immediately.
Assume below is the list of customers you have in your database.
I have already written a code which will notify you as soon as you open the excel file.
Sub Due_Notifier() Dim Duedate As Date Dim i As Long Duedate = Date i = 2 For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row If Duedate = DateSerial(Year(Date), Month(Cells(i, 3).Value), Day(Cells(i, 3).Value)) Then MsgBox "Customer Name : " & Cells(i, 1).Value & vbNewLine & "Premium Amount : " & Cells(i, 2).Value End If Next i End Sub
Copy the above code and paste in the VBA module.
Now double click on “This Workbook” option.
Now select “Workbook” from the above drop down.
As soon as you select the option “Workbook” you can see a private macro automatically opens.
Here macro name says “Workbook_Open ()” this means whenever this workbook opens what you have to do. Whenever this workbook opens we need to run the macro we have created.
So, here we need to call our macro we have created by its name. In the above code, our macro name is “Due_Notifier”.
Now save this workbook and close it.
After closing it, open the workbook and see the magic.
Now I will open….
Wow!!! It shows me customer name and their due amount for the current date.
Customer Name is “Amar” and the due amount is “20883”. The reason why it is showing this customer name because the due date for Mr. Amar is 15th March 2019 i.e. Today.
Now click on Ok, it will show other customer names if the due date is on today.
It is showing Mr. Arvind name, his due date is also on 15th March 2019.
Now, you can easily identify the customer names as soon as you come to the office. One of the big headaches is gone.
Similarly, I have created one more excel macro which will send auto birthday emails from your outlook.
VBA DATE – Example #3
Assume you are in an “Employee Engagement Team” and you are responsible to send birthday emails to your employees. Identify and sending the email to each and every one separately is a painful job.
Hello, my dear friend doesn’t worry I have created a macro for you to send the auto birthday emails to your employees.
I have created some data to test and below is the image of the same.
You just need to update the employee master according to the headings of the table. Below is the code to send the emails.
Copy the below code and paste in the module.
Sub Birthday_Wishes() Dim OutlookApp As Outlook.Application Dim OutlookMail As Outlook.MailItem Dim Mydate As Date Dim i As Long Set OutlookApp = New Outlook.Application Mydate = Date i = 2 For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row Set OutlookMail = OutlookApp.CreateItem(olMailItem) If Mydate = DateSerial(Year(Date), Month(Cells(i, 5).Value), Day(Cells(i, 5).Value)) Then OutlookMail.To = Cells(i, 7).Value OutlookMail.CC = Cells(i, 8).Value OutlookMail.BCC = "" OutlookMail.Subject = "Happy Birthday - " & Cells(i, 2).Value OutlookMail.Body = "Dear " & Cells(i, 2).Value & "," & vbNewLine & vbNewLine & _ "We wish you a happy birhday on behalf of the management and we wish all the success in the coming future" & vbNewLine & _ vbNewLine & "Regards," & vbNewLine & "StrIDE Team" OutlookMail.Display OutlookMail.Send End If Next i End Sub
As soon as you come to the office just open the file and run this code, it will automatically send birthday wishes to the respective email id’s,
This has been a guide to VBA Date Function. Here we learn how to use Date Function in VBA along with some simple to advanced examples. We also saw one of the projects using the DATE function in VBA where we created one macro that sends auto birthday emails from your outlook. Below are some useful excel articles related to VBA –
- VBA DateSerial
- Excel VBA Outlook
- Database in Excel
- VBA Worksheet Function
- VBA MOD
- VBA UBOUND
- VBA GoTo
- VBA InputBox
- VBA Select Case