项目三分类账管理系统.ppt

项目三 分类账管理系统的建立,学习目标 了解会计账簿有关基本概念 了解宏的编制 学会用宏建立分类账,工作思路,会计账簿概述,会计账簿的意义与作用 会计账簿是指以会计凭证为依据，在具有专门格式的账页中全面、连续、系统、综合地记录经济业务的簿籍。,会计账簿概述,会计账簿的类型 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 删除上次自动生成的数据 Sheet0.Range(Cells(LineNo0, 1), Cells(10000, 100).Delete 输出总分类账数据 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 如果有数据,则把最后的数据输出到相应的单元格 余额 = 余额 + 当日借方合计 - 当日贷方合计 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 设置数据格式 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