Trending September 2023 # How To Use Excel Vba Date Function With Examples? # Suggested October 2023 # Top 10 Popular | Dacvumuahe.com

Trending September 2023 # How To Use Excel Vba Date Function With Examples? # Suggested October 2023 # Top 10 Popular

You are reading the article How To Use Excel Vba Date Function With Examples? updated in September 2023 on the website Dacvumuahe.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 How To Use Excel Vba Date Function With Examples?

Excel VBA Date

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

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

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

The syntax of the DATE function in VBA.

How to Use Excel VBA Date Function?

We will learn how to use a VBA Date function with a 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 2: Define a sub-procedure to write, create and save a macro.

Code:

Sub

DateEx1()

End Sub

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

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

Code:

Sub

DateEx1()

Dim

CurrDate

As Date

CurrDate = Date

End Sub

Step 5: Use MsgBox to see the current system date under the 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

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

Note that the date shown in the screenshot is the date I have run this script. You may get a different date when 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 need a system to show me the message “Hey! You need to pay your EMI today.” Every time I open my sheet, the current system date is the value in cell A1. 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 whenever you open the worksheet.

Code:

Sub

auto_open()

End Sub

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

Code:

Sub

auto_open()

If

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

End Sub

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

Code:

Sub

auto_open()

If

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

Then

End Sub

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

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 needs 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

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 using the 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

Step 7: This is it; 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. If the EMI due date equals the system date, it will show the message below:

VBA Date Function – Example #3

VBA Date to Find Out the Credit Card Bill Payee

VBA Date could be handy in allowing you to automate 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

Step 2: Define two new variables, one of which will be helpful in looping the code up and another 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

Step 3: Now use the following piece of code, which helps search for the person with 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 and 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

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

On the first iteration, we can see that Mohan is the one who has a Bill of 12,900 due on 29-Apr-2023 (The 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-2023 (Rajani is the next).

This code will be handy when millions of customers have their bills due on one particular day. Please note that all the scripts mentioned in this article run on 29-Apr-2023. You might get different date values when you run these sample codes based on the system date.

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. It doesn’t 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, it does not define a variable holding value as a String/Integer. It will cause an error during the 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, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –

You're reading How To Use Excel Vba Date Function With Examples?

Update the detailed information about How To Use Excel Vba Date Function With Examples? on the Dacvumuahe.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!