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

項目三 分類賬管理系統(tǒng)的建立,學(xué)習(xí)目標(biāo) 了解會計賬簿有關(guān)基本概念 了解宏的編制 學(xué)會用宏建立分類賬,工作思路,會計賬簿概述,會計賬簿的意義與作用 會計賬簿是指以會計憑證為依據(jù)，在具有專門格式的賬頁中全面、連續(xù)、系統(tǒng)、綜合地記錄經(jīng)濟業(yè)務(wù)的簿籍。,會計賬簿概述,會計賬簿的類型 1. 日記賬簿 2. 分類賬簿 3. 備查賬簿,工作任務(wù)一 總分類賬的建立,設(shè)置總分類賬格式 總分類賬,利用宏填充科目,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 當(dāng)日借方合計 As Double Dim 當(dāng)日貸方合計 As Double Dim 當(dāng)月貸方合計 As Double Dim 當(dāng)月借方合計 As Double Dim 當(dāng)年貸方合計 As Double Dim 當(dāng)年借方合計 As Double,Dim 標(biāo)志 As Boolean 余額 = Sheet0.Cells(5, 9).Value 月 = 0 日 = 0 當(dāng)日借方合計 = 0 當(dāng)日貸方合計 = 0 當(dāng)月借方合計 = 0 當(dāng)月貸方合計 = 0 當(dāng)年借方合計 = 0 當(dāng)年貸方合計 = 0 標(biāo)志 = 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 如果科目代碼相符 標(biāo)志 = True 給標(biāo)志賦“真”值 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 如果日期變化 余額 = 余額 + 當(dāng)日借方合計 - 當(dāng)日貸方合計 Sheet0.Cells(LineNo0, 4).Value = 月 Sheet0.Cells(LineNo0, 5).Value = 日 Sheet0.Cells(LineNo0, 7).Value = 當(dāng)日借方合計 Sheet0.Cells(LineNo0, 8).Value = 當(dāng)日貸方合計 Sheet0.Cells(LineNo0, 9).Value = 余額 LineNo0 = LineNo0 + 1 當(dāng)日借方合計 = 0 當(dāng)日貸方合計 = 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 = 當(dāng)月借方合計 Sheet0.Cells(LineNo0, 8).Value = 當(dāng)月貸方合計 Sheet0.Cells(LineNo0, 9).Value = 余額 LineNo0 = LineNo0 + 1 當(dāng)月借方合計 = 0 當(dāng)月貸方合計 = 0 月 = Cells(LineNo1, 3).Value End If 當(dāng)日借方合計 = 當(dāng)日借方合計 + Sheet1.Cells(LineNo1, 11) 當(dāng)日貸方合計 = 當(dāng)日貸方合計 + Sheet1.Cells(LineNo1, 12) 當(dāng)月借方合計 = 當(dāng)月借方合計 + Sheet1.Cells(LineNo1, 11) 當(dāng)月貸方合計 = 當(dāng)月貸方合計 + Sheet1.Cells(LineNo1, 12) 當(dāng)年借方合計 = 當(dāng)年借方合計 + Sheet1.Cells(LineNo1, 11) 當(dāng)年貸方合計 = 當(dāng)年貸方合計 + Sheet1.Cells(LineNo1, 12) End If Next,If 標(biāo)志 = True Then 如果有數(shù)據(jù),則把最后的數(shù)據(jù)輸出到相應(yīng)的單元格 余額 = 余額 + 當(dāng)日借方合計 - 當(dāng)日貸方合計 Sheet0.Cells(LineNo0, 4).Value = 月 Sheet0.Cells(LineNo0, 5).Value = 日 Sheet0.Cells(LineNo0, 7).Value = 當(dāng)日借方合計 Sheet0.Cells(LineNo0, 8).Value = 當(dāng)日貸方合計 Sheet0.Cells(LineNo0, 9).Value = 余額 LineNo0 = LineNo0 + 1 Sheet0.Cells(LineNo0, 6).Value = “本月累計“ Sheet0.Cells(LineNo0, 7).Value = 當(dāng)月借方合計 Sheet0.Cells(LineNo0, 8).Value = 當(dāng)月貸方合計 Sheet0.Cells(LineNo0, 9).Value = 余額 LineNo0 = LineNo0 + 1 Sheet0.Cells(LineNo0, 6).Value = “本年累計“ Sheet0.Cells(LineNo0, 7).Value = 當(dāng)年借方合計 Sheet0.Cells(LineNo0, 8).Value = 當(dāng)年貸方合計 Sheet0.Cells(LineNo0, 9).Value = 余額 End If,設(shè)置邊框 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è)置數(shù)據(jù)格式 Range(“G5:I“ & LineNo0).Select Selection.NumberFormatLocal = “#,#0.00_ “,工作任務(wù)二 明細分類賬的建立,格式設(shè)置與總分類賬類似,自動生成明細科目列表,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,當(dāng)前明細科目 = Sheet.Cells(Li