标签：程序

在添加完现金日记账程序后，我们接着进行添加银行存款日记账程序，具体操作步骤如下所示。

步骤01：切换至“银行存款日记账”工作表，在“开发工具”里选择“Visual Basic编辑器”命令。

步骤02：在弹出的Visual basic编辑对话框中，输入如图10-27所示的代码。具体代码如下所示：

---

Private Sub WorkSheet_change(ByVal Target As Range)
Dim iRow, iCol, iRow_dn As Integer
Dim rng1, rngl2, rng, cel As Range
iRow = Target.Row
iCol = Target.Column
iRow_dn = [A65536].End(xlUp).Row        'A列的最后一行
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    If iRow >= 3 And iCol = 2 And Cells(iRow, iCol) <> "" Then
        Cells(iRow, 1) = Date        '在第一列填写日期
        ElseIf iRow >= 3 And iCol = 2 And Cells(iRow, iCol) = "" Then
                Range(Cells(iRow, 1), Cells(iRow, 5)).ClearContents
        ElseIf iRow >= 3 And (iCol = 3 Or iCol = 4) And iRow = iRow_dn Then
            Total1 = Application.WorkSheetFunction.Sum(Range("C3:C" & iRow))
            Total1 = Application.WorkSheetFunction.Sum(Range("D3:D" & iRow))
            Cells(iRow, 5) = Total1 - Total2        '在第5列运算
        ElseIf iRow >= 3 And (iCol = 3 Or iCol = 4) And iRow <> iRow_dn Then
            Set rng = Range("E" & iRow & " :E" & iRow_dn)
            For Each cel In rng
                Set rng1 = Range("C3:C" & cel.Row)
                Set rng2 = Range("D3:D" & cel.Row)
                Total1 = Application.WorkSheetFunction.Sum(rng1)
                Total2 = Application.WorkSheetFunction.Sum(rng2)
                cel.Value = Total1 - Total2
        Next cel
End If
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub

---

图10-27　输入银行存款日记账程序代码

步骤03：单击“保存”按钮，将输入的代码保存起来。此时，在银行存款日记账表中，当输入日期、摘要、存入、取出后，系统会自动计算余额。

在我们添加系统程序前，首先要添加日记账程序。具体操作步骤如下所示。

步骤01：切换至“现金日记账”工作表，在“开发工具”里，选择“Visual Basic编辑器”命令，如图10-25所示。

图10-25　选择“Visual Basic”编辑器

步骤02：在弹出的对话框中，输入如图10-26所示的代码。代码如下：

---

Private Sub WorkSheet_change(ByVal Target As Range)
Dim iRow, iCol, iRow_dn As Integer
Dim rng1, rngl2, rng, cel As Range
iRow = Target.Row
iCol = Target.Column
iRow_dn = [A65536].End(x1Up).Row  'A列的最后一行
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    If iRow > = 3 And iCol = 2 And Cells(iRow, iCol) <> ""  Then
        Cells (iRow, 1) = Date '在第一列填写日期
    ElseIf iRow > = 3 And iCol = 2 And Cells(iRow, iCol) = "" Then
        Range(Cells(iRow, 1), Cells(iRow, 5)).ClearContents
    ElseIf iRow >= 3 And (iCol = 3 Or iCol = 4) And iRow = iRow_dn Then
        Total1 = Application.WorkSheetFunction.Sum(Range("C4:C" & iRow))
        Total1 = Application.WorkSheetFunction.Sum(Range("D4:D" & iRow))
        Cells(iRow, 5) = Total1 - Total2 '在第5列运算
    ElseIf iRow >= 3 And (iCol = 3 Or iCol = 4) And iRow <> iRow_dn Then
        Set rng = Range("E" & iRow & " :E" & iRow_dn)
        For Each cel In rng
            Set rng1 = Range("C4:C" & cel.Row)
            Set rng2 = Range("D4:D" & cel.Row)
            Total1 = Application.WorkSheetFunction.Sum(rng1)
            Total2 = Application.WorkSheetFunction.Sum(rng2)
            cel.Value = Total1 - Total2
    Next cel
End If
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub

---

图10-26　插入现金日记账代码

输入以上代码后，我们可以控制数据的输入和余额的累计计算。如果需要打开“现金日记账”工作表中自动定位到需要输入数据的第一个单元格，还需要输入相关的定位代码。具体代码段如下所示：

---

Private Sub WorkSheet_Active()
ActiveSheet.[B65536].end(xlUp).Offset(1, 0) .Select '打开现金后自动
        '定位到需要输入数据的第一个单元格
End Sub

---