Excel VBA在辦公自動(dòng)化中的應(yīng)用

（陜西國防工業(yè)職業(yè)技術(shù)學(xué)院電子信息系，西安,710302）

Excel VBA在辦公自動(dòng)化中的應(yīng)用

李小遐

（陜西國防工業(yè)職業(yè)技術(shù)學(xué)院電子信息系，西安,710302）

Excel是辦公軟件Office中的一款，它以優(yōu)秀的數(shù)據(jù)錄入功能和強(qiáng)大的數(shù)據(jù)處理和分析功能深受廣大辦公人員的喜愛。在Excel中使用VBA，能夠高效率地實(shí)現(xiàn)數(shù)據(jù)處理的自動(dòng)化，將工作人員從簡單而重復(fù)的數(shù)據(jù)處理工作中解脫出來，更能通過VBA編程對Excel進(jìn)行二次開發(fā)，實(shí)現(xiàn)很多高級功能，提高辦公效率。

Excel;VBA;宏;辦公自動(dòng)化

1 VBA概述

VBA(Visual Basic for Application)是新一代標(biāo)準(zhǔn)宏語言，由微軟公司開發(fā)出來，在其桌面應(yīng)用程序中執(zhí)行通用的自動(dòng)化（OLE）任務(wù)的編程語言。所謂應(yīng)用程序自動(dòng)化，是指通過編寫程序讓常規(guī)應(yīng)用程序（如Excel、Word等）自動(dòng)完成工作，例如在Excel里自動(dòng)設(shè)置單元格格式或者多張工作表之間自動(dòng)進(jìn)行計(jì)算等等。VBA是基于Visual Basic（VB）發(fā)展而來的，是VB的一個(gè)子集，與VB一樣是屬于面向?qū)ο蟮木幊陶Z言，VBA繼承了VB的開發(fā)機(jī)制，與VB有著相似的語言結(jié)構(gòu)和開發(fā)環(huán)境。VBA與VB的不同之處在于，VBA是Office辦公軟件內(nèi)嵌的編程語言，所以VBA代碼必須“寄生”在宿主應(yīng)用程序中運(yùn)行，不能生成獨(dú)立的應(yīng)用程序。VBA根據(jù)其嵌入軟件的不同，增加了對相應(yīng)軟件中的對象的控制功能，正是因?yàn)槿绱耍琕BA最適于定制已有的桌面應(yīng)用程序。

2 Excel VBA的辦公應(yīng)用

Excel是最早支持VBA的組件，Excel VBA作為一種擴(kuò)展工具，在辦公中得到了越來越廣泛的應(yīng)用，原因在于，很多實(shí)際應(yīng)用中復(fù)雜的Excel操作都可以利用VBA編程得到簡化。迄今為止，在 Excel 中使用 VBA 最常見的原因就是自動(dòng)完成重復(fù)的工作，當(dāng)然VBA 不僅僅可用于重復(fù)任務(wù)，還可以構(gòu)建 Excel 的新功能，例如可以開發(fā)新算法來分析數(shù)據(jù)，然后使用 Excel 中的圖表功能顯示結(jié)果，也可以執(zhí)行將 Excel 與其他 Office 應(yīng)用程序集成的任務(wù)。事實(shí)上，在所有 Office 應(yīng)用程序中，Excel 最常用作一個(gè)類似于常規(guī)開發(fā)平臺，除了所有涉及列表和會計(jì)的顯而易見的任務(wù)之外，從數(shù)據(jù)可視化到軟件原型制作的大量任務(wù)中，開發(fā)人員都可使用VBA對Excel進(jìn)行二次開發(fā)。

3 宏

VBA是一種宏語言，是通過運(yùn)行宏來工作的。如果需要在Excel中頻繁執(zhí)行某項(xiàng)工作，使用宏是最簡單的方法。宏是可用于自動(dòng)執(zhí)行任務(wù)的一項(xiàng)或一組操作，其本質(zhì)是由一系列VBA命令組成的程序，通過宏可以將一系列Excel操作組合在一起，形成一個(gè)命令，以實(shí)現(xiàn)任務(wù)的自動(dòng)化。在Excel中創(chuàng)建宏有兩種方法，即錄制宏和用VBA語言編寫宏代碼。

3.1 錄制宏代碼

