While working in Microsoft Excel Visual Basic for Applications (VBA), we may feel the necessity of using only the day value rather than the entire date. To do that, the usage of the Day function comes into action. The Day function is a built-in function of Excel VBA in the Date/Time Functions category that can extract days only out of an entire date in two digits of date format. The Day function is a data and day-type function that has only one argument or parameter. In this article, you will learn the syntax and usage of the Day function in Excel VBA with 3 relevant examples.
DAY Function in VBA Overview
- Function Objective:
We use the Day function to extract only the day from a complete date format.
- Syntax:
Day(date_value)
- Arguments Explanation:
| Argument | Required/Optional | Explanation |
|---|---|---|
| date_value | Required | A date from which you want to extract only the day value. |
- Return Parameter:
It returns a two-digit date ranging from 1 to 31.
1. Using VBA Day Function to Show Current Day in a Pop-up Window
Suppose, you’re working with the data in Excel. But if you don’t need to use the whole date then you would like to use the Day function to separate only the day out of the date format.
In this section, we will discuss how we can retrieve the current day using the Day function in Excel. We can get to know today’s date using the Now function in Excel.
But the Now function will return the complete form of a date with day, month, and year. But most probably you don’t want that.
So all we can do is, use the Day and the Now function together in Microsoft Excel VBA. In that situation, the Now function will return the complete date format, and then the Day function will filter that out to extract only the Day out of the date format.
Now let’s get into the procedural steps,
❶ First of all, open Microsoft Excel.
❷ Then open a new worksheet on a new workbook in Excel.
❸ After that press the ALT + F11 to open the Excel VBA editor.
❹ Then select Insert from ribbon and hit Module.
❺ After that copy the following VBA code.
Sub CurrentDay()
Dim CurrentDay As Integer
CurrentDay = Day(Now)
MsgBox CurrentDay, vbInformation, "Current Day"
End Sub❻ Now paste the VBA code and save it on Excel VBA editor.
❼ After that press the F5 button to run the code.
At this point, the Macros window will pop up.
❽ Select the Sheet1.CurrentDay function in the Macros window and hit the RUN command.
❾ After that, you will see a small window called Current Day has popped up returning the current day which is 11.
Read more: How to Get the Current Date in VBA
2. Using VBA DAY Function to Show Current Day in Excel Worksheet
In the previous example, we retrieved the current day out of the standard day format using the Day function in Excel. Now, we all return the current day using the Day function in Excel directly to the Excel worksheet.
To do that, we’ve allocated cell C4 to store the current day. So, when we run the Day function in Excel, we will get the current day in cell C4.
Now follow the steps below to get the whole procedure,
❶ Press ALT + F11 to open the VBA editor in Excel.
❷ Open a new Module from the Insert ribbon as in the previous example.
❸ Copy the following code:
Sub CurrentDayOnSheet()
gCurrentDay = Now
Sheets("Second Example").Range("C4") = Day(gCurrentDay)
End Sub❹ Now paste and save this code.
❺ After that press the F5 button to run the VBA code.
When you’ve run the above VBA code, you will get the current day in cell C4 of the Excel worksheet.
Read more: How to Insert Current Date in Excel
Similar Readings
- How to Use Excel Date Shortcut
- VBA Date to String Conversion in Excel (6 Methods)
- Now and Format Functions in Excel VBA (4 Examples)
- Date Variable in VBA Codes (7 Uses of Macros with Examples)
3. Using DAY Function in Excel VBA to Show Current Day Name
In this last example, we will show the day name in a pop-up window using the Day function in Excel VBA. When we execute the VBA code, a pop-up window called “Today is:” will appear. Where the day will appear.
To do this, follow the steps below:
❶ Press the ALT + F11 keys to open the VBA editor.
❷ Go to Insert ▶ Module.
❸ Then copy the following VBA code.
Sub GetCurDay()
Dim x As Date
x = Date
MsgBox Format(x, "DDDD")
End Sub❹ Now, paste and save this VBA code.
❺ After that press the F5 button to run the code.
After pressing the F5 button, you will see the following window appear, which shows the current day name. For example, Tuesday.
Read more: How to Get the Day of Week Using VBA
Things to Remember
📌 Use ALT + F11 Keys to open Excel VBA editor.
📌 Press F5 to run Excel VBA Code.
Download the Practice Workbook
You can download the Excel file from the link below and practice along with it.
Conclusion
To sum up, we have shown 3 examples to help guide using the VBA Day function in Excel. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.
