VBA excel calendar/date picker has date issue-CodePudding

This comes from a video tutorial but when I go to run this the first day of the month does not appear in the correct day of the week for that month.

I believe (though I cannot confirm) that the issue is tied into the use of 'Year' on this line which can be found in ShowDate():

last_date = VBA.DateSerial(**Year**(first_date), Month(first_date)   1, 1) - 1

I can't help but to think that if Year is a problem in last_date then Month will also prove to be a similar problem, but ideally the solution will also be the same.

I've viewed similar tutorials and they all follow the same structure, but I run into the same problem. Not sure if it is a setting on my computer/excel program or something else entirely? The error code is

Compile Error: Wrong number of arguments or invalid property assignment

The full code is below.

    Private Sub cmbMonth_Change()

If Me.cmbMonth.Value <> "" And Me.cmbYear.Value <> "" Then
    Call ShowDate

End If
End Sub
Private Sub UserForm_Initialize()

Dim i As Integer

With Me.cmbMonth
    For i = 1 To 12
        .AddItem VBA.Format(VBA.DateSerial(2019, i, 1), "MMMM")
    Next i
    
    .Value = VBA.Format(VBA.Date, "MMMM")
    
End With

With Me.cmbYear
    For i = VBA.Year(Date) - 3 To VBA.Year(Date)   4
        .AddItem i
    Next i
    
    .Value = VBA.Format(VBA.Date, "YYYY")
    
End With

End Sub
Sub ShowDate()

Dim first_date As Date
Dim last_date As Date

first_date = VBA.CDate("1-" & Me.cmbMonth.Value & "_" & Me.cmbYear.Value)
last_date = VBA.DateSerial(Year(first_date), Month(first_date)   1, 1) - 1

Dim i As Integer
Dim btn As MSForms.CommandButton

''''to remove any caption from buttons
For i = 1 To 34
    Set btn = Me.Controls("CommandButton" & i)
    btn.Caption = ""
Next i

''''set first date of the month
For i = 1 To 7
    Set btn = Me.Controls("CommandButton" & i)
        If VBA.Weekday(first_date) = i Then
        btn.Caption = "1"
    End If
Next i


Dim btn1 As MSForms.CommandButton
Dim btn2 As MSForms.CommandButton

''''set all dates
For i = 1 To 33
    Set btn1 = Me.Controls("CommandButton" & i)
    Set btn2 = Me.Controls("CommandButton" & i   1)
    
    If btn1.Caption <> "" Then
        If VBA.CInt(btn1.Caption) < VBA.Day(last_date) Then
            btn2.Caption = btn1.Caption   1
        End If
  
Next i

End Sub

CodePudding user response：

So it turned out that an object, variable or function with the name Year is defined in the code (not in the piece that is shown). This Year hides the regular VBA function Year and led to the compiler message.

If you want to force VBA to take the build-in function, you can access it via the Global VBA-object, just write VBA.Year.

However, it is best practise to avoid such ambiguities by avoiding such names.