Excel VBA自動產生台指結算日

手邊有一些關於台指期VBA檔案都需要參考到結算日來計算，但結算日常需要手動輸入，今天花了一點時間寫了一個小程式，解決需要手動輸入台指期結算日的日期，這樣只要針對特定的日期去做調整就好。
程式的重點在於程式碼的16、38行的內容，尤其16行宣告為 nCount *0 與 0*0 的2維陣列，因為要讓陣列內容直接放入儲存格中，因此宣告 N*N 與 N*N 的2維陣列。若以N*1的1維陣列，38行中儲存格資料內容只會存入陣列第0個位置的資料，這部分需要多注意。
台指結算日為"每個月第三週的星期三"結算，有了結算日的觀念後，來看以下程式碼就會很快上手。

Sub 自動產生台指結算日()
    Dim dPastdate As Date
    Dim dBasedate As Date
    Dim dCurrentdate As Date
    Dim nCount As Integer, nGap As Integer
    Dim i As Integer, j As Integer
    Dim astrDatelist() As String

    With Workbooks(1).Sheets("結算日")
        .Range(.Cells(2, 1), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, 1)).Clear
    End With
    
    '以當月日期為基準前後計算10個月結算日
    nGap = 10
    nCount = nGap * 2 + 1
    ReDim astrDatelist(0 To nCount, 0 To 0)
    
    dPastdate = DateAdd("m", nGap * (-1), Date)
    For i = 0 To nCount
        dBasedate = DateAdd("m", i, DateSerial(Year(dPastdate), Month(dPastdate), 1))
        For j = 14 To 24
            dCurrentdate = dBasedate + j
            If 日期是當月第三週星期三(dCurrentdate) Then
                astrDatelist(i, 0) = Format(Year(dCurrentdate), "0000") & Format(Month(dCurrentdate), "00") & Format(Day(dCurrentdate), "00")
                Exit For
            End If
        Next
    Next
    
    With Workbooks(1).Sheets("結算日")
        For i = 2 To .Cells(Rows.Count, 2).End(xlUp).Row
            For j = LBound(astrDatelist) To UBound(astrDatelist)
                If Mid(astrDatelist(j, 0), 1, 6) = Mid(.Cells(i, 2), 1, 6) Then
                    astrDatelist(j, 0) = .Cells(i, 2)
                End If
            Next
        Next
        .Range(.Cells(2, 1), .Cells(2 + nCount, 1)).Value = astrDatelist
    End With
End Sub

Function 日期是當月第三週星期三(dBasedate As Date) As Boolean
    Dim weekofMonth As Integer, dayofweek As Integer
    日期是當月第三週星期三 = False
    weekofMonth = DatePart("ww", dBasedate) - DatePart("ww", DateSerial(Year(dBasedate), Month(dBasedate), 1))
    dayofweek = weekday(DateSerial(Year(dBasedate), Month(dBasedate), 1), 2)
    If dayofweek <= 3 Or dayofweek = 7 Then
        weekofMonth = weekofMonth + 1
    End If
    If weekofMonth = 3 And DatePart("w", dBasedate, 2) = 3 Then
        日期是當月第三週星期三 = True
    End If
End Function

執行結果：

結算日特性：

1. 每月第三周星期三。
2. 每月結算日落在14~24號之間。
3. 每月1號在星期三之前(含)，以當月第三周星期三為結算日。
4. 每月號在星期日，以當月第三周星期三為結算日。
5. 每月1號在星期三之後，以當月第四周星期三為結算日。

執行畫面中，有一欄 "特殊結算日"，這需手動輸入，目的是若當月結算日未依照 "每個月第三週的星期三" 這樣法則，如結算日遇到年節封關提早、農曆年節提早、7月~8月颱風延後，就需要另外輸入日期，以便日期比對可以完整。

參考資料：

• [VBA] 陣列 一次 填滿 工作表 Sheet
• 以陣列變數寫入EXCEL 儲存格範圍可節省運算時間