'源泉所得税高計算書
Option Explicit

Private mobjWS() As Worksheet   'ワークシート
'mobjWS(0)：Sheet1
'mobjWS(1)：Sheet5

'集計単位選択
Private Sub cboTuki_Change()

    Dim objText As MSForms.TextBox  'テキストボックス
    Dim i As Integer                '整数型カウンタ
    
    For i = 1 To 12
        Set objText = Controls("txtNum" & CStr(i))
        objText.Text = ""
    Next
    
    If Me.cboTuki.ListIndex > -1 Then
        If Me.cmdOK.Enabled = False Then Me.cmdOK.Enabled = True
    End If
    
    Set objText = Nothing
    
End Sub

'納期の特例選択
Private Sub chkTokurei_Click()

    Dim f As Integer    'フラグ
    
    If Me.chkTokurei.Value = 0 Then f = 0
    If Me.chkTokurei.Value = -1 Then f = 1
    
    If Me.cmdOK.Enabled = True Then Me.cmdOK.Enabled = False
    
    Call SetTani(f)
    
End Sub

'閉じるボタン
Private Sub cmdEND_Click()

    Unload Me
    
End Sub

'集計ボタン
Private Sub cmdOK_Click()

    Dim intIndex As Integer 'リストインデックス
    
    If Me.chkTokurei.Value = -1 Then
        If Me.cboTuki.ListIndex = 0 Then intIndex = 13
        If Me.cboTuki.ListIndex = 1 Then intIndex = 14
    ElseIf Me.chkTokurei.Value = 0 Then
        intIndex = Me.cboTuki.ListIndex + 1
    Else
        GoTo ErrorProc
    End If
    
    Call Syukei(intIndex)
    
    Exit Sub
    
ErrorProc:

    MsgBox "値が不正です。", 16 + 0, "給与計算システム"
    
End Sub

'フォームロード時
Private Sub UserForm_Initialize()

    Dim f As Integer    'フラグ
    
    If Me.chkTokurei.Value = 0 Then f = 0
    If Me.chkTokurei.Value = -1 Then f = 1
    
    Call NewData
    Call SetTani(f)
    
End Sub

'フォームクローズ
Private Sub UserForm_Terminate()

    Dim i As Integer
    
    'オブジェクトの開放
    For i = 0 To 1
        Set mobjWS(i) = Nothing
    Next
    
End Sub

'集計
Private Sub Syukei(ByVal Target As Integer)

