常廣炎 楊彬

摘要：Excel VBA編程通過對Excel自身功能的集成和擴展可以較為快捷高效地形成一個完整的數(shù)據(jù)處理軟件，用于解決重復或復雜數(shù)據(jù)的處理。該文以財務預算數(shù)據(jù)處理軟件開發(fā)為實例，簡要論述了Excel VBA開發(fā)數(shù)據(jù)處理軟件的設計思路、技術要點和方法，同時還列舉了一些通用代碼供大家參考和使用。

關鍵詞：宏；VBA；SQL；ADO

中圖分類號：TP311 文獻標識碼： A 文章編號：1009-3044（2014）22-5209-04

數(shù)據(jù)處理是單位財務工作日常工作的重要部分，現(xiàn)有很多知名的財務管理軟件在單位財務管理中起著重要的作用，但具體到某一項應用時，由于單位情況不同，不一定完全適用，有必要自行開發(fā)一些這類功能單一，實際需求這類數(shù)據(jù)處理軟件，由于使用的局限性和經(jīng)費的原因，不可能花費大量的人力、財力去研發(fā)，所以尋求一種簡單、高效、低成本的開發(fā)方法非常有意義的。筆者認為Excel VBA編程是一種值得推薦的方法，Excel 是微軟辦公套裝軟件的一個重要的組成部分，它可以進行各種數(shù)據(jù)的處理、統(tǒng)計分析和輔助決策操作，廣泛地應用于管理、統(tǒng)計財經(jīng)、金融等眾多領域。Visual Basic for Applications（VBA）是Visual Basic的一種宏語言，是微軟開發(fā)出來在其桌面應用程序中執(zhí)行通用的自動化（OLE）任務的編程語言。主要能用來擴展Windows的應用程式功能，利用VBA可使Microsoft Office軟件的應用更高效率。

1 軟件開發(fā)的關鍵技術

VBA是建立在Office軟件上的應用開發(fā)工具，其基本框架包括語句、對象、方法、屬性、事件等。

1） VBA基本語句

宏程序語句用來運行后可以完成一個功能。例如：

Sub test（） '開始語句

Range（"A1"） = 10

End Sub '結(jié)束語句

2） VBA對象

VBA中的對象其實就是我們操作的具有方法、屬性的excel中支持的個體，Excel中的幾個常用對象表示方法：

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

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

Workbooks （"工作簿名稱"） 某個工作簿；

工作表：Sheets（"工作表名稱"） 表示某個工作表；

Sheets（n） 表示按排列順序，第n個工作表；

ActiveSheet 表示活動工作表，光標所在工作表；

單元格：cells 所有單元格；

Range （"單元格地址"） 使用引用區(qū)域確定單元格；

Cells（行數(shù)，列數(shù)） 使用引用整行、整列確定單元格；

3） VBA屬性

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

4） VBA方法

是作用于VBA對象上的動作，表示用某個方法作用于VBA的對象上，可以用下面的格式：對象.方法 參數(shù)值名稱：=參數(shù)值

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

2 軟件設計目標

本軟件是用來完成單位各部門財務預算處理的。根據(jù)各部門實際的支出，財務部門用來控制各部門的支出情況。原始記錄如表1：

根據(jù)原始記錄，當選擇部門名稱和預算分項名稱時即可查詢出該部門該項預算的信息，并根據(jù)查詢結(jié)果決定該項是否可支出，支出后更新數(shù)據(jù)表，來控制部門預算的執(zhí)行。

3 軟件實現(xiàn)

根據(jù)上述介紹方法，系統(tǒng)框圖如圖1，該系統(tǒng)后臺使用Microsoft Office Access數(shù)據(jù)庫，數(shù)據(jù)表結(jié)構如表2。

2） 鼠標右Office按鈕→自定義快速訪問工具欄→公式→啟用迭代計算→最多迭代次數(shù)輸入1。

Excel2003：（1）點擊菜單“工具→宏→安全性”，在安全性對話框中選擇“低”。

2） 點擊菜單→工具→選項→重新計算，把迭代√一下→最多迭代次數(shù)輸入1。

由于篇幅關系，數(shù)據(jù)輸入模塊省略，讀者可參考下面更新事件來完成數(shù)據(jù)輸入。

預算處理界面如圖2，可查詢某部門的預算執(zhí)行情況，并處理帳務。

查詢按鈕的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.篩選結(jié)果（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

若本次有支出，點擊更新按鈕，更改數(shù)據(jù)庫記錄，更新按鈕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.執(zhí)行sql命令 （sql）

Next x

MsgBox "成功更新數(shù)據(jù)庫！"

CommandButton1.Visible = False

errorcheck：

End Sub

Data查詢類模塊代碼：

Sub 執(zhí)行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 篩選結(jié)果（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）

篩選結(jié)果 = rst.GetRows

Conn.Close

Set Conn = Nothing

End Function

4 結(jié)束語

本文以較簡單的實例介紹了用VBA開發(fā)Excel實用軟件的方法，把Excel和Access數(shù)據(jù)庫結(jié)合起來，來完成數(shù)據(jù)數(shù)據(jù)功能，具有使用方便、結(jié)構簡單、界面友好、維護方便的特點。它的使用，對于從事數(shù)據(jù)處理的相關人員提供一種思路，通過學習簡單的VBA代碼和基本的數(shù)據(jù)庫知識，就能完全勝任開發(fā)出符合自己工作需要的簡單數(shù)據(jù)處理軟件。在計算機的使用過程中，發(fā)揮自主創(chuàng)造力，充分利用Microsoft Office軟件各項強大功能，提高工作效率。

參考文獻：

[1] 李萍.利用ExcelVBA實現(xiàn)考場清冊中照片的批量插入[J].中國教育信息化，2013（4）.

[2] 祝昕剛.用Excel VBA編制變形監(jiān)測數(shù)據(jù)處理程序[J].地理空間信息，2011（3）.