Excel VBA中列表框數(shù)據(jù)的動(dòng)態(tài)填充

摘 要: 本文基于Microsoft Office Excel應(yīng)用軟件，利用Excel 開放的Visual Basic編輯器，以VBA語言為基礎(chǔ)，針對(duì)VBA開發(fā)中列表框數(shù)據(jù)動(dòng)態(tài)填充的問題， 給出了使用Additem方法和使用動(dòng)態(tài)數(shù)組兩種設(shè)計(jì)方法和程序。

關(guān)鍵詞: Excel VBA列表框 動(dòng)態(tài)數(shù)據(jù)填充

作為微軟公司常用辦公軟件Microsoft Office的重要組件之一，Microsoft Excel是一款電子表格處理軟件，它的基本功能是進(jìn)行各種數(shù)據(jù)的處理、統(tǒng)計(jì)分析和輔助決策操作。Excel的應(yīng)用往往不可避免地需要進(jìn)行大量的數(shù)據(jù)錄入，錄入的效率和數(shù)據(jù)的規(guī)范制約著Excel軟件的使用效率，所以在Excel實(shí)際的使用中，經(jīng)常會(huì)借助于使用Visual Basic For Application(VBA)來開發(fā)一些小型的輔助工具完成工作，這樣既可以在一定程度上提高效率，又可以保證數(shù)據(jù)的規(guī)范性。

在使用Excel VBA進(jìn)行的程序開發(fā)中，經(jīng)常會(huì)使用到列表框控件(ComboBox)以提供現(xiàn)有數(shù)據(jù)供用戶選擇。列表框的數(shù)據(jù)填充，可以使用預(yù)定義的方法，即在程序設(shè)計(jì)時(shí)將菜單數(shù)據(jù)固化在控件中，也可以在程序運(yùn)行過程中使用語句進(jìn)行數(shù)據(jù)的動(dòng)態(tài)填充。動(dòng)態(tài)填充主要采用兩種方法:使用列表框本身的Additem方法和使用數(shù)組對(duì)列表框的List屬性進(jìn)行賦值。

1. Additem方法介紹

Additem方法的作用為列表框或組合框添加數(shù)據(jù)項(xiàng)。其語法為:

expression.AddItem(Text，Index)。

expression為必選。該表達(dá)式返回一個(gè)ControlFormat 對(duì)象。

Text參數(shù)為String類型，必選，表示要添加的文字。

Index參數(shù)Variant類型，可選，表示新輸入項(xiàng)的輸入位置。如果列表中數(shù)據(jù)項(xiàng)數(shù)目少于指定索引號(hào)，則從列表末尾開始添加多個(gè)空數(shù)據(jù)項(xiàng)到指定索引號(hào)的位置。如果省略該參數(shù)，則將此數(shù)據(jù)項(xiàng)添加到現(xiàn)有列表中。

使用Additem方法的范例:

ComboBox1.Additem\"09春直屬工商企\"

上例是為列表框ComboBox1添加一個(gè)新數(shù)據(jù)項(xiàng)，“09春直屬工商企”為該數(shù)據(jù)項(xiàng)的文字。新添加的數(shù)據(jù)位于該列表框的最后。

2.Excel VBA中的數(shù)組介紹

Excel VBA中可以聲明一個(gè)數(shù)組來代表一群具有相同數(shù)據(jù)類型的值。數(shù)組是單一類型的變量，它具有很多的隔室來存儲(chǔ)很多值，而常規(guī)的變量只有一個(gè)存儲(chǔ)隔室，所以只能存儲(chǔ)一個(gè)值。

Excel VBA中的數(shù)組分為靜態(tài)數(shù)組和動(dòng)態(tài)數(shù)組。

靜態(tài)數(shù)組在聲明時(shí)具有固定大小，在程序運(yùn)行過程中不可對(duì)數(shù)組長度進(jìn)行更改。

動(dòng)態(tài)數(shù)組是指在聲明時(shí)沒有確定數(shù)組大小的數(shù)組，可以在執(zhí)行代碼時(shí)去改變數(shù)組大小。相對(duì)于靜態(tài)數(shù)組，動(dòng)態(tài)數(shù)組在使用中可以根據(jù)程序運(yùn)行需要對(duì)數(shù)組進(jìn)行擴(kuò)展，所以更具有靈活性。在本文中示范的數(shù)據(jù)填充使用的是動(dòng)態(tài)數(shù)組。