'引数[Target]：対象月

    Dim clsData As clsSalary    '源泉所得税集計クラス
    Dim A() As Variant          '金額データ
    Dim B() As Variant          'クラスのプロパティ値
    Dim intNen As Integer       '対象年
    Dim intTuki As Integer      '対象月
    Dim lngRow As Long          'レコード
    Dim i As Long               '長整数型カウンタ
    Dim j As Long               '長整数型カウンタ
    Dim k As Long               '長整数型カウンタ
    
    '配列再定義
    ReDim A(10) As Variant      '「6」以降のインデックスはダミー
    ReDim B(24) As Variant
    
    '対象年の取得
    intNen = Year(CDate(mobjWS(0).Cells(3, 1).Value))
    'レコード数の取得
    lngRow = mobjWS(1).Cells(65536, 1).End(xlUp).Row
    'クラスのインスタンス
    Set clsData = New clsSalary
    
    'NULL値回避
    For i = 0 To 10
        A(i) = 0
    Next
    
    '給与賞与集計
    j = 0
    k = 0
    For i = 1 To lngRow
        If intNen = Year(CDate(mobjWS(1).Cells(i, 4).Value)) Then
            If Target < 13 Then
                intTuki = Me.cboTuki.ListIndex + 1
                If intTuki = Month(CDate(mobjWS(1).Cells(i, 4).Value)) Then
                    clsData.GetProperty mobjWS(1), i, B(0), B(1), B(2), B(3), B(4), B(5), B(6), B(7), B(8), B(9), B(10), _
                                B(11), B(12), B(13), B(14), B(15), B(16), B(17), B(18), B(19), B(20), B(21), B(22), B(23), B(24)
            
                    If B(2) = 0 Then        '給与
                        clsData.DataSum A(0), A(1), A(2), A(6), A(7), A(8), A(9), A(10)
                        j = j + 1
                    ElseIf B(2) = 1 Then    '賞与
                        clsData.DataSum A(3), A(4), A(5), A(6), A(7), A(8), A(9), A(10)
                        k = k + 1
                    End If
                End If
            ElseIf Target = 13 Then
                intTuki = 7
                If intTuki > Month(CDate(mobjWS(1).Cells(i, 4).Value)) Then
                    clsData.GetProperty mobjWS(1), i, B(0), B(1), B(2), B(3), B(4), B(5), B(6), B(7), B(8), B(9), B(10), _
                                B(11), B(12), B(13), B(14), B(15), B(16), B(17), B(18), B(19), B(20), B(21), B(22), B(23), B(24)
            
                    If B(2) = 0 Then        '給与
                        clsData.DataSum A(0), A(1), A(2), A(6), A(7), A(8), A(9), A(10)
                        j = j + 1
                    ElseIf B(2) = 1 Then    '賞与
                        clsData.DataSum A(3), A(4), A(5), A(6), A(7), A(8), A(9), A(10)
                        k = k + 1
                    End If
                End If
            ElseIf Target = 14 Then intTuki = 6
                If intTuki < Month(CDate(mobjWS(1).Cells(i, 4).Value)) Then
                    clsData.GetProperty mobjWS(1), i, B(0), B(1), B(2), B(3), B(4), B(5), B(6), B(7), B(8), B(9), B(10), _
                                B(11), B(12), B(13), B(14), B(15), B(16), B(17), B(18), B(19), B(20), B(21), B(22), B(23), B(24)
            
                    If B(2) = 0 Then        '給与
                        clsData.DataSum A(0), A(1), A(2), A(6), A(7), A(8), A(9), A(10)
                        j = j + 1
                    ElseIf B(2) = 1 Then    '賞与
                        clsData.DataSum A(3), A(4), A(5), A(6), A(7), A(8), A(9), A(10)
                        k = k + 1
                    End If
                End If
            End If
        End If
    Next
    
    'コントロールに表示
    Me.txtNum1.Text = Format$(j, "#,##0")
    Me.txtNum2.Text = Format$(A(0), "#,##0")
    Me.txtNum3.Text = Format$(A(1), "#,##0")
    Me.txtNum4.Text = Format$(A(2), "#,##0")
    Me.txtNum5.Text = Format$(k, "#,##0")
    Me.txtNum6.Text = Format$(A(3), "#,##0")
    Me.txtNum7.Text = Format$(A(4), "#,##0")
    Me.txtNum8.Text = Format$(A(5), "#,##0")
    Me.txtNum9.Text = Format$(CLng(j + k), "#,##0")
    Me.txtNum10.Text = Format$(CLng(A(0) + A(3)), "#,##0")
    Me.txtNum11.Text = Format$(CLng(A(1) + A(4)), "#,##0")
    Me.txtNum12.Text = Format$(CLng(A(2) + A(5)), "#,##0")
    
    '配列の開放
    If IsEmpty(A) = True Then Erase A
    If IsEmpty(B) = True Then Erase B
    'クラスの開放
    Set clsData = Nothing
    
End Sub

'集計単位
Private Sub SetTani(ByVal flg As Integer)

'引数[flg]：納期の特例選択フラグ

    If flg = 0 Then
        With Me.cboTuki
            .Clear
            .List = Array("1月", "2月", "3月", "4月", "5月", "6月", "7月", "8月", "9月", "10月", "11月", "12月")
        End With
    ElseIf flg = 1 Then
        With Me.cboTuki
            .Clear
            .List = Array("1月〜6月", "7月〜12月")
        End With
    End If
    
End Sub

'既定値
Private Sub NewData()

    Dim objText As MSForms.TextBox  'テキストボックス
    Dim objLB As MSForms.Label      'ラベル
    Dim i As Integer                '整数型カウンタ
    
    '配列再定義
    ReDim mobjWS(1) As Worksheet
    'ワークシート
    Set mobjWS(0) = Workbooks(gstrName).Worksheets("Sheet1")
    Set mobjWS(1) = Workbooks(gstrName).Worksheets("Sheet5")
    
    'テキストボックス
    For i = 1 To 12
        Set objText = Controls("txtNum" & CStr(i))
        With objText
            .Locked = True
            .TextAlign = 3
            .Text = ""
        End With
    Next
    
    'ラベル
    For i = 1 To 8
        Set objLB = Controls("lblKomoku" & CStr(i))
        If i < 6 Then
            With objLB
                .TextAlign = 2
                .ForeColor = RGB(255, 255, 255)
                .BackColor = RGB(0, 0, 255)
                .SpecialEffect = 2
            End With
        End If
        If i > 5 Then objLB.TextAlign = 2
    Next
    
    'コマンドボタン
    If cmdOK.Enabled = True Then cmdOK.Enabled = False
    'チェックボックス
    Me.chkTokurei.Value = 0
    'コンボボックス
    Me.cboTuki.ListIndex = -1
    
    Set objText = Nothing
    Set objLB = Nothing
    
End Sub