完美Excel

Technorati 标签: Windows注册表,Excel,VBA

选择桌面左下角的“开始”，单击“运行”，在对话框中输入“regedit”后单击“确定”，可以查看注册表。如下图所示，Excel在注册表中存储的配置信息。

VBA提供了一些用于修改和读取注册表的函数。其中，函数SaveSetting能够创建新的注册表键或者更新已存在的注册表键；函数GetSetting可以获得与注册表键相关的数值，而GetAllSettings函数则可以获得与某应用程序相关的一系列键；函数DeleteSetting用来删除注册表键。

下面是一段VBA代码，示范了使用VBA注册表函数处理注册表：

   1: Sub UseWithRegistry()

   2:     Dim vaKeys As Variant

   3:     '创建新的注册表条目

   4:     SaveSetting "XLTest", "General", "App_Name", "XLTestSample"

   5:     SaveSetting "XLTest", "General", "App_Version", "1.0.0.0"

   6:     SaveSetting "XLTest", "General", "App_Date", "16/06/2008"

   7:

   8:     PrintRegistrySettings

   9:

  10:     '更新设置

  11:     SaveSetting "XLTest", "General", "App_Version", "1.0.0.1"

  12:

  13:     PrintRegistrySettings

  14:

  15:     '将所有的设置放到数组中

  16:     vaKeys = GetAllSettings("XLTest", "General")

  17:     PrintAllSettings vaKeys

  18:

  19:     '删除设置

  20:     DeleteSetting "XLTest", "General", "App_Name"

  21:     DeleteSetting "XLTest", "General", "App_Version"

  22:     DeleteSetting "XLTest", "General", "App_Date"

  23:

  24:     PrintRegistrySettings

  25: End Sub

  26: 

  27: Sub PrintRegistrySettings()

  28:     On Error Resume Next

  29:     Debug.Print "Application Name:" & GetSetting("XLTest", "General", "App_Name")

  30:     Debug.Print "Application Version:" & GetSetting("XLTest", "General", "App_Version")

  31:     Debug.Print "Application Date:" & GetSetting("XLTest", "General", "App_Nate")

  32:     Debug.Print "--------------------------------------------"

  33: End Sub

  34: 

  35: Sub PrintAllSettings(vaSettings As Variant)

  36:     Dim nItem As Integer

  37:     If IsArray(vaSettings) Then

  38:         For nItem = 0 To UBound(vaSettings)

  39:             Debug.Print vaSettings(nItem, 0) & ":" & vaSettings(nItem, 1)

  40:         Next

  41:     End If

  42:     Debug.Print "--------------------------------------------"

  43: End Sub

输出结果为：

Application Name:XLTestSample
Application Version:1.0.0.0
Application Date:
——————————————–
Application Name:XLTestSample
Application Version:1.0.0.1
Application Date:
——————————————–
App_Name:XLTestSample
App_Version:1.0.0.1
App_Date:06/16/2008
——————————————–
Application Name:
Application Version:
Application Date:
——————————————–

注意，使用GetAllSettings函数返回一个Variant型的二维数组，其第一维表示返回的每个设置，第二维则表示下列两项之一：键名或者键值。

使用SaveSetting函数创建的注册表键被添加到HKEY_CURRENT_USER\Software\VB and VBA Program Settings\中，如下图所示。

据cnBata消息：Open XML Format SDK 1.0 发布

使用这个API,程序员们就可以通过自己的程序直接生成Word和,PowerPoint,Excel 2007版本的文件,这是OpenXML编程的基础.
今天Open XML Format SDK 1.0正式发布了,Office相关的程序员们不可以错过.

• SDK download site: http://go.microsoft.com/fwlink/?LinkId=120908
• MSDN SDK online documentation: http://msdn.microsoft.com/en-us/library/bb448854.aspx
• XML in Office Developer Portal: http://msdn.microsoft.com/office/xml
• Open XML Formats Resource Center: http://msdn.microsoft.com/en-us/office/bb265236.aspx

