标题: [教程] Excel 97/VBA技术讲座之一
性别:男-离线 Bicsa (电脑番茄)
Rank: 42Rank: 42Rank: 42Rank: 42Rank: 42Rank: 42
番茄出品,必属精品


超级版主

家园功勋 30圣月天使已经发29904贴,以是财大论坛万王之王圣月天使
UID: 4904
精华: 59
积分: 96270
帖子: 29904
威望: 69
水钻: 18 枚
人品: 2316
阅读权限: 200
注册: 2004-3-5
恋人: 刮开看███
来自: 刮开看███
状态:
荣誉勋章
社区建设勋章 金点子勋章 原创写手勋章 优秀版主勋章 论坛贡献勋章 水钻勋章 灌水高手 影音大师勋章 终身成就勋章 宣传大使勋章 爱心绿丝带勋章 迎新志愿者勋章 财大社区人物勋章 特殊贡献勋章 巨蟹座 毕业留念勋章 番字号 xiexie B型 风雨同舟 论坛10周年纪念Bicsa版 Bicsa个性章章
发表于 2009-1-11 10:49  资料  主页 短消息  加为好友  您是本帖第 3063个阅读者
Excel 97/VBA技术讲座之一

今年5月,美国Microsoft公司在中国推出了Office套件的最新版本——Office 97中文版。该套件的各个软件都提供Visual Basic for Application(VBA)作为其宏语言,为使用这些软件的广大用户提供了一个新的、更高层次的开发平台。即使是非计算机专业的用户,利用VBA也可以方便地开发出高水平的应用软件系统。本讲座主要以Excel 97为例,介绍VBA的基本概念、主要特点和应用VBA开发应用软件的基本步骤。  

    一、概述 1. Excel 97

    Office 97中文版是Microsoft公司第一个完全汉化的Office套件,是运行于Windows 95/Windows NT环境下应用最为广泛的软件之一。其中有Word 97、Excel 97、Power Point 97、Access 97和Outlook 97。其中的Access 97和Power Point 97是首次汉化,Outlook 97是新增的个人事务管理组件。  

    Excel 97是Microsoft Office 97家族成员。是一个功能强大、技术先进、使用方便的表格式数据综合管理和分析系统。它采用电子表格方式进行数据处理,工作直观方便;提供了丰富的函数,可以进行各种数据处理、统计分析、辅助决策,适用于财政、金融、统计、管理等广泛的应用领域;具有强大的制图功能,可以方便地绘出各种专业图表,实现了图、文、表三者的完美结合;新推出的Excel 97还新增了预防宏病毒的功能,插入地图和超级链接的功能(直接链接来自Internet或其它网络地址的文档);以及视面管理器、共享工作簿等功能。Excel 97的强大功能还在于它提供的宏语言VBA。  

    2. Visual Basic for Application

    Visual Basic是Windows环境下开发应用软件的一种通用程序设计语言,功能强大,直观易用。一方面,它是可视性程序设计语言,许多复杂的用户界面设计都可以直观地利用鼠标拖放以及对各种对象属性的设置、修改完成,几乎无需编程。另一方面,程序的控制普遍采用了事件驱动机制,根据用户操作触发的事件,执行不同的动作,完成相应的功能,因而都是一些短小精练的过程,容易设计和调试。VBA则是根据其嵌入软件的不同,增加了对相应软件不同对象的控制功能。例如Excel 97的VBA,主要增加了关于Excel工作簿、工作表、区域、数据透视表等对象的属性、事件和方法。在Excel 97中使用VBA,可以更方遍地操作Excel,更好地控制Excel,进一步深入发掘Excel的强大功能,全面提高使用Excel工作的自动化水平。甚至可以在短短的几天时间内就在Excel环境中开发出所需的完整的管理信息系统。而且VBA广泛地应用于Microsoft公司开发的各种软件中,例如Office 97套件中的Powerpoint 97、Word 97、Outlook 97等。所以本讲座介绍的内容不仅对Excel有用,对于其它包含Visual Basic的应用程序也同样有用。  

    3. VBA与Excel 97的关系

    早期电子数据表的宏语言是模仿用户界面的,实质上就是一些按键的组合,再加上一些类似于Basic语言中的语句,执行的是创建区域名的操作。这样的宏不仅难以阅读,而且也与图形用户界面不适应。Excel 5.0以前的版本开始使用独立于用户界面的宏语言。例如在Excel 4.0版中至少有三种不同的方法复制一个区域:使用Ctrl+C复合键;使用工具栏中的“复制”工具按钮;使用“编辑”菜单中的“复制”命令。所有这些用户操作序列都被翻译成单一的宏函数,=COPY( )。这样的宏的主要缺点是Excel宏只能用于Excel,而无法适应其它应用程序。从Excel 5.0开始使用VBA作为宏语言,Excel 97中的VBA功能更为强大,使用也更方便。  

    宿主于Excel 97中的VBA,不是嵌入在Excel内部,而是通过一组称作对象库的特殊命令与Excel关联的。VBA与其它软件的这种控制关系,使得VBA不仅可以控制Excel,而且可以控制任何提供对象库的应用程序。例如所有的Office 97应用程序都提供对象库,其它一些Microsft和非Microsoft应用程序也提供对象库。同时宿主于Excel的VBA也不是唯一与Excel 对象库通信的语言,还可以使用宿主于Word的VBA,独立版本的Visual Basic,C++以及Delphi等操纵Excel。  

    4. VBA与Excel 对象的关系    Excel 97的众多对象之间是相互关联的,构成了一个有机的整体。其中Application对象位于最上层,代表了整个Excel应用程序。包括应用程序的各种选项以及当前活动的各种对象。其下面主要有Workbooks(工作簿集合)对象、Window(窗口)对象、Dialog(对话框)对象等。而Workbooks对象下面又有Worksheets(工作表)对象、Chart(图表)对象、Window(窗口)对象、Name(名称)对象等。Worksheets对象下面还有Range(区域)对象、Name(名称)对象等。其中Application对象下面的Window对象与Workbook对象下面的Window对象是不同的。前者包含了Excel的所有窗口,而后者只包含指定工作簿中的窗口。  

    不同的对象通常具有不同的方法、属性和事件。例如Workbooks对象有Add、Close、Open等方法,Application、Count、Item、Parent等属性。集合型对象都有Count、Item属性,可得到相应集合中的对象个数或指定的对象。而除了Application对象以外的所有的对象都有Parent属性,可得到相应对象的父对象。不同对象包含的事件也是不同的。例如Worksheet对象就有Activate、Deactivate、Change……等七个事件,而Workbook对象则包含了多达19个事件。  

    VBA主要是根据用户对不同对象的操作、触发的相应事件,去激活相应的程序,通过对相关的对象执行不同的方法,或是修改相关对象的属性来完成特定的工作。例如,下面是一段VBA程序:

Sub Example( )  
    Range(“A1:F1”).Select
    Selection.NumberFormat = “$#.##0”
    ActiveWindow.DisplayGridlines = False
End Sub

该段程序首先选定A1:F1单元区域(使用Range对象的Select方法),然后将选定对象的格式设置成指定的货币样式(修改Selection对象的NumberFormat属性),最后消除窗口中的网格线(修改ActiveWindow对象的DisplayGridlines属性)。  

    由于Excel 97对象众多,而不同的对象其属性、方法、事件又有较大的差异,学习起来较为困难。Excel 97的VBA提供了对象浏览器、立即窗口、本地窗口、联机帮助等多种工具,可以帮助我们系统地了解和学习对象。在实际使用VBA的过程中,还随时可以利用VBA的自动列表功能。例如在输入了“Selection.”后,VBA将自动弹出Selection对象可用的方法和属性的自动列表供用户选择。而当输入了某个函数名后,VBA将自动弹出该函数可用参数的自动列表。学习VBA的最有效的方法是利用宏记录器录制有关的操作,将其转换成VBA宏语言,然后在其基础上再编辑修改,建立自己需要的宏。

