将Excel VBA代码进行封装并调用的技术要点
2021-12-17张金沙陈建勇郑家翔雷辉刘惠莲
张金沙 陈建勇 郑家翔 雷辉 刘惠莲
摘要:为保护VBA代码,把VBA主要程序按VB格式修改,然后利用VB6进行编译,形成以二进制形式存在的DLL文件,在需要时调用。DLL文件很难破解,因而有效地保护了VBA的核心代码,大大提高了程序的安全性。
关键词:Excel;VBA;封装;DLL
中图分类号:TP311 文献标识码:A
文章編号:1009-3044(2021)33-0065-03
开放科学(资源服务)标识码(OSID):
1 背景
Excel VBA(Visual Basic for Applications)是内嵌于微软Office软件Excel中的程序开发语言,在Excel中可直接运行VBA程序,从而完成较复杂的工作[1-2],例如一次运行就能完成试卷质量的全部分析、操控网页实现学生成绩的批量上传[3]、实现复杂的统计工作等等,真正实现高效办公。
虽然Excel提供了对VBA代码的保护,即在Excel的VBE(Visual Basic Edirtor)界面下,找到“工具”的“VBAProject属性”,在“保护”页面进行密码设置,但是保护作用有限,只要具备一定VBA常识的人就能利用网络上的破解工具解密,且用VBA编写的程序代码不能在VBE环境下编译,而是“寄生”在宿主应用程序Excel中运行,解密后能直接编辑,给居心叵测之人有可乘之机,给程序带来严重安全隐患。
2 技术要点
鉴于VBA的优势,“试卷质量量化评测系统的开发与应用”课题组选用VBA为开发语言。为提高软件的安全性,利用Microsoft Visual Basic 6.0(以下简称VB6),将VBA的主要程序代码封装为很难破解的DLL(Dynamic Link Library)文件,在需要的地方进行调用。
为避免阅读长篇程序而分散注意力,下面用一个简单且完整的演示程序,分析将VBA封装为DLL文件并进行调用的技术要点。
演示程序功能使用一般VBA代码、VB与VBA通用代码和把代码封装后调用等三种不同方式,完成在C3单元格显示A3与B3单元格的积、在D3单元格显示实现方式,目的是清晰展示VBA代码按VB格式修改的关键步骤,见图1。
2.1 DLL 文件的创建
DLL 文件是动态链接文件,它存放的是各类程序的函数或子过程,当程序需要时可进行调用。一般软件使用DLL技术的主要目的是为了节省系统资源,提高程序的运行效率,但在本文是利用DLL文件的二进制属性、很难破解的特点,提高VBA代码的安全性[4-5]。
2.1.1 编写VBA代码
启动Excel,命名工作簿为“VBA封装调用”并保存。按Alt+F11进入VBE,点“插入”,选“模块”,在新增的“模块1”中输入下面程序。
Sub VBAcheng() '用一般VBA代码实现
Range("C3") = Cells(3, 1) * Cells(3, 2)
Range("d3") = "VBA"
End Sub
2.1.2 将VBA代码修改为VB所能识别的形式
在“模块1”中继续录入下面程序:
Sub VBcheng() '用 VB与VBA通用代码实现
Dim exlapp As Object '定义变量exlapp为object
Set exlapp = GetObject(, "Excel.Application") '使exlapp表示为EXCEL对象
exlapp.Range("C3") = exlapp.Cells(3, 1) * exlapp.Cells(3, 2)
exlapp.Range("d3") = "VB与VBA通用"
End Sub
说明:1)在制作DLL时要用VB6进行编译,必须将VBA代码修改为VB所能识别的形式,这是成功的关键。2)VB不能识别Excel的对象,故在程序中应先定义exlapp为EXCEL对象,然后在所有EXCEL对象前面均加上exlapp,例如sheet→exlapp.sheet,cells→exlapp.cells, range→exlapp.range,workbook→exlapp.workbook等等。3)如VBA程序中有Application.等代码,也要加上exlapp. 例如Application.quit要修改为exlapp.Application.quit、Application.ScreenUpdating要修改为exlapp.Application.ScreenUpdating等等。4)修改后的代码既能在Excel中直接运行,也可在VB中成功编译。5)为防止制作DLL后调试困难,根据经验,最好把要制作DLL文件的代码先在VBE中进行修改并调试无误后,再转入VB6中编译。
2.1.3 制作DLL文件
第一步:启动VB6,新建“ActiveX DLL”。修改工程名和类名分别为EncapDLL和MyClas,见图2。
第二步:建立引用。点击“工程”→“引用”→“Microsoft Office X Object Library”。其中X为版本号,与Excel的版本相关,一般在11.0以上。
第三步:编写代码。把上述VBE中经过修改、调试的代码(即VBcheng()过程)复制在代码窗。
Sub VBDcheng() '在名字中间加一个“D”,以示和VBcheng()过程区别
Dim exlapp As Object
Set exlapp = GetObject(, "Excel.Application")
exlapp.Range("C3") = exlapp.Cells(3, 1) * exlapp.Cells(3, 2)
exlapp.Range("d3") = "封装调用" '仅把显示内容修改
End Sub
第四步:生成DLL。为方便今后升级和修改程序,必须保存工程;生成DLL前,还要点菜单“运行”→“启动”,进一步检查修改后有无错误;最后点菜单“文件”→“生成EncapDLL.dll”。
2.2 DLL 的调用
2.2.1 注册DLL
手工注册较方便,方法是打开“VBA封装调用”工作簿,进入VBE,在菜单中点击“引用”→“浏览”→找到“EncapDLL.dll”所在文件夹并选中此文件。
2.2.2 引用DLL
进入VBE后,在左窗“工程”中,找到并双击ThisWorkbook,在弹出的窗口中添加如下代码:
Private Sub Workbook_BeforeClose(Cancel As Boolean) '关闭工作簿之前卸载EncapDLL文件
Shell "Regsvr32 /s /u " & Chr(34) & ThisWorkbook.Path & "\ EncapDLL.dll" & Chr(34)
End Sub
Private Sub Workbook_Open()'打开工作簿时加载EncapDLL文件
Shell "Regsvr32 /s " & Chr(34) & ThisWorkbook.Path & "\ EncapDLL.dll" & Chr(34)
End Sub
经过上述引用后,EncapDLL.dll与“VBA封装调用”工作簿必须如影随形地保存于同一文件夹内,否则会出错。
2.2.3 调用DLL
继续在VBE中,在“模块1”下添加如下代码:
Sub VBDLLcheng()
Dim ABC As New MyClas '定义ABC为新类,即为DLL文件中的类模块MyClas
ABC.VBDcheng '調用EncapDLL中提供的过程VBDcheng(),完成积的运算功能,而原VBA代码被隐藏
Set ABC = Nothing '释放类资源
End Sub
2.3 演示程序界面设计
2.3.1 界面设计
在表单中添加按钮的方法:点菜单“视图”→“工具栏”→“控件工具箱”→找到按钮即可添加按钮。其他设计参考图1。
2.3.2 按钮与过程关联
进入VBE后,在左窗“工程”中,找到并双击Sheets1,在弹出的窗口中添加如下代码,将按钮与对应过程关联:
Private Sub CommandButton1_Click()
Call VBAcheng '调用VBA代码编写的过程
End Sub
Private Sub CommandButton2_Click()
Call VBcheng '调用VB与VBA通用代码的过程)
End Sub
Private Sub CommandButton3_Click()
Call VBDLLcheng '调用封装后的过程
End Sub
Private Sub CommandButton4_Click() '清除运算结果
Range("C3") = ""
Range("d3") = ""
End Sub
至此演示程序完成。
实际运用时,在提供给用户的版本中,可将VBA的源代码(本例中的VBAcheng()过程和VBcheng()过程 )删除,只需保留能完成相功能的封装代码过程(本例中VBDLLcheng()过程)即可。
3 结束语
把VBA代码进行封装并调用的过程可总结如图3,其关键步骤是将VBA代码修改为VB可识别的形式。
制作DLL文件的主要目的是保护VBA代码。实践证明,把VBA程序代码经过简单修改,利用VB6进行编译,形成很难破解的以二进制形式存在的DLL文件,有效地保护了VBA的核心代码,大大提高了软件的安全性。
参考文献:
[1] 孟兵,刘琼.Excel VBA应用与技巧大全[M].北京:机械工业出版社,2021.
[2] 马致明,陈兴,林雨萌.巧用Excel VBA开发体温检测统计报表软件[J].电脑知识与技术,2020,16(33):209-211.
[3] 张金沙,金笛,石劭红.基于VBA成绩自动上传系统的开发[J].电脑知识与技术,2016,12(32):80-82.
[4] 周维京.VBA封装技术分析[J].电脑与电信,2008(4):34-35.
[5] 百度经验.封装ExcelVBA代码[EB/OL].[2020-12-20].
https://jingyan.baidu.com/article/f71d603756c55a1ab641d1fb.html.
【通联编辑:谢媛媛】