Excel会计与财务管理-理论、方案暨模型第11章Excel财务管理系统的建立课件.pptx

1、11-1本章主要内容：本章主要内容：11.1 宏和VBA 11.2 财务管理模型用户界面的设计 11.3 财务管理系统菜单的设计 11.4 控制应用系统自动启动的方法 11-211-3l VBA VBA是基于Visual Basic for Windows发展而来的新一代标准宏语言，提供了面向对象对象的程序设计方法和相当完整的程序设计语言。对于在工作中需要经常使用Office套装软件的用户，学习VBA有助于使工作自动化，提高工作效率。另外，由于VBA可直接应用Office套装软件的各项强大功能，所以对于程序设计人员的程序设计和开发也更加方便快捷。Excel VBA中有各种层次的对象对象，每个对

2、象又有其自身的属属性、方法和事件性、方法和事件11-4l 对象与对象集合v对象是代表应用程序的元素。具体到Excel中，对象就是工作簿、工作表、单元格、图表、窗体。VBA中使用对象的任一方法或属性之前，必须先识别对象。v对象集合对象集合是一个包含其它对象的对象，这些对象通常不是相同类型。他们经过一个关系集合全部连接到一个公用的源对象。集合中的成员是一单独的对象。如Workbooks集合中含有若干张Workbook，Sheets集合中含有若干张Sheet对象。具体内容参见“对象浏览器”11.1 宏VBA11-5l 属性、方法和事件属性、方法和事件v属性属性是关于某个对象的状态（数据），定义了对象

3、的特征，如球的大小、颜色等。可以通过修改对象的属性值来改变对象的特征。v方法方法是该对象可能被要求去执行的动作，如关闭、打开等。每一种想要对象做的操作都被称为方法。v事件事件是可被对象识别的动作，如单击鼠标、激活工作表等。并且可以编写代码来响应此动作。通常事件发生时，会执行相应的事件过程中的代码。11.1 宏VBA11-6l 属性、方法和事件举例属性、方法和事件举例VB中的窗体和控件是具有自己的属性、方法和事件的对象。可以把属性看作一个对象的性质，把方法看作对象的动作，把事件看作对象的响应。假设有电视对象，属性：大小、颜色、品牌。方法：打开、关闭、调频。事件：打开事件播放节目、调频事件调换节目

4、等用VBA编程如下：Television.Color=BlackTelevision.Size=43Television.Worked=False代码语法：对象（Television），接着属性（Color），对象和属性之间用点（.）隔开，然后是赋值（Black）通常：Object.Property Object.Method11.1 宏VBA11-7lExcel中的基本对象中的基本对象v Applicationv Workbookv Worksheetsv RangelVBA中的基本语句中的基本语句（自己复习自己复习）v 声明语句v 赋值语句v 可执行语句-顺序结构-选择结构-循环结构11.

5、1 宏VBA11-8l 宏 宏程序是指用VBA提供的各种函数、语句、对象、方法和属性等编写的程序。宏程序可以被多次重复使用，自动执行复杂的任务，减少完成任务所需的步骤。11-9l宏的创建和运行宏的创建和运行 v使用宏录制创建新宏 v使用Visual Basic 编辑器创建新宏11-10 宏录制器是Excel提供的一种软件工具，它能够将用户的操作过程记录下来，并自动将所记录的操作转换成VBA程序代码。对于经常重复进行的操作过程，可以通过宏录制器将它记录下来，当需要再次进行这些操作时，只需运行录制的宏，Excel就能自动完成这些重复的操作。需要注意的是，当录制宏的工作开始后，所有的操作步骤都将被记

6、录在宏中，所以应尽量减少不必要的或错误的操作，如果在录制宏时出现失误，更正失误的操作也会记录在宏中。11-11选择【开发工具】菜单上的【录制宏】命令。在“宏名”框中输入宏程序的名称。在“保存在”框中，指定存放宏的位置。在“说明”框中，输入要录制宏的有关说明信息。选择【确定】按钮，此时原来的【录制宏】变为【停止录制】按钮。用户可以进行各种操作，如选定单元格、输入标题、设置表格等。单击【停止录制】按钮，结束宏录制。11-12【例11-1】打开工作簿cwgl11，用录制宏的方式记录以下工作：插入一张工作表，在新插入的工作表上制作一张简单的分部销售情况表，表结构如图11-4所示。录制一个能够建立这种表

7、结构的宏Sales。图11-4：销售情况表11-13（1）选择【开发工具】菜单上【录制宏】命令；（2）在“宏名”框中输入宏程序的名称“Sales”；（3）在“保存在”框中指定存放宏的位置“当前工作簿”；（4）在“说明”框中，输入要录制宏的有关说明信息；（5）选择【确定】按钮，此时【停止录制】按钮出现在菜单上；（6）选择【插入】菜单上的【工作表】命令；（7）选择A1单元格，输入“销售情况表”；（8）依次在其他单元格输入文字，并调整字体字号；（9）选择范围A1:AF，合并单元格；（10）选择标题，居中显示，单击【字体】工具，选择14号，加粗字体；（11）单击【停止录制】按钮，结束宏录制。11-14