[ 本帖最后由 Bicsa 于 2009-1-11 10:51 编辑 ]





独自走在蓝天上

http://baidu.com/baidu?word=bicsa
-------
顶部
性别:男-离线 Bicsa (电脑番茄)
Rank: 42Rank: 42Rank: 42Rank: 42Rank: 42Rank: 42
番茄出品,必属精品


超级版主

家园功勋 30圣月天使已经发29904贴,以是财大论坛万王之王圣月天使
UID: 4904
精华: 59
积分: 96270
帖子: 29904
威望: 69
水钻: 18 枚
人品: 2316
阅读权限: 200
注册: 2004-3-5
恋人: 刮开看███
来自: 刮开看███
状态:
荣誉勋章
社区建设勋章 金点子勋章 原创写手勋章 优秀版主勋章 论坛贡献勋章 水钻勋章 灌水高手 影音大师勋章 终身成就勋章 宣传大使勋章 爱心绿丝带勋章 迎新志愿者勋章 财大社区人物勋章 特殊贡献勋章 巨蟹座 毕业留念勋章 番字号 xiexie B型 风雨同舟 论坛10周年纪念Bicsa版 Bicsa个性章章
发表于 2009-1-11 10:50  资料  主页 短消息  加为好友 
Excel 97/VBA技术讲座之二

二、创建简单的宏

    初次创建或使用宏时,最好使用宏记录器。其基本步骤是:先将要完成的工作做一遍,以确认操作的正确;如果工作正常,启动宏记录器,再做一遍。此时宏记录器将录制所做的操作,并将其转换成VBA宏语言保存起来。最后再根据需要编辑修改录制的宏,并设置宏执行的方法。下面以创建纵向合并单元格的宏为实例,说明创建宏的基本步骤。Excel 97的“格式”工具栏上已提供了横向合并单元格的工具按钮。但是有些表格需要纵向合并若干个单元格,为此我们创建一个纵向合并单元格的宏。具体步骤如下:

    1. 执行相应操作    首先使用菜单命令一步步执行相应的操作。设要处理的工作表如下所示:

   
    图1

    需要纵向合并A栏的行标题 选定区域A3:A10。(此时标题位于选定区域的最上面的单元格。)
    单击“格式”菜单中的“单元格”命令,然后单击“对齐”选项卡。
    选中“合并单元格”复选框,在“水平对齐”和“垂直对齐”下拉列表框中选“居中”;然后拖拽“方向”框中的红点,使其朝上旋转90°。

   
图2

   单元格格式对话框(对齐选项卡) 最后单击“确定”完成合并及旋转标题。合并结果如下所示:
   
    图3  

    纵向合并A3:A10 2.

录制宏
    通过刚才的操作已正确地完成了纵向合并若干个单元格的操作。下面可以录制这些操作,并将它们转换成VBA程序。在开始创建宏之前,先右击任何工具栏,在弹出的工具栏快捷菜单中选择Visual Basic工具栏
    选择区域A3:A10,然后单击Visual Basic工具栏上的“录制宏”按钮
    图4       录制新宏对话框 在“录制新宏”对话框中,用“MergeVertical”替换默认的宏名,用“纵向合并单元格”替换默认的描述,单击“确定”。这时状态栏出现“录制”字样,并出现“停止录制”按钮。此时开始录制宏。
    执行一遍刚才做过的一系列操作。宏记录器立刻将相应的语句插入到宏中。
    单击“停止录制”按钮
    这样就完成了录制宏的操作。

    3. 查看录制的宏
    第一次录制宏时,Excel创建一个新模块。以后每次录制新宏时,Excel都将新宏添加到同一模块的结尾。当需要查看宏时可以单击“运行宏”按钮,选定要查看宏的名称,再单击“编辑”即可查看“代码”窗口中的VBA程序。刚才录制的VBA程序如下所示:

Sub MergeVertical( )
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlGeneral
        .WrapText = False
        .Orientation = 90
        .ShrinkToFit = False
        .MergeCells = True
    End With
End Sub

宏是以Visual Baisc编写的,并遵从标准Visual Basic规则:以Sub开始,以End Sub结束。该宏显示了关于单元格对齐的6个不同的属性,它们与前面介绍的对话框中看到的选项完全一致。这里出现的With结构使得程序的可读性更好,也更简练。这里相当与每个“.”前面的属性都是Selection对象。

    4. 运行宏    现在可以试用一下宏,看它是如何工作的。
    选定工作表的A11:A16单元格。
    单击Visual Baisc工具栏上的“运行宏”按钮
    从列表中选定MergeVertical宏,然后单击“运行”。

    宏将选定的单元格区域纵向合并。
   
   
图5

      纵向合并后的工作表 如果创建的是经常使用的宏,可以在创建宏时指定快捷键,或是在编辑宏时为创建的宏填加快捷键。注意:Ctrl+<字母>复合键大多已经是某些操作的快捷键,所以最好使用Ctrl+Shift+<字母>的方式定义宏的快捷键。定义快捷键时,Ctrl键为缺省的,故只需按Shift键和相应的字母键即可。这里我们指定Ctrl+Shift+M作为纵向合并单元格宏的快捷键。以后使用时只要选定需要纵向合并的若干个单元格,再按Ctrl+Shift+M复合键即可。使用更普遍的宏还可以做成工具按钮放到工具栏中,或是添加到菜单命令中。有关操作方法,将在后续讲座中介绍。如果需要具体了解宏是如何工作的,还可以选择单步执行方式,一步步地运行宏。(按F8键可以逐条执行宏语句)

    5. 优化记录宏
    使用宏记录器录制的宏通常都是机械的,录制完后通常需要修改它们,使其更通用、更简洁。例如上面录制的宏中,可以删除

“.WrapText = False”、“.ShrinkToFit = False”

等与合并单元格无关的语句。如果录制的宏中有选定具体单元格的语句,为了使宏更为通用,通常也应删除它们。为了增加宏的可读性,还可以在宏语句后面添加有关的说明或注释。

[ 本帖最后由 Bicsa 于 2009-1-11 10:53 编辑 ]





独自走在蓝天上

http://baidu.com/baidu?word=bicsa
-------
顶部
性别:男-离线 Bicsa (电脑番茄)
Rank: 42Rank: 42Rank: 42Rank: 42Rank: 42Rank: 42
番茄出品,必属精品


超级版主

家园功勋 30圣月天使已经发29904贴,以是财大论坛万王之王圣月天使
UID: 4904
精华: 59
积分: 96270
帖子: 29904
威望: 69
水钻: 18 枚
人品: 2316
阅读权限: 200
注册: 2004-3-5
恋人: 刮开看███
来自: 刮开看███
状态:
荣誉勋章
社区建设勋章 金点子勋章 原创写手勋章 优秀版主勋章 论坛贡献勋章 水钻勋章 灌水高手 影音大师勋章 终身成就勋章 宣传大使勋章 爱心绿丝带勋章 迎新志愿者勋章 财大社区人物勋章 特殊贡献勋章 巨蟹座 毕业留念勋章 番字号 xiexie B型 风雨同舟 论坛10周年纪念Bicsa版 Bicsa个性章章
发表于 2009-1-11 10:54  资料  主页 短消息  加为好友 
Excel 97/VBA技术讲座之三

三、创建复杂的宏    如果要完成的工作较为复杂,创建宏时,最好采用下述步骤:

    将工作分解成若干步骤;
    针对每个步骤分别创建宏;
    将创建的若干个宏连接成一个宏。
    这样可以使得创建、调试宏的工作较为简单,当需要修改宏时,牵涉的范围也较小。下面通过向已有图表添加数据的工作说明。该工作并不太复杂,只是通过它示意创建复杂宏的方法步骤。

    1. 分解复杂操作    现有一个有关沈阳万利基金的个股股票工作簿,其中已建立了该股票K线图。另外每天可以从实时股票系统中接收当天的股票行情。需要完成的工作就是根据每天接收的新数据,更新已有的股票信息工作簿及K线图。因为该工作每个股票交易日都要进行,因此创建一个宏使其能够自动完成是较为理想的。我们可以将该工作分成如下步骤:
    打开股票行情数据库,筛选出所需的个股数据,删除不需要的字段,调整字段顺序,并将其行列转置。  


