在我们添加系统程序前，首先要添加日记账程序。具体操作步骤如下所示。
步骤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