8、宏程序录制完毕后，选择工具栏上的【宏】命令，弹出“宏”对话框。在“宏名”框中选择宏程序“Sales”，单击【编辑】按钮，则可显示刚才所录制的宏程序“Sales”。11-15宏录制器存在问题：u录制的宏往往语句冗长（记录了许多不需要的步骤，不需要的语句），会降低执行速度u当录制宏的工作开始后，所有的操作步骤都将被记录在宏中，如果在录制宏时出现失误，更正失误的操作也会记录在宏中。有时为了更正操作，可能需要重新录制新宏u宏录制器在灵活度方面有所欠缺u有些功能是无法通过录制实现的 因此需要用编写宏程序11-16子程序 子程序是VBA的最小程序单位。函数 函数由Function和End Function

9、 语句所包含起来的 VBA语句组成。u子程序可以执行某种操作，但无返回值。uFunction函数和Sub子程序很类似，但函数有一个返回值。11-17（1）顺序结构（2）选择结构（3）循环结构11-18（1）顺序结构在一般情况下，一个VBA的Sub子程序和Function函数的执行都是从程序代码的第一个语句行开始，逐条运行程序代码中的语句，直至遇到End Sub或End Function来结束整个程序的执行。这就是顺序结构。11-19【例11-2】编写一宏程序。过程名：PROCE1u 功能：在cwgl工作簿中的“Sales”工作表中，赋予B1单元报表的标题“销售情况分析表”；赋予B2单元日期“2

10、014年12月”；在A3、B3、C3、D3、E3单元格中依次输入“部门”、“实际销售额”、“保本销售额”、“盈亏状况”、和“销项税”；在A4、B4、C4单元格中依次输入“计算机部”、“100”和“80”。11-20编制的宏程序如下：u Sub proce1()u Range(B1).Selectu ActiveCell.FormulaR1C1=销售情况分析表u Range(B2).Selectu ActiveCell.FormulaR1C1=2010年12月u Range(A3).Selectu ActiveCell.FormulaR1C1=部门u Range(B3).Selectu Acti

11、veCell.FormulaR1C1=实际销售额u Range(C3).Select11-21u ActiveCell.FormulaR1C1=保本销售额u Range(D3).Selectu ActiveCell.FormulaR1C1=盈亏状况u Range(E3).Selectu ActiveCell.FormulaR1C1=销项税u Range(A4).Selectu ActiveCell.FormulaR1C1=计算机部u Range(B4).Value=100u Range(C4).Value=80u End Sub 11-22 有些时候，需要程序代码按一定的条件执行，当条件成立时

12、，执行一部分程序代码，条件不成立时执行另外一部分程序代码。这种功能需要用VBA的选择结构来实现。VBA中的条件判断语句有If语句和Select Case语句两种11-23单分支语句IfThen。1.单行结构条件语句单行结构条件语句是最基本的条件语句，其语法结构为：If 逻辑表达式 Then 语句2.块结构条件语句单行结构条件语句中，满足条件时只执行一条语句，若有多行语句需要执行，则需使用块结构条件语句。其语法结构如下：If 逻辑表达式 Then 语句序列1 语句序列211-24 二分支语句IfThenElse。其语法结构如下：If 逻辑表达式 Then 语句序列1 Else 语句序列2 End

13、 If11-25【例11-3】编写一宏程序。过程名：PROCE3u 功能：如果实际销售额大于等于保本点销售额，在屏幕上输出“胜利完成任务！”，并在盈亏状态单元格赋“盈利”；否则在屏幕上输出“仍需努力！”，并在盈亏状态单元赋“危险”。11-26编写的宏程序如下：Sub PROCE3()If Range(B3).Value=Range(C3).Value Then MsgBox 胜利完成任务！Range(D4).Select ActiveCell.FormulaR1C1=盈利 Else MsgBox 仍需努力！Range(D4).Select ActiveCell.FormulaR1C1=危险 E

14、nd IfEnd Sub11-27 多分支语句：ifThenElseIf。语法结构为：If 条件1 Then 语句组1ElseIf 条件2 Then 语句组2 ElseIf 条件n Then 语句组nElse 语句组n+1End If11-28【例11-4】某汽车出租公司可为顾客提供货物运送服务，根据货物的价值及运输路程设置运费，并且可根据路程远近给予适当的折扣。设运费F（单位为元），货物单价P（单位为元），货物重量W（单位为吨），路程S（公里）及优惠系数（D）之间的关系式为：F=P*W*S*(1-D)。优惠系数D与路程远近的关系如下，编写计算折扣的函数。1000S%101000S750%77

