基于VBA的ActiveX控件多關(guān)鍵字查詢技術(shù)

蔣正茂

摘? 要： 應(yīng)用辦公系統(tǒng)時(shí)，常常需要在其他Excel文檔中獲取數(shù)據(jù)填寫在新報(bào)表中。為了高效和準(zhǔn)確地完成任務(wù)，基于Excel辦公軟件的普及化現(xiàn)狀，提出基于VBA的ActiveX控件表達(dá)和Worksheet事件觸發(fā)實(shí)現(xiàn)多關(guān)鍵字查詢技術(shù)，實(shí)現(xiàn)從其他Excel工作表中獲取數(shù)據(jù)并填寫到新報(bào)表的工作。

關(guān)鍵詞： VBA; 多關(guān)鍵字; 數(shù)據(jù)處理; ActiveX

中圖分類號(hào)：TP3? ? ? ? ? 文獻(xiàn)標(biāo)識(shí)碼：A? ? ?文章編號(hào)：1006-8228（2020）04-04-03

VBA-based ActiveX control multi-keyword query technology

Jiang Zhengmao

（School of mechanical engineering Sichuan University， Chengdu， Sichuan 610065， China）

Abstract： When using the office system， it is often necessary to obtain data from other Excel documents and fill in the new report form. In order to complete the task efficiently and accurately， and in view of the popularization of Excel office software， this paper proposes to realize multi-keyword query technology by using VBA ActiveX control and worksheet event trigger， so as to implement the work of obtaining data from other Excel worksheets and filling in the new report form.

Key words： VBA; multi-keyword; data processing; ActiveX

0 引言

2019年2月中共中央、國(guó)務(wù)院印發(fā)了《中國(guó)教育現(xiàn)代化2035》，為我國(guó)教育發(fā)展描繪了遠(yuǎn)景藍(lán)圖。其中提到要提升一流人才培養(yǎng)與創(chuàng)新能力。加強(qiáng)創(chuàng)新人才特別是拔尖創(chuàng)新型人才的培養(yǎng)，加大應(yīng)用型、復(fù)合型、技術(shù)技能型人才培養(yǎng)比重[1]。衡量一所學(xué)校培養(yǎng)創(chuàng)新能力，統(tǒng)計(jì)學(xué)生參加國(guó)家認(rèn)可的競(jìng)賽是一個(gè)重要指標(biāo)，為此需要填寫【創(chuàng)新學(xué)生競(jìng)賽獲獎(jiǎng)名單】，名單中的學(xué)生、指導(dǎo)老師、大賽名稱等信息來(lái)源于保存在其他Excel文檔中的歷史數(shù)據(jù)或標(biāo)準(zhǔn)數(shù)據(jù)集，換句話說(shuō)，不是手工填寫，是從其他報(bào)表中提取已有數(shù)據(jù)進(jìn)行填寫。

1 VBA關(guān)鍵技術(shù)

微軟公司針對(duì)Microsoft Office用戶推出了易學(xué)易用的VBA開(kāi)發(fā)工具[2]，只需在安裝Microsoft office時(shí)選中開(kāi)發(fā)工具即可使用。使用VBA能夠幫助辦公人員從大量重復(fù)的統(tǒng)計(jì)、計(jì)算、分析工作中解脫出來(lái)，還可以完成各種復(fù)雜的、交互式的管理控制系統(tǒng)[5]。

ActiveX控件表達(dá)是Excel的一種用戶界面設(shè)計(jì)工具。用戶界面就像電視機(jī)的遙控板，是用戶與程序進(jìn)行互動(dòng)的窗口[2]。Excel中用兩種類型控件設(shè)計(jì)用戶界面：窗體控件和ActiveX控件。ActiveX控件擁有很多屬性和事件，可以在工作表和用戶窗體中使用。如果在編輯數(shù)據(jù)的同時(shí)還要進(jìn)行其他操作，那么使用ActiveX控件會(huì)靈活很多。

Worksheet事件是Excel的一種事件。Excel中的事件就是能被Excel對(duì)象識(shí)別的操作。Worksheet中文意思是工作表，Worksheet事件就是工作表中的對(duì)象能識(shí)別的操作。VBA編程環(huán)境是面向?qū)ο蟮木幊汰h(huán)境，比如工作表中的單元格是其中一個(gè)對(duì)象，對(duì)單元格的文字輸入是一個(gè)事件，該事件可以觸發(fā)一段程序塊的運(yùn)行，程序塊就是設(shè)計(jì)者需要編寫的劇本。如是利用面向?qū)ο笤砭涂梢栽贓xcel工作表中，讓指定數(shù)據(jù)，按照事先編輯好的邏輯進(jìn)行操作運(yùn)行。

2 實(shí)現(xiàn)要求

