人力资源部承载着企业人员劳资、人事、培训、社保、档案及文件的大量数据信息,而且信息量随时间不断积累,不断地做阶段“积分”工作——汇总统计、存档,有时做“微分”工作——特定信息查找、整理及出据报表,对于大中型企业人力资源管理,日常信息及数据处理工作量是相当大的。
如今,电脑在人事管理、办公应用已经相当普及了,几乎每个办公电脑都装有微软的Office软件,绝大多数人都会使用Word和Excel,特别是Excel在日常数据处理的应用相当多,Excel代替的以往的手工表格,能够快捷、直观、高效地管理简单的数据库。然而,面对人事大量重复性的、繁琐的、流水式的作业,要提高工作的效率,必需编程,使数据相互关联,使事务数据处理自动化。
通过近几年对人事工资、社保、教培应用软件的编程实践,感受到,对诸如人事琐碎、易变数据的编程管理,从程序的易用性,到编程的效率,以及快捷地掌握编程的方法,Excel下的VBA编程是最佳选择。
二、Excel下VBA程序的特点
1、 程序代码(宏)封装在电子文档中,以Office下的EXCEL为应用平台,数据和程序可保存在同一Excel文档中,也可把程序保存在一个Excel文档中,去调用另一个或多个Excel文档中的工作表数据,或者调用其他数据库中的数据。
2、 VBA编写的程序无需编译,无库文件,程序是“绿色”的。电子文档运行时,提示是否运行“宏”来决定是否运行程序,通过“数字签名”来确认程序开发者,保障程序的安全性,利用“工程保护”密码来保障设计者的程序不被修改和查看。
3、 VBA(Visual Basic for Application)是由Visual Basic发展而来的。Basic语言许多人曾经学习过,其语法符合人的常规思维习惯,学习起来容易掌握。VBA以其面向对象的程序设计方法,一方面提供了强大的功能,同时也便于学习掌握。对于一般用户,它允许用户通过宏记录器记录用户的各种操作,将其转换为VBA代码,从而使用户很容易地将日常琐碎的工作自动化,大大提高工作效率。对于程序设计人员,由于VBA可以直接应用Office软件的各项强大功能,支持应用程序的开发和应用,因而它使应用程序的设计和开发更加简便、快捷。
三、Excel下VBA数据库编程的一些实现方法
1、 数据库的要求及数据库指针的实现方法
通常,我们把特定的数据集合存放在工作表(sheet)形成数据表,列代表字段,行代表记录,在程序中要控制这一矩形块中的每个单元格中的数据,必需引入“记录指针”,才能进行数据库的插入、删除、修改、记录跳转、首尾记录的判定的操作。VBA没有提供这方面的控件或组件,因此,要把工作表当编程管理,必需作相应的限定,通过编写代码来实现。
首先,建立sheet数据表时必需充分考虑所需的字段,起始“行”是人为固定的,编写程序后字段(列)不得随意增减,起始“行”位置不能更改,否则要重新修改程序。
编程控制工作表中的数据,记录指针就是该条记录的行号(rows),首记录是固定的,尾记录的行号由程序来自动判定,得出数据行号的最大值,记录指针取值只能在首记录(最小值)到尾记录(最大值)之间。示例如下:
Dim Record as integer ‘定义记录指针为整数
Dim Recordmin as integer‘定义首记录为整数
Dim Recordmax as integer‘定义尾记录为整数
Recordmin=5 ‘首记录在第5行
Recordmax=Recordmin ‘赋初值
‘假定第3列为关键字段,即记录在该列的单元格不能为空
sheets(“sheet1”).select ‘激活或选取sheet1工作表
do until cells(Recordmax,3).value=” ‘判定为空时退出循环
recordmax=recordmax+1
loop ‘循环结束得到尾记录行号
Record=Recordmin ‘跳到首记录
Record=Recordmax ‘跳到尾记录
Record=Record+1 ‘记录指向下一行
If Record>Recordmax then Record=Recordmax ‘避免超过尾记录
Record=Record-1 ‘记录指向上一行
If Record<Recordmin then Record=Recordmin‘避免小于首记录
2、 数据库维护的界面实现方法
数据库的维护,可直接在电子表格中处理,但为了程序界面的美观、程序的完整性、减少数据偶然出错、以及提高数据录入效率,通常采用窗体(userform)及控件来进行数据的添加、插入、删除、修改,数据的转入及导出等操作。编程实现的过程简略概括有:
(1) 插入一个用户窗体(userform);
(2) 根据数据库处理字段及实施处理的功能添加控件及命令按钮;
(3) 表单初始化编程,使表单与相应工作簿下电子表格数据关联,设置“数据指针”,控件的初始化并与对应字段(列)数据关联,对命令按钮编程。示例如图1:
其中,要实现姓名列表框,只需采用如下语句:
with listbox1
record=recordmin 记录指针到首记录
do until sheets(“基本信息库”).cells(record,3).value=’’
.AddItem sheets(“基本信息库”).cells(record,3).value
record=record+1
Loop
End with
要实现照片图像框,利用图像调入函数(loadpicture),只需采用如下语句:
on error resume next
photoname=”./photo/”+gh+”.jpg” ‘按工号存在photo目录下
If dir(photoname)<>’’ Then ‘若照片文件存在,则调入
Image1.picture=LoadPicture(photoname)
End if
3、 数据库报表输出实现方法
EXCEL具有强大的报表实现和打印功能,现在许多应用程序编程都把数据转到电子表格下打印,在EXCEL下编程实现数据库的打印是极其简单、容易的,可以利用录制“宏”,改写后在程序中控制打印特定的工作表,也可把多个表中的数据根据要求转入一个新生成的工作表再来打印,从而实现复杂报表输出。
编程实现报表输出的程序设计中,基本上是调用EXCEl内的VBA函数,设置表格,特别其利用报表浏览功能,可根据自己的需求及数据的长度来调整行、列的宽度打印,自己设置打印页面,从而大大降低编程难度,提高报表输出的灵活性。编程实现的过程简略概括有:
(1) 设置工作表的表格线,程序示例如:
sheets(“sheets1”).select ‘选取工作表
Range(cells(4,1),cells(2+I,13)).select ‘选取划表线区域
With selection.Borders(xlEdgeLeft) ‘可用录制宏得到
.linestyle=xlContinous
.Weight=xlThin
.ColorIndex=xlAutomatic
end with……
(2) 设计报表的打印对话窗,实现的示例如图2:
在预览中调整设置,如图3,实现预览的语句如下:
Windows(Bm + Nian + "年.xls").Activate ‘选择工作簿
Sheets(“三月”).select ‘选择工作表
ActiveWindow.SelectedSheets.PrintPreview ‘浏览选择工作表
实施打印的语句:
ActiveWindow.SelectedSheets.PrintOut copies:=TextBox1.Text ‘根据打印份数输出到打印机
4、 程序的封装界面实现方法
利用MenuBars 建立菜单栏,EXCEL原有的菜单栏则自动删除,利用Menus增加菜单,利用MenuItems增加菜单项,实现的语句示例如下:
Sub SetMenu()
On Error Resume Next '忽略错误
Application.Caption = " "
MenuBars("MyMenu").Delete
'′删除自定义选单
MenuBars.Add ("MyMenu")
'自定义选单项
MenuBars("MyMenu").Menus.Add Caption:="工资数据维护"
MenuBars("MyMenu").Menus("工资数据维护").MenuItems.Add Caption:="工资活动项录入", OnAction:="gzlr"
MenuBars("MyMenu").Menus("工资数据维护")_
.MenuItems.BeginGroup = True
MenuBars("MyMenu").Menus("工资数据维护").MenuItems.Add _Caption:="个人所得税处理", OnAction:="sds"
(省略)
End sub
能实现如图4的菜单界面,在程序运行中,无需进入工作表下,保障了程序的完整性。
四、VBA程序应用的注意问题
1、 列内数据的数据属性
程序发放给应用人员使用中,通常遇到的问题有:VBA编程下的工作表具有数据库的相关性质,列代表字段,行代表记录,列字段单元格的字符属性是一致的,特别是储存“数值”的列,该列单元格都必需是数值而非字符,输入数据时,切勿把“空格”输入到数值格中,否则,计算时出错。尽量在程序中维护数据,可减少出错。
2、 关键引用名
在程序应用中,工作簿、工作表、列位置、首行位置,在程序实现中是“绝对地址”引用,不得随意更改,否则程序运行出错。当然,若有必要,也可对工作簿加密,使用户离开程序就无法打开工作簿;工作簿存放在多层子目录下,让别人不能轻易看到。
笔者近三年用VBA编程设计多个数据库应用程序,编程解决一些小问题,感受到其编程的效率比FOXPRO高得多,特别适用于人事数据的编程管理,掌握编程难度低,建立数据库容易。如果你动心了,书店有大量VBA编程书籍,通过学习,一定会成为你的编程利器,提高工作效率的好帮手。