Break-down required of UDF-function: fGetFirstButtonDate

[Irmaxx]

Hi,

Since Excel2003 or Excel2007 (I am not sure) there is no calendar function anymore.
So the last few years I have been trying to, somehow, recreate that calendar.
The one I am using now works perfectly, but I need some help explaning a piece of code I retained from somewhere(?), but I can't break it down (so to speak).

I am using the following UDF-functions in VBA Excel2016:

Public Function fGetFirstButtonDate(p_dtThisDate As Date) As Date
'Calculate the first DAYnumber from a specific date as starting point.
'==> Param [p_dtThisDate] = current date OR selected month/year with current daynumber.
    
    fGetFirstButtonDate = p_dtThisDate - ((Day(p_dtThisDate) \ 7 + IIf(Day(p_dtThisDate) Mod 7 = 0, 0, 1)) * 7 + Format(p_dtThisDate, "w", vbTuesday))

End Function

Public Function fGetTotDaysOfMonth(p_dtInitDate As Date, p_iFactor As Integer) As String
'Determine the total days of a specific month.
'==> Param [p_dtInitDate] = specific date.

    fGetTotDaysOfMonth = Day(DateSerial(Year(p_dtInitDate), Month(p_dtInitDate) + p_iFactor, 1) - 1)

End Function

This function is triggered by the following piece of code (see code in RED):

