基于VBA的ActiveX控件多關鍵字查詢技術

蔣正茂

摘? 要： 應用辦公系統時，常常需要在其他Excel文檔中獲取數據填寫在新報表中。為了高效和準確地完成任務，基于Excel辦公軟件的普及化現狀，提出基于VBA的ActiveX控件表達和Worksheet事件觸發實現多關鍵字查詢技術，實現從其他Excel工作表中獲取數據并填寫到新報表的工作。

關鍵詞： VBA; 多關鍵字; 數據處理; ActiveX

中圖分類號：TP3? ? ? ? ? 文獻標識碼：A? ? ?文章編號：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月中共中央、國務院印發了《中國教育現代化2035》，為我國教育發展描繪了遠景藍圖。其中提到要提升一流人才培養與創新能力。加強創新人才特別是拔尖創新型人才的培養，加大應用型、復合型、技術技能型人才培養比重[1]。衡量一所學校培養創新能力，統計學生參加國家認可的競賽是一個重要指標，為此需要填寫【創新學生競賽獲獎名單】，名單中的學生、指導老師、大賽名稱等信息來源于保存在其他Excel文檔中的歷史數據或標準數據集，換句話說，不是手工填寫，是從其他報表中提取已有數據進行填寫。

1 VBA關鍵技術

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

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

Worksheet事件是Excel的一種事件。Excel中的事件就是能被Excel對象識別的操作。Worksheet中文意思是工作表，Worksheet事件就是工作表中的對象能識別的操作。VBA編程環境是面向對象的編程環境，比如工作表中的單元格是其中一個對象，對單元格的文字輸入是一個事件，該事件可以觸發一段程序塊的運行，程序塊就是設計者需要編寫的劇本。如是利用面向對象原理就可以在Excel工作表中，讓指定數據，按照事先編輯好的邏輯進行操作運行。

2 實現要求

創新學生競賽獲獎名單表格如表1所示。

【創新學生競賽獲獎名單】中的授獎單位和大賽名稱來自于國家認可學科競賽項目名錄。填寫該信息時，操作者可根據自己習慣，在【授獎單位】或【大賽名稱】任意兩個空白單元格中輸入字詞，計算機即可動態地將符合條件的競賽條目顯示在待選列表框中，以供選擇。操作者用鼠標選中目標大賽條目后，計算機自動將信息填寫到【授獎單位】和【大賽名稱】單元格中。

綜上所述，系統功能有如下特點。①柔性化。根據輸入者偏好，實現任意關鍵字查詢數據。②模糊化。輸入者只需輸入部分關鍵字，實現查詢數據。③關聯性。在待選框中選中目標數據后，實現完整信息自動填入多個空白單元格。文章介紹基于VBA的ActiveX控件技術實現多關鍵字模糊化查詢，將查詢信息完整填寫在多個空白單元格中。

3 設計思路

中學創新基地建設評估體系是一種信息管理系統，采用本地Excel管理數據模式，是根據每所學校數據的獨立性和工作人員的習慣選定的最佳模式。

評估系統設計成多張Excel文檔組成的套表結構，其一，方便協同辦公，其二，保證數據的同一性和有效性。負責錄入教師打開名稱為【每年創新學生競賽獲獎名單.xlsm】文檔，直接在報表空白單元格內填寫信息。套表文檔之間相互關聯，比如【創新學生競賽獲獎名單】中的教師信息就來自于【本校創新培養教師名單.xlsm】和【校外創新培養教師名單.xlsm】工作簿，該工作簿記錄學校負責創新培養的教師信息，這樣保證教師信息的同一性，而不會出現一名教師多個姓名情況。【創新學生競賽獲獎名單】中的授獎單位和大賽名稱來源于基礎數據表中的【競賽名稱】工作表，這樣保證競賽獲獎信息的有效性。同一性和有效性是評估系統科學性的保障。

ActiveX控件中的文本控件TextBox和列表控件ListBox實現對目標對象的捕獲和數據呈現。數據輸入時，光標激活空白單元格（文本控件TextBox動態跟蹤并覆蓋活動單元格，輸入關鍵字由動態跟隨的TextBox獲取），數據查詢功能開始，隨著輸入關鍵字的不同，待選數據框中的數據列表信息內容跟隨變化（待選數據框由ListBox呈現，ListBox設計動態跟隨活動單元格，且隨輸入關鍵字不同隨時更新待選數據列表）。ActiveX控件設計成活動單元格跟隨功能。

4 功能實現

ActiveX控件動態跟隨功能實現。光標選中空白單元格進行輸入操作由Worksheet的SelectionChange事件觸發，實現代碼如下：

With Me.TextBox1? ?'設置文本框

.Visible=True

.Top=Target.Top

.Left=Target.Left

.Width=Target.Width

.Height=Target.Height

.Activate

End With

With Me.ListBox1? ? '設置列表框

.Visible=True

.Top=Target.Offset（1）.Top

.Left=Target.Left

.Width=350

.Height=Target.Height*9

End With

上面代碼實現一個與活動單元格大小相同，位置重合的文本框，一個與活動單元格左對齊，并懸掛在單元格下方的列表框。

活動單元格中輸入關鍵字，列表框中顯示相應待選項。列表框中顯示待選項由TextBox的KeyUp事件觸發。實現代碼如下：

Me.ListBox1.Clear

myStr=Me.TextBox1.Text

maxRow=21

ReDim Arr1（1 To maxRow， 1 To 2）

arr2=Array（"授獎單位"， "大賽名稱"）

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為數據有效性待選列號，實現模糊查詢

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? '搜到給定個數停止

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 '搜到給定個數停止

End If

Next i

End If

With Me.ListBox1

.Clear

.List=Arr1? '二維數組送列表框。

End With

實現效果如圖1所示。

【授獎單位】和【大賽名稱】具有關聯性數據的填寫。關聯信息的整體填寫，由ListBox的DblClick事件觸發。代碼如下：

With sh.ListBox1

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

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

End With

上面代碼實現，無論是通過【授獎單位】還是【大賽名稱】進行查詢，操作者選中目標信息后，整條信息將完整地填入到【授獎單位】和【大賽名稱】多個關聯空白單元格中。

5 結束語

利用當前常用的辦公軟件Excel中的VBA技術，提出了一種任意關鍵字模糊查詢并將整條信息完整地填入多個關聯空白單元格技術。這一技術被運用到信息采集系統中，對于數據的采集收到了非常好的效果，不僅提高了數據采集效率，而且使采集的數據更加標準規范。這種方法對于類似的數據信息系統均實用，能很好提高數據管理效率。

參考文獻（References）：

[1] 劉曉星.廣州多所中學發力拔尖創新人才培養對接高校欲成“早期孵化器”[J].廣州日報，2019.3.6.

[2] Excel Home.別怕，Excel VBA其實很簡單[M].電子郵件出版社，2012.

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

[4] 韋偉.Excel中利用VBA實現課表數據轉置[J].黃岡職業技術學院學報，2019.21（3）：99-101

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