15、50S500%5500S250%2250S0D11-29编写的函数如下：Function d(s)If s=1000 Then d=0.1 ElseIf s=750 Then d=0.07 ElseIf s=500 Then d=0.05 ElseIf s=250 Then d=0.02 Else d=0 End IfEnd Function 11-30 多分支语句：Select Case。Select Case语句的语法结构如下：Select Case 测试表达式Case 表达式1 语句组1Case 表达式2 语句组2 Case Else 语句组n End Select11-31【例】编写一

16、宏程序。过程名：PROCE4功能：如果实际销售额大于保本销售额，在屏幕上输出“盈利！”，并在盈亏状态单元格赋“盈利”；如果实际销售额等于保本销售额，在屏幕上输出“保本！”，并在盈亏状态单元格赋“保本”；如果实际销售额小于保本销售额，在屏幕上输出“亏损！”，并在盈亏状态单元格赋“亏损”。11-32编写的程序如下：Sub PROCE4()Dim sx As Double,bx As Double sx=Range(B4).Value bx=Range(C4).Value Select Case sx Case Is bx MsgBox 盈利！Range(D4).Select ActiveCell.

17、FormulaR1C1=盈利 End SelectEnd Sub 11-33 在顺序结构的程序中，每个语句只执行一次。在分支结构的程序中，根据逻辑表达式的值选择某一分支执行，所选分支的语句也只执行一次。然而，在处理实际问题时，经常要用同一种方法对不同的数据进行重复处理，这些相同的操作可以通过重复执行同一程序段来实现。这种重复执行具有特定功能程序段的程序就是循环程序。11-34 ForNext循环结构。语法结构为：For counter=start To end step 步长 循环语句1 循环语句2 循环语句3 循环语句nNext counter 该结构的功能是：将初值赋给计数器counter

18、。当初值start没有达到终值end时，VBA自动循环执行循环体中的循环语句，每执行一次循环，计数器加1或增加指定的步长step步长；当计数器达到终值时，跳出循环。11-35【例11-6】某公司职工档案数据保存在Excel工作表中，如图11-8所示。该公司共有1236名职工，每月要从工资表中扣除一定的住房公积金，假设住房公积金按以下的百分比扣除。2000S%102000S1500%71500S1200%51200S800%2800S500%1500S0D11-36首先，自定义函数计算公积金的百分比。Function countrate(salary As Double)Dim rate As

