Ecel收支理财管理与财务知识分析系统方案

1、Excel收支管理系统程序功能：银行信息记录a）存款利息计算b）银行总资金汇总c）银行年收益计算 收支记录a）收入项目记录，增加到银行账户b）支出项目记录，选择支出账户c）可对每条记录进行修改，并与账户关联d）收支项目管理，可增加或删除收支项目本程序操作灵活，界面人性化，比如删除“银行记录”金额，可将本条记录信息全部删 除（需要确认）；收支记录中信息输入完整，自动与银行账户信息关联；可自己添加银行并 修改利率。使用本程序可快速判别存款方式对收益的影响，比如5万存入工商银行：1. 整存整取两年，利息 44002. 整存整取一年，利息 3561 （两年后取）Of(II判期H期也轉茂數辂&利

2、期工两蚩厅50,（100寵碑匣取2010-11-44*42012 L1 4&U4C0|4机50圓旌存斬2Q1O11-41冲月3*62U1L 11 412C12 )1-4EX 66116 61现在银行利率也有差别，存不同银行收益相差多少也能方便了解。界面“银行记录”“银行记录”中复制代码如下:Private Sub Cale ndar1_Click()ActiveCell = Cale ndar1Cale ndar1.Visible = FalseEnd SubPrivate Sub Worksheet_SelectionChange(ByVal Target As Range)Dim

3、lvDim zhuancun(1 To 100)Dim lv_huo(1 To 1000)Dim lv_ding1_3(1 To 1000)Dim lv_ding1_6(1 To 1000)Dim lv_ding1_12(1 To 1000)Dim lv_ding1_24(1 To 1000)Dim lv_ding1_36(1 To 1000)Dim lv_ding1_60(1 To 1000)Dim lv_ding2_12(1 To 1000)Dim lv_ding2_36(1 To 1000)Dim lv_ding2_60(1 To 1000)Dim rng As Rangern = Ra

4、nge("b65536").End(xlUp).Row ' 最大行号= Range("b2").End(xlToRight).Column ' 最大列号Application.ScreenUpdating = False' 数据初始化If Sheet1.Cells(ActiveCell.Row, 1) = "" And Sheet1.Cells(ActiveCell.Row, 3) = "" And Sheet1.Cells(ActiveCell.Row, 2) <> "

5、;" ThenSheet1.Cells(ActiveCell.Row, 1) = " 中国银行 "End IfFor y = 3 To rnIf Sheet1.Cells(y, 1) <> "" ThenSheet4.Select' 查找银行名称Set rng = Sheet4.B:B.Find(Sheet1.Cells(y, 1)' 定位银行If Not rng Is Nothing Then'rng.Font.ColorIndex = 3 ' 颜色暂不设置Application.Goto Refe

