EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home VBA VBA Resources VBA Date & Time Functions VBA Date
Secondary Sidebar
VBA Date & Time Functions
  • VBA Date and Time
    • VBA Month
    • VBA DateSerial
    • VBA Date Format
    • VBA Time
    • VBA TIMER
    • VBA Date
    • VBA Format Number
    • VBA DateAdd
    • VBA DateValue
    • VBA DatePart
    • VBA DateDiff
    • VBA Format Date

VBA Date

By Madhuri ThakurMadhuri Thakur

VBA Date

Excel VBA Date

There are some functions which are really very handy, and we choke our life without those functions being a VBA user. DATE function is one of those functions which can be really very useful at times and can make life easier for a programmer. In an Excel spreadsheet, there is a function called TODAY() which gives the current date as a result based on the system date. On similar lines, VBA has DATE function which gives current date based on the system date.

All in One Financial Analyst Bundle(250+ Courses, 40+ Projects)
Financial ModelingInvestment BankingUS GAAPCFA-Level 1 & 2
Equity ResearchM & A ModelingPrivate Equity ModelingForex Trading
Price
View Courses
250+ Online Courses | 40+ Projects | 1000+ Hours | Verifiable Certificates | Lifetime Access
4.9 (86,088 ratings)

The VBA DATE function returns the current date based on system date as a result and has really very simple syntax.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

This function does not have any argument to be passed comes with the name of the function and empty parentheses. It is not mandatory to add the parentheses as well while calling this function. Isn’t this function really simple in nature?

The syntax of DATE function in VBA.

Syntax of DATE

How to Use Excel VBA Date Function?

We will learn how to use a VBA Date function with few examples in excel.

You can download this VBA Date Excel Template here – VBA Date Excel Template

VBA Date Function – Example #1

Suppose, you wanted to see the current date in MsgBox. How can you do that? Just follow the steps below and you’ll be through.

Step 1: Insert a new module in your Visual Basic Editor.

Example 1

Step 2: Define a sub-procedure to write create and save a macro.

Code:

Sub DateEx1()

End Sub

VBA Date Example 1-2

Step 3: Define a variable named CurrDate which can hold the value of the current date. Since, we are about to assign a date value to the variable, make sure you are defining it as a date.

Code:

Sub DateEx1()

Dim CurrDate As Date

End Sub

VBA Date Example 1-3

Step 4: Using the assignment operator, assign a value of the current system date to the variable newly created. You just need to add DATE in order to assign the date value. Use the following piece of code:

Code:

Sub DateEx1()

Dim CurrDate As Date

CurrDate = Date

End Sub

VBA Date Example 1-4

Step 5: Use MsgBox to be able to see the current system date under Message Box prompt. Use the line of code given below:

Code:

Sub DateEx1()

Dim CurrDate As Date

CurrDate = Date

MsgBox "Today's Date is: " & CurrDate

End Sub

VBA Date Example 1-5

Step 6: Hit F5 or run button manually to Run this code. You’ll be able to see a Message Box as shown in below screenshot with the current date.

Result of Example 1

Note that, the date shown here in the screenshot is the date I have run this script at. You may be getting a different date at the time you run this code, based on your system date.

This is the simplest example of getting the current date. You can also use Cells.Value function to get the date value in a particular cell of your excel sheet.

VBA Date Function – Example #2

Home Loan EMI payment due date

Suppose I have a worksheet and I need a system to show me a message “Hey! You need to pay your EMI today.” Every time I open my sheet and the value in cell A1 is the current system date. Let’s see step by step how we can do that.

Step 1: Insert a new module and define a new sub-procedure named auto_open() to create a macro. auto_open() allows your macro to run automatically every time you open the worksheet.

Code:

Sub auto_open()

End Sub

VBA Date Example 2-1

Step 2: Use If condition to assign the value of the current date in cell A1 of worksheet HomeLoan_EMI.

Code:

Sub auto_open()

If Sheets("HomeLoan_EMI").Range("A1").Value = Date

End Sub

VBA Date Example 2-2

Step 3: Now, use Then on the same line after IF so that we can add a statement which will execute as long as if-condition is true.

Code:

Sub auto_open()

If Sheets("HomeLoan_EMI").Range("A1").Value = Date Then

