利用ＶＢＡ編寫Ｅｘｃｅｌ中的工資條與工資查詢窗口

摘要：本文通過利用Excel內嵌的VBA編程語言和Excel的宏功能相結合，實現了Excel下工資條的制作與工資查詢窗口的建立。

關鍵詞：VBA；宏；辦公應用

中圖分類號：TP311文獻標識碼：A文章編號：1009-3044(2008)14-20888-03

1 引言

VBA是附屬在Office辦公軟件包中的一套程序語言，主要用于自定義應用程序中的功能，以及加強應用程序之間的互動[1]。它繼承了Basic的很多特性以及優點，加快了桌面軟件設計的發展。使用VBA語言不僅能夠減輕辦公人員的工作負擔，而且還可以使Execl的大量重復性操作變得簡單快捷。

VBA具有如下兩個特點[1]：

(1) 編輯器中提供有大量豐富的對象，在一個工作表中就可以很方便控制其它工作表、數據表和文檔，從而實現不同工作表之間的數據交流。

(2) 編輯器提供有豐富的控件和完備的語言系統，用戶可以根據需求編寫VBA應用程序，從而創建一個功能完備的信息管理系統。

2 背景

高校教師工資管理表中一般包括教師編號、姓名、基本工資、職位崗位補貼、應扣稅金、住房補貼和應扣保險等項目內容。我們假定每位教師的應發工資＝基本工資＋職位崗位補貼＋住房補貼－應扣稅金－應扣保險，我們在Excle中分別建立了教師基本資料表、工資表等。

工資條是發放工資時交給員工的工資項目清單，其數據來源于工資表，與工資表所不同的是：每個工資條都應該包含標題以及該員工所有工資數據的詳細信息。編制工資條就是在工資表中為每一位員工的工資數據加上標題，以使每一位員工拿到工資條的時候都能夠清晰地看到各項工資數據所代表的含義。我們平常的做法是在每個老師的記錄所在行插入一行標題，此種做法因為教工數量的增多時造成比較費時而且容易出錯。因此，我們可以在Excel的基礎上，利用VBA來實現自動編制工資條。

為方便各位老師查詢自己的具體工資情況，建立“工資查詢表”工作表，只要在查詢窗口輸入員工編號，就可以查詢員工的詳細資料及各項工資數據。

3 編程實現

3.1 工資條的實現

打開[窗體]工具欄，單擊[按鈕]按鈕，將鼠標指針移至“工資表中”，即可添加一個窗體按鈕。此時系統會自動地彈出與該窗體按鈕對應的[指定宏]對話框，然后在[宏名]文本框中輸入“創建工資條”，單擊[指定宏]對話框中的[新建(N)]按鈕進入該按鈕的代碼編輯窗口，然后添加如下代碼：

Sub 創建工資條()

Dim i As Integer， row As Integer， col As Integer

