基于ExcelVBA的数据处理软件开发

摘要：Excel VBA编程通过对Excel自身功能的集成和扩展可以较为快捷高效地形成一个完整的数据处理软件，用于解决重复或复杂数据的处理。该文以财务预算数据处理软件开发为实例，简要论述了Excel VBA开发数据处理软件的设计思路、技术要点和方法，同时还列举了一些通用代码供大家参考和使用。

关键词：宏；VBA；SQL；ADO

中图分类号：TP311 文献标识码： A 文章编号：1009-3044（2014）22-5209-04

数据处理是单位财务工作日常工作的重要部分，现有很多知名的财务管理软件在单位财务管理中起着重要的作用，但具体到某一项应用时，由于单位情况不同，不一定完全适用，有必要自行开发一些这类功能单一，实际需求这类数据处理软件，由于使用的局限性和经费的原因，不可能花费大量的人力、财力去研发，所以寻求一种简单、高效、低成本的开发方法非常有意义的。笔者认为Excel VBA编程是一种值得推荐的方法，Excel 是微软办公套装软件的一个重要的组成部分，它可以进行各种数据的处理、统计分析和辅助决策操作，广泛地应用于管理、统计财经、金融等众多领域。Visual Basic for Applications（VBA）是Visual Basic的一种宏语言，是微软开发出来在其桌面应用程序中执行通用的自动化（OLE）任务的编程语言。主要能用来扩展Windows的应用程式功能，利用VBA可使Microsoft Office软件的应用更高效率。

1 软件开发的关键技术

VBA是建立在Office软件上的应用开发工具，其基本框架包括语句、对象、方法、属性、事件等。

1） VBA基本语句

宏程序语句用来运行后可以完成一个功能。例如：

Sub test（） '开始语句

Range（"A1"） = 10

End Sub '结束语句

2） VBA对象

VBA中的对象其实就是我们操作的具有方法、属性的excel中支持的个体，Excel中的几个常用对象表示方法：

工作簿： Workbooks 代表工作簿集合，所有的工作簿；

Workbooks（N），表示激活第N个工作簿；

Workbooks （"工作簿名称"） 某个工作簿；

工作表：Sheets（"工作表名称"） 表示某个工作表；

Sheets（n） 表示按排列顺序，第n个工作表；

ActiveSheet 表示活动工作表，光标所在工作表；

单元格：cells 所有单元格；

Range （"单元格地址"） 使用引用区域确定单元格；

Cells（行数，列数） 使用引用整行、整定单元格；

3） VBA属性

就是VBA对象所具有的特点，表示某个对象的属性是：对象.属性=属性值。例如：Sheets（1）.Name = "工作表改名了" 工作表改名

4） VBA方法

是作用于VBA对象上的动作，表示用某个方法作用于VBA的对象上，可以用下面的格式：对象.方法 参数值名称：=参数值

例如：Sheet1.Move before：=Sheets（"Sheet3"） 将工作表sheet1移至工作表sheet3之前。

2 软件设计目标

本软件是用来完成单位各部门财务预算处理的。根据各部门实际的支出，财务部门用来控制各部门的支出情况。原始记录如表1：

根据原始记录，当选择部门名称和预算分项名称时即可查询出该部门该项预算的信息，并根据查询结果决定该项是否可支出，支出后更新数据表，来控制部门预算的执行。

3 软件实现

根据上述介绍方法，系统框图如图1，该系统后台使用Microsoft Office Access数据库，数据表结构如表2。

2） 鼠标右Office按钮自定义快速访问工具栏公式启用迭代计算最多迭代次数输入1。

Excel2003：（1）点击菜单“工具宏安全性”，在安全性对话框中选择“低”。

2） 点击菜单工具选项重新计算，把迭代√一下最多迭代次数输入1。

由于篇幅关系，数据输入模块省略，读者可参考下面更新事件来完成数据输入。

预算处理界面如图2，可查询某部门的预算执行情况，并处理帐务。

查询按钮的click事件代码如下：

Private Sub CommandButton2_Click（）

On Error GoTo errorcheck