图6  
行情数据工作簿 打开个股股票工作簿,将股票行情数据库中准备好的数据复制过来。
   
图7  
个股数据工作簿 更新K线图。
      
    图8      添加数据后的K线图

2. 创建分解的宏

    根据分解后的简单工作,按照上一节介绍的工作步骤,采用录制宏的方法创建三个宏:Import_Data、Copy_Data和Update_Chart。由于篇幅所限,不一一介绍具体录制宏的具体操作。以下给出录制、编辑后的VBA宏。

Sub Import_Data()
' 导入股票行情数据
' 快捷键: Ctrl+Shift+I
    Workbooks.Open FileName:="F:\EXCEL\ST1.XLS"
    Selection.AutoFilter
    Selection.AutoFilter Field:=2, Criteria1:="沈阳万利"
    Range("A1:K9").Select
    Selection.Copy
    Sheets.Add
    ActiveSheet.Paste
    Range("A:C,H:J").Select
    Range("C1").Activate
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Range("A2:E2").Select
    Selection.Copy
    Range("A3").Select
    Selection.PasteSpecial Paste:=xlAll, _
        Operation:=xlNone, SkipBlanks:=False, _
        Transpose:=True
End Sub
Sub Copy_Data()

' 复制股票行情数据
' 快捷键: Ctrl+Shift+C
    Windows("ST1.XLS").Activate
    Range("A3:A7").Select
    Selection.Copy
    Windows("ST2.XLS").Activate
    Range("A2").Select
    Selection.End(xlToRight).Select
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveSheet.Paste
End Sub
Sub Update_Chart()
' 更新K线图
' 快捷键: Ctrl+Shift+U
    Sheets("K线图").Select
    ActiveChart.SeriesCollection.Extend _
        Source:=Sheets("沈阳万利").Range("CS2:CS6"),_
        Rowcol:=xlRows, CategoryLabels:=False
End Sub

请注意,Import_Data宏中的文件名、路径名会因操作的环境不同,后半部分主要是关于工作表行列转置的操作的宏语句,具体操作方法可以参见联机帮助中“转置行与列”。Copy_Data宏中的Selection.End(xlToRight).Select语句表示选定当前行的最后一个包含数据的单元格(操作时按Ctrl+→键)。语句ActiveCell.Offset(0, 1).Range("A1").Select中的Offset属性表示相对引用,这里表示选定当前单元格同一行右边相临的单元格,其中的Range("A1")是虚拟单元格,并不表示实际的A1单元格。这样就保证了该宏的通用性。可以每次将数据都复制到指定工作表的最后一列。当需要使用相对引用时,在录制相应操作前单击“停止录制”按钮右边的“相对引用”按钮即可。

    3. 装配宏

现在已有了完成各个步骤的宏:
    Import_Data:导入股票行情数据。
    Copy_Data:将股票行情数据复制到个股数据工作表。
    Update_Chart:更新K线图。
    下面的工作是将其装配在一起。最简单的方法仍然是使用宏记录器,只是这次要录制的是运行其它宏的宏。首先单击“录制宏”按钮,键入宏的名称“Auto_Add”,单击“确定”。然后单击“运行宏”按钮,逐个运行Import_Data、Copy_Data、Update_Chart宏。最后单击“停止录制”按钮。录制的宏如下:

Sub Auto_Add( )
    Application.Run “ST2.xls!Import_Data”
    Application.Run “ST2.xls!Copy_Data
    Application.Run “ST2.xls!Update_Chart
End Sub

实际上宏名称前面的“Application.Run “ST2.xls!”可以简化掉,而不影响宏的运行。





独自走在蓝天上

http://baidu.com/baidu?word=bicsa
-------
顶部
性别:男-离线 Bicsa (电脑番茄)
Rank: 42Rank: 42Rank: 42Rank: 42Rank: 42Rank: 42
番茄出品,必属精品


超级版主

家园功勋 30圣月天使已经发29904贴,以是财大论坛万王之王圣月天使
UID: 4904
精华: 59
积分: 96270
帖子: 29904
威望: 69
水钻: 18 枚
人品: 2316
阅读权限: 200
注册: 2004-3-5
恋人: 刮开看███
来自: 刮开看███
状态:
荣誉勋章
社区建设勋章 金点子勋章 原创写手勋章 优秀版主勋章 论坛贡献勋章 水钻勋章 灌水高手 影音大师勋章 终身成就勋章 宣传大使勋章 爱心绿丝带勋章 迎新志愿者勋章 财大社区人物勋章 特殊贡献勋章 巨蟹座 毕业留念勋章 番字号 xiexie B型 风雨同舟 论坛10周年纪念Bicsa版 Bicsa个性章章
发表于 2009-1-11 10:56  资料  主页 短消息  加为好友 
Excel 97/VBA技术讲座之四

通过上一讲的学习,我们已初步掌握了如何利用VBA控制Excel。但都是利用宏记录器录制的VBA宏语句。在实际应用中,记录宏只能机械地重复执行某些操作,而不能完成其它更复杂的功能。这一讲将介绍如何编写超出记录宏以外的VBA程序,如何在编写宏时构造分支和循环,从而使编写出的宏功能更为强大。
    一、VBA控制结构    VBA是由Visual Basic语言发展而成的,其语言结构分为两部分:Visual Basic语言结构和其嵌入软件的对象模块(如Excel对象模块)。其中,Visual Basic是一套完全独立的Windows开发系统,是可视化的、面向对象的、采用事件驱动方式的结构化高级程序设计语言。与其它高级程序设计语言的控制结构一样,Visual Basic程序设计语言的控制结构分为三种:顺序结构、分支结构和循环结构。
    顺序结构:表示在每个过程或函数中程序依顺序执行每条语句,无条件判断转移和循环。原始记录宏的结构就属于顺序结构,既不能根据具体情况作出判断执行不同的操作,也不能对同一类问题自动地重复执行同一组操作。因而,要提高宏的灵活性,增加宏的功能,唯一办法就是修改宏,加入选择、判断、循环控制功能。
    1. 分支结构    VBA的分支结构有:If语句和Select Case语句。
    If 语句    If语句是根据条件判断控制程序的分支,有三种形式:一是单分支结构,仅当条件为真时执行Then语句块;二是双分支结构,当条件为真时执行Then语句块,条件为假时执行Else语句块;三是多分支结构,格式如下:

If 〈条件1〉 Then

〈语句块1〉

ElseIf 〈条件2〉 Then

〈语句块2〉

·

·

·

Else

〈语句块n+1〉

End If

当条件1为真时执行语句块1,否则,若条件2为真,则执行语句块2……如果所有的条件都不成立,则执行Else后面的语句块n+1。
    例如为了更方便地浏览指定的工作表,希望限定活动单元格指针只能在工作表的3~9列内移动;当活动单元格位于3~9列之外时,将单元格指针移至该行的第3列;当活动单元格位于第9列,再右移时,单元格指针自动移至下一行的第3列。为此可以使用If结构的宏实现。相应的VBA程序如下所示:

Sub Move( )

‘在工作表的3~9列内右移单元指针

‘快捷键 Ctrl+Shift+M

If  ActiveCell.Column < 3  Or  ActiveCell.Column > 9  Then

Cells(ActiveCell.Row, 3).Select

ElseIf  ActiveCell.Column = 9  Then

Cells(ActiveCell.Row + 1, 3).Select

Else

ActiveCell.Offset(0, 1).Select

End If

End  Sub

Select Case语句Select Case语句与If...Then...ElseIf语句非常相似,只是当判断控制仅取决于一个测试变量时,使用Select Case结构更有效、更简捷。Select Case语句的格式如下:

