Excel理财收支管理代码

Excel收支管理系统程序功能：银行信息记录a)存款利息计算b)银行总资金汇总c)银行年收益计算收支记录a)收入项目记录，增加到银行账户b)支出项目记录，选择支出账户c)可对每条记录进行修改，并与账户关联d)收支项目管理，可增加或删除收支项目本程序操作灵活，界面人性化，比如删除“银行记录”金额，可将本条记录信息全部删除（需要确认）；收支记录中信息输入完整，自动与银行账户信息关联；可自己添加银行并修改利率。使用本程序可快速判别存款方式对收益的影响，比如5万存入工商银行：1.整存整取两年，利息44002.整存整取一年，利息3561（两年后取）现在银行利率也有差别，存不同银行收益相差多少也能方便了解。界面“银行记录”“银行记录”中复制代码如下：PrivateSubCalendar1_Click()ActiveCell=Calendar1Calendar1.Visible=FalseEndSubPrivateSubWorksheet_SelectionChange(ByValTargetAsRange)DimlvDimzhuancun(1To100)Dimlv_huo(1To1000)Dimlv_ding1_3(1To1000)Dimlv_ding1_6(1To1000)Dimlv_ding1_12(1To1000)Dimlv_ding1_24(1To1000)Dimlv_ding1_36(1To1000)Dimlv_ding1_60(1To1000)Dimlv_ding2_12(1To1000)Dimlv_ding2_36(1To1000)Dimlv_ding2_60(1To1000)DimrngAsRangern=Range(b65536).End(xlUp).Row'最大行号cn=Range(b2).End(xlToRight).Column'最大列号Application.ScreenUpdating=False'数据初始化IfSheet1.Cells(ActiveCell.Row,1)=AndSheet1.Cells(ActiveCell.Row,3)=AndSheet1.Cells(ActiveCell.Row,2)ThenSheet1.Cells(ActiveCell.Row,1)=中国银行EndIfFory=3TornIfSheet1.Cells(y,1)ThenSheet4.Select'查找银行名称Setrng=Sheet4.[B:B].Find(Sheet1.Cells(y,1))'定位银行IfNotrngIsNothingThen'rng.Font.ColorIndex=3'颜色暂不设置Application.GotoReference:=rng.Address(,,xlR1C1)EndIfEndIflv_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'--------------'格式初始化WithRange(Sheet1.Cells(3,1),Sheet1.Cells(rn+30,cn)).Interior.Pattern=xlNone.TintAndShade=0.PatternTintAndShade=0EndWith'---------------'取消列表WithSheet1.Range(A:A).Validation.Delete.AddType:=xlValidateInputOnly,AlertStyle:=xlValidAlertStop,Operator_:=xlBetween.IgnoreBlank=True.InCellDropdown=True.InputTitle=.ErrorTitle=.InputMessage=.ErrorMessage=.IMEMode=xlIMEModeNoControl.ShowInput=True.ShowError=TrueEndWithWithSheet1.Range(C:C).Validation.Delete.AddType:=xlValidateInputOnly,AlertStyle:=xlValidAlertStop,Operator_:=xlBetween.IgnoreBlank=True.InCellDropdown=True.InputTitle=.ErrorTitle=.InputMessage=.ErrorMessage=.IMEMode=xlIMEModeNoControl.ShowInput=True.ShowError=TrueEndWith'银行列表更新rn4=Sheet4.Range(e65536).End(xlUp).Rowf==基本信息!E5:E&rn4&WithRange(A3).Validation.Delete.AddType:=xlValidateList,AlertStyle:=xlValidAlertStop,Operator:=_xlBetween,Formula1:=f.IgnoreBlank=True.InCellDropdown=True.InputTitle=.ErrorTitle=.InputMessage=.ErrorMessage=.IMEMode=xlIMEModeNoControl.ShowInput=True.ShowError=TrueEndWith'项目列表WithRange(C3).Validation.Delete.AddType:=xlValidateList,AlertStyle:=xlValidAlertStop,Operator:=_xlBetween,Formula1:=活期,整存整取,整存零取,零存整取,存本取息,定活两便.IgnoreBlank=True.InCellDropdown=True.InputTitle=.ErrorTitle=.InputMessage=.ErrorMessage=.IMEMode=xlIMEModeNoControl.ShowInput=True.ShowError=TrueEndWithForZ=3Torn'银行列表更新rn4=Sheet4.Range(e65536).End(xlUp).Rowf==基本信息!E5:E&rn4&WithRange(A&Z+1).Validation.Delete.AddType:=xlValidateList,AlertStyle:=xlValidAlertStop,Operator:=_xlBetween,Formula1:=f.IgnoreBlank=True.InCellDropdown=True.InputTitle=.ErrorTitle=.InputMessage=.ErrorMessage=.IMEMode=xlIMEModeNoControl.ShowInput=True.ShowError=TrueEndWith'项目列表WithRange(C&Z+1).Validation.Delete.AddType:=xlValidateList,AlertStyle:=xlValidAlertStop,Operator:=_xlBetween,Formula1:=活期,整存整取,整存零取,零存整取,存本取息,定活两便.IgnoreBlank=True.InCellDropdown=True.InputTitle=.ErrorTitle=.InputMessage=.ErrorMessage=.IMEMode=xlIMEModeNoControl.ShowInput=True.ShowError=TrueEndWith'-----------------------'年利率IfSheet1.Cells(Z,3)=HuoOrSheet1.Cells(Z,3)=活期Thenlv=lv_huo(Z)Sheet1.Cells(Z,6)=lvEndIfIfSheet1.Cells(Z,3)=ZZOrSheet1.Cells(Z,3)=整存整取ThenIfSheet1.Cells(Z,5)6Thenlv=lv_ding1_3(Z)'3个月EndIfIfSheet1.Cells(Z,5)=6AndSheet1.Cells(Z,5)12Thenlv=lv_ding1_6(Z)'半年EndIfIfSheet1.Cells(Z,5)=12AndSheet1.Cells(Z,5)24Thenlv=lv_ding1_12(Z)'1年EndIfIfSheet1.Cells(Z,5)=24AndSheet1.Cells(Z,5)36Thenlv=lv_ding1_24(Z)'2年EndIfIfSheet1.Cells(Z,5)=36AndSheet1.Cells(Z,5)60Thenlv=lv_ding1_36(Z)'3年EndIfIfSheet1.Cells(Z,5)=60Thenlv=lv_ding1_60(Z)'5年EndIfSheet1.Cells(Z,6)=lvEndIfIfSheet1.Cells(Z,3)=ZLLZBXOrSheet1.Cells(Z,3)=零存整取OrSheet1.Cells(Z,3)=整存零取OrSheet1.Cells(Z,3)=存本取息ThenIfSheet1.Cells(Z,5)=12AndSheet1.Cells(Z,5)36Thenlv=lv_ding2_12(Z)'1年EndIfIfSheet1.Cells(Z,5)=36AndSheet1.Cells(Z,5)60Thenlv=lv_ding2_36(Z)'3年EndIfIfSheet1.Cells(Z,5)=60Thenlv=lv_ding2_60(Z)'5年EndIfSheet1.Cells(Z,6)=lvEndIfIfSheet1.Cells(Z,3)=定活两便ThenIfSheet1.Cells(Z,4)=AndSheet1.Cells(Z,5)=Thendh=MsgBox(未区分各家银行计算方法，结果不一定准确，按利率60%计算？,vbYesNo,提示)Ifdh=vbYesThenIfSheet1.Cells(Z,5)6Thenlv=lv_ding1_3(Z)'3个月EndIfIfSheet1.Cells(Z,5)=6AndSheet1.Cells(Z,5)12