常廣炎 楊彬

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

關(guān)鍵詞：宏；VBA；SQL；ADO

中圖分類號(hào)：TP311 文獻(xiàn)標(biāo)識(shí)碼： A 文章編號(hào)：1009-3044（2014）22-5209-04

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

1 軟件開發(fā)的關(guān)鍵技術(shù)

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

1） VBA基本語句

宏程序語句用來運(yùn)行后可以完成一個(gè)功能。例如：

Sub test（） '開始語句

Range（"A1"） = 10

End Sub '結(jié)束語句

2） VBA對(duì)象

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

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

Workbooks（N），表示激活第N個(gè)工作簿；

Workbooks （"工作簿名稱"） 某個(gè)工作簿；

工作表：Sheets（"工作表名稱"） 表示某個(gè)工作表；

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

ActiveSheet 表示活動(dòng)工作表，光標(biāo)所在工作表；

單元格：cells 所有單元格；

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

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

3） VBA屬性

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

4） VBA方法

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

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

2 軟件設(shè)計(jì)目標(biāo)

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

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

3 軟件實(shí)現(xiàn)

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

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

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

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

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

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

查詢按鈕的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

若本次有支出，點(diǎn)擊更新按鈕，更改數(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 （部門，名稱，明細(xì)，日期，金額，支出金額，余額，分項(xiàng)總額，總額，備注） 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é)束語

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

參考文獻(xiàn)：

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

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