Excel 中ActiveX控件與公式及VBA的互動

Microsoft Excel作為制作電子表格的利器，除了擁有功能強大的內建函數，用于執行簡單或復雜的計算以外，還擁有很多自動化的功能。特別是自Excel 97以后，隨著ActiveX控件的嵌入以及VBA的強化，利用Microsoft Excel可以制作出自動化程度更高、功能更為強大的電子表格，因而在我們的工作及生活中也得到越來越廣泛的應用。本篇以一個實際案例從一個側面來闡述在Excel 中，配合使用公式、ActiveX控件及VBA，可以起到事半功倍的效果。
　　《營養指南》是在Microsoft Excel中制作的一款具有一定實用價值的電子表格，包含5個工作模塊，分別由5張工作表完成，可以提供570多種常用食物的營養成分查詢、提供單一營養素在不同食物中的含量豐富程度查詢以及提供個性化的“營養分析師”，幫助您規劃自己的飲食，或提供營養補充之參考等功能。主界面圖一所示：

　　圖一：工作表主界面

javascript:resizepic(this) border=0>
　　
　　限于篇幅，這里就以上主題引用第一個功能模塊“常用食品營養成分查詢”稍加闡述，以期拋磚引玉?！俺Ｓ檬称窢I養成分查詢”工作表已命名為“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.anti-gravitydesign.com