How to use Weekday function in VBA?

In VBA (Visual Basic for Applications), the `Weekday` function is used to get a numeric value representing the day of the week for a provided date. The value returned by the function follows the VBA `vbDayOfWeek` enumeration, where by default Sunday is 1, Monday is 2, and so on through Saturday, which is 7. However, you can specify a different starting day for the week by using the second optional argument.

Here is the syntax for the `Weekday` function:

Weekday(Date, [FirstDayOfWeek])

`Date`: The date for which you want to find the day of the week.
`FirstDayOfWeek` (optional): A constant that specifies the first day of the week. If this parameter is omitted, `vbSunday` is assumed.

Here are the constants you can use for `FirstDayOfWeek`:

`vbUseSystemDayOfWeek` (0): Use the National Language Support (NLS) API setting.
`vbSunday` (1): Sunday (default)
`vbMonday` (2): Monday
`vbTuesday` (3): Tuesday
`vbWednesday` (4): Wednesday
`vbThursday` (5): Thursday
`vbFriday` (6): Friday
`vbSaturday` (7): Saturday

Here’s an example of using the `Weekday` function in VBA code:

Sub ExampleWeekday()
    Dim dt As Date
    Dim dayOfWeek As Integer
    Dim firstDay As Integer
    
    ' Define a date value
    dt = DateSerial(2023, 4, 1) ' April 1st, 2023
    
    ' Get the day of the week using default first day (Sunday)
    dayOfWeek = Weekday(dt)
    
    ' Print the result in Immediate Window (press Ctrl+G to view the Immediate Window)
    Debug.Print "Day of the week (default): " & dayOfWeek
    
    ' Now, let's assume the first day of the week is Monday
    firstDay = vbMonday
    
    ' Get the day of the week with Monday as the first day of the week
    dayOfWeek = Weekday(dt, firstDay)
    
    ' Print the result
    Debug.Print "Day of the week (Monday as first day): " & dayOfWeek
End Sub

In this example, the `Weekday` function is used twice: once with the default `FirstDayOfWeek`, and once with `vbMonday` to indicate that the week starts on Monday. The results are then printed to the Immediate Window. The date provided is April 1st, 2023. Adjust the `DateSerial` function arguments to test different dates.

facebooklinkedin