Excel VBA在辦公自動化中的應用

（陜西國防工業職業技術學院電子信息系，西安,710302）

Excel VBA在辦公自動化中的應用

李小遐

（陜西國防工業職業技術學院電子信息系，西安,710302）

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

Excel;VBA;宏;辦公自動化

1 VBA概述

VBA(Visual Basic for Application)是新一代標準宏語言，由微軟公司開發出來，在其桌面應用程序中執行通用的自動化（OLE）任務的編程語言。所謂應用程序自動化，是指通過編寫程序讓常規應用程序（如Excel、Word等）自動完成工作，例如在Excel里自動設置單元格格式或者多張工作表之間自動進行計算等等。VBA是基于Visual Basic（VB）發展而來的，是VB的一個子集，與VB一樣是屬于面向對象的編程語言，VBA繼承了VB的開發機制，與VB有著相似的語言結構和開發環境。VBA與VB的不同之處在于，VBA是Office辦公軟件內嵌的編程語言，所以VBA代碼必須“寄生”在宿主應用程序中運行，不能生成獨立的應用程序。VBA根據其嵌入軟件的不同，增加了對相應軟件中的對象的控制功能，正是因為如此，VBA最適于定制已有的桌面應用程序。

2 Excel VBA的辦公應用

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

3 宏

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

3.1 錄制宏代碼

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

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

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

3.2 編寫宏代碼

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

實現功能：庫存預期報警。問題描述：Excel工作表中存放著所有商品的庫存信息，A列為名稱，B列為計量單位，C列為目前庫存數量，庫管人員提供某個商品名稱和最小庫存數量時，低于這個數值的庫存數量單元格自動變為紅色、加粗，給出庫存報警提示。

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

Sub 庫存_Click()

Dim Mc As String

Dim x&, y&

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

y = InputBox("請輸入報警數量:")

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中，如果要編寫宏、運行以前錄制的宏或者用VBA創建與Office程序一起使用的應用程序，需要將“開發工具”選項卡顯示出來，與早期的Excel版本有所不同，Excel2010的默認環境中，“開發工具”選項卡是隱藏的。操作方法是點擊“文件-選項-自定義功能區”，選中“開發工具”復選框，確定后即可在Excel窗口中看到“開發工具”選項卡，然后在“控件”組的“插入”按鈕里選擇“按鈕”窗體控件，即可在工作表中添加一個命令按鈕。

3.3 自定義功能區

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

實現功能：隔行格式化工作表。問題描述：Excel工作表中有多行數據，為了便于閱讀和使用，需要把工作表設置為隔行一致的效果，比如隔行添加黃色底紋。這個工作用手工方法也能完成，但是比較枯燥還浪費時間，在此使用VBA編寫宏代碼，然后按照上述方法，把宏定義成工具按鈕，操作時先選中要設置的區域，直接單擊該按鈕即可調用綁定的VBA代碼，實現一鍵設置。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 結論

VBA語言要素非常多，不論是使用VBA代碼創建宏，還是修改錄制宏時產生的代碼都需要具備一定VBA或VB編程基礎，文中僅通過3個應用實例說明用VBA編程解決問題的思路。對辦公人員來說，除了掌握Excel自身強大的數據處理功能外，適當地學習了解一些VBA編程知識，對提高工作效率，解放自己的雙手是非常有幫助的。

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

[2] 姚文濤.ExcelVBA應用開發經典案例[M]. 北京：清華大學出版社.2009(7)

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

[4] 王東燕.用Excel的宏和VBA實現方便快捷的調查統計[J].辦公自動化.2010（11）

[5] 楊群.ExcelVBA應用實戰技巧[M].北京：清華大學出版社.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－），女，副教授，陜西省戶縣，研究方向為計算機技術及應用。