Excel 中ActiveX控件与公式及VBA的互动

Microsoft Excel作为制作电子表格的利器，除了拥有功能强大的内建函数，用于执行简单或复杂的计算以外，还拥有很多自动化的功能。特别是自Excel 97以后，随着ActiveX控件的嵌入以及VBA的强化，利用Microsoft Excel可以制作出自动化程度更高、功能更为强大的电子表格，因而在我们的工作及生活中也得到越来越广泛的应用。本篇以一个实际案例从一个侧面来阐述在Excel 中，配合使用公式、ActiveX控件及VBA，可以起到事半功倍的效果。
　　《营养指南》是在Microsoft Excel中制作的一款具有一定实用价值的电子表格，包含5个工作模块，分别由5张工作表完成，可以提供570多种常用食物的营养成分查询、提供单一营养素在不同食物中的含量丰富程度查询以及提供个性化的“营养分析师”，帮助您规划自己的饮食，或提供营养补充之参考等功能。主界面图一所示：

　　图一：工作表主界面

javascript:resizepic(this) border=0>
　　
　　限于篇幅，这里就以上主题引用第一个功能模块“常用食品营养成分查询”稍加阐述，以期抛砖引玉。“常用食品营养成分查询”工作表已命名为“Food_Composition”，该工作表中共使用了三个控件：两个列表框（ListBox）控件和一个文本框（TextBox）控件，工作表界面及控件说明如图二所示：
　　图二：“常用食品营养成分查询”之工作表界面及控件说明
　
　
　　由于食物名称及种类繁多，如果单靠一个列表框或使用单元格数据有效性之序列等方法来进行选择，或利用树状结构等方法来实现该功能的话，列表会变得很长且可读性较差，会给实际使用带来较大的不方便。而使用两个适当高度的列表框，一个用来列示食物的大类（表中为ListBoxFoodMainType），当选定某类食物时，再使用另一个列表框来列示所有属于该类食物的食品名称以供用户选择（表中为食物小类列表框ListBoxFoodSubType），则比较符合使用习惯，也能正确反应出数据结构和相互之间的关联性，所以这里使用了两个列表框，它们之间的互动也就是本篇要讲述的内容。
　　这两个列表框控件的属性设置是不一样的，其中，ListBoxFoodMainType的MultiSelect属性设置为fmMultiSelectSingle，这可以给列表框中的选项制造选项按钮的效果，使工作表看起来更为生动，也符合我们的使用习惯；而ListBoxFoodSubType的MultiSelect属性为默认值以维持整体感，两者的背景及前景颜色分别设为浅蓝色及深蓝色，以保持工作表界面色系的统一，改观工作表的视觉效果。
　　
　　　　友情提示：工作表中控件属性的设置
　　在工作表中点击菜单->视图->工具栏->Visual Basic，然后点击设计模式图标　，之后右击相关控件，选择“属性”即可打开属性对话框，在属性对话框中可以对该控件进行各项属性设置。
　　
　　要使控件实现丰富的功能，达成预期目的，就必须将之与工作表和/或VBA代码联系起来。下面我们就给这两个ListBox控件添加相关内容，同时介绍工作表中使用的一些公式。
　　首先给食物大类列表框ListBoxFoodMainType添加列表项。给列表框添加列表项可以通过设置其ListFillRange属性来完成，比如有A、B、C、D四个选项，保存在当前工作表的A1到A4单元格中，那么要将这四个选项添加到列表框控件中，只要在该列表框的ListFillRange属性中填上“A1:A4”即可，它的意思是告诉列表框从A1到A4单元格中提取数据作为列表项。但是，如果待添加的数据项不在当前工作表中的话，就不能用这种方式来加载列表框的数据源，但实际工作中，为了便于数据的管理和维护，经常会把一些常用数据放在另一个工作表中以待引用，这时候，我们可以通过命名待引用数据源区域，然后将该名称赋值给ListFillRange属性即可实现跨工作表的数据引用，在本例中，所有待引用数据均放置在一个名为“DATA”的工作表中（该工作表通常处于隐藏状态），并预先对不同类别的数据区域进行了命名以供不同的控件引用，食物大类列表框ListBoxFoodMainType的列表项数据源在此命名为List_Food_Type_Main，如图三所示，图四为ListFillRange的属性设置：
　　
图三：数据源List_Food_Type_Main　　

　　　　　
图四：ListFillRange属性设置： 

　　 
　　接下来就是构筑这两个列表框之间的连通。从以上的分析得知，当我们选中某类食物时，只要能动态获取该类食物中所有食品名称的列表，并将该列表传递给食物小类列表框作为其列表项，就可以提供给用户选择了。这里会用到一些公式及少量VBA代码，为了便于理解，现将该案中用于存放各类食物营养成分之明细数据的工作表“Details”作一个部分展示，该工作表是经过排序处理过的，依“类别”作升序排列，“类别”区域范围为“$A$1:$A$574”，如图五：
　　图五：营养成分明细表

　　
　　当选择某类食物时，比如“谷类”，我们需要得知这样两个数据：谷类食物的起始行及终止行，就可以构筑食物小类列表框的数据源了。这可以由Excel提供的公式来完成，表中使用的部分公式及说明如图六所示：
　　图六：部分公式及备注

　　
　　表中AA1000单元格（已命名为FCSelectFoodMainType）的值是由食物大类列表框传递过来的，这可以通过设置ListBoxFoodMainType的LinkedCell 属性来实现，该属性的功能是：将当前控件的值传递给工作表中指定的单元格，设置情景如图四之属性对话框所示。
　　AA1003单元格中是公式：=MATCH(AA1000,Details!$A$1:$A$574,0)
　　MATCH函数可以返回在指定方式下与指定数值匹配的数组中元素的相应位置，其语法为：MATCH(目标值,查询区域,匹配类型)，匹配类型为0指的是精确匹配。
　　上述公式的解释为：在工作表“Details”的单元格A1到A574之间的连续区域中查找单元格字串为“谷类”的单元格所在的位置，这正是我们需要的数据之一：谷类食物的起始行。
　　AA1004单元格中是公式：=COUNTIF(Details!$A$1:$A$574,AA1000)+AA1003-1
　　COUNTIF函数可用于计算/统计某指定区域中满足给定条件的单元格的个数，其语法为：COUNTIF（指定区域，给定条件），其中给定条件形式可以为数字、表达式或文本。
　　上述公式的解释为：在工作表“Details”的单元格A1到A574之间的连续区域中统计单元格字串为“谷类”的单元格总数，然后加上谷类食物的起始行行号并减去一次重复计数，这正是我们需要的数据之二：谷类食物的终止行。
　　AA1005单元格中的公式就简单了：="Details!$B$"&AA1003&":$B$"&AA1004，只是完成一下字串合并，以产生可被Excel识别的单元格引用格式，此处的计算结果为Details!$B$89:$B$122。该单元格已命名为 FCFoodSubTypeAddr。
　　到此，我们的工作基本上就快完成了，剩下的事情就是把AA1004单元格中的地址字串赋值给食物小类列表框的ListFillRange属性就可以了。很显然，这里要传递的是对AA1004单元格中的地址字串的一个引用，而不是AA1004单元格本身，这有多种方式可以达成，本例中使用了少量的VBA代码来实现该功能。代码及说明如下：
　　操作方法：右击工作表标签->查看代码，在打开的窗口中键入以下代码：
　　　　Private Sub ListBoxFoodMainType_Click()
　　　　10    On Error Resume Next
　　　　20    With ListBoxFoodSubType
　　　　30        .ListFillRange = Range("FCFoodSubTypeAddr").Value
　　　　40        .ListIndex = 0
　　　　50        .Height = 202.5
　　　　60    End With
　　　　End Sub
　　　　
语句标号
说明
10
忽略错误并继续运行下一步
20
这里采用了一个With结构，当要操作某个对象的多个属性时，采用该结构可以提高代码的效率，到End With结束。
30
Range("FCFoodSubTypeAddr").Value即单元格AA1005中的字串值：Details!$B$89:$B$122
40
每次变更食物大类时，默认选定该类中第一款食品
50
列表框数据源更新时，其高度会有变化，这里将其固定住以保持工作表界面的整洁。
60
With结构的结束标记。
上述代码在每交次点击食物大类列表框ListBoxFoodMainType时均会被执行到。
通过以上操作就我们就实现了食物大类和食物小类这两个列表框之间的互动。

<本文曾发表于《视窗世界》>

原文转自：http://www.ltesting.net