創(chuàng)新學(xué)生競(jìng)賽獲獎(jiǎng)名單表格如表1所示。

【創(chuàng)新學(xué)生競(jìng)賽獲獎(jiǎng)名單】中的授獎(jiǎng)單位和大賽名稱來(lái)自于國(guó)家認(rèn)可學(xué)科競(jìng)賽項(xiàng)目名錄。填寫該信息時(shí)，操作者可根據(jù)自己習(xí)慣，在【授獎(jiǎng)單位】或【大賽名稱】任意兩個(gè)空白單元格中輸入字詞，計(jì)算機(jī)即可動(dòng)態(tài)地將符合條件的競(jìng)賽條目顯示在待選列表框中，以供選擇。操作者用鼠標(biāo)選中目標(biāo)大賽條目后，計(jì)算機(jī)自動(dòng)將信息填寫到【授獎(jiǎng)單位】和【大賽名稱】單元格中。

綜上所述，系統(tǒng)功能有如下特點(diǎn)。①柔性化。根據(jù)輸入者偏好，實(shí)現(xiàn)任意關(guān)鍵字查詢數(shù)據(jù)。②模糊化。輸入者只需輸入部分關(guān)鍵字，實(shí)現(xiàn)查詢數(shù)據(jù)。③關(guān)聯(lián)性。在待選框中選中目標(biāo)數(shù)據(jù)后，實(shí)現(xiàn)完整信息自動(dòng)填入多個(gè)空白單元格。文章介紹基于VBA的ActiveX控件技術(shù)實(shí)現(xiàn)多關(guān)鍵字模糊化查詢，將查詢信息完整填寫在多個(gè)空白單元格中。

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

中學(xué)創(chuàng)新基地建設(shè)評(píng)估體系是一種信息管理系統(tǒng)，采用本地Excel管理數(shù)據(jù)模式，是根據(jù)每所學(xué)校數(shù)據(jù)的獨(dú)立性和工作人員的習(xí)慣選定的最佳模式。

評(píng)估系統(tǒng)設(shè)計(jì)成多張Excel文檔組成的套表結(jié)構(gòu)，其一，方便協(xié)同辦公，其二，保證數(shù)據(jù)的同一性和有效性。負(fù)責(zé)錄入教師打開(kāi)名稱為【每年創(chuàng)新學(xué)生競(jìng)賽獲獎(jiǎng)名單.xlsm】文檔，直接在報(bào)表空白單元格內(nèi)填寫信息。套表文檔之間相互關(guān)聯(lián)，比如【創(chuàng)新學(xué)生競(jìng)賽獲獎(jiǎng)名單】中的教師信息就來(lái)自于【本校創(chuàng)新培養(yǎng)教師名單.xlsm】和【校外創(chuàng)新培養(yǎng)教師名單.xlsm】工作簿，該工作簿記錄學(xué)校負(fù)責(zé)創(chuàng)新培養(yǎng)的教師信息，這樣保證教師信息的同一性，而不會(huì)出現(xiàn)一名教師多個(gè)姓名情況。【創(chuàng)新學(xué)生競(jìng)賽獲獎(jiǎng)名單】中的授獎(jiǎng)單位和大賽名稱來(lái)源于基礎(chǔ)數(shù)據(jù)表中的【競(jìng)賽名稱】工作表，這樣保證競(jìng)賽獲獎(jiǎng)信息的有效性。同一性和有效性是評(píng)估系統(tǒng)科學(xué)性的保障。

ActiveX控件中的文本控件TextBox和列表控件ListBox實(shí)現(xiàn)對(duì)目標(biāo)對(duì)象的捕獲和數(shù)據(jù)呈現(xiàn)。數(shù)據(jù)輸入時(shí)，光標(biāo)激活空白單元格（文本控件TextBox動(dòng)態(tài)跟蹤并覆蓋活動(dòng)單元格，輸入關(guān)鍵字由動(dòng)態(tài)跟隨的TextBox獲?。?，數(shù)據(jù)查詢功能開(kāi)始，隨著輸入關(guān)鍵字的不同，待選數(shù)據(jù)框中的數(shù)據(jù)列表信息內(nèi)容跟隨變化（待選數(shù)據(jù)框由ListBox呈現(xiàn)，ListBox設(shè)計(jì)動(dòng)態(tài)跟隨活動(dòng)單元格，且隨輸入關(guān)鍵字不同隨時(shí)更新待選數(shù)據(jù)列表）。ActiveX控件設(shè)計(jì)成活動(dòng)單元格跟隨功能。

4 功能實(shí)現(xiàn)

ActiveX控件動(dòng)態(tài)跟隨功能實(shí)現(xiàn)。光標(biāo)選中空白單元格進(jìn)行輸入操作由Worksheet的SelectionChange事件觸發(fā)，實(shí)現(xiàn)代碼如下：