End Sub

VBA Date Example 2-3

Step 4: Add a statement to be executed for the condition which is true.

Code:

Sub auto_open()

If Sheets("HomeLoan_EMI").Range("A1").Value = Date Then

MsgBox ("Hey! You need to pay your EMI today.")

End Sub

VBA Date Example 2-4

This statement will pop-up under Message Box as soon as the If a condition is true.

Step 5: As we know, every IF condition always needed an Else condition. Add an Else condition to this loop.

Code:

Sub auto_open()

If Sheets("HomeLoan_EMI").Range("A1").Value = Date Then

MsgBox ("Hey! You need to pay your EMI today.")

Else

Exit Sub

End Sub

VBA Date Example 2-5

This else condition will terminate the automatic opening of Macro if a date in cell A1 is not the current system date.

Step 6: Finally, End the IF loop by using statement End IF.

Code:

Sub auto_open()

If Sheets("HomeLoan_EMI").Range("A1").Value = Date Then

MsgBox ("Hey! You need to pay your EMI today.")

Else

Exit Sub

End If

End Sub

VBA Date Example 2-6

Step 7: This is it, now every time you open your worksheet the system will automatically run the above code and see if the date value in cell A1 is your EMI due date or not. If the EMI due date equals to the system date, it will show the message as below:

Result of Example 2

VBA Date Function – Example #3

VBA Date to Find out the Credit Card Bill Payee

Suppose I have a list of customers who have a credit card and you want to know who has payment due today. So that you can call them and ask them to pay their due immediately by EOD.

Example 3

VBA Date could be handy in allowing you to automate the things instead of checking the dates one by one. Let’s see how to do this step by step:

Step 1: Define a New macro using a sub-procedure under a module.

Code:

Sub DateEx3()

End Sub

VBA Date Example 3-2

Step 2: Define two new variables one of which will be useful in looping the code up and another one in order to hold the value of the current system date.

Code:

Sub DateEx3()
Dim DateDue As Date
Dim i As Long
DateDue = Date
i = 2
End Sub

VBA Date Example 3-3

Step 3: Now use the following piece of code which helps in searching the person who has a credit card bill due date as the current system date. This code allows checking the Customer who has bill payment due on the current system date along with the bill amount.

Code:

Sub DateEx3()
Dim DateDue As Date
Dim i As Long
DateDue = Date
i = 2
For i = 2 To Sheets("CC_Bill").Cells(Rows.Count, 1).End(xlUp).Row
If DateDue = DateSerial(Year(DateDue), Month(Sheets("CC_Bill").Cells(i, 3).Value), 
Day(Sheets("CC_Bill").Cells(i, 3).Value)) Then
MsgBox "Customer Name : " & Sheets("CC_Bill").Cells(i, 1).Value & vbNewLine 
& "Premium Amount : " & Sheets("CC_Bill").Cells(i, 2).Value
End If
Next i
End Sub

VBA Date Example 3-4

Step 4: Run this code by hitting F5 or Run button manually and see the output.

VBA Date Example 3-5

On the first iteration, we can see that Mohan is the one who has Bill of 12,900 due on 29-Apr-2019 (Current system date on which this code is run). If we hit OK, we can see the next customer name who has a bill due on 29-Apr-2019 (Rajani is the next).

Result of Example 3-1

This code will really be handy when you are having millions of rows of customers who have their bill due on one particular day. Please note that all the scripts mentioned in this article are run on 29-Apr-2019. You might get different date value when you run this sample codes based on the system date.

Result of Example 3-2

Things to Remember

  • VBA DATE function returns the current system date and as parallel to Excel’s TODAY() function.
  • VBA DATE function does not have any argument to be passed in excel. Doesn’t even need the parentheses to be called while using this function in the code.
  • VBA DATE is a non-volatile function in excel.
  • VBA stores Date values as DATE at the time of execution. So, does not define a variable holding value as a String/Integer. It will cause an error during execution of the code.

Recommended Articles

This has been a guide to Excel VBA Date. Here we have discussed how to use Excel VBA Date Functions along with practical examples and downloadable excel template. You can also go through our other suggested articles –

  1. VBA Date Format
  2. VBA GoTo
  3. VBA RGB
  4. VBA DateSerial
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign In
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more