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

  MicrosoftExcel作為製作電子表格的利器,除了擁有功能強大的內建函數,用於執行簡單或複雜的計算以外,還擁有很多自動化的功能。特別是自Excel97以後,隨著ActiveX控件的嵌入以及VBA的強化,利用MicrosoftExcel可以製作出自動化程度更高、功能更為強大的電子表格,因而在我們的工作及生活中也得到越來越廣泛的應用。本篇以一個實際案例從一個側面來闡述在Excel中,配合使用公式、ActiveX控件及VBA,可以起到事半功倍的效果。

《營養指南》是在MicrosoftExcel中製作的一款具有一定實用價值的電子表格,包含5個工作模塊,分別由5張工作表完成,可以提供570多種常用食物的營養成分查詢、提供單一營養素在不同食物中的含量豐富程度查詢以及提供個性化的「營養分析師」,幫助您規劃自己的飲食,或提供營養補充之參考等功能。主界面圖一所示:

圖一:工作表主界面

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

限於篇幅,這裡就以上主題引用第一個功能模塊「常用食品營養成分查詢」稍加闡述,以期拋磚引玉。「常用食品營養成分查詢」工作表已命名為「Food_Composition」,該工作表中共使用了三個控件:兩個列表框(ListBox)控件和一個文本框(TextBox)控件,工作表界面及控件說明如圖二所示:

圖二:「常用食品營養成分查詢」之工作表界面及控件說明

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

由於食物名稱及種類繁多,如果單靠一個列表框或使用單元格數據有效性之序列等方法來進行選擇,或利用樹狀結構等方法來做到該功能的話,列表會變得很長且可讀性較差,會給實際使用帶來較大的不方便。而使用兩個適當高度的列表框,一個用來列示食物的大類(表中為ListBoxFoodMainType),當選定某類食物時,再使用另一個列表框來列示所有屬於該類食物的食品名稱以供用戶選擇(表中為食物小類列表框ListBoxFoodSubType),則比較符合使用習慣,也能正確反應出數據結構和相互之間的關聯性,所以這裡使用了兩個列表框,它們之間的互動也就是本篇要講述的內容。

這兩個列表框控件的屬性設置是不一樣的,其中,ListBoxFoodMainType的MultiSelect屬性設置為fmMultiSelectSingle,這可以給列表框中的選項製造選項按鈕的效果,使工作表看起來更為生動,也符合我們的使用習慣;而ListBoxFoodSubType的MultiSelect屬性為默認值以維持整體感,兩者的背景及前景顏色分別設為淺藍色及深藍色,以保持工作表界面色系的統一,改觀工作表的視覺效果。

Excel 中ActiveX控件與公式及VBA的互動
  友情提示:工作表中控件屬性的設置

在工作表中點擊菜單->視圖->工具欄->VisualBasic,然後點擊設計模式圖標Excel 中ActiveX控件與公式及VBA的互動
  ,之後右擊相關控件,選擇「屬性」即可打開屬性對話框,在屬性對話框中可以對該控件進行各項屬性設置。

要使控件做到豐富的功能,達成預期目的,就必須將之與工作表和/或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

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

圖四:ListFillRange屬性設置:

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

接下來就是構築這兩個列表框之間的連通。從以上的分析得知,當我們選中某類食物時,只要能動態獲取該類食物中所有食品名稱的列表,並將該列表傳遞給食物小類列表框作為其列表項,就可以提供給用戶選擇了。這裡會用到一些公式及少量VBA代碼,為了便於理解,現將該案中用於存放各類食物營養成分之明細數據的工作表「Details」作一個部分展示,該工作表是經過排序處理過的,依「類別」作升序排列,「類別」區域範圍為「$A$1:$A$574」,如圖五:

圖五:營養成分明細表

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

當選擇某類食物時,比如「谷類」,我們需要得知這樣兩個數據:谷類食物的起始行及終止行,就可以構築食物小類列表框的數據源了。這可以由Excel提供的公式來完成,表中使用的部分公式及說明如圖六所示:

圖六:部分公式及備註

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

表中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 OnError Resume Next

20 WithListBoxFoodSubType

30.ListFillRange = Range(“FCFoodSubTypeAddr”).Value

40.ListIndex = 0

50.Height = 202.5

60 EndWith

End Sub

語句標號

說明

10

忽略錯誤並繼續運行下一步

20

這裡採用了一個With結構,當要操作某個對象的多個屬性時,採用該結構可以提高代碼的效率,到End With結束。

30

Range(“FCFoodSubTypeAddr”).Value即單元格AA1005中的字串值:Details!$B$89:$B$122

40

每次變更食物大類時,默認選定該類中第一款食品

50

列表框數據源更新時,其高度會有變化,這裡將其固定住以保持工作表界面的整潔。

60

With結構的結束標記。

上述代碼在每交次點擊食物大類列表框ListBoxFoodMainType時均會被執行到。

通過以上操作就我們就做到了食物大類和食物小類這兩個列表框之間的互動。