在Excel2010中，利用“視圖”選項(xiàng)卡中“宏”組中的“錄制宏”按鈕，在“錄制新宏”對話框中，設(shè)置宏的名稱、保存位置以及快捷鍵，再單擊“確定”按鈕，就可以開始宏錄制，此時(shí)“錄制宏”按鈕狀態(tài)變成了“停止制”，點(diǎn)擊該按鈕即可完成宏錄制。宏錄制好后，使用快捷鍵即可運(yùn)行宏。在錄制宏的過程中，Excel將操作轉(zhuǎn)換為VBA代碼保存在宏中，運(yùn)行宏時(shí)，Excel驅(qū)動(dòng)這些代碼自動(dòng)完成記錄的操作。使用

Sub 宏1()

' 快捷鍵: Ctrl+k

With Selection.Font

.Name = "黑體"

.Size = 16

.Strikethrough = False

.Superscript = False

.Subscript = False

.OutlineFont = False

.Shadow = False

.Underline = xlUnderlineStyleNone

.ThemeColor = xlThemeColorLight1

.TintAndShade = 0

.ThemeFont = xlThemeFontNone

End With

Selection.Font.Bold = True

End Sub

上述代碼片段中加下劃線的部分就是多余的代碼，可以全部刪除，僅保留需要設(shè)置效果的3個(gè)語句，執(zhí)行的效果與之前是完全一致的。當(dāng)然修改宏代碼需要有一定的VBA基礎(chǔ)，所以說，錄制宏對不熟悉VBA編程的人員來說無疑是非常方便的，對于學(xué)習(xí)VBA編程也有很大幫助。

3.2 編寫宏代碼

錄制的宏可以忠誠地記錄Excel 操作，但有其自身的局限性，如錄制的宏不夠智能化，無法交互工作，代碼冗長，沒有判斷和循環(huán)能力，只能簡單執(zhí)行等，因此對于一些無法錄制的復(fù)雜操作，就需要在VBE窗口中使用VBA代碼直接編寫，然后將宏分配給VBA對象，如按鈕、圖形、控件和快捷鍵等，這樣執(zhí)行宏就像點(diǎn)擊按鈕或快捷鍵一樣簡單。以下通過一個(gè)具體的應(yīng)用案例進(jìn)行說明。

實(shí)現(xiàn)功能：庫存預(yù)期報(bào)警。問題描述：Excel工作表中存放著所有商品的庫存信息，A列為名稱，B列為計(jì)量單位，C列為目前庫存數(shù)量，庫管人員提供某個(gè)商品名稱和最小庫存數(shù)量時(shí)，低于這個(gè)數(shù)值的庫存數(shù)量單元格自動(dòng)變?yōu)榧t色、加粗，給出庫存報(bào)警提示。

實(shí)現(xiàn)這個(gè)功能的最佳途徑是通過按鈕運(yùn)行宏。在庫存工作表中添加一個(gè)命令按鈕，把這個(gè)按鈕指定給宏，用VBA編寫宏代碼，實(shí)現(xiàn)當(dāng)庫管人員點(diǎn)擊按鈕時(shí)會彈出對話框，在對話框里輸入要查詢的商品名稱和報(bào)警數(shù)值，如果庫存量不夠的話給出提示，VBA代碼如下所示。

Sub 庫存_Click()

Dim Mc As String

Dim x&, y&

Mc = InputBox("請輸入名稱:")

y = InputBox("請輸入報(bào)警數(shù)量:")

For x = 2 To Range("A65536").End(xlUp).Row

If Cells(x, 1) = Mc And Cells(x, 3) <= y Then

Cells(x, 3).Font.ColorIndex = 3

Cells(x, 3).Font.Bold = True

End If

Next x

End Sub

在Excel2010中，如果要編寫宏、運(yùn)行以前錄制的宏或者用VBA創(chuàng)建與Office程序一起使用的應(yīng)用程序，需要將“開發(fā)工具”選項(xiàng)卡顯示出來，與早期的Excel版本有所不同，Excel2010的默認(rèn)環(huán)境中，“開發(fā)工具”選項(xiàng)卡是隱藏的。操作方法是點(diǎn)擊“文件-選項(xiàng)-自定義功能區(qū)”，選中“開發(fā)工具”復(fù)選框，確定后即可在Excel窗口中看到“開發(fā)工具”選項(xiàng)卡，然后在“控件”組的“插入”按鈕里選擇“按鈕”窗體控件，即可在工作表中添加一個(gè)命令按鈕。