Select Case 〈测试变量〉

Case 〈表达式1〉

〈语句块1〉

Case 〈表达式2〉

〈语句块2〉

·

·

·

Case Else

〈语句块n+1〉

End Select

该语句的功能是:首先将测试变量的值与Case后面表达式的值相比较,哪个表达式与测试变量匹配就转向执行哪段语句块,执行完毕便转向End Select语句之后继续往下执行。如果所有的条件都不成立,则执行Case Else后面的语句块n+1。
    在Select Case语句中,测试变量可以是变量、属性或表达式。Case语句后面的表达式有可以为一般表达式,也可以为Is关系表达式。
    一般表达式可以由多个表达式组成,其间用“,”和“To”分隔,前者表示“或”,只要其中有一个表达式匹配即执行该Case语句对应的语句块。后者表示范围,指定范围时,表达式值须从小到大排列。例如:Case -20 To -15,15 To 20
    Is关系表达式。表示测试变量应该与Is关键字之后的表达式比较,当指定范围为真,则执行该Case下面的语句块,反之转向比较其它表达式。
    注意:测试变量的类型必须与Case后面表达式的类型一致。
    例如现有学生的考试成绩存放在StScore工作表中,需要根据考试成绩,在其右邻单元给出优、良、中、及格、不及格五个等级。这可以方便地利用Select Case语句实现。相应的VBA宏程序如下所示:

Sub Score( )

‘给出优、良、中、及格、不及格五个等级。

‘快捷键 Ctrl+Shift+S

    Select Case  ActiveCell.Value

      Case  Is < 60

        Cells(ActiveCell.Row, ActiveCell.Column + 1) = "不及格"

      Case  60 To 69.9

        Cells(ActiveCell.Row, ActiveCell.Column + 1) = "及格"

      Case  70 To 79.9

        Cells(ActiveCell.Row, ActiveCell.Column + 1) = "中"

      Case  80 To 89.9

        Cells(ActiveCell.Row, ActiveCell.Column + 1) = "良"

      Case  Else

         Cells(ActiveCell.Row, ActiveCell.Column + 1) = "优"

    End Select

End Sub






独自走在蓝天上

http://baidu.com/baidu?word=bicsa
-------
顶部
性别:男-离线 Bicsa (电脑番茄)
Rank: 42Rank: 42Rank: 42Rank: 42Rank: 42Rank: 42
番茄出品,必属精品


超级版主

家园功勋 30圣月天使已经发29904贴,以是财大论坛万王之王圣月天使
UID: 4904
精华: 59
积分: 96270
帖子: 29904
威望: 69
水钻: 18 枚
人品: 2316
阅读权限: 200
注册: 2004-3-5
恋人: 刮开看███
来自: 刮开看███
状态:
荣誉勋章
社区建设勋章 金点子勋章 原创写手勋章 优秀版主勋章 论坛贡献勋章 水钻勋章 灌水高手 影音大师勋章 终身成就勋章 宣传大使勋章 爱心绿丝带勋章 迎新志愿者勋章 财大社区人物勋章 特殊贡献勋章 巨蟹座 毕业留念勋章 番字号 xiexie B型 风雨同舟 论坛10周年纪念Bicsa版 Bicsa个性章章
发表于 2009-1-11 11:01  资料  主页 短消息  加为好友 
Excel 97/VBA技术讲座之五

2. 循环结构VBA的循环结构有:For...Next循环、For Each...Next循环和Do...Loop循环。
    For...Next循环For...Next循环重复执行一组语句,直到循环次数达到指定次数为止。其格式如下:

For 〈变量〉=〈初值〉 To 〈终值〉 Step 〈步长〉

〈语句块〉

Next 〈变量〉

下面举例说明For循环的使用方法。
    设计算机等级考试成绩存放在Deg_Score工作表上,其中笔试成绩存放在E列、上机成绩存放在F列,且这两个区域分别命名为Pening、Operating。现需要创建一个宏,将不及格的以红色显示、成绩优秀(笔试成绩和上机成绩都在85分以上)的以绿色显示。相应的VBA宏程序如下所示:

Sub DegreeScore( )

Dim i As Integer

For i = 1 To Range("Pening").Cells.Count

If Range("Penging").Cells(i) < 60 Then

    Range("Pening").Cells(i).Interior.Color = vbRed

End If

If Range("Operating").Cells(i) < 60 Then

    Range("Operating ").Cells(i).Interior.Color = vbRed

End If

If Range("Pening").Cells(i) > 85 And Range("Operating ").Cells(i) > 85 Then

    Range("Pening").Cells(i).Interior.Color = vbGreen

    Range("Operating ").Cells(i).Interior.Color = vbGreen

End If

Next i

End Sub

其中,Range("Pening").Cells.Count给出了区域内单元格的个数;Range("Pening").Cells(i)返回Pening区域内的第i个单元格;Range对象的Interior属性返回引用的新对象:Interior对象。Interior对象控制单元格内部的颜色和图案格式。此外,在Visual Basic中已经为常用颜色预先定义了名字,引用时直接引用颜色的名字即可。

For Each...Next循环    在Excel对象库中处理一类对象时,应使用集合。如WorkBooks(工作簿集合)、WorkSheets(工作表集合)等等,称为对象集或对象类。要从对象集合中选定单一对象,需要指明对象的序号或是对象的名称。例如,要得到Book2工作簿中第三页工作表的C9单元格数据,若不知道第三页工作表的名称,则可表示为:

Workbooks(“Book2”).Worksheets(3).Range(“C9”)

对于对象集或对象类,VBA提供了一种有效的循环控制结构:For Each循环,其格式如下:

For Each 〈对象变量〉 In 〈对象集〉
〈语句块〉
Next 〈对象变量〉

其中,For Each语句的作用类似于Set语句,将对象引用赋值给变量。不过For Each语句是将同一对象集中的每个对象引用依次赋值给同一变量。For Each循环的功能是为对象集中的每个对象重复执行一组语句。
    下面举例说明For Each循环的使用方法。
    Excel提供了打印预览工作表的方法,然而,每次只能对活动工作表进行打印预览。假设现有一个包含若个部门预算工作表的工作簿,在打印前需要预览每一个预算工作表,那么可使用For Each循环编写如下宏实现:

Sub PrintPreviewSheets( )

Dim mySheet As Worksheet

For Each mySheet In Worksheets

mySheet.Select

mySheet.PageSetup.Orientation = xlLandscape

mySheet.PrintPreview

Next mySheet

End Sub

PrintPreviewSheets宏的执行过程是:依次将工作表集合中的每个对象(工作表)引用赋值给变量mySheet,再选定对象指针指向的对象(即激活工作表),然后将页打印方向设置为横向,最后打印预览该工作表。
    同样,可使用For循环编写宏实现上述功能:

Sub PrintPreviewSheets( )

Dim mySheet As Worksheet

Dim i As Integer

For i = 1 To Worksheets.Count

Set mySheet = Worksheets(i)

mySheet.Selete

mySheet.PageSetup.Orientation = xlLandscape

mySheet.PrintPreview

Next i

End Sub

请注意区分:For语句是将一个值赋给变量,而For Each语句是将一个对象引用赋给变量。通常,使用For Each循环比使用For循环更方便、更快捷,但For循环的使用范围更广。例如,当需要利用计数器控制循环时,或要在两个同类的对象集(如两个区域)之间进行比较时,就只能使用For循环,而不能使用For Each循环。
   
Do…Loop循环    For Each循环通过对象集来控制循环的执行与结束,For循环通过计数器的初值、终值、步长来控制循环执行的次数。然而在某些情况下,需要通过条件判断来控制循环的执行与结束。为此,Visual Basic提供了另一种更有效的循环控制结构:Do循环。
    Visual Basic提供了多种灵活的Do循环形式,一方面可以用Until 〈条件〉或while 〈条件〉来控制循环,另一方面Until 〈条件〉和while 〈条件〉既可放在Do语句中,也可放在Loop语句中,使用十分方便。其中,最常用的Do循环格式如下:


Do While 〈条件表达式〉

〈语句块〉

Loop

该Do循环的功能是:只要条件为真,就重复执行语句块,直到条件为假时才结束循环,执行Loop语句的下一条语句。
    下面举例说明Do While循环的使用方法。
    假设需要对活动工作簿的Sheet1工作表先按第一列数据进行排序,然后删除包含重复数据的行,使用Do While循环编写如下VBA宏实现:

Sub DeleteRepeatData( )

Worksheets("Sheet1").Range("A1").Sort_

key1 :=Worksheets("Sheet1").Range("A1")

Set currentCell = Worksheets("Sheet1").Range("A1")

Do While Not IsEmpty(currentCell)

    Set nextCell = currentCell.Offset(1, 0)

    If nextCell.Value = currentCell.Value Then

        currentCell.EntireRow.Delete

    End If

    Set currentCell = nextCell

Loop

End Sub

Do循环具有很强的适应性,但使用时必须保证在循环体内有产生循环结束的程序代码,以避免出现死循环。譬如,在本例中若没有Set currentCell = nextCell语句,就会出现死循环。
    VBA提供了功能强大的分支结构和循环结构。如果能将循环控制结构与条件控制结构联合使用,则可以设计出灵活多变、功能强大的宏。
   
3. 自定义函数    在VBA的整个程序结构中,自定义函数是非常重要且十分方便的工具。
    自定义函数的结构与过程的结构非常相似,只是自定义函数的参数是必不可少的,且具有返回值。其基本结构如下所示:

  Function 〈函数名〉(〈参数1〉,〈参数2〉,…)
   
〈语句块〉
End Function

下面举例说明用户如何建立自定义函数。
    Excel和Visual Basic都有产生0与1之间随机数的函数。Excel的随机函数RAND( )可用于工作表,而Visual Basic的随机函数Rnd只能用于Visual Baisc宏。现建立一个可随时随地调用,不受上述限制的产生随机数的自定义函数Random。其宏程序如下:

Function Random(Optional  Midpoint = 0.5, Optional Range = 0.5, Optional Round = False)

Application.Volatile True

Random = Rnd * (Range * 2) + (Midpoint - Range)

If Round Then

    Random = CLng(Random)

End If

End Function

其中:
    函数的参数Midpoint、Range、Round描述了随机数区间,依次给出了:区间中点、正负范围、是否对随机数进行四舍五入取整运算。
    函数参数前的关键字Optional设定参数为可选项,参数后用等号给出参数的默认值。
    Application.Volatile True语句使得该函数为易失函数。对于大多数函数,只有当与函数的参数相关联的单元格值改变时,才重新计算。而有些函数(如Excel中的RAND( )函数),只要工作表任何一个单元格的值发生变化或按F9键,便重新计算,具有这种性质的函数称为易失函数。
    函数CLng将一个数舍入成整数,再转换为长整型。
    例如,选定某一单元格,键入下列公式:
    =Random(1000,500,True)
    则在此单元格产生1000-500到1000+500,即500到1500之间的随机整数。





独自走在蓝天上

http://baidu.com/baidu?word=bicsa
-------
顶部
性别:男-离线 Bicsa (电脑番茄)
Rank: 42Rank: 42Rank: 42Rank: 42Rank: 42Rank: 42
番茄出品,必属精品


超级版主

家园功勋 30圣月天使已经发29904贴,以是财大论坛万王之王圣月天使
UID: 4904
精华: 59
积分: 96270
帖子: 29904
威望: 69
水钻: 18 枚
人品: 2316
阅读权限: 200
注册: 2004-3-5
恋人: 刮开看███
来自: 刮开看███
状态:
荣誉勋章
社区建设勋章 金点子勋章 原创写手勋章 优秀版主勋章 论坛贡献勋章 水钻勋章 灌水高手 影音大师勋章 终身成就勋章 宣传大使勋章 爱心绿丝带勋章 迎新志愿者勋章 财大社区人物勋章 特殊贡献勋章 巨蟹座 毕业留念勋章 番字号 xiexie B型 风雨同舟 论坛10周年纪念Bicsa版 Bicsa个性章章
发表于 2009-1-11 11:06  资料  主页 短消息  加为好友 
Excel 97/VBA技术讲座之六

二、VBA程序调试    在程序运行过程中,总会或多或少地遇到各种各样的错误,这些错误大致可分为以下几类:
    语法错误:指程序的某一语句的语法出现错误,如左右引号或括号不匹配等。当程序设计人员输入完一行语句时,Visual Baisc编辑器会自动检测语法错误,并提醒程序员错误所在。
    编译错误:指在程序的编译过程中检测出来的错误。只检测单行语句是不能发现编译错误的。例如,以For Each开始的循环结构没有Next语句。通常语法错误和编译错误是容易发现和改正的。
    逻辑错误:指思维错误——导致程序运行结果与程序员的预期结果不同的编程思路错误。例如,想把工作簿的标题改为“My Workbook”,却拼写成“My Werkbook”;应该是“>=”却写成“>”等等。逻辑错误是最常见也是最麻烦的一类错误,程序调试的大部分时间都耗费在发现和纠正逻辑错误上。一般可通过设置断点、单步执行、观察值的变化来发现和纠正逻辑错误。
    实时错误:指在程序运行过程中发现的错误。有时过程中的某条语句在某些条件下能正确执行,而在另一些条件下就不能正确执行。例如,有一条语句用于打开硬盘上某个文件,若文件存在,可以打开,语句执行正确;若文件不存在,怎么打开?在实时错误中,有些是Visual Baisc能指出错误所在的,有些是程序员能预料的、能让Visual Baisc自动处理的。
    在程序调试过程中,VBA提供了各种强有力的调试工具来查找和纠正错误。
    1.使用中断模式    中断模式是指在程序执行过程中被暂时停止。此时所有变量、属性、表达式之值都维持在最新的状态,可以进行分析、测试或是重新设置等,以便检错纠错。
    进入中断模式的方法有以下几种:
    单步执行:按F8键。单步执行类似在下一条语句上设置断点,执行当前语句并清除断点。单步执行时,可观察每条语句的执行情况。
    设置断点:单击要设置断点的语句行的左侧边界区域;或从“调试”菜单中选“切换断点”命令。相应语句行左侧出现一个深红色圆点,该行也以深红色背景显示。
    设置临时断点(即只用一次的断点):单击要设置断点的语句行,从“调试”菜单中选“运行到光标处”命令。
    在宏程序中需中断处加入Stop语句。
    在宏执行时,按Ctrl+Break键。
    在宏执行时,产生实时错误,程序自动进入中断模式。
    符合〈监视表达式〉的条件与设定,程序进入中断模式。
    2.利用各种窗口    在VBA程序设计环境中,提供了“代码”窗口、“本地”窗口、“立即”窗口、“监视”窗口、“工程”窗口、“属性”窗口和“对象浏览器”窗口等7个窗口。从“查看”菜单中选择相应的命令可以显示出这些窗口。其中,“代码”窗口、“本地”窗口、“立即”窗口、“监视”窗口是调试程序的得力工具。

   

    “代码”窗口:在中断模式下运行宏时,可以通过“代码”窗口来仔细地观察宏的执行过程,也可以通过将鼠标指针指向某变量来检测变量的值。此时,在“代码”窗口内,准备执行的语句以黄色高亮方式显示,在其左边有一个黄色箭头。
