VBA基礎(chǔ)知識(shí)普及常用代碼大全菜單工具欄打開關(guān)閉工作簿函數(shù)調(diào)用圖表修改返回最后一行

1、目 錄前言2正文2第一部分 基本知識(shí)簡(jiǎn)介21激活、選擇對(duì)象22引用單元格2引用單個(gè)單元格2引用區(qū)域34選中/刪除/剪切/插入/隱藏行或列34.1 選中行或列34.2 刪除行或列34.3 剪切行或列44.4 插入行或列44.5 隱藏行或列45復(fù)制并粘貼45.1 復(fù)制并選擇性粘貼45.2 復(fù)制并粘貼56循環(huán)語句56.1 Do While 循環(huán)56.2 Do Until循環(huán)56.3 For Next 循環(huán)57 if Then end if 語句68 With語句69清除單元格數(shù)據(jù)710 InputBox、MsgBox710.1 InputBox函數(shù)710.2 MsgBox函數(shù)811 Sub函數(shù)與Fo

2、untion函數(shù)8函數(shù)結(jié)構(gòu)811.2 Sub函數(shù)與Fountion函數(shù)9函數(shù)舉例912提高VBA代碼運(yùn)行速度的重要代碼9第二部分常用代碼大全101添加自定義菜單代碼102添加自定義工具欄代碼114啟動(dòng)工作簿自動(dòng)運(yùn)行宏13pen 事件13用Auto_Open宏135 VBA調(diào)用函數(shù)的方法146 sub 的遞歸147 userange函數(shù)找到數(shù)據(jù)邊界158 End (xldown) 和End(xlup)語句159修改圖表數(shù)據(jù)系列的引用數(shù)據(jù)1610窗體制作代碼1611打開相應(yīng)路徑的工作表1712以B2：B10為主要關(guān)鍵字排序17前言本文前半部分適用于初學(xué)VBA基礎(chǔ)的人，后半部分的常用代碼在初學(xué)者乍看

3、起來可能有點(diǎn)難度，但是初學(xué)者可以將本文檔中的代碼復(fù)制下來放到Excel VBA窗口中試運(yùn)行，學(xué)習(xí)起來會(huì)事半功倍。本文第一部分簡(jiǎn)要講解了VBA基礎(chǔ)對(duì)象操作的基礎(chǔ)知識(shí)。學(xué)會(huì)了這些，就能編制一些簡(jiǎn)單的含有常用的基本功能的VBA代碼。第二部分是一些非常有用的VBA代碼，涵蓋了添加自定義菜單、添加自定義工具欄、打開關(guān)閉工作簿、函數(shù)調(diào)用、Sub函數(shù)遞歸、尋找并返回?cái)?shù)據(jù)區(qū)域邊界、自定義排序、制作窗體代碼、修改圖表數(shù)據(jù)引用、啟動(dòng)工作簿自動(dòng)運(yùn)行VBA代碼、通過VBA輸入公式等等，涵蓋了大部分常用重要的功能。希望能給大家?guī)韼椭Ｗ髡咚接邢?，耗費(fèi)一晚寫成，文中紕漏在所難所，望讀者批準(zhǔn)指正。正文在進(jìn)行Excel

