徐志偉，孫航

（1.陜西鐵路工程職業(yè)技術(shù)學(xué)院陜西渭南714000；2.開封大學(xué)河南開封475000）

Microsoft Office軟件是微軟公司開發(fā)的辦公自動(dòng)化應(yīng)用軟件，Microsoft Excel是其中的一個(gè)重要的組成部分，由于它包含大量的公式函數(shù)對(duì)各種大量數(shù)據(jù)有很強(qiáng)的處理、統(tǒng)計(jì)分析能力，被廣泛地應(yīng)用于管理、統(tǒng)計(jì)、金融等領(lǐng)域。伴隨計(jì)算機(jī)的普及已經(jīng)成為日常辦公不可或缺的工具。

VBA（Visual Basic for Application），是基于Visual Basic f or Windows發(fā)展而來的[1]。在執(zhí)行特定功能或重復(fù)性高的操作時(shí)，使用VBA有助于使工作自動(dòng)化，提高工作效率。另外，由于VBA可以直接應(yīng)用Office套裝軟件的各項(xiàng)強(qiáng)大功能，所以對(duì)于程序設(shè)計(jì)人員的程序設(shè)計(jì)和開發(fā)更加方便快捷。

1 需求分析

在工作中經(jīng)常會(huì)做數(shù)據(jù)的收集匯總，制定特定表結(jié)構(gòu)的工作表模板供其他人員填寫，然后回收匯總合并成一個(gè)完整表，一般完成這個(gè)任務(wù)主要采用手工打開工作表進(jìn)行復(fù)制、粘貼這樣簡(jiǎn)單而重復(fù)性較高的操作，容易使人疲憊導(dǎo)致操作錯(cuò)誤，難以察覺，工作效率低。

我們知道Excel宏可使任務(wù)自動(dòng)化，用戶可創(chuàng)建并執(zhí)行一個(gè)宏，以替代人工進(jìn)行一系列費(fèi)時(shí)而重復(fù)的Excel操作。事實(shí)上，它是一個(gè)自定義命令，用來完成所需任務(wù)。如：加速日常編輯和格式設(shè)置、組合多個(gè)命令、使對(duì)話框中的選項(xiàng)更易于訪問、使一系列復(fù)雜的任務(wù)自動(dòng)執(zhí)行等[2]。宏所使用的語言是VBA語言，那么通過VBA編程可以更靈活、高效、快速地編制出應(yīng)用程序，高效完成工作任務(wù)，而且在以后的工作中需要稍加修改或不用修改進(jìn)行重復(fù)使用，這樣一天或幾天的工作任務(wù)就可以在幾分鐘輕松完成。

2 應(yīng)用案例

某高校每年都進(jìn)行職業(yè)技能考試，各種考試采用統(tǒng)一制定好格式的報(bào)名表，分發(fā)到各班級(jí)填寫報(bào)名表，如圖1、圖2所示，之后由負(fù)責(zé)人員將這些原始報(bào)名數(shù)據(jù)匯總成一個(gè)總表，如圖3所示。匯總的過程如果采用不斷地打開原始工作表，復(fù)制報(bào)考學(xué)生信息數(shù)據(jù)，然后粘貼到總表中，如此重復(fù)操作，耗費(fèi)大量的時(shí)間和精力。類似的信息統(tǒng)計(jì)工作在其他企事業(yè)單位也常常遇到，如對(duì)所有子公司的銷售數(shù)據(jù)匯總統(tǒng)計(jì)，所有下屬部門工作人員的信息表收集等。本文即是利用VBA編程來快速解決這樣一類問題。

2.1 應(yīng)用條件分析

