VBA WeekdayName Function

The VBA WeekdayName function is a powerful tool that allows you to retrieve the name of a weekday from a specific date. This function is particularly useful when dealing with dates and data that need to be organized according to weekdays. It helps in automating tasks and saving time while coding in VBA.

VBA WeekdayName Function – Purpose, Syntax and Arguments

Purpose:

The purpose of the WeekdayName function is to return the name of a weekday from a given date. It helps in retrieving the weekday in a user-friendly format, making it easier to display, print, or manipulate in a VBA code.

Syntax:

WeekdayName(Weekday, [Abbreviate], [FirstDayOfWeek])

Arguments:

• Weekday: This is a required argument that specifies the weekday for which you want to retrieve the name. It can be a number from 1 to 7, where 1 represents Sunday and 7 represents Saturday.
• [Abbreviate]: This is an optional argument that specifies whether the weekday name should be abbreviated. It can take either True or False as values. By default, it is set to False, meaning the full weekday name will be returned.
• [FirstDayOfWeek]: This is also an optional argument that specifies the first day of the week. It can take a number from 1 to 7, where 1 represents Sunday and 7 represents Saturday. By default, it is set to the system’s setting value.

Example:

Dim myDate As Date
myDate = #6/16/2021#
MsgBox "The weekday is " & WeekdayName(Weekday(myDate))

Remarks:

• The WeekdayName function is only available in VBA. It cannot be used in Excel formulas.
• If the ‘Weekday’ argument is not within the range of 1 to 7, the function will return an empty string.
• If the ‘Abbreviate’ argument is set to True, the abbreviated weekday name will be returned. For example, Sat instead of Saturday.
• The WeekdayName function is not case-sensitive. It will return the same result for both uppercase and lowercase weekday names.

Important Notes:

• The WeekdayName function is affected by the ‘FirstDayOfWeek’ argument. If the system’s setting is changed to another day, the returned weekday name will also change accordingly.
• This function can also be used to retrieve weekday names from cell values by first converting the cell value to a date using the VBA ‘CDate’ function.

Understanding VBA WeekdayName Function with Examples

Example 1: Basic Syntax and Outputs

The WeekdayName function in VBA is used to return the weekday name of a given date. It takes two arguments, the first being the weekday number (from 1 to 7) and the second being optional to specify the first day of the week (from 1 to 7). If the second argument is omitted, the default first day of the week is Sunday (1).

Dim day As Integer
day = 3 ' Wednesday
MsgBox WeekdayName(day) ' Output: "Wednesday"

1. First, we declare a variable day of type Integer and assign it a value of 3, which represents Wednesday (since it is the 3rd day of the week).
2. Then, we use the WeekdayName function with day as the first argument and no second argument (indicating the default first day of the week, which is Sunday).
3. The function returns the weekday name of the given number, in this case, “Wednesday”.
4. Finally, we use the MsgBox function to display the returned weekday name in a message box.

This basic example shows how the WeekdayName function can be used to easily return the weekday name of a given number without having to manually specify the weekday names.

Example 2: Specifying the First Day of the Week

The second argument of the WeekdayName function allows us to specify the first day of the week. This can be useful if your week starts on a different day than the default Sunday.

Dim day As Integer
day = 2 ' Tuesday
MsgBox WeekdayName(day, 2) ' Output: "Monday"

1. First, we declare a variable day of type Integer and assign it a value of 2, which represents Tuesday.
2. Then, we use the WeekdayName function with day as the first argument and 2 as the second argument (indicating that Monday is the first day of the week).
3. The function returns the weekday name of the given number, in this case, “Monday”.
4. Finally, we use the MsgBox function to display the returned weekday name in a message box.

In this example, we can see how changing the second argument can change the returned weekday name. This can be useful in situations where the first day of the week is not Sunday and you want to get the correct weekday name.

Example 3: Using the ‘vbUseSystem’ Constant

The WeekdayName function also allows us to use the ‘vbUseSystem’ constant as the second argument to automatically use the first day of the week specified in the user’s regional settings.

Dim day As Integer
day = 4 ' Thursday
MsgBox WeekdayName(day, vbUseSystem) ' Output: "Wednesday"

1. First, we declare a variable day of type Integer and assign it a value of 4, which represents Thursday.
2. Then, we use the WeekdayName function with day as the first argument and vbUseSystem as the second argument.
3. The function will use the first day of the week specified in the user’s regional settings (in this example, it is set to Wednesday) and return the weekday name of the given number.
4. Finally, we use the MsgBox function to display the returned weekday name in a message box.

This functionality is particularly useful when working with multiple users from different regions, as it ensures that the correct first day of the week is used for each individual user.

Example 4: Handling Invalid Inputs

If the first argument of the WeekdayName function is not within the range of 1 to 7, an error will occur. To avoid this, we can use a simple ‘If’ statement to check for invalid inputs and handle them appropriately.

Dim day As Integer
day = 8 ' Invalid weekday number
If day > 0 And day <= 7 Then
  MsgBox WeekdayName(day) ' Output: "Invalid procedure call or argument"
Else
  MsgBox "Invalid weekday number"
End If

1. First, we declare a variable day of type Integer and assign it an invalid value of 8, which is outside the range of 1 to 7.
2. Next, we use an ‘If’ statement to check if day is greater than 0 and less than or equal to 7.
3. If the condition is met, the WeekdayName function will be called and the returned value will be displayed in a message box. However, if the condition is not met, it will display a custom message stating the invalid weekday number.

This example shows how we can handle potential errors that may occur while using the WeekdayName function. It is always a good practice to check for invalid inputs and handle them appropriately to avoid any unexpected errors.

Conclusion

The WeekdayName function in VBA is a useful tool for returning the weekday name of a given number. By understanding its basic syntax and usage, as well as its various optional arguments, you can make use of this function to dynamically retrieve weekday names in your VBA projects. Remember to always handle invalid inputs and specify the correct first day of the week to get accurate results. With this knowledge, you can effectively use the WeekdayName function in your VBA coding for a smoother and more efficient workflow.