本例使用了组合框和选项按钮来跟踪和调整物品的状态，如下图所示。当在组合框中选择相应的物品后，下面的方框上方的文字会相应改变为该组合框物品名称，方框中的选项按钮根据该物品是否充足进行相应的选择。同时，在组合框中选择物品后，如果更改方框中选项按钮的状态，则下方库存状态列表中的文字也相应更改。单元格区域B11：C18为物品及相应的库存状态列表。

例如，在组合框中选择物品“潜水泵”，单元格B5会显示“潜水泵 充足还是不充足？”，因为在B11：C18中的潜水泵库存状态为充足，所以方框中名为“充足”的选项按钮被选中。此时，若将选项按钮更改为名为“不足”的选项按钮，那么B11：C18中潜水泵所对应的库存状态也会更改为“不足”。

下面来看看这个示例是如何实现的。

第一步：在工作表中放置一个组合框、两个选项按钮，并调整格式如上图。在B11：C18区域录入数据，当然可以将数据录入到工作表的其他区域或其他工作表中，并且可以为区域命名，以方便调用和扩展。在本例中，为了演示方便，将数据区域与窗体控件放置在一起。

第二步：设置组合框，如下图所示。

第三步：设置选项按钮，将选项按钮的单元格链接为C8。

第四步：设置单元格B5，使之显示相关的提示信息。在单元格B5中输入公式：

=INDEX(B12:B18,D3) & ” 充足还是不充足?”

第五步：编写组合框和选项按钮相关联的代码。在VBE的标准模块中输入下面的代码：

   1: Sub GetStates()

   2:     Dim ws As Worksheet

   3:     Dim iWPNumber As Integer

   4:     Dim sStates As String

   5:     On Error Resume Next

   6:     Set ws = ThisWorkbook.Worksheets("组合框示例")

   7:     iWPNumber = ws.Range("D3")

   8:     '获取组合框中当前所选物品的状态

   9:     sStates = ws.Range("B11").Offset(iWPNumber, 1)

  10:     If sStates = "充足" Then

  11:         '激活名为"充足"的选项按钮

  12:         ws.Range("C8").Value = 1

  13:     Else

  14:         '激活名为"不足"的选项按钮

  15:         ws.Range("C8").Value = 2

  16:     End If

  17:     Set ws = Nothing

  18: End Sub

  19:  

  20: Sub SetStates()

  21:     Dim ws As Worksheet

  22:     Dim iWPNumber As Integer

  23:     On Error Resume Next

  24:     Set ws = ThisWorkbook.Worksheets("组合框示例")

  25:     iWPNumber = ws.Range("D3")

  26:     If ws.Range("C8").Value = 1 Then

  27:         '更新物品的状态为充足

  28:         ws.Range("B11").Offset(iWPNumber, 1).Value = "充足"

  29:     Else

  30:         '更新物品的状态为不足

  31:         ws.Range("B11").Offset(iWPNumber, 1).Value = "不足"

  32:     End If

  33:     Set ws = Nothing

  34: End Sub

其中，GetStates过程为组合框对应的宏代码，当组合框选项发生变化时更改对应的选项按钮。SetStates过程为选项按钮对应的宏代码，当组合框选项选定后，更改选项按钮设置时，相应更改库存列表中物品的库存状态。

第六步：为组合框指定宏为GetStates，为两个选项按钮指定宏为SetStates。

窗体控件在Excel 5中引入，使用窗体控件也能够创建出丰富的用户体验。这篇文章和下篇文章将分别列举一个例子。

控制工作簿中工作表的显示和隐藏是复选框控件的一种典型应用。如下图所示，首先在工作表中放置一个分组框，然后在该分组框中放置三个复选框，将这四个控件的标题分别改为如图所示的文字。

接下来，设置这三个复选框分别链接至其背后的单元格B4、B5、B6，并设置它们的“颜色与线条”为“自动”，使其覆盖背后的单元格，这样该单元格中的文字就会被遮住。

注意，要想复选框后面的单元格文字不可见，以避免与复选框文本相冲突，须设置该复选框的“颜色与线条”格式为“自动”。

设置控件格式的方法为，在某控件中单击右键，选择“设置控件格式”。在“设置控件格式”对话框（如下图所示）中，选择相应的选项卡，对该控件的格式进行设置。

