項目三分類賬管理系統(tǒng).ppt

項目三 分類賬管理系統(tǒng)的建立,學習目標 了解會計賬簿有關基本概念 了解宏的編制 學會用宏建立分類賬,工作思路,會計賬簿概述,會計賬簿的意義與作用 會計賬簿是指以會計憑證為依據(jù)，在具有專門格式的賬頁中全面、連續(xù)、系統(tǒng)、綜合地記錄經濟業(yè)務的簿籍。,會計賬簿概述,會計賬簿的類型 1. 日記賬簿 2. 分類賬簿 3. 備查賬簿,工作任務一 總分類賬的建立,設置總分類賬格式 總分類賬,利用宏填充科目,ListBox1.Clear Dim Sheet1 As Worksheet Set Sheet1 = Worksheets(“總賬科目期初余額表“) Dim MaxLineNo1 As Integer MaxLineNo1 = Sheet1.UsedRange.Item(Sheet1.UsedRange.Count).Row Dim BeginLineNo1 As Integer BeginLineNo1 = 4 Dim Sheet2 As Worksheet Set Sheet2 = Worksheets(“會計憑證表“) Dim MaxLineNo2 As Integer MaxLineNo2 = Sheet2.UsedRange.Item(Sheet2.UsedRange.Count).Row,Dim BeginLineNo2 As Integer BeginLineNo2 = 4 Dim Code As String Dim LineNo1, LineNo2 As Integer For LineNo1 = BeginLineNo1 To MaxLineNo1 Code = Sheet1.Cells(LineNo1, 1).Value For LineNo2 = BeginLineNo2 To MaxLineNo2 If Code = Sheet2.Cells(LineNo2, 7).Value Then,ListBox1.AddItem , ListBox1.ListCount ListBox1.List(ListBox1.ListCount - 1, 0) = Code ListBox1.List(ListBox1.ListCount - 1, 1) = Sheet1.Cells(LineNo1, 2).Value Exit For End If Next Next,利用宏自動生成總分類賬,Dim Sheet0 As Worksheet Set Sheet0 = Worksheets(“總分類賬“) Dim LineNo0 As Integer LineNo0 = 6 Dim Sheet1 As Worksheet Set Sheet1 = Worksheets(“會計憑證表“) Dim MaxLineNo1, LineNo1, BeginLineNo1 As Integer BeginLineNo1 = 4 MaxLineNo1 = Sheet2.UsedRange.Item(Sheet2.UsedRange.Count).Row,Dim Code As Integer 科目代號 Code = ListBox1.List(ListBox1.ListIndex, 0) Sheet0.Cells(3, 6).Value = ListBox1.List(ListBox1.ListIndex, 1) Sheet0.Cells(5, 7).Value = “=VLOOKUP(“ & Code & “,總賬科目期初余額表!A4:D94,3,TRUE)“ Sheet0.Cells(5, 8).Value = “=VLOOKUP(“ & Code & “,總賬科目期初余額表!A4:D94,4,TRUE)“ Sheet0.Cells(5, 9).Value = “=g5-h5“ 定義變量 Dim 余額 As Double Dim 月 As Integer Dim 日 As Integer Dim 當日借方合計 As Double Dim 當日貸方合計 As Double Dim 當月貸方合計 As Double Dim 當月借方合計 As Double Dim 當年貸方合計 As Double Dim 當年借方合計 As Double,Dim 標志 As Boolean 余額 = Sheet0.Cells(5, 9).Value 月 = 0 日 = 0 當日借方合計 = 0 當日貸方合計 = 0 當月借方合計 = 0 當月貸方合計 = 0 當年借方合計 = 0 當年貸方合計 = 0 標志 = False 刪除上次自動生成的數(shù)據(jù) Sheet0.Range(Cells(LineNo0, 1), Cells(10000, 100).Delete 輸出總分類賬數(shù)據(jù) For LineNo1 = BeginLineNo1 To MaxLineNo1,If Sheet1.Cells(LineNo1, 7).Value * 1 = Code Then 如果科目代碼相符 標志 = True 給標志賦“真”值 If 日 = 0 Then 如果是第一次 月 = Sheet1.Cells(LineNo1, 3).Value 日 = Sheet1.Cells(LineNo1, 4).Value End If If 月 Sheet1.Cells(LineNo1, 3).Value Or 日 Sheet1.Cells(LineNo1, 4).Value Then 如果日期變化 余額 = 余額 + 當日借方合計 - 當日貸方合計 Sheet0.Cells(LineNo0, 4).Value = 月 Sheet0.Cells(LineNo0, 5).Value = 日 Sheet0.Cells(LineNo0, 7).Value = 當日借方合計 Sheet0.Cells(LineNo0, 8).Value = 當日貸方合計 Sheet0.Cells(LineNo0, 9).Value = 余額 LineNo0 = LineNo0 + 1 當日借方合計 = 0 當日貸方合計 = 0 日 = Sheet1.Cells(LineNo1, 4).Value End If,If 月 Sheet1.Cells(LineNo1, 3).Value Then 如果月份變化 Sheet0.Cells(LineNo0, 6).Value = “本月累計“ Sheet0.Cells(LineNo0, 7).Value = 當月借方合計 Sheet0.Cells(LineNo0, 8).Value = 當月貸方合計 Sheet0.Cells(LineNo0, 9).Value = 余額 LineNo0 = LineNo0 + 1 當月借方合計 = 0 當月貸方合計 = 0 月 = Cells(LineNo1, 3).Value End If 當日借方合計 = 當日借方合計 + Sheet1.Cells(LineNo1, 11) 當日貸方合計 = 當日貸方合計 + Sheet1.Cells(LineNo1, 12) 當月借方合計 = 當月借方合計 + Sheet1.Cells(LineNo1, 11) 當月貸方合計 = 當月貸方合計 + Sheet1.Cells(LineNo1, 12) 當年借方合計 = 當年借方合計 + Sheet1.Cells(LineNo1, 11) 當年貸方合計 = 當年貸方合計 + Sheet1.Cells(LineNo1, 12) End If Next,If 標志 = True Then 如果有數(shù)據(jù),則把最后的數(shù)據(jù)輸出到相應的單元格 余額 = 余額 + 當日借方合計 - 當日貸方合計 Sheet0.Cells(LineNo0, 4).Value = 月 Sheet0.Cells(LineNo0, 5).Value = 日 Sheet0.Cells(LineNo0, 7).Value = 當日借方合計 Sheet0.Cells(LineNo0, 8).Value = 當日貸方合計 Sheet0.Cells(LineNo0, 9).Value = 余額 LineNo0 = LineNo0 + 1 Sheet0.Cells(LineNo0, 6).Value = “本月累計“ Sheet0.Cells(LineNo0, 7).Value = 當月借方合計 Sheet0.Cells(LineNo0, 8).Value = 當月貸方合計 Sheet0.Cells(LineNo0, 9).Value = 余額 LineNo0 = LineNo0 + 1 Sheet0.Cells(LineNo0, 6).Value = “本年累計“ Sheet0.Cells(LineNo0, 7).Value = 當年借方合計 Sheet0.Cells(LineNo0, 8).Value = 當年貸方合計 Sheet0.Cells(LineNo0, 9).Value = 余額 End If,設置邊框 Sheet0.Select Range(“D4:I“ & LineNo0).Select Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic,End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With With Selection.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With 設置數(shù)據(jù)格式 Range(“G5:I“ & LineNo0).Select Selection.NumberFormatLocal = “#,#0.00_ “,工作任務二 明細分類賬的建立,格式設置與總分類賬類似,自動生成明細科目列表,Dim Code As Integer Dim Sheet As Worksheet Set Sheet = Worksheets(“會計憑證表“) Dim LineNo, BeginLineNo, MaxLineNo As Integer BeginLineNo = 4 MaxLineNo = Sheet2.UsedRange.Item(Sheet2.UsedRange.Count).Row Code = ListBox1.List(ListBox1.ListIndex, 0) ListBox2.Clear For LineNo = BeginLineNo To MaxLineNo,當前明細科目 = Sheet.Cells(Li