“本地”窗口:在运行一个宏时,可以通过“本地”窗口观察宏程序所使用的变量、表达式、对象的变化来寻找程序错误;也可在“本地”窗口直接改变属性值,这与用VBA语句改变属性具有同样的效果;还可以通过“本地”窗口快速浏览某个对象的所有属性。它清楚地显示了哪些是包含值的属性(在值栏中有值的属性)和哪些是包含对象引用的属性(在旁边有加号的属性)。通过单击属性的值看它能否改变,能很容易地发现具有值的属性是不是只读的。
    “立即”窗口:在程序调试中,有时需要给某一变量指定一个新值,或输出显示某些变量的值,对此使用“立即”窗口最为方便。在“立即”窗口,可以输入任何语句并立即执行它。例如,要查看活动工作簿中所有工作表的名称,则可在“立即”窗口中键入语句For Each x in Workskeets: ?x.Name:Next x。通常,在“立即”窗口中,一条语句占一行,当多条语句排列在一行上时,要用冒号将其分开。并且在“立即”窗口中大都使用简短、无意义的变量名,这并不影响语句的执行。
    “监视”窗口:在执行宏前,或进入中断模式后,打开“监视”窗口,添加监视表达式以便观察关键的测试变量或表达式随宏执行的变化情况。在“监视”窗口中添加监视表达式的方法是:使用鼠标选中监视表达式,再单击“调试”菜单上的“添加监视”或“快速监视”命令。要移去监视的变量,只需单击该变量,再按Del键。
    3.错误处理    当发生实时错误时,VBA一般会显示一个错误信息对话框,进入中断模式。对于这样的错误,在宏设计中可以采用程序加以控制、监测错误,这种方法称为“错误捕获”。它可检测出错误并控制程序的流向。
    设置忽略错误    语句1:On Error Resume Next
    表示:若发生错误,则忽略它,跳到下一条语句继续执行。
    语句2:On Error GoTo 行号(或标号)
    表示:若运行有错,则跳到标号指定位置,转去执行错误处理例程。
    获取错误信息    在宏执行过程中,可以通过专用调试对象Err的属性值来了解是否发生实时错误以及发生了什么样的错误。若Err.Number值为0,则表明没有产生错误,反之有错误。
    下面通过两个例子说明如何使用错误捕获技术。
    例1,假设有一学生档案工作簿StudentBook,其中已有若干学生档案工作表Student1、Student2……以及其它一些工作表。现需要建立一个新的Student学生档案工作表,但又不想删除已有的Student工作表,如同Excel增加新工作表一样,只是将工作表名称的后缀加1。则实现这一功能的VBA宏程序如下所示:

Sub MakeNextStudent( )

Dim Sheet As Worksheet

Dim Base As String

Dim Suffix As Integer

Set Sheet = WorkSheets.Add

Base = “Student”

Suffix = 1

On Error Resume Next

Sheet.Name = Base & Suffix

Do Until Err.number = 0

Err.Clear

Suffix = Suffix + 1

Sheet.Name = Base & Suffix

Loop

End Sub

MakeNextStudent宏的执行过程是:先建立一个新工作表,再试着以Student为基本名、1为后缀构成的名称给新工作表命名。On Error Resume Next语句的作用是:若已有同名工作表存在,Excel不能给新工作表命名时,Visual Baisc并不终止所执行的程序,而是自动给Err对象的Number属性赋值,设置出错信息代码,执行Do Until循环。Do Until循环首先检查命名是否成功,若不成功,则增加后缀值,再试着重新命名,再检查……直到没有产生错误(即重新命名成功Err.Number=0),才结束循环。
    Do语句后面的Err.Clear语句的作用是将出错信息代码重新置为0,让Visual Baisc忘记曾经发生的错误。
    例2,下面宏的功能是:当试图删除打开的文件时,错误处理例程先调用MsgBox函数提示用户文件正在使用,让用户确认是否要删除该文件,再作相应的处理。

Sub KillFile( )

On Error GoTo KillFile_Err

Open "MyFile" For Output as #1

Kill "MyFile"

Exit Sub

KillFile_Err:

myCheck = MsgBox("MyFile文件正在使用,是否要删除?", vbYesNo)

If myCheck = vbYes Then

Close #1

Kill "MyFile"

End If

End Sub

综上所述,VBA为程序的调试、查错、纠错以及错误处理提供了方便的工具和方法,是一个优秀的程序设计环境。





独自走在蓝天上

http://baidu.com/baidu?word=bicsa
-------
顶部
性别:男-离线 Bicsa (电脑番茄)
Rank: 42Rank: 42Rank: 42Rank: 42Rank: 42Rank: 42
番茄出品,必属精品


超级版主

家园功勋 30圣月天使已经发29904贴,以是财大论坛万王之王圣月天使
UID: 4904
精华: 59
积分: 96270
帖子: 29904
威望: 69
水钻: 18 枚
人品: 2316
阅读权限: 200
注册: 2004-3-5
恋人: 刮开看███
来自: 刮开看███
状态:
荣誉勋章
社区建设勋章 金点子勋章 原创写手勋章 优秀版主勋章 论坛贡献勋章 水钻勋章 灌水高手 影音大师勋章 终身成就勋章 宣传大使勋章 爱心绿丝带勋章 迎新志愿者勋章 财大社区人物勋章 特殊贡献勋章 巨蟹座 毕业留念勋章 番字号 xiexie B型 风雨同舟 论坛10周年纪念Bicsa版 Bicsa个性章章
发表于 2009-1-11 11:08  资料  主页 短消息  加为好友 
Excel 97/VBA技术讲座之七

通过前两讲的介绍,我们已掌握了如何录制和编写VBA宏,将日常工作自动化的方法。但是要让一个对Excel了解不多的人也能够正确使用你编写的宏,或是要开发完整的应用系统,应该说还有很多内容需要学习和掌握。这一讲将介绍开发VB工程的基本概念,介绍如何将各种ActiveX控件添加到工作表中,并通过实例说明封装应用系统的主要步骤。
    一、开发Visual Basic工程    前面创建和编写的宏都是独立完成某个功能的一段Visual Basic程序。在一个工作表或是一个工作簿中可能有多个宏,它们都存储在模块(Module)中。一个工作簿中可以有多个模块,每个模块中可以有多个宏,有的还可能有一些事件控制程序。Visual Basic将所有的程序作为一个“工程”,通过“工程资源管理器”进行管理。首先单击Visual Basic工具栏上的“Visual Basic编辑器”按钮 ,进入Visual Basic。然后单击“工程资源管理器”按钮 ,可以通过“工程”窗口查看和管理工作簿中的所有程序。

   

    从中可以看出有些程序存储在模块中,有些程序则附在工作表中。需要注意的是附在工作表中的程序会随着复制工作表复制到新的工作表,也会随着工作表的删除而被破坏。而且它的应用范围也局限于相应的工作表。
   
1. 事件驱动    Visual Basic程序的突出特点之一是它的事件驱动机制。该机制使得开发程序时,隐藏了许多Windows程序设计中繁琐的细节,而将主要精力放在程序中事务的处理上。Excel 97/VBA针对Excel 97的多种对象提供了多种不同的事件。例如工作表,有Activate、Deactivate 、Change、SelectionChange等7个事件,其中SelectionChange为默认事件。而工作簿有15个事件,其中WorkbookOpen为默认事件。如果需要在用户执行了某个动作后,完成相应的操作,只需将相应的程序代码写入到有关事件的事件控制程序中即可。
    2. 事件控制程序    事件控制程序同宏一样,也是一段VBA程序。与宏不同的是,它不是通过宏对话框来编辑和运行,而是通过用户激活的事件来驱动。一段典型的事件控制程序如下所示:

Private Sub Workbook_Open()
        Sheets("Sheet1").Select
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    End Sub

第一个关键字Private说明该程序不出现在宏对话框中。程序的名字中,下划线前面部分为对象的名称,后面部分为事件的名称。该事件控制程序在工作簿打开时执行,其操作是选定第一个工作表,并将其对象、内容、方案等置为保护模式。
    要创建事件控制程序,可以在Visual Basic环境中,先在“工程”窗口选定工作簿或某个工作表,然后单击“查看代码”按钮,或是直接单击“视图”菜单中的“代码窗口”命令。此时出现代码窗口,左边的下拉列表框列出了当前所有的对象;右边的下拉列表框则列出了相应对象的所有事件。选定某个对象及事件,输入程序代码即可。也可以将录制的宏语句复制、粘贴过来。
    利用工程资源管理器,我们可以将众多的宏和事件控制程序有机地进行组织,创建功能强大的、高度自动化的应用系统。
   