Sheets.Add after:=Sheets(\"工資表\")

ActiveSheet.Name = \"工資條\"

'在“工資表”的后面創建一個新的“工資條”工作表；

Sheets(\"工資表\").Activate

row = Sheets(\"工資表\").[A1].CurrentRegion.Rows.Count

col = Sheets(\"工資表\").[A1].CurrentRegion.Columns.Count

Range(Cells(1， 1)， Cells(row， col)).Copy

Sheets(\"工資條\").Activate

ActiveSheet.Paste

Selection.PasteSpecial Paste:=xlPasteColumnWidths， operation:=xlNone， skipblanks:=False， Transpose:=False

For i = 2 To row - 1

Cells(i*2-1，1).Select

Selection.EntireRow.Insert

Next i

Range(\"1:1\").Copy

For i = 2 To row - 1

Cells(i*2-1，1).Select

ActiveSheet.Paste

Next i

Application.CutCopyMode = False

Range(\"A1\").Select

End Sub

3.2 工資查詢窗口的實現

第一步：在同個工作簿中新創建一個名為“工資查詢表”的工作表，選擇好一張合適的圖片作為[工作表背景]，打開VBE窗口界面，在該窗口空白處單擊鼠標右鍵，插入并設計好一個名為“UserForm1”的用戶窗體。

在用戶窗體上雙擊“開始查詢”命令按鈕進入該按鈕的代碼編輯窗口，添加如下代碼：

Private Sub CommandButton1_Click()

On Error GoTo error

id = TextBox1.Text

Sheets(\"基本資料表\").Activate

Sname = Application.WorksheetFunction.VLookup(id， Range(\"A1:F18\")， 2， False)

xueli = Application.WorksheetFunction.VLookup(id， Range(\"A1:F18\")， 4， False)

Sheets(\"工資表\").Activate

gw = Application.WorksheetFunction.VLookup(id， Range(\"A1:H18\")， 4， False)

zf = Application.WorksheetFunction.VLookup(id， Range(\"A1:H18\")， 5， False)

tax = Application.WorksheetFunction.VLookup(id， Range(\"A1:H18\")， 6， False)

bx = Application.WorksheetFunction.VLookup(id， Range(\"A1:H18\")， 7， False)

gjj = Application.WorksheetFunction.VLookup(id， Range(\"A1:H18\")， 8， False)

UserForm1.Hide

UserForm2.Show

Exit Sub

error:

Sheets(\"工資查詢表\").Activate

MsgBox \"對不起，不存在這個教工編號！\"

End Sub

第二步：設計顯示查詢窗口界面。

在工程資源管理器的窗口的任意位置單擊鼠標右鍵，插入一個UserForm2用戶窗體，并將標題修改為“顯示查詢結果”，并添加一系列的“標簽”、“文字框”按鈕，具體見圖2。

在VBE窗口界面中雙擊“顯示查詢結果”用戶窗體打開UserForm2代碼窗口，然后添加如下代碼：

Private Sub UserForm_Activate()

Sheets(\"工資查詢表\").Activate

lid.Value = id

lname.Value = Sname

subxueli

'調用該函數

lgw.Value = \" +\" + Str(gw) + \"元\"

lzf.Value = \" +\" + Str(zf) + \"元\"

ltax.Value = \" -\" + Str(tax) + \"元\"

lbx.Value = \" -\" + Str(bx) + \"元\"

lgjj.Value = \"-\" + Str(gjj) + \"元\"

money = 600 + mxueli + gw + zf - tax - bx - gjj

lmoney.Value = Str(money) + \"元\"

End Sub

Sub subxueli()

Select Case xueli

Case \"專科以下\"

mxueli = 0

lxueli.Value = xueli + \" 無學歷加成\"

Case \"專科\"

mxueli = 400

lxueli.Value = xueli + \" +\" + Str(mxueli) + \"元\"

Case \"本科\"

mxueli = 800

lxueli.Value = xueli + \" +\" + Str(mxueli) + \"元\"

Case \"碩士\"

mxueli = 1200

lxueli.Value = xueli + \" +\" + Str(mxueli) + \"元\"

Case \"博士\"

mxueli = 1600

lxueli.Value = xueli + \" +\" + Str(mxueli) + \"元\"

'此函數假設教師的基本工資與該員工的學歷有關，學歷越高其基本工資也越高。

End Select

End Sub

Private Sub CommandButton1_Click()

UserForm2.Hide

End Sub

Private Sub CommandButton2_Click()

UserForm2.Hide

UserForm1.Show

End Sub

4 結論

本模塊的完成是在消化了由劉宇作者完成的編著《Excel高效辦公——VBA入門與實戰》的基礎上，結合本單位的實際情況開發而成，在此表示感謝！作為非常流行的應用程序開發語言Visual Basic的子集，VBA具有VB語言的大多數特征和易用性，并可以將Excel作為開發平臺來開發應用程序，可以應用Excel的所有功能，例如其數據處理、數據庫連接、內置函數，等等，從而降低編程難度，加快了開發速度。對于我們來說，我們完全可以通過VBA來編制各類專業應用，以提高日常工作的效率。

參考文獻：

[1] 劉宇. Excel高效辦公——VBA入門與實戰[M]. 北京：人民郵電出版社，2006.

[2] 馬維峰. Excel VBA應用開發從基礎到實踐[M]. 北京：電子工業出版社，2007.1-30.

[3] 白金牛，王培吉. 利用VBA實現Excel的分頁匯總統計[J]. 2004，(8):55-56.

[4] 何兵，侯濤. 基于VBA的Excel下教師教學工作量統計模板開發[J]. 2006，(2):23-26.

[5] 李桂英. 基于XML和VBA的《計算機應用基礎》在線考試系統[J]. 2005，(219):25-28.

注：本文中所涉及到的圖表、注解、公式等內容請以PDF格式閱讀原文