基于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