3.3 自定義功能區(qū)

日常辦公應(yīng)用中，除了經(jīng)常使用快捷鍵和按鈕運(yùn)行宏以外，還有一個(gè)顯為人知的快捷方法，即就是自定義功能區(qū)，然后把日常用到的一些功能定義成工具按鈕，以后工作時(shí)就可以同標(biāo)準(zhǔn)工具按鈕一樣點(diǎn)擊即可。操作方法是點(diǎn)擊“文件-選項(xiàng)-自定義功能區(qū)”，在打開的“Excel選項(xiàng)”對話框中進(jìn)行功能區(qū)和組的創(chuàng)建。操作時(shí)要在“主選項(xiàng)卡”中添加新建選項(xiàng)卡和新建組并重命名為一個(gè)合適的的名稱，然后把“常用命令”文本框中的宏添加到新建組中。設(shè)置完成后即可在Excel窗口看到新建的功能區(qū)以及其中的工具按鈕，并且這個(gè)設(shè)置對本地機(jī)的任何一個(gè)Excel工作薄都是有效的。以下通過一個(gè)具體的應(yīng)用案例進(jìn)行說明。

實(shí)現(xiàn)功能：隔行格式化工作表。問題描述：Excel工作表中有多行數(shù)據(jù)，為了便于閱讀和使用，需要把工作表設(shè)置為隔行一致的效果，比如隔行添加黃色底紋。這個(gè)工作用手工方法也能完成，但是比較枯燥還浪費(fèi)時(shí)間，在此使用VBA編寫宏代碼，然后按照上述方法，把宏定義成工具按鈕，操作時(shí)先選中要設(shè)置的區(qū)域，直接單擊該按鈕即可調(diào)用綁定的VBA代碼，實(shí)現(xiàn)一鍵設(shè)置。VBA程序代碼如下所示。

Sub Colorsheet()

Dim i As Long

For i = 1 To Application.Selection.Rows.Count

If i Mod 2 = 1 Then

Selection.Rows(i).Interior.Color = RGB(255, 255, 120)

End If

Next i

End Sub

4 結(jié)論

VBA語言要素非常多，不論是使用VBA代碼創(chuàng)建宏，還是修改錄制宏時(shí)產(chǎn)生的代碼都需要具備一定VBA或VB編程基礎(chǔ)，文中僅通過3個(gè)應(yīng)用實(shí)例說明用VBA編程解決問題的思路。對辦公人員來說，除了掌握Excel自身強(qiáng)大的數(shù)據(jù)處理功能外，適當(dāng)?shù)貙W(xué)習(xí)了解一些VBA編程知識，對提高工作效率，解放自己的雙手是非常有幫助的。

[1] 伍云輝.精通Excel VBA[M]. 北京：電子工業(yè)出版社.2013(5)

[2] 姚文濤.ExcelVBA應(yīng)用開發(fā)經(jīng)典案例[M]. 北京：清華大學(xué)出版社.2009(7)

[3] 陳軍民.Excel VBA在Office中的應(yīng)用案例[J].電腦知識與技術(shù).2013(4)

[4] 王東燕.用Excel的宏和VBA實(shí)現(xiàn)方便快捷的調(diào)查統(tǒng)計(jì)[J].辦公自動(dòng)化.2010（11）

[5] 楊群.ExcelVBA應(yīng)用實(shí)戰(zhàn)技巧[M].北京：清華大學(xué)出版社.2013（4）

Application of Excel VBA in office automation

Li Xiaoxia
(Shaanxi Institute Of Technology,Xi'an,710302)

Excel is a software Office,it deeply to the data input function excellent and powerful data processing andanalysis function of the office personnel's affection.The use of VBA in Excel,can effectively realize the automationof data processing,the data processing work relief staff from simple and repeated in and out,can through the VBA programming for two Excel to develop, realize manyadvanced functions,improve office efficiency.

Excel;VBA;Macro;Office automation

李小遐（1968－），女，副教授，陜西省戶縣，研究方向?yàn)橛?jì)算機(jī)技術(shù)及應(yīng)用。