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

1、Excel中ActiveX控件与公式及VBA的互动作者：顾斌来源：本站原创更新：12/15/2005MicrosoftExce1作为制作电子表格的利器，除了拥有功能强大的内建函数，用于执行简单或复杂的计算以外，还拥有很多自动化的功能。特别是自Excel97以后，随着ActiveX控件的嵌入以及VBA的强化，利用MicrosoftExcel可以制作出自动化程度更高、功能更为强大的电子表格，因而在我们的工作及生活中也得到越来越广泛的应用。本篇以一个实际案例从一个侧面来阐述在Excel中，配合使用公式ActiveX控件及VBA，可以起到事半功倍的效果。营养指南是在MicrosoftExcel中制作的

2、一款具有一定实用价值的电子表格，包含5个工作模块，分别由5张工作表完成，可以提供570多种常用食物的营养成分查询、提供单一营养素在不同食物中的含量丰富程度查询以及提供个性化的“营养分析师”，帮助您规划自己的饮食，或提供营养补充之参考等功能。主界面图一所示：图一：工作表主界面限于篇幅，这里就以上主题引用第一个功能模块“常用食品营养成分查询”稍加阐述，以期抛砖引玉。“常用食品营养成分查询”工作表已命名为“Food_Composition”，该工作表中共使用了三个控件：两个列表框（ListBox）控件和一个文本框（TextBox）控件，工作表界面及控件说明如图二所示：图二：“常用食品营养成分查询”之

3、工作表界面及控件说明由于食物名称及种类繁多，如果单靠一个列表框或使用单元格数据有效性之序列等方法来进行选择，或利用树状结构等方法来实现该功能的话，列表会变得很长且可读性较差，会给实际使用带来较大的不方便。而使用两个适当高度的列表框，一个用来列示食物的大类（表中为ListBoxFoodMainType），当选定某类食物时，再使用另一个列表框来列示所有属于该类食物的食品名称以供用户选择（表中为食物小类列表框ListBoxFoodSubType），则比较符合使用习惯，也能正确反应出数据结构和相互之间的关联性，所以这里使用了两个列表框，它们之间的互动也就是本篇要讲述的内容。这两个列表框控件的属性设置是

4、不一样的，其中，ListBoxFoodMainType的MultiSelect属性设置为fmMultiSelectSingle,这可以给列表框中的选项制造选项按钮的效果，使工作表看起来更为生动，也符合我们的使用习惯；而ListBoxFoodSubType的MultiSelect属性为默认值以维持整体感，两者的背景及前景颜色分别设为浅蓝色及深蓝色，以保持工作表界面色系的统一，改观工作表的视觉效果。友情提示：工作表中控件属性的设置在工作表中点击菜单-视图-工具栏-VisualBasic，然后点击设计模式图标漑，之后右击相关控件，选择“属性”即可打开属性对话框，在属性对话框中可以对该控件进行各项属性

5、设置。要使控件实现丰富的功能，达成预期目的，就必须将之与工作表和/或VBA代码联系起来。下面我们就给这两个ListBox控件添加相关内容，同时介绍工作表中使用的一些公式。首先给食物大类列表框ListBoxFoodMainType添加列表项。给列表框添加列表项可以通过设置其ListFillRange属性来完成，比如有A、B、C、D四个选项，保存在当前工作表的A1到A4单元格中，那么要将这四个选项添加到列表框控件中，只要在该列表框的ListFillRange属性中填上“A1:A4”即可，它的意思是告诉列表框从A1到A4单元格中提取数据作为列表项。但是，如果待添加的数据项不在当前工作表中的话，就不能

6、用这种方式来加载列表框的数据源，但实际工作中，为了便于数据的管理和维护，经常会把一些常用数据放在另一个工作表中以待引用，这时候，我们可以通过命名待引用数据源区域，然后将该名称赋值给ListFillRange属性即可实现跨工作表的数据引用，在本例中，所有待引用数据均放置在一个名为“DATA”的工作表中（该工作表通常处于隐藏状态），并预先对不同类别的数据区域进行了命名以供不同的控件引用，食物大类列表框ListBoxFoodMainType的列表项数据源在此命名为List_Food_Type_Main,如图三所示，图四为ListFillRange的属性设置：图三：数据源List_Food_Type_

7、Main母吃的学问-营养指南小“-暫文件的编辑视图插入格式工耳数虫3盘翁樹I圖匾尊*智参IkC7嚼計TBIu屋奎尋屋修ListBoxFoo.T氏=EMBED(Borins.ListBo_l_常用食品營养*蛋类豆类糕点谷类菌轆肉类乳类蔬菜水产水果糟类像料油脂杂项0老养补充谟品ListBozFoodlainlListBoitColiJirirLCuiiiLt1AColiJirirLHeadsF：=LlseColijjnrL,ili,idthsEnabledTrueF宋体ForeColor匚|OT0800000&Height193.5IMEMode0-fmIMEModeNoContr工nAegi_：

8、LHei巨htTrueLeft15.75LinkedCellFCSelectFoodMainTypeLiEtFillR：=LTLgeimtFoodType；MainListStyle1-tmLiEtStylAOp+io:LockedTrueMatchiErLtry0-fmM:mtchiEntryFirEMonselccmQfone)M：iUEeFi：iirLte：r0fmFfldueeFointEtlle：按字母序HOME/DATAIjMMar.tffiESESffiffl就绪e吃的学问-营养.图四：ListFillRange属性设置:接下来就是构筑这两个列表框之间的连通。从以上的分析得知，当

9、我们选中某类食物时,只要能动态获取该类食物中所有食品名称的列表，并将该列表传递给食物小类列表框作为其列表项，就可以提供给用户选择了。这里会用到一些公式及少量VBA代码，为了便于理解,现将该案中用于存放各类食物营养成分之明细数据的工作表“Details”作一个部分展示，该工作表是经过排序处理过的，依“类别”作升序排列，“类别”区域范围为“$A$1:$A$574”，如图五：图五：营养成分明细表口如粕-料养倚肃飞“匚画園*月PH五匚】45准拧丈爰丈*3J5(品短验/3K5小豆粥油炸旦用冃0；百怕右ffj耳由匣杪j32E,THdCr4rkvft1*40r3rd中隹丸n.A町Efrl）测flHE查看代码

10、，在打开的窗口中键入以下代码：PrivateSubListBoxFoodMainType_Click()10OnErrorResumeNext20WithListBoxFoodSubType30.ListFillRange=Range(FCFoodSubTypeAddr).Value40.ListIndex=050.Height=202.560EndWithEndSub语句标号说明10忽略错误并继续运行下一步20这里采用了一个With结构，当要操作某个对象的多个属性时，采用该结构可以提高代码的效率，到EndWith结束。30Range(FCFoodSubTypeAddr).Value即单元格AA1005中的字串值：Details!$B$89:$B$12240每次变更食物大类时，默认选定该类中第一款食品50列表框数据源更新时，其高度会有变化，这里将其固定住以保持工作表界面的整洁。60With结构的结束标记。上述代码在每交次点击食物大类列表框ListBoxFood