4、VBA編程的過程中，無時(shí)無刻不在接觸與使用對(duì)象。在Excel VBA中，操作和設(shè)置的主體都是對(duì)象，這是因?yàn)镋xcel應(yīng)用程序是由對(duì)象組成的，大到Excel本身，小到一個(gè)單元格，它們都是對(duì)象。VBA對(duì)象有ApplicationWorkbookWorksheetrange打開VBA編輯窗口： 打開VBA窗口的快捷鍵：alt+F11。也可以依次點(diǎn)擊開發(fā)工具Visual Basic新建模塊在模塊中輸入VBA代碼。也可以在MicroSoft Excel對(duì)象中輸入代碼。第一部分 基本知識(shí)簡(jiǎn)介1激活、選擇對(duì)象Sheets("工作表名").Activate 激活工作表Sheets(&quo

5、t;工作表名").Select 選擇工作表Range(A1).Select 選擇A1單元格2引用單元格如果要引用C4單元格，有以下幾種方式： Workbook("工作表名").Worksheets("工作簿名").Range ("C4")注意可以簡(jiǎn)寫成Sheets("工作簿名").Range ("C4") Sheets.Cells (4, 3)如果要引用C3到C10Range ("C3:C10")同理如果要引用C3到F10Range ("C3:F10&qu

6、ot;)到這里你可能要問了，假如把10換成變量y呢? Range ("C3:F" & y) 那么假如3也換成變量x呢? Range ("C" & x & ":F" & y)如果更進(jìn)一步你可能又要問了，假如C和F都是變量怎么辦，還能引用嗎？如果要實(shí)現(xiàn)這樣一種功能：通過VBA代碼給單元格B10 寫入公式：=COUNTIF($C$2:$F$9,"*立項(xiàng)*")(注釋：這個(gè)公式的意義：在C2:F9中統(tǒng)計(jì)含有”立項(xiàng)”內(nèi)容的單元格個(gè)數(shù))，如果C和F、2和9都是變量怎么辦？請(qǐng)看下文：定義行號(hào)用變量x

7、(x1,x2)表示，列號(hào)用y(y1,y2)表示，那么代碼如下Sheets("工作表名").Cells(10, 2).FormulaR1C1 = "=COUNTIF(R" & x1 & "C" & y1 & ":R" & x2 & "C" & y2 & ",""*立項(xiàng)*"")"4選中/刪除/剪切/插入/隱藏行或列 選中行或列選中第5行的代碼如下 Rows(5).select或

8、者Rows(“5:5”).select選中第5行到第10行的代碼如下 Rows(“5:10”).select選中第C列到第E列的代碼如下 Columns("C:E").SelectEntirecolumn屬性、Entirerow屬性Range("A3").Entirecolumn 表示A列 Range("A3").Entirerow 表示第3行 刪除行或列刪除第2行代碼如下Sub 刪除行 ()Sheets(“工作表名”).Rows(2).DeleteEnd Sub刪除第3列到第5列的代碼如下Sub 刪除列 () Sheets(“工作表

9、名”).Columns("C:E").SelectEnd Sub4.3 剪切行或列Sheets(“工作表名”).Rows(2).Cut 剪切第2行Sheets(“工作表名”). .Columns("C:E").Cut 剪切C列到E列4.4 插入行或列代碼1Sub 在第2行插入1行() Rows(2).SelectEnd Sub代碼2Sub 在第2行插入3行() Rows(“2:4”).SelectEnd Sub代碼3Sub 在C列插入1列() Columns("C:C").SelectEnd Sub代碼4Sub 在C列插入3列() C

10、olumns("C:E").SelectEnd Sub4.5 隱藏行或列Rows("4:" & Q).Select '將第4行到第Q行隱藏Selection.EntireRow.Hidden = True隱藏列代碼讀者可自行嘗試編制取消隱藏行Rows("4:" & Q).Select '取消第4行到第Q行隱藏Selection.EntireRow.Hidden = False取消隱藏列代碼讀者可自行嘗試編制5復(fù)制并粘貼5.1 復(fù)制并選擇性粘貼將工作表的C1：D4復(fù)制后選擇性粘貼到工作表2的D1單元格Sh

11、eets(1).Range("C1:E4").Select 選擇C1：C4 復(fù)制選中的單元格 Sheets(工作表名).Activate 激活名稱為“工作表名”的工作表 Sheets(2).Range("D1").Select 選中工作表2的單元格D1 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False5.2 復(fù)制并粘貼將工作表的C1：D4復(fù)制后選擇性粘貼到工作表2的D1單元格Sheets(1).Ran

