资源预览内容
第1页 / 共37页
第2页 / 共37页
第3页 / 共37页
第4页 / 共37页
第5页 / 共37页
第6页 / 共37页
第7页 / 共37页
第8页 / 共37页
第9页 / 共37页
第10页 / 共37页
亲,该文档总共37页,到这儿已超出免费预览范围,如果喜欢就下载吧!
资源描述
Excel 收支管理系统程序功能: 银行信息记录a) 存款利息计算b) 银行总资金汇总c) 银行年收益计算 收支记录a) 收入项目记录,增加到银行账户b) 支出项目记录,选择支出账户c) 可对每条记录进行修改,并与账户关联d) 收支项目管理,可增加或删除收支项目本程序操作灵活,界面人性化,比如删除“银行记录”金额,可将本条记录信息全部删除(需要确认) ;收支记录中信息输入完整,自动与银行账户信息关联;可自己添加银行并修改利率。使用本程序可快速判别存款方式对收益的影响,比如 5 万存入工商银行:1. 整存整取两年,利息 44002. 整存整取一年,利息 3561(两年后取)现在银行利率也有差别,存不同银行收益相差多少也能方便了解。界面“银行记录”“银行记录”中复制代码如下:Private Sub Calendar1_Click()ActiveCell = Calendar1Calendar1.Visible = FalseEnd SubPrivate Sub Worksheet_SelectionChange(ByVal Target As Range)Dim 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 = Range(b65536).End(xlUp).Row 最大行号cn = 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) ThenSheet4.Select查找银行名称Set rng = Sheet4.B:B.Find(Sheet1.Cells(y, 1)定位银行If Not rng Is Nothing Thenrng.Font.ColorIndex = 3 颜色暂不设置Application.Goto Reference:=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 + 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, ActiveCell.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, cn).Interior.Pattern = xlNone.TintAndShade = 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 = .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 = .ErrorTitle = .InputMessage = .ErrorMessage = .IMEMode = xlIMEModeNoControl.ShowInput = True.ShowError = TrueEnd With 银行列表更新rn4 = Sheet4.Range(e65536).End(xlUp).Rowf = =基本信息 !E5:E & rn4 & With Range(A3).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项目列表With Range(C3).Validation.Delete.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _xlBetween, Formula1:=活期,整存整取,整存零取,零存整取,存本取息, 定活两便.IgnoreBlank = True.InCellDropdown = True.InputTitle = .ErrorTitle = .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(A & Z + 1).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项目列表With Range(C & Z + 1).Validation.Delete.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _xlBetween, Formula1:=活期,整存整取,整存零取,零存整取,存本取息, 定活两便.IgnoreBlank = True.InCellDropdown = True.InputTitle = .ErrorTitle = .InputMessage = .ErrorMessage = .IMEMode = xlIMEModeNoControl.ShowInput = True.ShowError = TrueEnd With-年利率If Sheet1.Cells(Z, 3) = Huo Or Sheet1.Cells(Z, 3) = 活期 Thenlv = lv_huo(Z)Sheet1.Cells(Z, 6) = lvEnd IfIf Sheet1.Cells(Z, 3) = ZZ Or Sheet1.Cells(Z, 3) = 整存整取 ThenIf Sheet1.Cells(Z, 5) = 6 And Sheet1.Cells(Z, 5) = 12 And Sheet1.Cells(Z, 5) = 24 And Sheet1.Cells(Z, 5) = 36 And Sheet1.Cells(Z, 5) = 60 Thenlv = lv_ding1_60(Z) 5 年End IfSheet1.Cells(Z, 6) = lvEnd IfIf Sheet1.Cells(Z, 3) = ZL LZ BX Or Sheet1.Cells(Z, 3) = 零存整取 Or Sheet1.Cells(Z, 3) = 整存零取 Or Sheet1.Cells(Z, 3) = 存本取息 ThenIf Sheet1.Cells(Z, 5) = 12 And Sheet1.Cells(Z, 5) = 36 And Sheet1.Cells(Z, 5) = 60 Thenlv = lv_ding2_60(Z) 5 年End IfSheet1.Cells(Z, 6) = lvEnd IfIf Sheet1.Cells(Z, 3) = 定活两便 ThenIf Sheet1.Cel
网站客服QQ:2055934822
金锄头文库版权所有
经营许可证:蜀ICP备13022795号 | 川公网安备 51140202000112号