[原创]《Excel专业开发》解析之加载宏

fanjy

前面对《Excel专业开发》一书的第4章进行了解析，现在让我们继续。加载宏是第5章的主要内容，本书从现在开始，使用VBA来进行实用程序的开发。
1 背景知识
 在继续深入研究本章之前，读者可以看看《加载宏基础》一文，以加深对Excel加载宏的认识和理解。
2 实例解析
2.1 准备工作
首先，应将前一章实例工作簿的工作表代码名称进行相应的设置，即将工作表代码名称分别重命名为wksTimeEntry和wksProgramData。在工作表TimeEntry中，增加2个工作表级的名称，即：
TimeEntry!rgnClearInputs=TimeEntry!$G$3:$G$4,TimeEntry!$F$7:$K$21
TimeEntry!ptrInsertRow=TimeEntry!$A$20
注意，ptrInsertRow应设置为定义输入区域的最后一行，本实例中最后一行为第20行。
2.2 实例应用程序设计概述
本应用程序分为两大块。一块用于设计，其代码位于模块MUtilities中，在该模块中的过程分别用来利用加载宏工作表中的设置值，对应用程序中的工作表进行设置、删除应用程序中所应用的设置、以及读取应用程序中的设置到加载宏工作表中。另一块用于应用程序运行，包括5个模块，即MGlobals模块、MOpenClose模块、MEntryPoints模块、MStandardCode模块和MSystemCode模块。有关这些模块的作用将在下面详细介绍，具体代码在所附的实例工作簿应用程序中。
此外，在设计加载宏工作簿(时间记录系统加载宏.xla)时，应将应用程序工作簿(时间记录系统模板.xls)打开。
2.3 创建加载宏工作簿
(1) 在该加载宏工作簿中，有一个工作表，用来设置用户界面工作表。现在来创建该工作表。
新建工作簿，除Sheet1工作表外，删除其它工作表，并改名为wksUISettings，在VBE窗口中，将该工作表中的代码名称改为wksUISettings。在该工作表中，输入如图1所示的数据。
 
图1：创建含有用户界面设置值的工作表，该工作表是加载宏工作簿中唯一的工作表。
其中单元格E2和E3使用了名称常量，即xlUnlockedCells和xlNoRestrictions。
(2) 在该工作表中定义名称
wksUISettings!tblRangeNames=OFFSET(wksUISettings!$A$1,0,1,1,COUNTA(wksUISettings!$1:$1)-1)
wksUISettings! tblSheetNames=OFFSET(wksUISettings!$A$1,1,0,COUNTA(wksUISettings!$A:$A)-1,1)
wksUISettings!xlNoRestrictions=0
wksUISettings!xlNoSelection=-4142
wksUISettings!xlUnlockedCells=1
(3) 在VBE窗口中，插入一个标准模块，并重命名为MUtilities。该模块用来创建或维护应用程序，仅在设计时使用，对终端用户来说该模块应不可使用，因此在该模块顶部放置声明语句Option Private Module。也就是说，设计者可以利用该模板方便地对实例工作簿进行设置。下面是该模块中的过程：
1) WriteSettings过程用于将加载宏工作表中的设置值作为预定义名称写到指定的用户界面工作表中，本例的用户界面为“时间记录系统模板.xls”
2) RemoveSettings过程用于删除在时间记录系统工作簿中的设置，以便更容易维护工作簿。
3) ReadSettings过程用于将时间记录系统工作簿中的预定义名称设置写入加载宏工作表中。
4) sSheetTabName函数用于将工作表对象代码名称转换成与之一致的工作表标签名称。
(4) 在VBE窗口中，插入一个标准模块，并重命名为MGlobals。该模块用来定义和设置加载宏中的全局变量和常量。
(5) 在VBE窗口中，插入一个标准模块，并重命名为MStandardCode。该模块用来创建应用程序中所使用的一些通用代码，这些代码在不同的工程中一般无需修改。该模块包含的过程有：
1) ResetAppProperties过程用于恢复应用程序的默认属性。
2) sSheetTabName函数用于将工作表代码名称转换为工作表标签名称。
3) lCountVisibleWorkbooks函数用于获取当前所有打开的可见工作簿数量。
(6) 在VBE窗口中，插入一个标准模块，并重命名为MSystemCode。该模块中包含应用程序的支持过程，可在主程序中调用它们。
1) BuildCommandBars过程用来创建命令栏。这里，创建了带有四个按钮的命令栏。
2) MakeWorksheetSettings过程用来设置时间记录工作簿中的所有工作表。
3) bIsTimeEntryBookActive函数用来判断时间记录工作簿是否处于活动状态。
(7) 在VBE窗口中，插入一个标准模块，并重命名为MEntryPoints。该模块包含命令栏中按钮所调用的过程。
1) PostTimeEntriesToNetwork过程保存已完成的时间记录工作簿副本到网络中指定的位置。
2) AddMoreRows过程用于在数据输入表中添加行，添加的行总位于最底部。
3) ClearDataEntryAreas过程用于清除工作表中输入区域的数据，以便于该工作表能重复使用。
4) ExitApplication过程用于退出系统。
(8) 在VBE窗口中，插入一个标准模块，并重命名为MOpenClose。该模块为应用程序的主模块，用于设置应用程序启动和关闭时的相关操作。
1) Auto_Open过程，每次打开应用程序时运行该过程，过程中包括调用其它模块过程的代码。
2) Auto_Close过程，在应用程序关闭时运行。
3) ShutdownApplication过程，用于关闭应用程序的代码，在其中设置有关闭应用程序时应进行的操作。
2.4 本实例的运行结果
运行后的最终界面如图2所示，用户通过该工作簿输入每周的数据，然后将结果存储在网络或计算机上的一个中心区域。
 