With Me.TextBox1? ?'設(shè)置文本框

.Visible=True

.Top=Target.Top

.Left=Target.Left

.Width=Target.Width

.Height=Target.Height

.Activate

End With

With Me.ListBox1? ? '設(shè)置列表框

.Visible=True

.Top=Target.Offset（1）.Top

.Left=Target.Left

.Width=350

.Height=Target.Height*9

End With

上面代碼實(shí)現(xiàn)一個(gè)與活動(dòng)單元格大小相同，位置重合的文本框，一個(gè)與活動(dòng)單元格左對(duì)齊，并懸掛在單元格下方的列表框。

活動(dòng)單元格中輸入關(guān)鍵字，列表框中顯示相應(yīng)待選項(xiàng)。列表框中顯示待選項(xiàng)由TextBox的KeyUp事件觸發(fā)。實(shí)現(xiàn)代碼如下：

Me.ListBox1.Clear

myStr=Me.TextBox1.Text

maxRow=21

ReDim Arr1（1 To maxRow， 1 To 2）

arr2=Array（"授獎(jiǎng)單位"， "大賽名稱"）

j=j+1

Arr1（j，1）=arr2（0）

Arr1（j，2）=arr2（1）

For i=1 To UBound（Arrsj）

ai=InStr（Arrsj（i， MyCol）， myStr）

'MyCol為數(shù)據(jù)有效性待選列號(hào)，實(shí)現(xiàn)模糊查詢

If ai=1 Then

j=j+1

Arr1（j，1）=Arrsj（i，1）

Arr1（j，2）=Arrsj（i，2）

If j>maxRow-1 Then Exit For? '搜到給定個(gè)數(shù)停止

End If

Next i

If j

For i=1 To UBound（Arrsj）

ai=InStr（Arrsj（i， MyCol）， myStr）

If ai>1 Then

j=j+1

Arr1（j，1）=Arrsj（i，1）

Arr1（j，2）=Arrsj（i， 2）

If j>maxRow-1 Then Exit For '搜到給定個(gè)數(shù)停止

End If

Next i

End If

With Me.ListBox1

.Clear

.List=Arr1? '二維數(shù)組送列表框。

End With

實(shí)現(xiàn)效果如圖1所示。

【授獎(jiǎng)單位】和【大賽名稱】具有關(guān)聯(lián)性數(shù)據(jù)的填寫。關(guān)聯(lián)信息的整體填寫，由ListBox的DblClick事件觸發(fā)。代碼如下：

With sh.ListBox1

ActiveCell.Offset（， 1 - MyCol）.Value=.List（.ListIndex， 0）

ActiveCell.Offset（， 2 - MyCol）.Value=.List（.ListIndex， 1）

End With

上面代碼實(shí)現(xiàn)，無(wú)論是通過(guò)【授獎(jiǎng)單位】還是【大賽名稱】進(jìn)行查詢，操作者選中目標(biāo)信息后，整條信息將完整地填入到【授獎(jiǎng)單位】和【大賽名稱】多個(gè)關(guān)聯(lián)空白單元格中。

5 結(jié)束語(yǔ)

利用當(dāng)前常用的辦公軟件Excel中的VBA技術(shù)，提出了一種任意關(guān)鍵字模糊查詢并將整條信息完整地填入多個(gè)關(guān)聯(lián)空白單元格技術(shù)。這一技術(shù)被運(yùn)用到信息采集系統(tǒng)中，對(duì)于數(shù)據(jù)的采集收到了非常好的效果，不僅提高了數(shù)據(jù)采集效率，而且使采集的數(shù)據(jù)更加標(biāo)準(zhǔn)規(guī)范。這種方法對(duì)于類似的數(shù)據(jù)信息系統(tǒng)均實(shí)用，能很好提高數(shù)據(jù)管理效率。

參考文獻(xiàn)（References）：

[1] 劉曉星.廣州多所中學(xué)發(fā)力拔尖創(chuàng)新人才培養(yǎng)對(duì)接高校欲成“早期孵化器”[J].廣州日?qǐng)?bào)，2019.3.6.

[2] Excel Home.別怕，Excel VBA其實(shí)很簡(jiǎn)單[M].電子郵件出版社，2012.

[3] 李小遐.Excel VBA 在辦公自動(dòng)化中的應(yīng)用[M].科技論壇，2014.22：105-106

[4] 韋偉.Excel中利用VBA實(shí)現(xiàn)課表數(shù)據(jù)轉(zhuǎn)置[J].黃岡職業(yè)技術(shù)學(xué)院學(xué)報(bào)，2019.21（3）：99-101

[5] 韋立梅.使用Excel VBA管理工資資料[J].電腦與電信，2013.6：70-75