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

Excel中ActiveX控件与公式及VBA的互动MicrosoftExcel作为制作电子表格的利器，除了拥有功能强大的内建函数，用于执行简单或复杂的计算以外，还拥有很多自动化的功能。特别是自Excel97以后，随着ActiveX控件的嵌入以及VBA的强化，利用MicrosoftExcel可以制作出自动化程度更高、功能更为强大的电子表格，因而在我们的工作及生活中也得到越来越广泛的应用。本篇以一个实际案例从一个侧面来阐述在Excel中，配合使用公式、ActiveX控件及VBA，可以起到事半功倍的效果。《营养指南》是在MicrosoftExcel中制作的一款具有一定实用价值的电子表格，包含5个工作模块，分别由5张工作表完成，可以提供570多种常用食物的营养成分查询、提供单一营养素在不同食物中的含量丰富程度查询以及提供个性化的“营养分析师”，帮助您规划自己的饮食，或提供营养补充之参考等功能。主界面图一所示：图一：工作表主界面:+1囱帯用施品对成^i营齐与扱馬旳预馬I**►“\用严:+1囱帯用施品对成^i营齐与扱馬旳预馬I**►“\用严.症!!覆彌SBWTHmEgWLgMJlll'—.flEmOWHIECy鼬十肝叩"/匪迂 ‘ 按于戛吃的寸甸-静弄抽朮据卄勺：艾甘®SHfl©WSKWftA0）裕式迪ZMd）戏据⑩用口创用助曲舍式 、-审h口曲2占世鼻広砂再尙强■畫•豔壺■祁嘶•©.・•雹处鹹］•中■■固囲粘』甥器《谁_”空•血-一E21 * <值富ffjIKr,斥自于强茁霍弊'运畫的佢祥、亢足的睛舉以艮乐耙的t态!寫中戒痛■曲H目旳t年I。耳出啟朗电中・HfW累啊*CmDXfdT$・X申h平心細性期滴解LflMTE寸1 M士的珂生聿工#1Wtwian.tlblvl4KS剳.壬于⑹限于篇幅，这里就以上主题引用第一个功能模块“常用食品营养成分查询”稍加阐述，以期抛砖引玉。“常用食品营养成分查询”工作表已命名为“Food_Composition”，该工作表中共使用了三个控件：两个列表框（ListBox）控件和一个文本框（TextBox）控件，工作表界面及控件说明如图二所示：图二：“常用食品营养成分查询”之工作表界面及控件说明

+■'*卜H聲贏阴,Cgn吁由理』na为+■'*卜H聲贏阴,Cgn吁由理』na为ADOO）AAd）格式啦工HUY慰描⑪If助®企五 ■■审x口曲囚凶型母®聲鼻盂弗…声 整E-卽却聊厉™1•切.亠•为光加：7呆诵 •$ -IlJU122*&常用食晶營养成分姿询；□B}0+引最町IljxtFoi）疽件.用于列说皿LkXtB-MiT皿出*11炖・文斗IS（丁俪幡肛>理出用于舊祖^用竟直己奇若为r^KtB-iMraodfrjTi訓I辞fl]< ）1?件.用于列示现莫S1.巴苛屯为Li1tY皿Fea nT肝*aniffiN丈来的誉存刪畫(MAIHJISi.m1£*L■%*c弭日用6180cVI、wh3.WcAte.w・武Vi■<nn«i£0040ck弱■£VA■0=23■<0.1&■常k210.OS«c2.0D*C匸■…_亠丄一二_- .丄_j_丄■亠—■Ji—：厶丄$二二^2■:由于食物名称及种类繁多，如果单靠一个列表框或使用单元格数据有效性之序列等方法来进行选择，或利用树状结构等方法来实现该功能的话，列表会变得很长且可读性较差，会给实际使用带来较大的不方便。而使用两个适当高度的列表框，一个用来列示食物的大类（表中为ListBoxFoodMainType）,当选定某类食物时，再使用另一个列表框来列示所有属于该类食物的食品名称以供用户选择（表中为食物小类列表框ListBoxFoodSubType）,则比较符合使用习惯，也能正确反应出数据结构和相互之间的关联性，所以这里使用了两个列表框，它们之间的互动也就是本篇要讲述的内容。这两个列表框控件的属性设置是不一样的，其中，ListBoxFoodMainType的MultiSelect属性设置为fmMultiSelectSingle，这可以给列表框中的选项制造选项按钮的效果，使工作表看起来更为生动，也符合我们的使用习惯；而ListBoxFoodSubType的MultiSelect属性为默认值以维持整体感，两者的背景及前景颜色分别设为浅蓝色及深蓝色，以保持工作表界面色系的统一，改观工作表的视觉效果。友情提示：工作表中控件属性的设置友情提示：工作表中控件属性的设置在工作表中点击菜单->视图->工具栏->VisualBasic，然后点击设计模式图标，之后右击相关控件，选择“属性”即可打开属性对话框，在属性对话框中可以对该控件进行各项属性设置。要使控件实现丰富的功能，达成预期目的，就必须将之与工作表和/或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母吃的学问-暫文件®骗辑⑥视图迪插入®格式⑨工且⑴数割□◎扇倉魁I曇直尊！站噩|八—用ListBoxFoo...〒TlB1旦|言尋音堡|昼

=EMBEDCFQListBoxFoo...〒f用食品營养2LietBoxfoodlainJListEox类:蔬菜水产水果糰类•饮料油脂杂项营养补充;負品OLietBoxfoodlainJListEox类:蔬菜水产水果糰类•饮料油脂杂项营养补充;負品OColiuTirLCDuxit1A|CciliifTiriHeadEF：=ilEeColiJITirLl|ll，idthsEnabledTrueFont宋体ForeColor□ftHDOSOOOOOftHeight193.5IMEMode0-fmIMEModeNoContrTtLtegr：±lHeightTrueL血15.75LinkedCellFCSelectFoodMainTypeLiEtFillRangeListStyle1-fmListStyleLlptio:LockedTrueMatchiErLtry0一fmMatcIiEntryFirsMouselconQTone)MQueeF«：iinter0~fmMuiiEeFoin.terDe按字母序按势类序HH1 ►就绪理匹」I：血TA忍Ei11B口：辽己.阳火.t应吃的学问-营养..图四：ListFillRange属性设置:

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