12、ge("C1:E4").Select Sheets(工作表名).Activate Sheets(2).Range("D1").Select 選中工作表2的D1單元格剪切并粘貼的代碼讀者請(qǐng)自行嘗試編制。6循環(huán)語句6.1 Do While 循環(huán)Do While 是當(dāng)條件不成立時(shí)停止循環(huán)，換言之就是條件為True時(shí)執(zhí)行循環(huán)，直到條件為False時(shí)停止循環(huán)。（假設(shè)A列數(shù)據(jù)是連續(xù)的，即沒有從C1到最后一個(gè)單元格之間沒有空格）Sub 找到C列最后一個(gè)非空單元格的行數(shù) ()H = 4Do While Not IsEmpty(Sheets(1).Cells(H, 3).

13、Value)H = H + 1Loop6.2 Do Until循環(huán) Do Until循環(huán)與Do Loop 循環(huán)相反，Do Until是當(dāng)條件成立時(shí)停止循環(huán)，換言之就是條件為False時(shí)執(zhí)行循環(huán)，直到條件為True時(shí)停止循環(huán)。這里就不再贅述了。6.3 For Next 循環(huán)給單元格A1到D4分別賦值1,2,3,416Sub 賦值()Dim i As Integer, j As Integer, n As Integern = 1For i = 1 To 4 For j = 1 To 4 Sheets("工作表名").Cells(i, j) = n n = n + 1 Next

14、NextEnd Sub運(yùn)行結(jié)果如下：7 if Then end if 語句在上面的例子中設(shè)定：假如i>3，就跳出代碼，代碼如下Sub 賦值()Dim i As Integer, j As Integer, n As Integern = 1For i = 1 To 4 If i > 3 Then Exit Sub End If For j = 1 To 4 Sheets("工作表名").Cells(i, j) = n n = n + 1 NextNextEnd Sub運(yùn)行結(jié)果如下：8 With語句With 語句在一個(gè)單一對(duì)象或一個(gè)用戶定義類型上執(zhí)行一系列的語句。

15、語法With objectstatementsEnd With舉例如下：Sub 例子()With Sheets("工作表名稱") .Cells(2, 3) = 4 .Range(C3) = 10End Sub從上面的例子可以看出在with結(jié)構(gòu)中，可以將sheets("工作表名稱").Cells(2, 3) = 4和sheets("工作表名稱").Range(C3) = 10簡(jiǎn)寫成.Cells(2, 3) = 4，.Range(C3) = 10，避免了重復(fù)代碼的輸入，可以提高代碼速度。9清除單元格數(shù)據(jù)Sheets(“工作表名”).Ran

