Excel VBA中列表框數據的動態填充

摘 要: 本文基于Microsoft Office Excel應用軟件，利用Excel 開放的Visual Basic編輯器，以VBA語言為基礎，針對VBA開發中列表框數據動態填充的問題， 給出了使用Additem方法和使用動態數組兩種設計方法和程序。

關鍵詞: Excel VBA列表框 動態數據填充

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

在使用Excel VBA進行的程序開發中，經常會使用到列表框控件(ComboBox)以提供現有數據供用戶選擇。列表框的數據填充，可以使用預定義的方法，即在程序設計時將菜單數據固化在控件中，也可以在程序運行過程中使用語句進行數據的動態填充。動態填充主要采用兩種方法:使用列表框本身的Additem方法和使用數組對列表框的List屬性進行賦值。

1. Additem方法介紹

Additem方法的作用為列表框或組合框添加數據項。其語法為:

expression.AddItem(Text，Index)。

expression為必選。該表達式返回一個ControlFormat 對象。

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

Index參數Variant類型，可選，表示新輸入項的輸入位置。如果列表中數據項數目少于指定索引號，則從列表末尾開始添加多個空數據項到指定索引號的位置。如果省略該參數，則將此數據項添加到現有列表中。

使用Additem方法的范例:

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

上例是為列表框ComboBox1添加一個新數據項，“09春直屬工商企”為該數據項的文字。新添加的數據位于該列表框的最后。

2.Excel VBA中的數組介紹

Excel VBA中可以聲明一個數組來代表一群具有相同數據類型的值。數組是單一類型的變量，它具有很多的隔室來存儲很多值，而常規的變量只有一個存儲隔室，所以只能存儲一個值。

Excel VBA中的數組分為靜態數組和動態數組。

靜態數組在聲明時具有固定大小，在程序運行過程中不可對數組長度進行更改。

動態數組是指在聲明時沒有確定數組大小的數組，可以在執行代碼時去改變數組大小。相對于靜態數組，動態數組在使用中可以根據程序運行需要對數組進行擴展，所以更具有靈活性。在本文中示范的數據填充使用的是動態數組。

動態數組定義的范例:

Dim ArrayName( )As Single

上例中定義的動態數組ArrayName，其數據類型為Single。除Dim外，也可以使用 Static、Private或Public語句來聲明數組，并使括號內為空。

在程序運行過程中可以使用ReDim語句更改動態數組，但是做這個動態數組時，數組內存在的值會消失。如果要保留原來的值，則可以使用ReDim Preserve語句來擴充數組。下例語句是將ArrayName( )數組擴充了1個元素，而原本數組中的值并沒有消失:

ReDim Preserve ArrayName(UBound(ArrayName)+1)

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

3.基本設計

為描述方便，本文使用圖1中的列表框和圖2中的表格數據進行描述。其中數據是從Excel表格中“臨時表”中的第一列數據(圖1)，通過動態數組將其填充到VBA程序中的列表框ComboBox1內(圖2)。

4.實現方法

4.1使用Additem方法進行數據填充

4.1.1定義索引變量i，用于遍歷表格數據。

dim i as integer

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

With臨時表′對表格“臨時表”進行操作

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

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

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

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

Wend

End With

4.2使用動態數組進行數據填充

4.2.1定義存放數據的動態數組temp( )。

Dim temp( )As String

4.2.2定義變量。其中索引變量j為，用于遍歷表格數據;ArrLength存放的是動態數組的長度。

dim j，ArrLength as integer

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

ArrLength=0 ′記錄動態數組的長度

With臨時表′對表格“臨時表”進行操作

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

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

ArrLength=ArrLength+1 ′增加動態數組長度

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

temp(ArrLength-1)=Cells(j，1)′將單元格內容添加到數組最后，因為VBA中數組起始索引為0，所以數組最后 一個元素的索引應是數組長度-1

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

Wend

End With

通過上述代碼，程序將所有需要填充入列表框的數據全部存入動態數組temp(圖3)。

4.2.4使用動態數組數據對列表框進行數據填充

在數據存入動態數組后，對列表框的填充就變的簡單了，只需要將數組temp賦值給列表框ComboBox1的List屬性就可以。代碼如下:

ComboBox1.List=temp

5.需要注意的問題

在對列表框進行數據填充的時候需要注意一些問題。

5.1對列表框的數據填充可清除由ListFillRange 屬性指定的任何區域。

ListFillRange屬性返回或者設置用于填充指定列表框的工作表區域，String類型，可讀寫。對該屬性進行設置將破壞列表框中的所有列表項。

5.2使用ReDim語句擴展動態數組時不能拼錯數組名。

動態數組可以在過程中使用ReDim語句來做數組擴展。當使用ReDim語句時不能拼錯數組的名稱。否則即使在模塊中有包含Option Explicit語句，仍然會因此而生成第二個數組。

5.3對動態數組進行擴展時需使用Preserve關鍵字保留原來的數據。

對于過程中的動態數組的大小，可以使用ReDim語句去改變它的維數，定義元素的數目以及每個維數的底層綁定。每當需要時，可以使用ReDim語句去更改動態數組。然而當做這個動作時，數組中存在的值會丟失。若要保存數組中原先的值，則可以使用ReDim Preserve語句來擴充數組。當對動態數組使用Preserve關鍵字時，只可以改變最后維數的上層綁定，而不能改變維數的數目。

6.結語

本文給出了Excel VBA中對列表框進行動態數據填充的兩種方法。從比較上看，使用Additem方法進行填充，代碼較為簡單，程序流程更加簡潔;而使用動態數組進行填充，雖然在算法上較前者復雜一些，但是因為使用數組進行數據的中轉，在填充之前可以對數組中的數據進行較為復雜的操作，比如數據排序等，功能擴展更為靈活。我們在實際使用中應當根據需要選擇合適的方法。

本文范例中的源數據是Excel表格中的數據，其他來源的數據，例如程序動態計算的結果、用戶外部輸入的數據等都可以套用該方法。

參考文獻:

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

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

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

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