町Kttt）晴*即屈＜&UAd）格式熾工1310⑩BHdJ皐助®注 '-flx口曲日凶©*®聲工褊•丁。••…■霊烈血码《»» •0. ••晝詈申诵 •$•iiPll羊■■固遡舛’詢理能谜田-全・ 朮口&8BS町Kttt）晴*即屈＜&UAd）格式熾工1310⑩BHdJ皐助®注 '-flx口曲日凶©*®聲工褊•丁。••…■霊烈血码《»» •0. ••晝詈申诵 •$•iiPll羊■■固遡舛’詢理能谜田-全・ 朮口&8BS占：占：占占•H.码|1»-.品若小豆粥曲炸旦用冃网电*其黑》曲曲匣洌冃Pili匚）專恋盼丈鳖丈米力禮血杓我怡塗/將弄IIIBillET^yrdZI验.23855549旳］T!5?1S3V9.9超月0.460咽0.T150317<吕0.6co.i1.1070fto1GT074119J4& li010IMoa0黠l<4106078.5S0&D77.1is1Q神32.60.$26E.]03电自Z.70EZ4212.e05-n[tS$04?呂rHoiith/!lafftrencHSctiiis47当选择某类食物时，比如“谷类”，我们需要得知这样两个数据：谷类食物的起始行及终止行，就可以构筑食物小类列表框的数据源了。这可以由Excel提供的公式来完成，表中使用的部分公式及说明如图六所示：图六：部分公式及备注表中AA1000单元格（已命名为FCSelectFoodMainType）的值是由食物大类列表框传递过来的，这可以通过设置ListBoxFoodMainType的LinkedCell属性来实现，该属性的功能是：将当前控件的值传递给工作表中指定的单元格，设置情景如图四之属性对话框所示。AA1003单元格中是公式：=MATCH（AA1000,Details!$A$l:$A$574,0）MATCH函数可以返回在指定方式下与指定数值匹配的数组中元素的相应位置，其语法为:MATCH（目标值,查询区域,匹配类型），匹配类型为0指的是精确匹配。上述公式的解释为：在工作表“Details”的单元格A1到A574之间的连续区域中查找单元格字串为“谷类”的单元格所在的位置，这正是我们需要的数据之一：谷类食物的起始行。AA1004单元格中是公式：=COUNTIF(Details!$A$l:$A$574,AA1000)+AA1003-lCOUNTIF函数可用于计算/统计某指定区域中满足给定条件的单元格的个数，其语法为：COUNTIF(指定区域，给定条件)，其中给定条件形式可以为数字、表达式或文本。上述公式的解释为：在工作表“Details”的单元格A1到A574之间的连续区域中统计单元格字串为“谷类”的单元格总数，然后加上谷类食物的起始行行号并减去一次重复计数，这正是我们需要的数据之二：谷类食物的终止行。AA1005单元格中的公式就简单了：="Details!$B$"&AA1003&":$B$"&AA1004,只是完成一下字串合并，以产生可被Excel识别的单元格引用格式，此处的计算结果为Details!$B$89:$B$122。该单元格已命名为FCFoodSubTypeAddr。到此，我们的工作基本上就快完成了，剩下的事情就是把AA1004单元格中的地址字串赋值给食物小类列表框的ListFillRange属性就可以了。很显然，这里要传递的是对AA1004单元格中的地址字串的一个引用，而不是AA1004单元格本身，这有多种方式可以达成，本例中使用了少量的VBA代码来实现该功能。代码及说明如下：操作方法：右击工作表标签-＞查看代码，在打开的窗口中键入以下代码：PrivateSubListBoxFoodMainType_Click()10OnErrorResumeNext20WithListBoxFoodSubType30 .ListFillRange =Range("FCFoodSubTypeAddr").Value40 .ListIndex=050 .Height=202.560EndWithEndSub语句标号说明10忽略错误并继续运行下一步20这里采用了一个Wi