二、ActiveX控件    以前,创建图形用户界面是计算机专家才有能力涉足的领域,现在越来越多的可视化程序设计工具,使得用户可以方便地在自定义的对话框中加入图形控件。Excel97/VBA也有这些功能,用户几乎不用编写什么程序,就可以在工作表中直接使用各种图形化的控件。这些控件称作ActiveX控件,它们使得宏更容易使用 。下面通过创建一个称作“投资小助手”的实例说明一些常用ActiveX控件,例如列表框、数值调节钮、组合框等的应用方法。
    现在的老百姓普遍有了一些结余的钱,存银行、上保险、炒股票还是购国库卷;如果上保险,是趸交(一次付款),还是分期付款;如果是分期付款,是分5年、10年还是20年付清。怎么样收益最好?这个问题用Excel 97的现值函数(PV)和未来值函数(FV)可以方便的帮助做出分析。但是需要清楚地了解这些函数的用法,Excel 97的操作。我们为不会用Excel的朋友创建一个方便的小小计算器,如下所示:

   

    这样当在只要在相应单元格中输入有关的数据,即可立即算出相应的现值和未来值。例如购买某保险,按20年限交,每年需交503元,而趸交则需交4878元。如果直接按交款额计算,分期付款共付503×20=10060元。显然趸交合适,但是如果考虑利息,按月息0.71%计算(目前一年定期的利率),其现值为4753元,即分期付款更划算。如果按国库卷等其它投资的利率计算,则分期付款更为划算。
    这个小助手虽然好用,但是还有点缺陷。例如当输入利率8.52而未加小数点时,它也会接受,而算出一个完全不切实际的值。下面我们为这个小计算器添加一些ActiveX控件,防止它在计算过程中出错,并且使它更容易使用。
    首先限定周期的数值只能在5、10、15……30之间变动。为此添加一个“数值调节钮”控件。从“视图”菜单中选“工具栏”命令,单击“控件工具箱”。单击“控件工具箱”中的“数值调节钮”控件,然后在周期单元格的右侧拖拽出一个大小合适的“数值调节钮”。这时自动进入Visual Basic设计模式,此时的控件周围有控制柄,可以拖放它们以改变控件的大小。当鼠标置于控件上时,鼠标指针会变成十字星形状,可以拖放控件移动它的位置。

   

    单击“控件工具箱”上的“属性”按钮,在出现的属性窗口中设置Max属性值为30,Min属性值为5,LinkCell属性值为C4(它使得单击“数值调节钮”时,C4单元格的内容会相应变动),SmallChange属性值为5(它使得每单击“数值调节钮”一次,C4单元个的值增加或减少5)。
    下面为利率添加数值调节钮。希望利率的取值范围是5~15,变化率为0.01。由于这个数值增减幅度很小,再使用数值调节钮不太方便。故使用滚动条控件。这样当需要大幅度变化时,可以直接拖动滑块,当需要精细调节时可以使用两端的调节钮。但是无论是数值调节钮还是滚动条,都只能以整数值增减。为此需要利用一个中间单元实现0.01的变化。先按创建数值调节钮的方法在E3单元格创建一个滚动条。然后设置Max、Min、SmallChange的属性值分别为1500、500和1。但是Linkcell属性的值不能直接用C3,而是用K3,同时相应地在C3单元格键入公式“K3/10000”,即可实现数值调节钮按1%增减。
    再给投资额也创建一个滚动条,为了使创建的控件大小一致,可以采用复制控件的方法创建。再设置Max、Min、SmallChange和LinkCell属性的值分别为30000、100、1和C7。完成的“投资小助手”如下所示。

   

    最后还需要将该工作表保护起来,使得使用者只能通过控件指定数值,而不能在单元格中直接键入数据,否则还会出现错误。例如如果在C3单元格输入一个数,将清除单元格中的公式,导致相应的滚动条时效。如果简单地将将工作表保护起来,虽然会避免用户输入无效值,但工作表被保护起来的同时,连ActiveX控件也不能改变相应单元格的值了。我们采用另一种方法,既可以保护工作表 ,又能用Visual Basic程序改变单元格的值,这就是用事件控制程序。
    首先,把与控件相连的单元格清除,即不用控件往单元格内填值,转而用事件控制程序往单元格内填写值。单击“控件工具箱”上的“设计模式”按钮,进入“设计模式”。选定第一个滚动条,将其的LinkCell属性清空。再单击“查看代码”按钮,在名为Private Sub ScrollBar1_Change()的事件控制程序中,添加下述语句:

Range("C3").Value = ScrollBar1.Value / 10000

类似地清除“数字调节钮”和第二个滚动条的LinkCell属性,在ScrollBar2_Change()事件过程中添加语句:

Range("C5").Value = ScrollBar2.Value

在SpinButton1_Change()事件过程中添加语句:

Range("C4").Value = SpinButton1.Value

这些事件控制程序使得单元格中的值随着控件的变化而变化。这时可以保护工作表了。在“工程”窗口中双击“ThisWorkbook”对象,进入“代码”窗口。在Workbook_Open()事件控制程序中加入下述语句:

Sheets(“Sheet1”).Protect User InterfaceOnly := True

Workbook_Open事件过程是工作簿的默认事件,每当工作簿打开时执行。该语句的UserInterfaceOnly参数 使得宏可以改变那些用户和控件不能改变的单元格。这样用户就不能往工作表中输入内容了,实际上也不必输入,只要用鼠标简单操作哪些控件就可以了。图形用户界面最大的好处是将可选功能限制于那些有效功能之间,因此减少,甚至杜绝了用户出错的机会。





独自走在蓝天上

http://baidu.com/baidu?word=bicsa
-------
顶部
性别:男-离线 Bicsa (电脑番茄)
Rank: 42Rank: 42Rank: 42Rank: 42Rank: 42Rank: 42
番茄出品,必属精品


超级版主

家园功勋 30圣月天使已经发29904贴,以是财大论坛万王之王圣月天使
UID: 4904
精华: 59
积分: 96270
帖子: 29904
威望: 69
水钻: 18 枚
人品: 2316
阅读权限: 200
注册: 2004-3-5
恋人: 刮开看███
来自: 刮开看███
状态:
荣誉勋章
社区建设勋章 金点子勋章 原创写手勋章 优秀版主勋章 论坛贡献勋章 水钻勋章 灌水高手 影音大师勋章 终身成就勋章 宣传大使勋章 爱心绿丝带勋章 迎新志愿者勋章 财大社区人物勋章 特殊贡献勋章 巨蟹座 毕业留念勋章 番字号 xiexie B型 风雨同舟 论坛10周年纪念Bicsa版 Bicsa个性章章
发表于 2009-1-11 11:09  资料  主页 短消息  加为好友 
Excel 97/VBA技术讲座之八

三、封装应用系统    通过以上介绍,了解了Visual Basic工程的基本概念,掌握了应用ActiveX控件的方法。以下通过实例说明封装一个应用系统的大致过程。
    该实例是一个虚拟的有关保险公司不同险种缴费的查询系统。保险公司有多个险种,不同的险种其缴费方法、缴费金额各不相同。例如递增养老保险,不同性别标准不同;相同性别开始领取年龄不同缴费标准也不同;同一领取年龄,还有趸交、十年限交、十五年限交…等不同付款方式,每次缴费额亦不同。所以相当复杂。该系统选定输入某个险种后,根据该险种的规则,指定其中若干项后,即可输出相应的缴费标准。
    为了使软件更吸引人,通常都采用生动的图形化界面,如有可能,还可以利用VBA的时钟控件创建动画效果。由于篇幅所限,该软件的主画面只是由一个图片和几个按钮组成。当打开该工作簿时,首先出现软件的主画面:

   

    该软件虽然是在Excel 97中创建的,但是从主画面看很像一个独立的应用系统。有自己的标题和自己的菜单栏。这需要用自定义的菜单栏、窗口和工具栏替换Excel 97的环境,并保证当退出时还原初始的Excel 97环境。下面将具体说明是如何完成它们的。
   