图2：通过该工作簿输入每周的数据
输入完数据，并单击“传递到网络”按钮，将数据传送到指定的存储中心，如图3所示。
 
图3：传递数据到存储中心
单击“确定”，此时在指定的存储中心生成的工作簿如图4所示。
  图4：所保存的数据文件
3 主要的技术与方法
3.1 在VBA中使用名称
 (1) 添加名称，例如WriteSettings()过程中的代码：
wksSheet.Names.Add rngName.Value, "=" & rngSetting.Value
该代码在当前工作表中定义名称。
 (2) 在程序中使用名称和常量，使得程序更容易修改。这样，只需要修改常量或者名称定义的范围，而不必逐一修改程序中的代码。
3.2 使用工作表代码名称
在程序中，使用了工作表的代码名称，而不是工作表标签名，这样可以避免用户修改工作表标签名而使代码无法运行。同时，在需要使用工作表标签名的地方，使用了一个通用函数过程sSheetTabName()将工作表代码名称转换为工作表标签名称。
3.3 控制工作表编辑
对工作表设置保护但允许有可以编辑的区域，同时，限制了滚动区域和选择。
3.4 使用预定义的工作表和工具模块
使用预定义的工作表为其它工作簿中的工作表动态添加名称，同时也可以读取其它工作簿工作表的名称，即实现工作表的相交互。
使用工具模块，使得在设计和维护时更容易对工作表进行调试。
3.5 通用过程
提供了几个通用的模块过程和函数，如属性恢复、代码名相应的工作表名、判断打开但未隐藏的工作簿数量等。
3.6 创建命令栏
本章中的代码创建了一个简单的命名栏，创建命令栏在本书后面将有详细介绍。其中要注意的是，在创建命令栏或开启工作簿时应删除可能存在的相同菜单的副本。
3.7 使代码在工作簿打开时自动运行
一般有两种方法，一种是本例中所应用的在标准模块中的Auto_Open()过程和Auto_Close()过程，这两个过程是Excel为保持向后兼容而存在的；另一种是ThisWorkbook模块中的Workbook_Open()事件过程，在此过程中的代码将在工作簿打开时自动运行。
3.8 防错技术
本例的代码中，有很多位置将On Error Resume Next和On Error GoTo 0配合使用，例如下面的代码段：
On Error Resume Next
Application.CommandBars(gsBAR_TOOLBAR).Delete
On Error GoTo 0
语句On Error Resume Next用来忽略删除的命令栏不存在而导致的错误；语句On Error GoTo 0用来恢复Excel的错误处理。
3.9 使用了一些方法和函数
代码中使用了一些VBA相关函数和方法，如Intersect方法，用来返回相重叠的交叉区域；Evaluate方法，用于将名称转换为对象或值。
代码中使用了GetSetting函数，这是VBA中操作注册表的几个函数之一。一般所设置的值可以在注册表的下面路径中找到：
\\HKEY_CURRENT_USER\Software\VB and VBA Program Settings
4 小结
本章使用了VBA代码，并提供了很多好的设计技术和方法。通过研究这些代码和实现实例的过程，读者可以学习到很多VBA的知识以及程序设计技巧和方法。

分类：《Excel专业开发》——第5章

By fanjy in 2007-8-7

fanjy

中文版实例下载： CpmIzRrA.rar (36.08 KB, 下载次数: 506)

2007-8-8 08:59 上传

点击文件名下载附件
[原创]《Excel专业开发》解析之加载宏

jsxlop

看来这本书难度有点大了，感兴趣的人不多呀，

dsp5000

慢慢就会懂了

XUZHANCHENG

是啊

这本书很难，我在天津出差的时候见过，感觉很好，就是很难，慢慢学习把

naur

我怎么觉得挺简单的，越学越有趣了。

支持楼主！

三戒

QUOTE:

以下是引用XUZHANCHENG在2007-9-30 14:42:25的发言：

是啊

这本书很难，我在天津出差的时候见过，感觉很好，就是很难，慢慢学习把

贵在坚辞，看不懂的可以先跳过，然后在看，慢慢就有感觉了！

lvwan

比较晦涩，读得有时候一头雾水，可能要慢慢看。现在我放在马桶旁边看。

wenxu4660

这本书真的很难啊！！

myx2006

在无锡有这本书,我也是在书店碰到高人指教买的,那位高人应该也是EXCEL HOME的家里人哦!

楼上的说的对,贵在坚持,细细品味吧!