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参数 使得宏可以改变那些用户和控件不能改变的单元格。这样用户就不能往工作表中输入内容了，实际上也不必输入，只要用鼠标简单操作哪些控件就可以了。图形用户界面最大的好处是将可选功能限制于那些有效功能之间，因此减少，甚至杜绝了用户出错的机会。