16、ge("A3:E6").ClearComments '清除A3：E6單元格批注內(nèi)容Sheets(“工作表名”).Range("A3:E6").ClearContents '清除A3：E6單元格內(nèi)容Sheets(“工作表名”).Range("A3:E6").Clear 清除A3：E6所有內(nèi)容、格式（包括批注）10 InputBox、MsgBox10.1 InputBox函數(shù)產(chǎn)生一個(gè)對(duì)話框，作為輸入數(shù)據(jù)的界面，并返回所輸入的內(nèi)容。舉例如下Sub 輸入()Dim a As Integer a = Val(InputBox(&

17、quot;請(qǐng)輸入數(shù)字a") b = Val(InputBox("請(qǐng)輸入數(shù)字b") Range("A1") = a + bEnd Sub運(yùn)行結(jié)果如下：跳出第一個(gè)輸入框，如下圖所示輸入“3”點(diǎn)擊確定輸入5并點(diǎn)擊確定后得到結(jié)果A1=8，如下圖所示在這里你或許會(huì)問，如果運(yùn)行代碼后，只跳出一個(gè)窗口，在這個(gè)窗口中有兩個(gè)輸入框可以輸入數(shù)據(jù)，分別輸入3,5，然后點(diǎn)擊確定，就得到結(jié)果：給單元格賦值8，那么怎么編制代碼？這是完全可以實(shí)現(xiàn)的，請(qǐng)看本文第二部分第10條：窗體制作代碼。10.2 MsgBox函數(shù)功能是彈出一個(gè)對(duì)話框。舉例如下：Sub 歡迎您()MsgBo

18、x "歡迎您！"End Sub11 Sub函數(shù)與Fountion函數(shù)11.1函數(shù)結(jié)構(gòu)Sub函數(shù)結(jié)構(gòu)Sub 函數(shù)名()End SubFountion函數(shù)結(jié)構(gòu)Fountion函數(shù)名()End Fountion11.2 Sub函數(shù)與Fountion函數(shù)1.function可以返回值，sub則不可以返回值2.sub可以直接執(zhí)行，但function需要調(diào)用才可以執(zhí)行明白了這兩點(diǎn)也就可以解決上面提到的矛盾了1.如果需要過程名（function或sub的名字）能返回值，則必用function2.如果需要直接執(zhí)行（F5或鏈接到按鈕控件），那么就要用sub了11.3Fountion函數(shù)舉例示

19、例1function 自定義函數(shù)，可以像普通公式一樣在單元格中引用。用Function函數(shù)給單元格D1賦予公式=A1+B1-C1Function gs(a, b, c) gs = a + b - c '把公式的計(jì)算過程寫到這里，輸入公式時(shí)只要傳入?yún)?shù)End Function在D1輸入 =gs(A1,B1,C1) 示例2Public Function 程序()Select Case Range("g12") Case Is = "煤炭" MsgBox "本月已有數(shù)據(jù)，請(qǐng)從新選擇！" Case Is ="土地"

20、 MsgBox "正確！"End SelectEnd Function這個(gè)函數(shù)可以再Sub函數(shù)中調(diào)用。12提高VBA代碼運(yùn)行速度的重要代碼關(guān)閉代碼運(yùn)行過程中，屏幕自動(dòng)刷新以提高代碼運(yùn)行速度的代碼在代碼的開頭輸入Application.ScreenUpdating = False '克服緩慢刷屏的毛病在代碼的結(jié)尾輸入Application.ScreenUpdating = True關(guān)閉excel中表格自動(dòng)重新計(jì)算以提高代碼運(yùn)行速度的代碼在代碼的開頭輸入：Application.Calculation = xlManual '關(guān)閉系統(tǒng)對(duì)excel表格中公式的自動(dòng)

21、重新計(jì)算在代碼的結(jié)尾輸入：Application.Calculation = xlAutomatic使用With end with 結(jié)構(gòu)可以省去很多重復(fù)代碼，提高VBA代碼運(yùn)行速度。第二部分常用代碼大全1添加自定義菜單代碼Sub 在菜單中添加菜單項(xiàng)()Dim cbr As CommandBarDim cbp As CommandBarPopupOn Error Resume Next CommandBars("Worksheet menu bar").Controls("數(shù)據(jù)更新").Delete Set cbr = CommandBars("

22、Worksheet Menu Bar")Set cbp = cbr.Controls.Add(msoControlPopup)With cbp .Caption = "數(shù)據(jù)更新" With .Controls.Add(msoControlButton) .Caption = "匯總項(xiàng)目" .OnAction = "improve1" End With With .Controls.Add(msoControlButton) .Caption = "數(shù)據(jù)更新" .OnAction = "impro

23、ve2" End With With .Controls.Add(msoControlButton) .Caption = "更新節(jié)點(diǎn)表" .OnAction = "improve3" End With With .Controls.Add(msoControlButton) .Caption = "更新項(xiàng)目看板" .OnAction = "improve4" End With With .Controls.Add(msoControlButton) .Caption = "上市項(xiàng)目得分&quo

24、t; .OnAction = "improve5" End With With .Controls.Add(msoControlButton) .Caption = "上市項(xiàng)目報(bào)表" .OnAction = "improve6" End WithEnd WithEnd SubSub improve1()End SubSub improve2()End SubSub improve3()End SubSub improve4()End SubSub improve5()End SubSub improve6()End Sub運(yùn)行結(jié)果如下

25、圖所示：2添加自定義工具欄代碼Sub 在菜單中添加個(gè)人工具欄()Dim cbr As CommandBarDim cbn As CommandBarButtonOn Error Resume NextCommandBars("我的工具集").DeleteOn Error GoTo 0Set cbr = CommandBars.Add("我的工具集", msoBarFloating)With cbr Set cbn = .Controls.Add(msoControlButton) With cbn .Caption = "匯總項(xiàng)目"

26、.Style = msoButtonCaption .OnAction = "improve1" End With Set cbn = .Controls.Add(msoControlButton) With cbn .Caption = "圖數(shù)更新" .Style = msoButtonCaption .OnAction = "improve2" End With Set cbn = .Controls.Add(msoControlButton) With cbn .Caption = "節(jié)點(diǎn)表" .Style

27、= msoButtonCaption .OnAction = "improve3" End With Set cbn = .Controls.Add(msoControlButton) With cbn .Caption = "項(xiàng)目看板" .Style = msoButtonCaption .OnAction = "improve4" End With Set cbn = .Controls.Add(msoControlButton) With cbn .Caption = "上市項(xiàng)目得分" .Style = ms

28、oButtonCaption .OnAction = "improve5" End With Set cbn = .Controls.Add(msoControlButton) With cbn .Caption = "上市項(xiàng)目報(bào)表" .Style = msoButtonCaption .OnAction = "improve6" End With cbr.Visible = TrueEnd WithEnd SubSub improve1()End SubSub improve2()End SubSub improve3()End S

29、ubSub improve4()End SubSub improve5()End SubSub improve6()End Sub運(yùn)行結(jié)果如下圖所示：添加自定義工具欄的代碼跟添加自定義菜單的代碼實(shí)際上同出一轍4啟動(dòng)工作簿自動(dòng)運(yùn)行宏如果需要在打開Excel工作簿時(shí)自動(dòng)運(yùn)行某個(gè)宏，可以用下面的兩個(gè)方法： 4.1 Workbook.Open 事件 1.在Excel中按快捷鍵Alt+F11，打開VBA編輯器。“工程”窗口中雙擊“ThisWorkBook”，在右側(cè)的代碼窗口頂部左側(cè)的“對(duì)象”框中，選擇“WorkBook”。這時(shí)，Excel自動(dòng)在代碼窗口中輸入下面的代碼： Private Sub Work

30、book_Open() End Sub 然后在其中輸入需要執(zhí)行的宏代碼，如下例： Private Sub Workbook_Open() MsgBox ("Workbook_Open事件" & Chr(10) & ThisWorkbook.Name & "工作簿已打開！") End Sub 3.保存工作簿并重新打開該工作簿。如上例中將彈出一個(gè)消息框。用Auto_Open宏 1.在Excel中按快捷鍵Alt+F11，打開VBA編輯器?！安迦肽K”，在右側(cè)的代碼窗口中輸入名稱為“Auto_Open”的宏，如下面的代碼： Sub Aut

31、o_Open() MsgBox ("Auto_Open" & Chr(13) & "已打開" & ThisWorkbook.Name & "工作簿！") End Sub 3.保存工作簿并重新打開該工作簿。如上例中將彈出一個(gè)消息框。上述兩種方法可以同時(shí)存在，但在打開工作簿時(shí)按照先“Workbook_Open“后“Auto_Open”的順序執(zhí)行。 5 VBA調(diào)用函數(shù)的方法如果sub函數(shù)名是變量將Sub函數(shù)作為一個(gè)元素裝進(jìn)數(shù)組里例如符合條件1就調(diào)用Sub函數(shù)A，符合條件2就調(diào)用Sub函數(shù)B，那么可以將函數(shù)名賦

32、值給數(shù)組a=array(A,B)Sub 調(diào)用函數(shù)()myarray = Array(A, B)IF 條件1成立 ThenApplication.Run array(0) Array(0)表示數(shù)組的第一個(gè)元素，即Sub函數(shù)A，同理Array(1)表示第二個(gè)元素，即Sub函數(shù)BEnd sub如果直接調(diào)用sub函數(shù)，可以用CallSub 調(diào)用函數(shù)()Call 函數(shù)名End sub注明：Call可以省略6 sub 的遞歸任何一個(gè)過程都可以遞歸，所謂遞歸，就是過程調(diào)用其自身。看上去有點(diǎn)像死循環(huán)，但是在某些情況下也可以使用遞歸來完成任務(wù)。例如要在工作表忠一共創(chuàng)建6個(gè)工作表，如果工作表已經(jīng)包含3個(gè)工作表，呢

33、么再創(chuàng)建3個(gè)就夠了；如果工作簿已經(jīng)包含了2個(gè)工作表，那么還需要再創(chuàng)建4個(gè)，以此類推。此問題如用常規(guī)方法，代碼如下：Sub 常規(guī)方法示例() Dim icount As Integer Worksheets.Add , Worksheets(Worksheets.Count), icountEnd Sub如用遞歸調(diào)用方法，代碼如下：首先判斷工作表總數(shù)是否等于6，如果等于6則退出該過程。否則在最后新建一個(gè)工作表，然后調(diào)用過程自身Sub 過程遞歸示例() If Worksheets.Count = 6 Then Exit Sub Worksheets.Add , Worksheets(Workshe

34、ets.Count) Call 過程遞歸示例End Sub7 userange函數(shù)找到數(shù)據(jù)邊界返回工作表中數(shù)據(jù)區(qū)域的第一行，最后一行，第一列，最后一列。請(qǐng)注意，如果數(shù)據(jù)區(qū)域不是區(qū)域的，例如第一行第三行都有數(shù)據(jù)，而第二行沒有數(shù)據(jù)，那么返回的最后一行將是3。如果第4行沒有內(nèi)容，但是第4行設(shè)置了格式，如涂了黃色或者設(shè)置了粗體字，那么返回的最后一行將是4。代碼如下：Sub userange()Dim firstcol As Integer, lastcol As IntegerDim firstrow As Long, lastrow As LongMsgBox "數(shù)據(jù)區(qū)域最后一行是工作表中

35、第" & lastrow + firstrow - 1 & "行,數(shù)據(jù)區(qū)域最后一列是工作表中第" & lastcol + fistcol - 1 & "列。"End Sub運(yùn)行結(jié)果如下圖所示8 End (xldown) 和End(xlup)語句假如單元格A1包含數(shù)據(jù)，下面的代碼引用A列中從單元格A1開始一直向下的最后一個(gè)包含數(shù)據(jù)的單元格。如果A1，A2有數(shù)據(jù)，而A3是空的，以下代碼將引用A2。Range(“A1”).End (xldown)相反的以下代碼是從工作表最后一行向上，引用第1列中最后一個(gè)有數(shù)據(jù)的單元格C

36、ells(Rows.Count,1) .End(xlup) 括號(hào)里的1表示第1列到此，你就會(huì)明白，同上文第6條一樣，借用以上方法也能找到最后一行的行數(shù)，只不過有空單元格時(shí)結(jié)果會(huì)不一樣9修改圖表數(shù)據(jù)系列的引用數(shù)據(jù)Sheets(j).ChartObjects("Chart 6").Activate '這段代碼修改快速通道項(xiàng)目狀態(tài)各系列值的引用區(qū)域 ActiveChart.SeriesCollection(1).Name = "=" & sheet(1) & "!$AU$" & 1 設(shè)置數(shù)據(jù)系列1的名稱為AU

37、1單元格的內(nèi)容 ActiveChart.SeriesCollection(1).Values = "=" & sheet1 & "!$AU$" & 2 & ":$AU$" & 10 設(shè)置數(shù)據(jù)系列1的引用數(shù)據(jù)位AU2：AU10，以下代碼意思請(qǐng)類推。 ActiveChart.SeriesCollection(2).Name = "=" & sheet1 & "!$AW$" & 1 ActiveChart.SeriesCollection

38、(2).Values = "=" & sheet1 & "!$AW$" &2 & ":$AW$" & 10 ActiveChart.SeriesCollection(3).Name = "=" & sheet1 & "!$AY$" & 1 ActiveChart.SeriesCollection(3).Values = "=" & sheet1 & "!$AY$" &

39、2 & ":$AY$" & 10 For bii = 1 To 3 ActiveChart.SeriesCollection(bii).XValues = "=" & sheet1 & "!$AP$" & 1 & ":$AP$" & 10 Next ActiveChart.SeriesCollection(4).Values = "=" & sheet1 & "!$BA$" & 2 & &

40、quot;:$BA$" & 10 ActiveChart.SeriesCollection(4).XValues = "=" & sheet1 & "!$AZ$" & 2 & ":$AZ$" & 10 ActiveChart.Axes(xlValue, xlSecondary).MaximumScale = Sheets(sheet1).Cells(1, 37) - Sheets(sheet1).Cells(1, 31) '更新項(xiàng)目狀態(tài)圖的副縱坐標(biāo)軸的最大值為Cells(1, 37)ActiveChart.Axes(xlValu