在VBE中编写的代码如下：

   1: Sub SetWorksheetVisibility()

   2:     Dim ws As Worksheet

   3:     On Error Resume Next

   4:     Set ws = ThisWorkbook.Worksheets("控制工作表可视状态")

   5:     Application.ScreenUpdating = False

   6:     ThisWorkbook.Worksheets("Sheet1").Visible = ws.Range("B4").Value

   7:     ThisWorkbook.Worksheets("Sheet2").Visible = ws.Range("B5").Value

   8:     ThisWorkbook.Worksheets("Sheet3").Visible = ws.Range("B6").Value

   9:     Application.ScreenUpdating = True

  10: End Sub

最后，分别在这三个复选框中单击右键，选择“指定宏”，在“指定宏”对话框分别将宏均指定为SetWorksheetVisibility。

这样，当在工作表中选中某复选框时，该复选框标题所对应的工作表可见，当取消复选框选择时，相应的工作表将隐藏。

下面记录下使用VB6为Excel创建COM加载项的步骤，以供日后有用时参考。

步骤1：打开VB6。在“新建工程”对话框中选择“外接程序”，单击“打开”按钮，如下图所示。

此时，创建一个名为MyAddIn的工程，包含一个名为frmAddIn的窗体和一个名为Connect的Designer类变量。

步骤2：将工程名称改为MyFirstCOMAddIn。

步骤3：删除默认的窗体frmAddIn。

步骤4：删除Connect中的全部代码。

步骤5：在工程资源管理器窗口双击Connect打开加载宏设计器窗口，并对其进行相应的设置，如下图所示。

上图中，在“通用”选项卡中设置了COM加载项在Excel的COM加载项对话框中显示的名称及相应的描述，并设置应用程序为Microsoft Excel，选择初始化加载行为为Startup，表示在Excel每次启动时都运行该加载项，当然还有其他三个选项分别设置何时启用加载项。该对话框还有一个名为“高级”的选项卡，此时我们暂且不用。

步骤6：在工程资源管理器窗口右击Connect，选择“查看代码”，打开代码窗口。

步骤7：在代码窗口顶部左侧的对象列表框中选择AddinInstance对象，在右侧的事件列表框中选择OnConnection事件，并输入下面的代码：

   1: Private Sub AddinInstance_OnConnection(ByVal Application As Object, _

   2:                                        ByVal ConnectMode As AddInDesignerObjects.ext_ConnectMode, _

   3:                                        ByVal AddInInst As Object, _

   4:                                        custom() As Variant)

   5:     MsgBox "这是使用VB6创建的COM加载项"

   6: End Sub

然后，再在事件列表框中选择OnDisconnection事件，并输入下面的代码：

   1: Private Sub AddinInstance_OnDisconnection(ByVal RemoveMode As AddInDesignerObjects.ext_DisconnectMode, _

   2:                                           custom() As Variant)

   3:     MsgBox "将要关闭Excel工作簿,您保存里面的数据了吗."

   4: End Sub

步骤8：保存所有的工程文件。

步骤9：单击菜单“文件——生成MyFirstCOMAddIn.dll”，如下图所示。

至此，这个COM加载项制作完成。

步骤10：打开Excel 2003，将会出现一个消息框，这是在COM加载项的OnConnection事件中设置的，如下图所示。

关闭Excel 2003时，将会出现一个消息框，这是在COM加载项的OnDisconnection事件中设置的，如下图所示。

这的加载项之所以在Excel 2003会话开启时运行，是因为我们在设计加载项时选择了选项Startup，即在Excel启动时加载该加载项。下面，在Excel 2003中设置能够启动或关闭COM加载项。

步骤11：在Excel 2003中单击菜单“工具——自定义”，在“自定义”对话框中的选择“命令”选项卡，在左侧的类别列表框中选择“工具”，在右侧的命令列表框中找到“COM加载项”条目（如下图所示），并将其拖至Excel菜单栏中“工具”菜单下。单击“关闭”按钮关闭自定义对话框。

步骤12：单击菜单“工具——COM加载项”，弹出“COM加载项”对话框，其中列出了所有可用的COM加载项，如下图所示。

为了使刚创建的My First COM Add-In加载项不可用，可取消其前面的复选框选择。也可以通过“删除”按钮删除该加载项。