本案例中各班級(jí)上交的報(bào)名登記表表名稱為班級(jí)名稱，工作表模板由考試管理中心制定下發(fā)，表結(jié)構(gòu)格式是固定的、統(tǒng)一的，數(shù)據(jù)均填寫在工作薄的第一張工作表中。各班報(bào)名人數(shù)不同，即表中的數(shù)據(jù)記錄行數(shù)不同，但每個(gè)人的報(bào)名信息要求相同。在最后得到的匯總表增加了序號(hào)、班級(jí)標(biāo)題字段，當(dāng)某一班級(jí)的報(bào)名表數(shù)據(jù)被錄入?yún)R總表時(shí)，對(duì)應(yīng)班級(jí)字段填寫報(bào)名表文件名，用來標(biāo)注數(shù)據(jù)來源，方便后續(xù)數(shù)據(jù)統(tǒng)計(jì)處理。通過對(duì)此類大量工作表進(jìn)行數(shù)據(jù)匯總的應(yīng)用案例分析，發(fā)現(xiàn)一些規(guī)律，所有的表格具有相同的表結(jié)構(gòu)形式，匯總?cè)艘龅木褪谴蜷_這些表復(fù)制其中的有效數(shù)據(jù)，粘貼到匯總表中。

圖1 源數(shù)據(jù)1Fig.1Source data 1

圖2 源數(shù)據(jù)2Fig.2Source data 2

圖3 匯總工作表Fig.3Consolidated worksheet

2.2 VBA編程具體實(shí)現(xiàn)數(shù)據(jù)匯總

2.2.1 設(shè)計(jì)思路

在使用VBA編程實(shí)現(xiàn)數(shù)據(jù)匯總的過程中，加入一些用和程序間的交互設(shè)計(jì)，使得程序的適用性更強(qiáng)，具體設(shè)計(jì)思路如下：

1）準(zhǔn)備工作：將所有源工作薄存放于一個(gè)文件夾中，匯總工作薄放在其他文件夾下，將匯總結(jié)果錄入到工作薄匯總表.xls中第二個(gè)工作表（匯總表）中，對(duì)該工作表設(shè)計(jì)標(biāo)題行，在A列、B列分別添加序號(hào)、班級(jí)列標(biāo)題，其他列標(biāo)題和報(bào)名登記表列標(biāo)題相同，并將單元格格式與源工作表格式相同。

2）用戶能夠通過界面中的瀏覽按鈕選擇瀏覽源工作薄所在的文件夾，獲取源工作表所在文件夾的路徑。利用VBA代碼調(diào)用瀏覽文件夾對(duì)話框常用有3種方法[3]：使用API方法，使用Shell.Application方法，使用FileDialog方法。本程序?yàn)g覽按鈕編程應(yīng)用API方法調(diào)用文件夾瀏覽窗口打開及選擇制定文件夾。為獲取選擇的文件夾路徑，設(shè)計(jì)一個(gè)文本框來保存文件夾路徑。可以用菜單“視圖”→“工具欄”→“控件工具箱”添加一個(gè)文本框?qū)崿F(xiàn)。

3）自定義參數(shù)，在對(duì)源工作表讀取數(shù)據(jù)時(shí)，需要用戶根據(jù)工作表模板設(shè)置讀取源工作表數(shù)據(jù)的起始行和總列數(shù)，及匯總表的起始行數(shù)、列數(shù)。開始錄入數(shù)據(jù)，利用上一步添加控件方法添加一些文本框供用戶填寫相關(guān)參數(shù)，這樣既實(shí)現(xiàn)了程序與用戶的交互，也提高了程序的適用性。

4）用戶發(fā)出開始匯總指令，程序即開始依次將制定文件夾中所有的文件中的數(shù)據(jù)讀取并順序存儲(chǔ)到匯總表中。這一步是整個(gè)VBA編程主用部分，當(dāng)用戶點(diǎn)擊開始按鈕，程序讀取選擇文件夾路徑及設(shè)置的參數(shù)，根據(jù)文件夾路徑獲取所有文件名存入文件名數(shù)組（FilesArr）中，然后開始遍歷文件名數(shù)組中對(duì)應(yīng)的每一個(gè)工作薄，讀取其中第一個(gè)工作表的數(shù)據(jù)。此時(shí)利用VBA引用Excel對(duì)象進(jìn)行編程，不必顯示打開每一個(gè)工作薄來讀取數(shù)據(jù)，通過Excel對(duì)象的一些方法、屬性來計(jì)算源工作表有效數(shù)據(jù)的行數(shù)，根據(jù)指定的起始行和列將有效數(shù)據(jù)復(fù)制到匯總工作表中，并在班級(jí)字段對(duì)應(yīng)單元格填充文件名，用來說明數(shù)據(jù)來源，序號(hào)列自動(dòng)按序列進(jìn)行填充，直到將所有源數(shù)據(jù)復(fù)制到匯總表中。