動(dòng)態(tài)數(shù)組定義的范例:

Dim ArrayName( )As Single

上例中定義的動(dòng)態(tài)數(shù)組ArrayName，其數(shù)據(jù)類型為Single。除Dim外，也可以使用 Static、Private或Public語句來聲明數(shù)組，并使括號(hào)內(nèi)為空。

在程序運(yùn)行過程中可以使用ReDim語句更改動(dòng)態(tài)數(shù)組，但是做這個(gè)動(dòng)態(tài)數(shù)組時(shí)，數(shù)組內(nèi)存在的值會(huì)消失。如果要保留原來的值，則可以使用ReDim Preserve語句來擴(kuò)充數(shù)組。下例語句是將ArrayName( )數(shù)組擴(kuò)充了1個(gè)元素，而原本數(shù)組中的值并沒有消失:

ReDim Preserve ArrayName(UBound(ArrayName)+1)

其中的UBound( )函數(shù)的作用是確定數(shù)組的指定維的最大可用下標(biāo)。

3.基本設(shè)計(jì)

為描述方便，本文使用圖1中的列表框和圖2中的表格數(shù)據(jù)進(jìn)行描述。其中數(shù)據(jù)是從Excel表格中“臨時(shí)表”中的第一列數(shù)據(jù)(圖1)，通過動(dòng)態(tài)數(shù)組將其填充到VBA程序中的列表框ComboBox1內(nèi)(圖2)。

4.實(shí)現(xiàn)方法

4.1使用Additem方法進(jìn)行數(shù)據(jù)填充

4.1.1定義索引變量i，用于遍歷表格數(shù)據(jù)。

dim i as integer

4.1.2讀取數(shù)據(jù)并添加到列表框ComboBox1中。其中表格數(shù)據(jù)存放在表格“臨時(shí)表”中的第一列，數(shù)據(jù)從第一行開始，默認(rèn)為遇到空單元格即為數(shù)據(jù)結(jié)束。代碼如下:

With臨時(shí)表′對(duì)表格“臨時(shí)表”進(jìn)行操作

i=1 ′設(shè)置索引變量初始值，使其指向表格中第一行

While.Cells(i，1)<>\"\"′判斷當(dāng)前行第一列單元格是否為空，為空則退出循環(huán)

ComboBox1.Additem.Cells(i，1)′將當(dāng)前行第一列單元格的內(nèi)容填充到ComboBox1中

i=i+1′索引指向下一行

Wend

End With

4.2使用動(dòng)態(tài)數(shù)組進(jìn)行數(shù)據(jù)填充

4.2.1定義存放數(shù)據(jù)的動(dòng)態(tài)數(shù)組temp( )。

Dim temp( )As String

4.2.2定義變量。其中索引變量j為，用于遍歷表格數(shù)據(jù);ArrLength存放的是動(dòng)態(tài)數(shù)組的長度。

dim j，ArrLength as integer

4.2.3讀取數(shù)據(jù)并存入數(shù)組。表格數(shù)據(jù)存放格式與3.1相同。代碼如下:

ArrLength=0 ′記錄動(dòng)態(tài)數(shù)組的長度

With臨時(shí)表′對(duì)表格“臨時(shí)表”進(jìn)行操作

j=1′設(shè)置索引變量初始值，使其指向表格中第一行

While.Cells(j，1)<>\"\" ′判斷當(dāng)前行第一列單元格是否為空，為空則退出循環(huán)

ArrLength=ArrLength+1 ′增加動(dòng)態(tài)數(shù)組長度

ReDim Preserve temp(ArrLength)′以新的長度擴(kuò)展數(shù)組，并保留原有數(shù)據(jù)

temp(ArrLength-1)=Cells(j，1)′將單元格內(nèi)容添加到數(shù)組最后，因?yàn)閂BA中數(shù)組起始索引為0，所以數(shù)組最后 一個(gè)元素的索引應(yīng)是數(shù)組長度-1

j=j+1 ′索引指向下一行

Wend

End With