Private Sub NewMonth()
'The month of the selected date ("frmCalendar.Tag") is shown with weeknumbers.

    Dim dtThisDate     As Date
    Dim dtButtonDate   As Date
    Dim dtFirstWeekDay As Date
    Dim sTotDaysMonth  As String
    Dim bNotFirstPass  As Boolean

    bNotFirstPass = False
    With frmCalendar
        'First letter of the current month is written with a capitol.
        .lblNavMonthYear.Caption = WorksheetFunction.Proper(Format(.Tag, "mmmm  yyyy"))
        'Assign todays date to variable.
        dtThisDate = CDate(.Tag)
    End With
    Dim i As Integer
    For i = 0 To 41
        'When first weekday number has to be calculated, then continue.
        If bNotFirstPass = False Then
            'GoTo FUNC: [Calculate the first DAYnumber from a specific date as starting point].
            dtButtonDate = fGetFirstButtonDate(dtThisDate)
            'GoTo FUNC: [Determine the total days of a specific month].
            sTotDaysMonth = fGetTotDaysOfMonth(dtThisDate, 0)
            'First calendar row MUST contain at least ONE day of the selected month.
            If Day(dtButtonDate) < (sTotDaysMonth - 6) Or Day(dtButtonDate + 6) = sTotDaysMonth Then
                 dtButtonDate = (dtButtonDate + 7)
            End If
            dtFirstWeekDay = dtButtonDate
            bNotFirstPass = True
        Else
            dtButtonDate = (dtButtonDate + 1)
        End If
        'Calculate the WEEK-numbers on the first passthrough.
        If i = 0 Then
            Dim j As Integer
            For j = 0 To 5
                'GoTo FUNC: [Calculate [weeknumber] from a specific date as starting point].
                frmCalendar("lblWeek" & j + 1).Caption = fGetWeekNumber(dtFirstWeekDay)
                dtFirstWeekDay = (dtFirstWeekDay + 7)
            Next
        End If
        'All the 'day-buttons' are getting a DAY-number.
        With frmCalendar("lblDay" & i + 1)
            .Caption = Format(dtButtonDate, "d")
            'GoTo FUNC: [Calculate [weeknumber] from a specific date as starting point].
            'If WEEK-number of current date equals WEEK-number of the first day of the calendar,...
            If fGetWeekNumber(Date) = fGetWeekNumber(dtButtonDate) _
               And Year(Date) = Year(dtThisDate) Then
                '...then BackStyle and BorderStyle are DARK-GREY(&H00DDDDDD&).
                .BackStyle = fmBackStyleOpaque
                .BorderStyle = fmBorderStyleSingle
            Else
                '...else BackStyle and BorderStyle LIGHT-GREY [=background] (&H80000004&).
                .BackStyle = fmBackStyleTransparent
                .BorderStyle = fmBorderStyleNone
            End If
            'Establish whether it's a WEEK-day or a WEEKEND-day.
            If (i = 5 Or i = 6) Or _
               (i = 12 Or i = 13) Or _
               (i = 19 Or i = 20) Or _
               (i = 26 Or i = 27) Or _
               (i = 33 Or i = 34) Or _
               (i = 40 Or i = 41) Then
                'In case of a WEEKEND-day (sat/sun), then DAY-number is BOLD.
                .Font.Bold = True
                'In case the MONTH-number of the button equals the MONTH-number of current date,...
                '...then Fontcolor is BLUE (&H00D77800&) and ELSE it's GREY (&H00AFB9BC&).
                .ForeColor = IIf(Month(dtButtonDate) = Month(dtThisDate), 14120960, 11516348)
                'In case the MONTH-number of the button equals the MONTH-number of current date AND...
                '...DAY-number on the button equals DAY-number of current date, ....
                If Month(dtButtonDate) = Month(dtThisDate) Then
                    '...then BorderColor = DARK-RED (&H000000C0&)and ELSE it's LIGHT-GREY [=background] (&H80000004&).
                    .BorderColor = IIf(dtButtonDate = Date, 192, -2147483644)
                    '...and FontColor = DARK-RED (&H000000C0&)and ELSE it's BLUE (&H00D77800&).
                    .ForeColor = IIf(dtButtonDate = Date, 192, 14120960)
                Else
                    '...else BorderColor = LIGHT-GREY [background] (&H80000004&)...
                    .BorderColor = -2147483644
                    '...and FontColor = GREY (&H00AFB9BC&)
                    .ForeColor = 11516348
                End If
            Else
                'In case of a WEEK-day (mon/fri), then DAY-number is REGULAR (not bold).
                .Font.Bold = False
                'In case the MONTH-number of the button equals the MONTH-number of current date,...
                '...then Fontcolor is ANTRACITE-BLACK (&H00404040&) and ELSE it's GREY (&H00AFB9BC&).
                .ForeColor = IIf(Month(dtButtonDate) = Month(dtThisDate), 4210752, 11516348)
                'In case the MONTH-number of the button equals the MONTH-number of current date AND...
                '...DAY-number on the button equals DAY-number of current date, ....
                If Month(dtButtonDate) = Month(dtThisDate) Then
                    '...then BorderColor = DARK-RED (&H000000C0&)and ELSE it's LIGHT-GREY [=background] (&H80000004&).
                    .BorderColor = IIf(dtButtonDate = Date, 192, -2147483644)
                    '...and FontColor = DARK-RED (&H000000C0&)and ELSE it's ANTRACITE-BLACK (&H00404040&).
                    .ForeColor = IIf(dtButtonDate = Date, 192, 4210752)
                Else
                    '...else BorderColor = LIGHT-GREY [background] (&H80000004&)...
                    .BorderColor = -2147483644
                    '...and FontColor = GREY (&H00AFB9BC&)
                    .ForeColor = 11516348
                End If
            End If
        End With
    Next
    'GoTo PROC: [Establish the (in)visibility of the [Today]-button].
    Call TodayButtonVisibility

End Sub

My question revolves around this piece of code: ((Day(p_dtThisDate) \ 7 + IIf(Day(p_dtThisDate) Mod 7 = 0, 0, 1)) * 7 + Format(p_dtThisDate, "w", vbTuesday))

If the date is set to 17-01-2022 (January 17th) then the following values are 'calculated':

VBA - Controls.jpg

It ends in a calendar that looks like this:

Calendar.jpg

If someone could help me out, that would be awesome!

[alansidman]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)