5）查看清除匯總結(jié)果，為方便用戶查看或清除匯總表匯總內(nèi)容，設(shè)置了查看和清空匯總表數(shù)據(jù)按鈕。當(dāng)點(diǎn)擊清除按鈕，自動(dòng)將匯總表中標(biāo)題行以下的所有數(shù)據(jù)刪除掉。

2.2.2 界面控件設(shè)計(jì)

在工作薄匯總表.xls中第一個(gè)工作表中進(jìn)行界面設(shè)計(jì)，如圖4所示，添加5個(gè)文本框名稱屬性分別為：txtPath，txtCols，txtSRowBegin，txtTColBegin，txtTRowBegin，4個(gè)命令按鈕的名稱屬性分別為：btnPath，btnCommit，btnReslut，btnClear。其他一些說明性的文字直接編輯到工作表的單元格中，另外為了界面美觀將區(qū)域的填充色設(shè)置為淺藍(lán)色。

圖4 界面設(shè)計(jì)Fig.4Interface design

2.2.3主要過程、函數(shù)代碼功能

1）點(diǎn)擊瀏覽按鈕調(diào)用過程代碼如下，實(shí)現(xiàn)添加源文件所在文件夾路徑。

…此處省略打開文件夾對(duì)話框代碼

以下瀏覽按鈕選擇源工作薄文件夾路徑

Private Sub btnPath_Click（）

Dim FName As String

FName=BrowseFolderA（Caption:=“選擇源工作薄所在文件夾”）

If FName＜＞vbNullString Then txtPath.Text=FName

End Sub

2）開始按鈕對(duì)應(yīng)過程，實(shí)現(xiàn)將選擇的文件夾路徑中所有工作薄中的第一個(gè)工作表中的數(shù)據(jù)導(dǎo)入到匯總表中，對(duì)應(yīng)程序流程圖[4]如圖5所示。

圖5 流程圖Fig.5Flow chart

Private Sub btnCommit_Click（）

…，省略了為使代碼更快而在運(yùn)行前保存并關(guān)閉Excel相關(guān)狀態(tài)的代碼

Dim i，j，rCount，s_Rows，s_Col，s_BeginRow，t_BeginRow，t_BeginCol As Long

s_BeginRow=txtSRowBegin.Value′獲取源表要讀取的數(shù)據(jù)起始行

s_Col=txtCols.Value′獲取源表要讀取的數(shù)據(jù)總列數(shù)

t_BeginRow=txtTRowBegin.Value

t_BeginCol=txtTColBegin.Value

以上兩行表示匯總表起始寫入行/列，不包含標(biāo)題行/新建字段列，并在后面運(yùn)行過程中自增1下移/右移到新空白行/列供新的數(shù)據(jù)導(dǎo)入。

Dim MyFolder As Folder

Dim FileArr

Dim MyFile As File

Dim FilePath As String

以下打開登記文件夾下所有的文件名

Set MyFSO=CreateObject（“Scripting.FileSystemObject”）

Set MyFolder=MyFSO.GetFolder（txtPath）

Set FilesArr=MyFolder.Files

FilePath=txtPath.Value&“”

以下代碼依次打開每個(gè)源工作薄，檢測(cè)其中數(shù)據(jù)記錄個(gè)數(shù)，并循環(huán)導(dǎo)入到匯總表中。

For Each MyFile In FilesArr

Set xlapp=CreateObject（“Excel.Application”）′創(chuàng)建EXCEL對(duì)象

Set xlbook=xlapp.Workbooks.Open（FilePath&MyFile.Name）′打開已經(jīng)登記的EXCEL工件簿文件

xlapp.Visible=False′設(shè)置EXCEL對(duì)象不可見

Set xlsheet=xlbook.Worksheets（1）′設(shè)置工作表對(duì)象

s_Rows=xlsheet.Range（“a65535”）.End（xlUp）.Row′獲取源工作表A列已使用的總行數(shù)包括標(biāo)題行