通過上述代碼，程序?qū)⑺行枰畛淙肓斜砜虻臄?shù)據(jù)全部存入動(dòng)態(tài)數(shù)組temp(圖3)。

4.2.4使用動(dòng)態(tài)數(shù)組數(shù)據(jù)對(duì)列表框進(jìn)行數(shù)據(jù)填充

在數(shù)據(jù)存入動(dòng)態(tài)數(shù)組后，對(duì)列表框的填充就變的簡單了，只需要將數(shù)組temp賦值給列表框ComboBox1的List屬性就可以。代碼如下:

ComboBox1.List=temp

5.需要注意的問題

在對(duì)列表框進(jìn)行數(shù)據(jù)填充的時(shí)候需要注意一些問題。

5.1對(duì)列表框的數(shù)據(jù)填充可清除由ListFillRange 屬性指定的任何區(qū)域。

ListFillRange屬性返回或者設(shè)置用于填充指定列表框的工作表區(qū)域，String類型，可讀寫。對(duì)該屬性進(jìn)行設(shè)置將破壞列表框中的所有列表項(xiàng)。

5.2使用ReDim語句擴(kuò)展動(dòng)態(tài)數(shù)組時(shí)不能拼錯(cuò)數(shù)組名。

動(dòng)態(tài)數(shù)組可以在過程中使用ReDim語句來做數(shù)組擴(kuò)展。當(dāng)使用ReDim語句時(shí)不能拼錯(cuò)數(shù)組的名稱。否則即使在模塊中有包含Option Explicit語句，仍然會(huì)因此而生成第二個(gè)數(shù)組。

5.3對(duì)動(dòng)態(tài)數(shù)組進(jìn)行擴(kuò)展時(shí)需使用Preserve關(guān)鍵字保留原來的數(shù)據(jù)。

對(duì)于過程中的動(dòng)態(tài)數(shù)組的大小，可以使用ReDim語句去改變它的維數(shù)，定義元素的數(shù)目以及每個(gè)維數(shù)的底層綁定。每當(dāng)需要時(shí)，可以使用ReDim語句去更改動(dòng)態(tài)數(shù)組。然而當(dāng)做這個(gè)動(dòng)作時(shí)，數(shù)組中存在的值會(huì)丟失。若要保存數(shù)組中原先的值，則可以使用ReDim Preserve語句來擴(kuò)充數(shù)組。當(dāng)對(duì)動(dòng)態(tài)數(shù)組使用Preserve關(guān)鍵字時(shí)，只可以改變最后維數(shù)的上層綁定，而不能改變維數(shù)的數(shù)目。

6.結(jié)語

本文給出了Excel VBA中對(duì)列表框進(jìn)行動(dòng)態(tài)數(shù)據(jù)填充的兩種方法。從比較上看，使用Additem方法進(jìn)行填充，代碼較為簡單，程序流程更加簡潔;而使用動(dòng)態(tài)數(shù)組進(jìn)行填充，雖然在算法上較前者復(fù)雜一些，但是因?yàn)槭褂脭?shù)組進(jìn)行數(shù)據(jù)的中轉(zhuǎn)，在填充之前可以對(duì)數(shù)組中的數(shù)據(jù)進(jìn)行較為復(fù)雜的操作，比如數(shù)據(jù)排序等，功能擴(kuò)展更為靈活。我們?cè)趯?shí)際使用中應(yīng)當(dāng)根據(jù)需要選擇合適的方法。

本文范例中的源數(shù)據(jù)是Excel表格中的數(shù)據(jù)，其他來源的數(shù)據(jù)，例如程序動(dòng)態(tài)計(jì)算的結(jié)果、用戶外部輸入的數(shù)據(jù)等都可以套用該方法。

參考文獻(xiàn):

[1]陳代國，朱志富.基于VBA的EXCEL成績分類統(tǒng)計(jì)[J].辦公自動(dòng)化，2009，(02).

[2]郭海濱.Excel中提高VBA效率的方法研究[J].硅谷，2009，(03).

[3]劉靜.VBA在Excel中的應(yīng)用[J].科技信息，2009，(08).

[4]李生山.淺談VB程序設(shè)計(jì)要素——數(shù)組[J].科技信息，2008，(32).