6、rence:=rng.Address(, , xlR1C1)End IfEnd Iflv_huo(y) = Sheet4.Cells(ActiveCell.Row + 3, ActiveCell.Column + 1) lv_ding1_3(y) = Sheet4.Cells(ActiveCell.Row + 6, ActiveCell.Column + 1) lv_ding1_6(y) = Sheet4.Cells(ActiveCell.Row + 7, ActiveCell.Column + 1) lv_ding1_12(y) = Sheet4.Cells(ActiveCell.Row +

7、 8, ActiveCell.Column + 1) lv_ding1_24(y) = Sheet4.Cells(ActiveCell.Row + 9, ActiveCell.Column + 1) lv_ding1_36(y) = Sheet4.Cells(ActiveCell.Row + 10, ActiveCell.Column + 1) lv_ding1_60(y) = Sheet4.Cells(ActiveCell.Row + 11, ActiveCell.Column + 1) lv_ding2_12(y) = Sheet4.Cells(ActiveCell.Row + 13, A

8、ctiveCell.Column + 1)lv_ding2_36(y) = Sheet4.Cells(ActiveCell.Row + 14, ActiveCell.Column + 1)lv_ding2_60(y) = Sheet4.Cells(ActiveCell.Row + 15, ActiveCell.Column + 1)' 返回 sheet “银行项目”Sheet1.SelectNext ' 格式初始化With Range(Sheet1.Cells(3, 1), Sheet1.Cells(rn + 30, ).Interior .Pattern = xlNone.T

9、intAndShade = 0.PatternTintAndShade = 0End With取消列表With Sheet1.Range("A:A").Validation.Delete.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween.IgnoreBlank = True.InCellDropdown = True.InputTitle = "".ErrorTitle = "".InputMessage = &quo

10、t;".ErrorMessage = "".IMEMode = xlIMEModeNoControl.ShowInput = True.ShowError = TrueEnd WithWith Sheet1.Range("C:C").Validation.Delete.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween.IgnoreBlank = True.InCellDropdown = True.InputTitle = &q

11、uot;".ErrorTitle = "".InputMessage = "".ErrorMessage = "".IMEMode = xlIMEModeNoControl.ShowInput = True.ShowError = TrueEnd With' 银行列表更新rn4 = Sheet4.Range("e65536").End(xlUp).Rowf = "= 基本信息 !E5:E" & rn4 & ""With Range("

12、;A3").Validation.Delete.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=f.IgnoreBlank = True.InCellDropdown = True.InputTitle = "".ErrorTitle = "".InputMessage = "".ErrorMessage = "".IMEMode = xlIMEModeNoControl.S

13、howInput = True.ShowError = TrueEnd With' 项目列表With Range("C3").Validation.Delete.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _xlBetween, Formula1:=" 活期 , 整存整取 , 整存零取 , 零存整取 , 存本取息 , 定活两便II.IgnoreBlank = True.InCellDropdown = True.InputTitle = "".Er

14、rorTitle = "".InputMessage = "".ErrorMessage = "".IMEMode = xlIMEModeNoControl.ShowInput = True.ShowError = TrueEnd WithFor Z = 3 To rn' 银行列表更新rn4 = Sheet4.Range("e65536").End(xlUp).Rowf = "= 基本信息 !E5:E" & rn4 & ""With Range(&qu

15、ot;A" & Z + 1).Validation.Delete.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=f.IgnoreBlank = True.InCellDropdown = True.InputTitle = "".ErrorTitle = "".InputMessage = "".ErrorMessage = "".IMEMode = xlIMEMo

16、deNoControl.ShowInput = True.ShowError = TrueEnd With' 项目列表With Range("C" & Z + 1).Validation.Delete.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _xlBetween, Formula1:="活期 , 整存整取 , 整存零取 , 零存整取 , 存本取息 , 定活两便.IgnoreBlank = True.InCellDropdown = True.InputTi

17、tle = "".ErrorTitle = "".InputMessage = "".ErrorMessage = "".IMEMode = xlIMEModeNoControl.ShowInput = True.ShowError = TrueEnd With' 年利率活期 " Then整存整取 " ThenIf Sheet1.Cells(Z, 3) = "Huo" Or Sheet1.Cells(Z, 3) = "lv = lv_huo(Z)Sheet1

18、.Cells(Z, 6) = lvEnd IfIf Sheet1.Cells(Z, 3) = "ZZ" Or Sheet1.Cells(Z, 3) = "If Sheet1.Cells(Z, 5) < 6 Then lv = lv_ding1_3(Z) '3 个月End IfIf Sheet1.Cells(Z, 5) >= 6 And Sheet1.Cells(Z, 5) < 12 Then lv = lv_ding1_6(Z) ' 半年End IfIf Sheet1.Cells(Z, 5) >= 12 And Sheet1

19、.Cells(Z, 5) < 24 Then lv = lv_ding1_12(Z) '1 年End IfIf Sheet1.Cells(Z, 5) >= 24 And Sheet1.Cells(Z, 5) < 36 Thenlv = lv_ding1_24(Z) '2 年End IfIf Sheet1.Cells(Z, 5) >= 36 And Sheet1.Cells(Z, 5) < 60 Then lv = lv_ding1_36(Z) '3 年End IfIf Sheet1.Cells(Z, 5) >= 60 Then lv

20、= lv_ding1_60(Z) '5 年End IfSheet1.Cells(Z, 6) = lvEnd IfIf Sheet1.Cells(Z, 3) = "ZL LZ BX" Or Sheet1.Cells(Z, 3) = "Sheet1.Cells(Z, 3) = " 整存零取 " Or Sheet1.Cells(Z, 3) = "存本取息If Sheet1.Cells(Z, 5) >= 12 And Sheet1.Cells(Z, 5) < 36 Thenlv = lv_ding2_12(Z) '

21、1 年End IfIf Sheet1.Cells(Z, 5) >= 36 And Sheet1.Cells(Z, 5) < 60 Then lv = lv_ding2_36(Z) '3 年End IfIf Sheet1.Cells(Z, 5) >= 60 Then lv = lv_ding2_60(Z) '5 年End IfSheet1.Cells(Z, 6) = lvEnd IfIf Sheet1.Cells(Z, 3) = "定活两便 " ThenIf Sheet1.Cells(Z, 4) = "" And Shee

22、t1.Cells(Z, 5) = "" Thendh= MsgBox(" 未区分各家银行计算方法，结果不一定准确，按利率", vbYesNo, " 提示 ")If dh = vbYes ThenIf Sheet1.Cells(Z, 5) < 6 Thenlv = lv_ding1_3(Z) '3个月End IfIf Sheet1.Cells(Z, 5) >= 6 And Sheet1.Cells(Z, 5) < 12 Thenlv = lv_ding1_6(Z) '半年End IfIf Sheet1.

23、Cells(Z, 5) >= 12 And Sheet1.Cells(Z, 5) < 24 Then lv = lv_ding1_12(Z) '1 年End IfIf Sheet1.Cells(Z, 5) >= 24 And Sheet1.Cells(Z, 5) < 36 Thenlv = lv_ding1_24(Z) '2年End IfIf Sheet1.Cells(Z, 5) >= 36 And Sheet1.Cells(Z, 5) < 60 Then零存整取 " OrThen60%计算？lv = lv_ding1_36(Z)

24、'3年End IfIf Sheet1.Cells(Z, 5) >= 60 Thenlv = lv_ding1_60(Z) '5年End Iflv = lv * 0.6ElseSheet1.Cells(Z, 3) = ""End IfElseIf Sheet1.Cells(Z, 5) < 6 Thenlv = lv_ding1_3(Z) '3个月End IfIf Sheet1.Cells(Z, 5) >= 6 And Sheet1.Cells(Z, 5) < 12 Then lv = lv_ding1_6(Z) '半年E

25、nd IfIf Sheet1.Cells(Z, 5) >= 12 And Sheet1.Cells(Z, 5) < 24 Then lv = lv_ding1_12(Z) '1 年End IfIf Sheet1.Cells(Z, 5) >= 24 And Sheet1.Cells(Z, 5) < 36 Then lv = lv_ding1_24(Z) '2年End IfIf Sheet1.Cells(Z, 5) >= 36 And Sheet1.Cells(Z, 5) < 60 Then lv = lv_ding1_36(Z) '3年

26、End IfIf Sheet1.Cells(Z, 5) >= 60 Thenlv = lv_ding1_60(Z) '5年End Iflv = lv * 0.6End IfSheet1.Cells(Z, 6) = lvEnd If存入日期 " And Target.Row > 2 AndOn Error Resume NextIf Target.Column = 4 And Target.Value <> " Target.Row <= rn ThenCalendar1.Visible = True ' 日历控件Calendar

27、1.Left = Cells(Target.Row, 4).LeftCalendar1.Top = Cells(Target.Row + 1, 4).TopWith Calendar1 ' 当前日期.Year = Year(Now).Month = Month(Now).Day = Day(Now)End WithElseIf Target.Column = 1 And Target.Row = 1 ThenCalendar1.Visible = True ' 日历控件 Calendar1.Left = Cells(Target.Row, 1).Left Calendar1.T

28、op = Cells(Target.Row + 1, 1).Top With Calendar1 '当前日期.Year = Year(Now) .Month = Month(Now) .Day = Day(Now) End WithElseCalendar1.Visible = FalseEnd IfOn Error Resume Nextnian = Int(Sheet1.Cells(Z, 5) / 12) ' 年数 yue = Sheet1.Cells(Z, 5) - 12 * nian ' 月数lixishui = 0 ' 利息税率daoqi = Shee

29、t1.Cells(Z, 2) + Sheet1.Cells(Z, 2) * (lv / 100) * (Sheet1.Cells(Z,5) / 12) * (1 - lixishui) '到期总数' 当前日期与存入日期相差月数If Year(Sheet1.Cells(1, 1) >= Year(Sheet1.Cells(Z, 4) And Month(Sheet1.Cells(1, 1) >= Month(Sheet1.Cells(Z, 4) And Day(Sheet1.Cells(1,1) >= Day(Sheet1.Cells(Z, 4) Thenm =

30、 (Year(Sheet1.Cells(1, 1) - Year(Sheet1.Cells(Z, 4) * 12 + Month(Sheet1.Cells(1, 1) - Month(Sheet1.Cells(Z, 4)End IfIf Year(Sheet1.Cells(1, 1) >= Year(Sheet1.Cells(Z, 4) And Month(Sheet1.Cells(1, 1) >= Month(Sheet1.Cells(Z,4) And Day(Sheet1.Cells(1,1)< Day(Sheet1.Cells(Z, 4) Thenm = (Year(S

31、heet1.Cells(1, 1) - Year(Sheet1.Cells(Z, 4) * 12 + Month(Sheet1.Cells(1, 1) - Month(Sheet1.Cells(Z, 4) - 1End IfIf Year(Sheet1.Cells(1, 1) >= Year(Sheet1.Cells(Z, 4) And Month(Sheet1.Cells(1, 1) < Month(Sheet1.Cells(Z, 4) And Day(Sheet1.Cells(1, 1) >= Day(Sheet1.Cells(Z, 4) Thenm = (Year(Sh

32、eet1.Cells(1, 1) - Year(Sheet1.Cells(Z, 4) * 12 - Month(Sheet1.Cells(1, 1) + Month(Sheet1.Cells(Z, 4)End IfIf Year(Sheet1.Cells(1, 1) >= Year(Sheet1.Cells(Z, 4) And Month(Sheet1.Cells(1, 1) < Month(Sheet1.Cells(Z, 4) And Day(Sheet1.Cells(1, 1) >= Day(Sheet1.Cells(Z, 4) Thenm = (Year(Sheet1.

33、Cells(1, 1) - Year(Sheet1.Cells(Z, 4) * 12 - Month(Sheet1.Cells(1, 1) + Month(Sheet1.Cells(Z, 4) - 1End IfIf Year(Sheet1.Cells(1, 1) < Year(Sheet1.Cells(Z, 4) Thenm = 0End IfIf Year(Sheet1.Cells(1, 1) = Year(Sheet1.Cells(Z, 4) And Month(Sheet1.Cells(1, 1) < Month(Sheet1.Cells(Z, 4) Thenm = 0En

34、d IfIf Year(Sheet1.Cells(1, 1) = Year(Sheet1.Cells(Z, 4) And Month(Sheet1.Cells(1, 1) = Month(Sheet1.Cells(Z, 4) And Day(Sheet1.Cells(1, 1) < Day(Sheet1.Cells(Z, 4) Thenm = 0End Ifzhuan = Int(m / Sheet1.Cells(Z, 5) - 1 '转存次数If zhuan < 0 Thenzhuan = 0End If' 数据写入If Z > 2 And Sheet1.C

35、ells(Z, 6) <> "" ThenSheet1.Cells(Z, 7) = daoqi '-到期总数If Sheet1.Cells(Z, 4) <> "" Then到期日Sheet1.Cells(Z, 8).FormulaR1C1 = _ "=DATE(YEAR(RC-4),MONTH(RC-4)+RC-3,DAY(RC-4)" '- 期End IfEnd IfIf Sheet1.Cells(Z, 7) <> "" ThenSheet1.Cells(Z,

36、9) = (Sheet1.Cells(Z, 7) - Sheet1.Cells(Z, 2) '-利息ElseSheet1.Cells(Z, 9) = ""End IfSheet1.Cells(Z, 10) = zhuan '-转存次数zhuancun(1) = Sheet1.Cells(Z, 7) + Sheet1.Cells(Z, 7) * (lv / 100) * (Sheet1.Cells(Z, 5) / 12) * (1 - lixishui) '第一次转存到期总数If zhuan >= 2 ThenFor i = 2 To zhuan

37、zhuancun(i) = zhuancun(i - 1) + zhuancun(i - 1) * (lv / 100) * (Sheet1.Cells(Z, 5) / 12) * (1 - lixishui)Next End IfIf Int(m / Sheet1.Cells(Z, 5) = m / Sheet1.Cells(Z, 5) Then Sheet1.Cells(Z, 11).FormulaR1C1 = _ "=DATE(YEAR(RC-7),MONTH(RC-7)+RC-6*(RC-1+1),DAY(RC-7)" '- 转存到期日期ElseSheet1

38、.Cells(Z, 11).FormulaR1C1 = _ "=DATE(YEAR(RC-7),MONTH(RC-7)+RC-6*(RC-1+1),DAY(RC-7)" '- 转存到期日期End IfIf zhuan < 1 ThenSheet1.Cells(Z, 10) = ""Sheet1.Cells(Z, 11) = ""Sheet1.Cells(Z, 12) = Sheet1.Cells(Z, 7)Sheet1.Cells(Z, 13) = Sheet1.Cells(Z, 9) ElseSheet1.Cells(

39、Z, 12) = zhuancun(zhuan) '-当前总额当前Sheet1.Cells(Z, 13) = Sheet1.Cells(Z, 12) - Sheet1.Cells(Z, 2) '- 利息End If' 格式If Z Mod 2 = 1 Then ' 当前行数除以 2 的余数为 1(奇数)With Range(Sheet1.Cells(Z, 1), Sheet1.Cells(Z, 14).Interior .Pattern = xlSolid.PatternColorIndex = xlAutomatic .Color = 10198015.Tin

40、tAndShade = 0 .PatternTintAndShade = 0End WithElseIf Z Mod 2 = 0 Then ' 当前行数除以 2的余数为 0(偶数) With Range(Sheet1.Cells(Z, 1), Sheet1.Cells(Z, 14).Interior.Pattern = xlSolid.PatternColorIndex = xlAutomatic.Color = 13421823.TintAndShade = 0.PatternTintAndShade = 0End WithEnd If' 未选择银行不计利率If Sheet1

41、.Cells(Z, 1) = "" Then lv = 0Sheet1.Cells(Z, 6) = "" Sheet1.Cells(Z, 7) = "" Sheet1.Cells(Z, 9) = ""End If' 活期不考虑到期时间，按存入时间到当前日期利息计算If Sheet1.Cells(Z, 3) = "活期 " Thentian = Date - Sheet1.Cells(Z, 4) nian1 = Int(tian / 365) '年数yue1 = Sheet1.Ce

42、lls(Z, 5) - 12 * nian '月数Sheet1.Cells(Z, 7) = Sheet1.Cells(Z, 2) + Sheet1.Cells(Z, 2) * lv / 100 * (tian / 365)Sheet1.Cells(Z, 9) = Sheet1.Cells(Z, 7) - Sheet1.Cells(Z, 2) Sheet1.Cells(Z, 5) = ""Sheet1.Cells(Z, 6) = lvSheet1.Cells(Z, 8) = "" Sheet1.Cells(Z, 10) = ""

43、 Sheet1.Cells(Z, 11) = ""Sheet1.Cells(Z, 12) = Sheet1.Cells(Z, 7)Sheet1.Cells(Z, 13) = Sheet1.Cells(Z, 9)End If' 未选择存入时间If Sheet1.Cells(Z, 4) = "" ThenSheet1.Cells(Z, 9) = ""Sheet1.Cells(Z, 10) = "" Sheet1.Cells(Z, 11) = "" Sheet1.Cells(Z, 13) =

44、""Sheet1.Cells(Z, 7) = Sheet1.Cells(Z, 2) Sheet1.Cells(Z, 12) = Sheet1.Cells(Z, 2)End If' 到期提示If Sheet1.Cells(Z, 8) < Sheet1.Cells(1, 1) ThenWith Sheet1.Cells(Z, 8).Font.Color = -65281 .TintAndShade = 0End WithElseWith Sheet1.Cells(Z, 8).Font.ThemeColor = xlThemeColorLight1 .TintAnd

45、Shade = 0End WithEnd IfIf Sheet1.Cells(Z, 11) < Sheet1.Cells(1, 1) ThenWith Sheet1.Cells(Z, 11).Font.Color = -65281 .TintAndShade = 0End WithElseWith Sheet1.Cells(Z, 11).Font .ThemeColor = xlThemeColorLight1 .TintAndShade = 0End WithEnd IfNextFor j = 3 To rn + 1000提示 ")If Sheet1.Cells(j, 2)

46、= "" And Sheet1.Cells(j, 3) <> "" Then sc = MsgBox("确认删除该项目？ ", vbYesNo, "If sc = vbYes ThenSheet1.Cells(j, 1) = ""Sheet1.Cells(j, 3) = ""Sheet1.Cells(j, 4) = ""Sheet1.Cells(j, 5) = ""Sheet1.Cells(j, 6) = ""Sh

47、eet1.Cells(j, 7) = ""Sheet1.Cells(j, 8) = ""Sheet1.Cells(j, 9) = ""Sheet1.Cells(j, 10) = ""Sheet1.Cells(j, 11) = ""Sheet1.Cells(j, 12) = ""Sheet1.Cells(j, 13) = ""Sheet1.Cells(j, 14) = ""End IfRows(j).DeleteEnd IfNext

48、9; 图表数据处理' 图表 1 ，饼图，显示各银行资金分布Sheet6.Columns("A:B").ClearSheet6.Cells(1, 1) = "图表 1"zong = 0 ben = 0 nianli = 0For K = 3 To rnrn1 = Sheet6.Range("a65536").End(xlUp).RowIf rn1 < 2 Thenrn1 = 2End If flag = 0 For k1 = 3 To rn1If Sheet1.Cells(K, 1) = Sheet6.Cells(k1,

49、1) Thenflag = flag + 1Sheet6.Cells(k1, 2) = Sheet6.Cells(k1, 2) + Sheet1.Cells(K, 12)End IfNextIf flag = 0 ThenSheet6.Cells(rn1 + 1, 1) = Sheet1.Cells(K, 1)Sheet6.Cells(rn1 + 1, 2) = Sheet1.Cells(K, 12)End Ifzong = zong + Sheet1.Cells(K, 12)ben = ben + Sheet1.Cells(K, 2)nianli = nianli + (Sheet1.Cel

50、ls(K, 13) / (Sheet1.Cells(K, 5) + Sheet1.Cells(K,5) * Sheet1.Cells(K, 10) * 12NextSheet1.Cells(1, 15) = "银行总额 :" & Format(zong, "#,#0.0")Sheet1.Cells(2, 15) = Application.Text(Format(zong, "#0"), "DBNum2") ' 大 写Sheet1.Cells(3, 15) = " 本金： " &

51、amp; Application.Text(Format(ben, "#0"), "DBNum2") ' 大写Sheet1.Cells(4, 15) = " 年 收 益 ： " & Application.Text(Format(nianli, "#0"), "DBNum1") & "" & Format(nianli, "#0") & "" '大写' 图表设置r = Active

52、Cell.Row c = ActiveCell.Column 'Range("A1:C" & 5 & ", E1:G" & 5 & "")等价于 Range("A1:C5, E1:G5")rn1 = Sheet6.Range("a65536").End(xlUp).RowIf rn1 >= 2 ThenActiveSheet.ChartObjects(" 图表 1").ActivateActiveChart.SetSourceDa

53、ta Source:=Sheet6.Range("A2:A" & rn1 & ", B2:B" & rn1 & "")Sheet1.Cells(r, c).Select' 设置绘图区格式With Sheets(1).ChartObjects(" .Name .Left .Top .Height = 250 .Width .Chart.ChartTypeEnd WithEnd IfWith Range("O3", "O4") .Horizontal

54、Alignment = xlRight .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = FalseEnd WithWith Range("O3").Font .Color = -16751104 .TintAndShade = 0End WithWith Range("O4").Fo

55、nt .Color = -3368704 .TintAndShade = 0End WithRange("O3", "O4").Font.Size = 9图表 1")End SubI'IZ'JC'j曰20L4】T6IX)1 11A20L2-J1-614界面“收支记录”fl“收支记录”中复制代码如下:Dim riDim r(0 To 1)Dim r1(0 To 1)Dim r2(0 To 1)Dim r3(0 To 1)Dim r10(0 To 1)Dim r11(0 To 1)Dim r12(0 To 1)Dim r13

56、(0 To 1)Dim kongDim kong1Dim kong2Dim kong10Dim kong11Dim kong12Dim flag_sha nDim flag_sha n10Private Sub Cale ndar1_Click()ActiveCell = Cale ndar1Cale ndar1.Visible = FalseEnd SubPrivate Sub Worksheet_Selectio nCha nge(ByVal Target As Range) ri = ri + 1 'ri定义为鼠标点击次数rik = ri Mod 2 'rik为 0 或

57、1rns = Ran ge("B65536").E nd(xlUp).Row '收入最大行号rnz = Ran ge("L65536").E nd(xlUp).Row '支出最大行号If rns >= rnz The nrn = rnsElsern = rnzEnd If最大列号最大列号cns = Range("B2").End(xlToRight).Columncnz = Range("L2").End(xlToRight).Column ' 格式初始化With Range(Shee

58、t2.Cells(3, 1), Sheet2.Cells(rn + 10, cnz).Interior .Pattern = xlNone.TintAndShade = 0.PatternTintAndShade = 0End WithWith Sheet2.Range("A:N").Validation.Delete.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _ :=xlBetween.IgnoreBlank = True.InCellDropdown = True.Inpu

59、tTitle = "".ErrorTitle = "".InputMessage = "".ErrorMessage = "".IMEMode = xlIMEModeNoControl.ShowInput = True.ShowError = TrueEnd With' 收入项目列表更新rn7s = Sheet7.Range("A65536").End(xlUp).Rowf = "= 收支项目 !A2:A" & rn7s & ""W

60、ith Range("B3").Validation.Delete.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=f.IgnoreBlank = True.InCellDropdown = True.InputTitle = "".ErrorTitle = "".InputMessage = "".ErrorMessage = "".IMEMode = xlIME

61、ModeNoControl.ShowInput = True.ShowError = TrueEnd With支出项目列表更新rn7z = Sheet7.Range("C65536").End(xlUp).Row f = "= 收支项目 !C2:C" & rn7z & ""With Range("L3").Validation.Delete.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween,

62、Formula1:=f.IgnoreBlank = True.InCellDropdown = True.InputTitle = "".ErrorTitle = "".InputMessage = "".ErrorMessage = "".IMEMode = xlIMEModeNoControl.ShowInput = True.ShowError = TrueEnd With' 收入账户列表更新rn4 = Sheet4.Range("e65536").End(xlUp).Rowf =

63、 "= 基本信息 !E5:E" & rn4 & ""With Range("D3").Validation.Delete.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=f.IgnoreBlank = True.InCellDropdown = True.InputTitle = "".ErrorTitle = "".InputMessage = "".ErrorMessage = "".IMEMode = xlIMEModeNoControl.ShowInput = True.ShowError = TrueEnd With' 支出账户列表更新rn4 = Sheet4.Range("e65536").End(xlUp).Rowf = "= 基本信息 !E5:E" &