rCount=s_Rows－s_BeginRow+1計(jì)算源工作表sheets（1）數(shù)據(jù)記錄的個(gè)數(shù)

ThisWorkbook.Sheets（3）.Range（“B”&t_BeginRow&“:”&“B”&（t_BeginRow+rCount－1））=MyFile.Name′在匯總表B列t_BeginRow開始的rCount個(gè)單元格填充已打開的工作薄的名稱，表示本行數(shù)據(jù)記錄來源。

For i=s_BeginRow To s_Rows

For j=1 To s_Col

ThisWorkbook.Sheets（3）.Cells（t_BeginRow，t_BeginCol+j－1）=xlsheet.Cells（i，j）′將源工作表中數(shù)據(jù)導(dǎo)入到匯總表該工作薄名稱單元格后單元格中

Next

ThisWorkbook.Sheets（3）.Cells（t_BeginRow，1）=t_BeginRow－txtTRowBegin.Value+1′在第一列序號(hào)列實(shí)現(xiàn)自動(dòng)填充

t_BeginRow=t_BeginRow+1′匯總表開始行下移逐條實(shí)現(xiàn)導(dǎo)入

Next

以下代碼關(guān)閉源工作薄xlbook.Close xlapp.Quit

Set xlapp=Nothing

Next

…′省略程序運(yùn)行后，恢復(fù)Excel原來的狀態(tài)的代碼

End Sub

3）查看按鈕對(duì)應(yīng)代碼，打開匯總表查看結(jié)果。

Private Sub btnResult_Click（）

Worksheets（"匯總表"）.Activate’激活匯總表，查看結(jié)果End Sub

4）清空匯總表數(shù)據(jù)按鈕對(duì)應(yīng)代碼，清除匯總表中數(shù)據(jù)。

Private Sub btnClear_Click（）

Dim a，b As Long

a=txtTRowBegin.Value

b=65536

Worksheets（“匯總表”）.Rows（a&":"&b）.ClearContents’匯總表數(shù)據(jù)記錄行清除內(nèi)容

End Sub

3 結(jié)束語

在通過VBA編程實(shí)現(xiàn)多個(gè)Excel工作薄的數(shù)據(jù)匯總案例中，應(yīng)用Excel對(duì)象來操作工作薄、工作表及單元格，而VBA開發(fā)的主要的難點(diǎn)和問題就在于相應(yīng)的對(duì)象模型的理解及其應(yīng)用。在用VBA開發(fā)中解決問題的方法比較靈活，如在對(duì)瀏覽文件夾對(duì)話框調(diào)用時(shí)，就可以有多種方法。Microsoft Excel辦公軟件所提供的命令按鈕可以滿足辦公員平時(shí)辦公需要，然而通過本例可以看到，要想使辦公自動(dòng)化程度更高、效率更高，則要學(xué)習(xí)利用VBA對(duì)Excel進(jìn)行二次開發(fā)，研究制定出靈活、高效的解決方案，可以讓辦公人員從大量繁瑣的操作中解脫出來。所開發(fā)VBA程序可以解決一類問題，用戶只需要根據(jù)自己的不同需求，進(jìn)行相應(yīng)的修改，便可以快速解決類似數(shù)據(jù)匯總問題。

[1]Walkenbach J.Excel 2003高級(jí)VBA編程寶典[M].蓋江南，王勇譯.北京：電子工業(yè)出版社，2005.

[2]伍云輝.完全手冊(cè)：Excel VBA辦公應(yīng)用開發(fā)詳解[M].北京：電子工業(yè)出版社，2008.

[3]Excel Home.Excel應(yīng)用大全[M].北京：人民郵電出版社，2008.

[4]史寶會(huì).Visual Basic程序設(shè)計(jì)教程[M].北京：機(jī)械工業(yè)出版社，2005.

[5]馬維峰.Excel VBA應(yīng)用開發(fā)從基礎(chǔ)到實(shí)踐[M].北京：電子工業(yè)出版社，2006.

[6]Kimmel P T，Bullen S，Green J，et al.Excel 2003 VBA Programmer’s Reference[M].Birmingham:WROX，2004.