Dim mydata As New Data查询

Dim sql As String， arr， x， y， sk

If mydata.是否存在（"Ruku"， "部门"， [b6]） = False Then

MsgBox "该部门不存在"

Exit Sub

Else

Application.EnableEvents = False

Range（"a8：f33"） = ""

sql = "select * from RuKu where 部门='" & Trim（[b6]） & "'" & " and 名称='" & Trim（[d6]） & "'"

arr = mydata.筛选结果（sql）

[f6] = arr（7， 0）

[h6] = arr（8， 0）

For x = 0 To UBound（arr， 2）

For y = 1 To UBound（arr） - 2

If y = 1 Then

Cells（x + 8， y） = arr（y + 1， x）

Else

If y = 6 Then

sk = 2

Else

sk = 0

End If

Cells（x + 8， y + 1） = arr（y + 1 + sk， x）

If y = 5 Then

Cells（x + 8， y） = 0

End If

End If

Next y

Next x

CommandButton1.Visible = True

Application.EnableEvents = True

End If

errorcheck：

End Sub

若本次有支出，点击更新按钮，更改数据库记录，更新按钮click事件代码如下：

Private Sub CommandButton1_Click（）

On Error GoTo errorcheck

Dim l， arr， arr1， x As Integer， mydate As Date， hm As String， sr As String， sr1 As String， sql As String

Dim mydata As New Data查询

Dim bm， mc， ze， fxze

Dim conn As New Connection

Dim rst As New Recordset

arr = Range（"A8：g34"）

bm = [b6]

mc = [d6]

ze = [h6]

fxze = [f6]

conn.Open "provider=Microsoft.jet.OLEDB.4.0；data source=" & ThisWorkbook.Path & "/Database/CangKu.mdb"

sql = "delete from RuKu where 部门='" & Trim（[b6]） & "'" & " and 名称='" & Trim（[d6]） & "'"

sr = "'" + bm + "'，'" + mc + "'，'" + arr（x， 1） + "'，'" + arr（x， 3） + "'，" & arr（x， 4） & "，" & arr（x， 5） & "，" & arr（x， 6）

sr = sr & "，" & fxze & "，" & ze & "，'" & arr（x， 7） & "'"

sql = "Insert into ruku （部门，名称，明细，日期，金额，支出金额，余额，分项总额，总额，备注） values（" & sr & "）"

mydata.执行sql命令 （sql）

Next x

MsgBox "成功更新数据库！"

CommandButton1.Visible = False

errorcheck：

End Sub

Data查询类模块代码：

Sub 执行sql命令（sq As String）

Dim Conn As New Connection

Dim rst As New Recordset

Conn.Open "provider=Microsoft.jet.OLEDB.4.0；data source=" & ThisWorkbook.Path & "/Database/CangKu.mdb"

Conn.Execute （sq）

Conn.Close

Set Conn = Nothing

End Sub

Function 筛选结果（sq As String）

Dim Conn As New Connection

Dim rst As New Recordset

Conn.Open "provider=Microsoft.jet.OLEDB.4.0；data source=" & ThisWorkbook.Path & "/Database/CangKu.mdb"

Set rst = conn.Execute（sq）

筛选结果 = rst.GetRows

Conn.Close

Set Conn = Nothing

End Function

4 结束语

本文以较简单的实例介绍了用VBA开发Excel实用软件的方法，把Excel和Access数据库结合起来，来完成数据数据功能，具有使用方便、结构简单、界面友好、维护方便的特点。它的使用，对于从事数据处理的相关人员提供一种思路，通过学习简单的VBA代码和基本的数据库知识，就能完全胜任开发出符合自己工作需要的简单数据处理软件。在计算机的使用过程中，发挥自主创造力，充分利用Microsoft Office软件各项强大功能，提高工作效率。

参考文献：

[1] 李萍.利用ExcelVBA实现考场清册中照片的批量插入[J].中国教育信息化，2013（4）.

[2] 祝昕刚.用Excel VBA编制变形监测数据处理程序[J].地理空间信息，2011（3）.