19、Double If salary 2000 Thenrate=0.1 ElseIf salary 1500 Then rate=0.07 ElseIf salary 1200 Thenrate=0.05 ElseIf salary 1000 Thenrate=0.02 ElseIf salary 800 Thenrate=0.01 Else rate=0 End If countrate=rateEnd Function11-37编写计算所有职工工积金的宏PRO5：Sub PROCE5()For i=3 To 1236 r=countrate(Cells(i,2)Cells(i,3)=r Ce

20、lls(i,3).Style=Percent Cells(i,4)=r*Cells(i,2)Cells(i,5)=Cells(i,2)-Cells(i,4)NextEnd Sub11-38DoLoop循环结构。语法结构是：Do While 条件 语句序列Loop 功能：当条件成立时，VBA重复执行语句序列直到条件不成立，跳出循环。11-39【例11-7】继续例11-6，假设并不知道有多少位职工，在其他条件都相同的条件下，现用DoLoop循环结构来计算每位职工的住房公积金比例、应交公积金和实际发放工资。11-40Sub PROCE6()i=3Dor=countrate(Cells(i,2)Cel

21、ls(i,3)=rCells(i,3).Style=PercentCells(i,4)=r*Cells(i,2)Cells(i,5)=Cells(i,2)-Cells(i,4)i=i+1Loop Until(IsEmpty(Cells(i,2)End Sub11-41For EachNext语句。For EachNext循环与ForNext循环类似，但它针对数组或对象集合中的每一元素重复一组语句，而不是重复语句一定的次数。语法结构：For Each 对象元素变量 in 对象集合 语句序列1 Exit For 语句序列2Next 对象元素变量11-42【例11-10】编写一程序过程名：PROCE

22、7功能：关闭所有打开的工作簿。编写的宏程序如下：Sub PROCE7()For Each Book in Workbooks()Book.Close Next BookEnd Sub11-431.在Excel界面运行宏2.在开发环境下运行宏 11-441.在Excel界面运行宏 在Excel界面运行宏，适用于所有环境，可通过【开发工具】，点击“宏”，再选择宏名，点击【执行】或者在工作表打开状态下直接调用快捷键ALT+F8，弹出宏界面，再选择宏名，点击【执行】。通过选择自定义菜单或点击自定义按钮的方式执行宏（详见本章11.2和11.3内容）将宏配置为在打开工作簿时自动运行（详见本章11.4节内容

23、）。11-452.在开发环境下运行宏 首先打开Microsoft Visual Basic界面，然后在【运行】菜单上单击【运行宏】，或者单击工具栏中的“运行宏”或者按F5键调出“宏”对话框。11-46 11.1 宏和VBA 11.2 财务管理模型用户界面的设计 11.3 财务管理系统菜单的设计 11.4 控制应用系统自动启动的方法 11-47本节主要内容：11.2.1 界面设计的主要工具11.2.2 建立各模块用户界面11.2.3 建立财务管理系统界面11-48主要工具：v 形状工具-插入/插图/形状v 绘图工具-开始/绘图v 表单控件-开发工具/插入/表单控件11-4911-501.绘制图形

24、2.建立图形与各功能模块之间的链接11-51 11.1 宏和VBA 11.2 财务管理模型用户界面的设计 11.3 财务管理系统菜单的设计 11.4 控制应用系统自动启动的方法 11-52本节主要内容：11.3.1 菜单管理器11.3.2 在菜单栏中增加【财务管理】菜单11.3.3 财务管理系统框架11-531.选择【文件】菜单下的【选项】【自定义功能区】命令，2.在弹出【自定义功能区】对话框“从下列位置选择命令”中选择【宏】，3.“自定义功能区”选择【主选项卡】后，就可以进行各种修改操作了。11-54财务分析账务处理筹资决策数据处理基础项目投资决策证券投资分析盈亏平衡与经济订货量最优化问题财

25、务预测比率分析杜邦分析长期借款筹资决策租赁借款决策.固定资产更新决策.债券投资分析模型股票投资分析模型盈亏平衡模型经济订货量模型资金管理生产管理投资组合销售预测模型利润预测模型Excel财务管理系统财务管理系统图11-17 Excel财务管理系统功能结构图11-55 11.1 宏和VBA 11.2 财务管理模型用户界面的设计 11.3 财务管理系统菜单的设计 11.4 控制应用系统自动启动的方法 11-56本节主要内容：11.4.1 对话框操作11.4.2 自动运行宏程序11.4.3 将财务管理系统所在的工作簿设置为在 Excel 启动时自动打开11-57 MsgBox函数:MsgBox对话框

26、函数可用以向用户显示一些提示信息。函数用法：MsgBox(prompt,buttons,title,helpfile,context)11-58其中各参数及其说明:参数参数说明说明prompt必选。字符串表达式，在对话框中显示为消息。prompt的最大长度约为1024个字符，具体长度取决于所用字符的宽度。如果prompt包含多行，可在各行之间使用回车符、换行符或回车符与换行符的组合来分隔各行。buttons可选。其值为数值表达式，它是值的总和，这些值指定要显示的按钮的数目和类型、要使用的图标样式、默认按钮的标识以及消息框的模态。如果省略此参数，则buttons的默认值为0。title可选。字符

27、串表达式，显示在对话框的标题栏中。如果省略title，则标题栏中将显示应用程序名称。helpfile可选。字符串表达式，用于标识为对话框提供上下文相关帮助的帮助文件。如果提供helpfile，必须同时提供 context。context可选。其值为数值表达式，它是帮助文件的作者为相应帮助主题分配的帮助上下文编号。如果提供 context，必须同时提供 helpfile。11-59VBA提供了两个非常有用的自动运行宏：Auto_Open将在打开工作簿时被自动执行 Auto_Close会在关闭工作簿时被自动调用11-60【例11-12】建立一个当工作簿打开时自动运行的宏程序，其功能是：显示“欢迎您

28、使用Excel财务管理系统”，并自动显示财务管理系统主界面。建立的宏程序如下：Sub Auto_Open()MsgBox(欢迎您使用Excel财务管理系统)Sheets(Excel财务管理系统界面).SelectEnd Sub11-61【例11-13】在“Excel财务管理系统”工作簿中，编写一个当工作簿关闭时自动运行的宏程序，其功能是：显示“感谢您使用财务管理系统，再见！”编写的宏程序如下：Sub Auto_Close()MsgBox(感谢您使用财务管理系统，再见！)End Sub11-62【例11-14】将“Excel财务管理系统”工作簿设置为自动启动工作簿。方法：用【文件】菜单中【另存为】命令或者文件管理器中的【复制】命令，将“Excel财务管理系统”工作簿复制到Excel目录下的XLSTART子目录下。11-63本章小结本章小结本章主要学习了建立完整的财务管理系统的方法，包括:宏的录制与编写建立用户界面设置宏按钮设计系统菜单将宏程序指定给宏按钮或菜单项（命令）编写自动运行宏设置模型自动启动其他11-64