1. 菜单栏的替换替换Excel 97的菜单栏是很容易的,只需创建一个新的菜单栏就会删除Excel 97的菜单栏。当需要恢复Excel 97的菜单栏时,只要删除新创建的菜单栏就可以了。该系统的自定义菜单中只需两个命令按钮,一个用来返回到系统的主画面(ReturnMAIN),另一个用来退出系统(ExitSYS)。下面是模块(Module)中有关的宏或是事件控制程序。

  Sub ZapMenu( )
        On Error Resume Next
        CommandBars(“保险查询系统”).Delete
    End Sub
    这是一个用来删除自定义菜单栏的宏。语句On Error Resume Next保证无论自定义菜单栏是否存在都能正确删除它。
    Sub ExitSYS( )
        ZapMenu
        ActiveWorkbook.Close SaveChanges := False
    End Sub
    这是用来退出系统的宏。它删除自定义菜单,并关闭活动的工作簿(不提示保存修改)。
    Sub ReturnMAIN( )
       Worksheets(“保险查询系统”).Select
    End Sub
    该宏用来返回主画面。它激活“保险查询系统”工作表。
    Sub SetMenu( )
        Dim myBar As CommandBar
        Dim myButton As CommandBarButton
        ZapMenu
        Set myBar = CommandBars.Add(Name:=“保险查询系统”, _
            Position :=msoBarTop, _
            MenuBar :=True)
        Set myButton = myBar.Controls.Add(msoControlButton)
        myButton.Style = msoButtonCaption
        myButton.Caption = “退出[&E]”
        myButton.OnAction = “ExitSYS”
        Set myButton = myBar.Controls.Add(msoControlButton)
        myButton.Style = msoButtonCaption
        myButton.Caption = “返回[&R]”
        myButton.OnAction = “ReturnMAIN”
        myButton.Visible = False
        myBar.Protection = msoBarNoMove + msoBarNoCustomize
        myBar.Visible = True
    End Sub

这个宏包含五部分。第一部分定义了一对变量。第二部分首先运行ZapMenu宏,保证保险查询系统菜单栏是不存在的,然后创建它。参数MenuBar的值设为True,确保这个新创建的命令栏为一菜单栏。第三部分和第四部分将两个命令按钮加入到菜单栏中。并设置ReturnMAIN命令按钮的初始状态为不可见状态。最后一部分保护这个新创建的菜单栏,使用户不能移动也不能自定义新菜单栏。
   
2. 窗口的替换    封装应用系统时为应用系统创建大小合适的窗口是很有必要的。但当关闭应用系统时,必须恢复被应用系统覆盖的窗口。恢复窗口比恢复Excel 97的菜单栏要困难,因为必须记忆被覆盖的窗口的大小。可以使用变量保存被覆盖的窗口的大小。但是应注意用关键字Dim声明的变量只有在程序运行时才存在,程序运行结束后,其中的值是不保存的。因此应使用关键字Static声明变量。保存和恢复窗口的程序如下:

Sub SetWindow(State)
        Const MyWidth = 420
        Const MyHeight = 320
        Static myOldWidth
        Static myOldHeight
        Static myOldState
        If State = xlOn Then
            myOldWidth = Application.Width
            myOldHeight = Application.Height
            myOldState = Application.WindowState
            Application.WindowState = xln1g
            Application.Width = myWidth
            Application.Height = myHeight
            Application.Caption = “保险查询系统”
            Application.DisplayFormulaBar = False
            Application.DisplayStatusBar = False
            ActiveWindow.DisplayHorizontalScrollBar = False
            ActiveWindow.DisplayVerticalScrollBar = False
            ActiveWindow.DisplayWorkbookTabs = False
        Else
            Application.Caption = Empty
            Application.Width = myOldWidth
            Application.Height = myOldHeight
            Application.WindowState = myOldState
            Application.DisplayFormulaBar = True
            Application.DisplayStatusBar = True
            Application.DisplayFormulaBar = True
            Application.DisplayStatusBar = True
            ActiveWindow.DisplayHorizontalScrollBar = True
            ActiveWindow.DisplayVerticalScrollBar = True
            ActiveWindow.DisplayWorkbookTabs = True
        End If
    End Sub

可以用“SetWindow xlOn”和“SetWindow xlOff”语句设置用户窗口和恢复Excel 97窗口。其中的xlOn、xlOff为Excel 97内置常量。注意其中的“l”是小写英文字母“l”,而不是数字“1”。
   
3. 工具栏的替换    类似地还要移去Excel 97的工具栏,并在退出应用系统时恢复它。但是工具栏的保存和恢复较窗口复杂,窗口只要三个变量保存高度,宽度,状态就可以了。而工具栏则可能是一个系列,数目不定,有时可能还有不知名称的自定义工具栏。为此可以使用自定义集合(它是Visual Basic的十分有用的工具)来处理。有关的宏如下:

  Sub SetBars(State)
        Static myOldBars As New Collection
        Dim myBar
        If State = xlOn Then
            For Each myBar In Application.CommandBars
                If myBar.Type <> 1 And myBar.Visible Then
                    myOldBars.Add myBar
                    myBar.Visible = False
                End If
            Next myBar
        Else
            For Each myBar In myOldBars
                myBar.Visible = True
            Next myBar
        End If
    End Sub

将下述语句添加到ExitSYS宏中:

SetWindow xlOff
    Set Bars xlOff

以保证当单击“退出”按钮时,能恢复Excel 97的原来的环境。


4. 完成封装    应用系统中所有的宏和事件控制程序完成后,还需要使它们在打开工作簿时能自动连接为一个整体。完成各自的功能。故最后在Workbook对象中插入下述程序:

Private Sub WorkBook_Open( )
        SetMenu
        SetWindow xlOn
        SetBars xlOn
    End Sub
    Private Sub WorkBook_BeforeClose(Cancel As Boolean)
        ZapMenu
    SetWindow xlOff
        SetBars xlOff
   
        ActiveWorkbook.Saved = True
End Sub

前者使得每次打开该工作簿,都能保证显示系统的主画面以及相应的菜单。后者则使得每次关闭工作簿时(防止用户不通过“退出”按钮,而直接通过窗口的关闭按钮关闭工作簿),能删除用户的自定义菜单,恢复Excel 97的环境。设置活动工作簿的Saved属性值为True,使Excel认为所有的修改均已存盘,不显示提示存盘信息。
    到此为止,应用系统已经封装完成了,它虽然简单,但仍不失为一个封装良好、具有一定功能的系统。
    通过本讲座的学习,读者不难看出,掌握VBA宏语言,对于更好地应用Excel 97具有十分重要的意义。不仅可以使工作更高效,更方便,而且可以更加自动化,甚至智能化。即使是非计算机专业的人员,也可以利用Excel 97/VBA,方便、快捷地开发出结合自己专业的高水平应用软件。而计算机专业人员开发一些应用于特定领域的系统,也不必非要使用专业的程序开发工具,而可以在Excel 97等软件基础上,利用相应软件的强大功能,再配合有关的宏语言,可以快速、高效的开发出相应的应用软件。(完)





独自走在蓝天上

http://baidu.com/baidu?word=bicsa
-------
顶部
 



当前时区 GMT+8, 现在时间是 2020-4-8 09:59

    本论坛支付平台由支付宝提供
携手打造安全诚信的交易社区 Powered by Discuz! 5.5.0  © 2001-2007 Comsenz Inc.
Processed in 0.018548 second(s), 7 queries , Gzip enabled

清除 Cookies - 联系我们 - 山西财经大学论坛 - 第5次升级版本 - Archiver