Ecel收支理財管理與財務(wù)知識分析系統(tǒng)方案

1、Excel收支管理系統(tǒng)程序功能：銀行信息記錄a）存款利息計算b）銀行總資金匯總c）銀行年收益計算 收支記錄a）收入項(xiàng)目記錄，增加到銀行賬戶b）支出項(xiàng)目記錄，選擇支出賬戶c）可對每條記錄進(jìn)行修改，并與賬戶關(guān)聯(lián)d）收支項(xiàng)目管理，可增加或刪除收支項(xiàng)目本程序操作靈活，界面人性化，比如刪除“銀行記錄”金額，可將本條記錄信息全部刪 除（需要確認(rèn)）；收支記錄中信息輸入完整，自動與銀行賬戶信息關(guān)聯(lián)；可自己添加銀行并 修改利率。使用本程序可快速判別存款方式對收益的影響，比如5萬存入工商銀行：1. 整存整取兩年，利息 44002. 整存整取一年，利息 3561 （兩年后?。㎡f(II判期H期也轉(zhuǎn)茂數(shù)輅&利

2、期工兩蚩廳50,（100寵碑匣取2010-11-44*42012 L1 4&U4C0|4機(jī)50圓旌存斬2Q1O11-41沖月3*62U1L 11 412C12 )1-4EX 66116 61現(xiàn)在銀行利率也有差別，存不同銀行收益相差多少也能方便了解。界面“銀行記錄”“銀行記錄”中復(fù)制代碼如下: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' 數(shù)據(jù)初始化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 ' 顏色暫不設(shè)置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 “銀行項(xiàng)目”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' 項(xiàng)目列表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' 項(xiàng)目列表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(" 未區(qū)分各家銀行計算方法，結(jié)果不一定準(zhǔn)確，按利率", 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 ' 當(dāng)前日期.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 '當(dāng)前日期.Year = Year(Now) .Month = Month(Now) .Day = Day(Now) End WithElseCalendar1.Visible = FalseEnd IfOn Error Resume Nextnian = Int(Sheet1.Cells(Z, 5) / 12) ' 年數(shù) yue = Sheet1.Cells(Z, 5) - 12 * nian ' 月數(shù)lixishui = 0 ' 利息稅率daoqi = Shee

29、t1.Cells(Z, 2) + Sheet1.Cells(Z, 2) * (lv / 100) * (Sheet1.Cells(Z,5) / 12) * (1 - lixishui) '到期總數(shù)' 當(dāng)前日期與存入日期相差月數(shù)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 '轉(zhuǎn)存次數(shù)If zhuan < 0 Thenzhuan = 0End If' 數(shù)據(jù)寫入If Z > 2 And Sheet1.C

35、ells(Z, 6) <> "" ThenSheet1.Cells(Z, 7) = daoqi '-到期總數(shù)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 '-轉(zhuǎn)存次數(shù)zhuancun(1) = Sheet1.Cells(Z, 7) + Sheet1.Cells(Z, 7) * (lv / 100) * (Sheet1.Cells(Z, 5) / 12) * (1 - lixishui) '第一次轉(zhuǎn)存到期總數(shù)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)" '- 轉(zhuǎn)存到期日期ElseSheet1

38、.Cells(Z, 11).FormulaR1C1 = _ "=DATE(YEAR(RC-7),MONTH(RC-7)+RC-6*(RC-1+1),DAY(RC-7)" '- 轉(zhuǎn)存到期日期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) '-當(dāng)前總額當(dāng)前Sheet1.Cells(Z, 13) = Sheet1.Cells(Z, 12) - Sheet1.Cells(Z, 2) '- 利息End If' 格式If Z Mod 2 = 1 Then ' 當(dāng)前行數(shù)除以 2 的余數(shù)為 1(奇數(shù))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 ' 當(dāng)前行數(shù)除以 2的余數(shù)為 0(偶數(shù)) 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' 活期不考慮到期時間，按存入時間到當(dāng)前日期利息計算If Sheet1.Cells(Z, 3) = "活期 " Thentian = Date - Sheet1.Cells(Z, 4) nian1 = Int(tian / 365) '年數(shù)yue1 = Sheet1.Ce

42、lls(Z, 5) - 12 * nian '月數(shù)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("確認(rèn)刪除該項(xiàng)目？ ", 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; 圖表數(shù)據(jù)處理' 圖表 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") & "" '大寫' 圖表設(shè)置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' 設(shè)置繪圖區(qū)格式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“收支記錄”中復(fù)制代碼如下: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定義為鼠標(biāo)點(diǎn)擊次數(shù)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' 收入項(xiàng)目列表更新rn7s = Sheet7.Range("A65536").End(xlUp).Rowf = "= 收支項(xiàng)目 !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支出項(xiàng)目列表更新rn7z = Sheet7.Range("C65536").End(xlUp).Row f = "= 收支項(xiàng)目 !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" &