Excel - VBA 每月的第一个工作日

Question

我有一个 workbook_open 宏，如下所示；

Private Sub Workbook_Open()
Sheets("Staff List").Activate

If Range("G1") = "Yes" Then

Sheets(Format(Now, "mmmm")).Activate

Range("A1").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

End If

End Sub

G1中的公式如下；
=IF(OR(TODAY()=WORKDAY(EOMONTH(TODAY(),-1),1,0),TODAY()=WORKDAY(EOMONTH(TODAY(),-1),2,0)),"Yes","No")
所以检查一下今天是一个月的第一个工作日还是第二个工作日。如何消除引用 G1 的需要，而是将公式直接用于 VBA？

非常感谢

Answer 1

不使用工作表函数的方法:

Function WorkdayOfMonth(ByVal dtInput As Date) As Integer
    Dim dt As Date
    Dim i As Integer
    If Weekday(dtInput, vbMonday) < 6 Then
        For dt = DateSerial(Year(dtInput), Month(dtInput), 1) To dtInput Step 1
            If Weekday(dt, vbMonday) < 6 Then i = i + 1
        Next dt
        WorkdayOfMonth = i
    Else
        WorkdayOfMonth = 0
    End If
End Function

Sub Test()
    If WorkdayOfMonth(Date) > 0 and WorkdayOfMonth(Date) <= 2 Then
        'do stuff
    End If
End Sub