|
Excel作為使用最廣泛的電子表格軟件，它以操作簡便，功能豐富和效率高而著稱，非常適合對計算機熟悉程度不高的人員使用。但是隨著學習的深入，工作上的需求，提高工作效率，用戶期望Excel提供更加自動化的操作，這就需要借助Excel VBA。 VBA的優點： 1. 解決重復的操作。（大大提高了工作效率） 2. 在Excel的現有基礎上再增添一些更適合自己的新功能 3. 可以自定義函數（來簡化冗長的公式和計算,方便初,中級用戶） 4. 設置個性化的操作界面（更適合自己工作的菜單，快捷菜單） 5. 二次開發 本教材包括的內容，前面第一講到第七講是VBA基礎內容；第八講到第十五講是提高篇和實例講解篇 第一講VBA與宏及VBE窗口的介紹 一．VBA的定義： 內置在Excel里一種最簡單的編程語言。打個比方我們去英國，那我們要會英文，才能和當地的人進行溝通，我們要用和Excel進行人機對話，那我們要會VBA，也就是說VBA是Excel能識別的編程語言，也是編程員能識別的一種最簡單的編程語言。 二．什么是宏： 宏是一連串動作的集合，動作是由VBA代碼組成的，打個比方說，我們家里以前的洗衣機是有加水，加洗衣粉，轉動，放水，甩干，現在我們把其它打包成一個命令按鈕，這樣我們只要按一下，衣服就洗好了，以前要用按五下，這樣就大提高了工作效率，實現辦公自動化。所以接觸過Excel的朋友都會通過錄制宏來完成一些重復的工作。 三．VBE窗口的介紹： 1. 進入VBE窗口的方法： 因為VBE窗口是VBA代碼編輯窗口，我們所有的代碼都在VBE窗口里編寫。 第一種方法：Alt+F11，第二種方法：通過命令按鈕進行，（如圖1）(備注我們常用工具欄上沒有這個，要從自定義里拖出來才有)，進入VBE編輯的方法不只這二種 2.VBE窗口的介紹： A．視圖菜單下：工程資源管理器；屬性窗口；立即窗口。 B．插入菜單下：插入模塊；插入過程；插入用戶窗體。 C．調試菜單下：逐語句調試（快捷鍵F8）。切換斷點（快捷鍵F9） D．運行菜單下：運行子過程（快捷鍵F5） F．工具菜單下：選項c編輯器格式選項卡c字體大小為11磅 3．代碼的縮進： A．Tab鍵右縮進 B．Shift+Tab鍵，還原右縮進一步 4．模塊： A．普通模塊，對象模塊（工作表模塊，工作薄模塊），窗體模塊，類模塊。 第二講錄制宏 修改宏 保存宏 指定宏 自定義按鈕 加載宏 1. 錄制行高的宏 方法: 選擇第3行ú選擇工具菜單ú宏ú錄制新宏，跳出一個對話框，確定后，選擇第3行,單擊鼠標右鍵，行高ú設置為30ú確定，然后選擇工具ú宏ú停止錄制 ALT+F11,打開VBE編輯窗口,左鍵雙擊模塊，這就是剛才錄制的宏代碼， 1# Sub Macro1() 2# Selection.RowHeight = 30 3 # End Sub 代碼解析 第一行表示子過程開始;第二行Selection是選擇的意思, RowHeigh行高,選擇的行高設置為30;第三行是結束的意思 繼續，在畫圖工具中選擇橢圓，再左鍵點橢圓,右鍵，點擊指定宏，做到這步以后，選擇第6行,然后點擊橢圓，看看效果，這個就是錄制宏到使用宏 2. 錄制選擇性粘貼的宏 方法:打開工作薄”選擇性粘貼的宏(未做的)”備注這個工作薄在課件里 ú選擇工作表1ú選擇工具菜單ú宏ú錄制宏，跳出一個對話框，絕對引用ú確定后ú選擇區域B3:C7ú單擊鼠標的右鍵ú復制ú選擇區域H3:I7ú選擇性粘貼ú加ú確定ú雙擊單元格E3, ú單擊單元格E4ú停止錄制 ALT+F11,打開VBE編輯窗口,這就是剛才錄制的宏代碼， 1# Sub Macro1()' 2# ' Macro1 Macro '3# 宏由 999寶藏網 錄制，時間: 2011-2-7 4# Range('B3:C7').Select 5# Selection.Copy 6# Range('H3:I7').Select 7# Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd, SkipBlanks:= _ False, Transpose:=False 8# End Sub 代碼解析:第四行代碼單元B3:C7選中,第五行代碼復制B3:C7,第六行代碼單元格H3:I7選中,第7行代碼選擇性粘貼,加 然后把宏指定給一個橢圓,試試 3. 錄制一個清除數據的宏 方法: 選擇工具菜單ú宏ú錄制宏，跳出一個對話框，絕對引用ú選擇區域B3:C7,按Delete鍵然ú停止錄制 ALT+F11,打開VBE編輯窗口,這就是剛才錄制的宏代碼， 1# Sub Macro2()' Macro2 Macro ' 宏由 雨林木風 錄制，時間: 2010-11-24 2# Range('B3:C7').Select 3# Selection.ClearContents 4# End Sub 代碼解釋: 第二行單元格B3:C7選中;第三行清除選中的單元格里的數字 然后把宏指定給一個橢圓,試試 4. 修改宏: 修改第2個宏 1# Sub 匯總() 2# Range('B3:C7').Copy 3# Range('H3:I7').PasteSpecial Paste:=xlPasteAll, Operation:=xlAdd 4# Application.CutCopyMode = 0 5# End Sub 代碼解釋第二行復制單元格B3:C7;第三行把復制的數據選擇性粘貼到單元格H3:I7, Operation:=xlAdd的意思是選擇加;第四行代碼取消復制模式,也就是那個虛線框 5. 保存宏 一種是保存在當前工作薄,一種是保存在個人宏工作薄,另一種保存新工作薄 6. 自定義按鈕 視圖菜單ú工具欄ú自定義ú命令選項卡ú類別ú宏ú按住左鍵不放,把自定義按鈕那個笑臉拖到常用工具欄上ú把光標放在那個笑臉上(前提條件是不要關閉自定義工具欄ú單擊鼠標右鍵ú命名ú更改按鈕圖像ú指定宏(指定你要實現的此功能 7. 指定宏: 可以把宏指定給圖片,自選圖形。 第三講 VBA的語法基礎 1.數據類型: 學生的姓名,學生的年齡,學生的出生日期(常用數據類型有:Byte(字節型); Integer (整型); Long(長整型); Single(單精度浮點型,7位有效數字); Double (雙精度浮點型,有效數字15位); String(字符串型);對象型(例如單元格區域和工作表); Variant(變體型,也叫萬能的類型) 2.變量:變量可以使程序變得更加富有活力 (1).變量名稱要注意幾點: A.不能把關鍵字做變量名 B.部分符號不能在變量中出現,如(#*%!) C.不區分大小寫,但我們一般大小寫混寫，第一個字母大寫，其它的小寫。 D.也可以使用中文名稱，但是盡量不用中文名稱，如果在英文版會報錯。 (2)變量聲明(電腦里劃出一個儲存區域來儲存變量的值,變量的聲明就決定了變量的大小,而大小就是我們前面講的數據類型決定的) (3)變量的聲明方式 A.語法格式: Dim 變量 as 數據類型.如Dim i as Integer B.也可以一行定義多個變量,之間用逗號分開 如 Dim i As Integer, F As String, D As Long (4).強制聲明:變量使用前聲明是一個好習慣,由于VBA里可以不聲明變量,因此用戶會忘記這一點,所以我們要求變量進行強制聲明(工具菜單==選項==編輯器==要求聲明變量) (5)變量的作用域: A.過程級變量 B.模塊級變量 c.全局級變量 3.運算符和表達式: (1).算術運算符:有+,-,*,/,& (2).比較運算符:=,<>,>=,<=,Like (3).邏輯運算符:And,Or,Not (4)表達式:右邊的值賦給運算符左邊的 第四講Range對象 Range對象是Excel應用程序中最常用的對象，一個Range對象代表一個單元格、一行、一列、包含一個或者更多單元格區域（可以是連續的單元格，也可以是不連續的單元格）中選定的單元格，在操作Excel 內的任何區域之前都需要將其表示為一個Range對象，然后使用該Range對象的方法和屬性。 1.單元格的引用方法及應用 在VBA中經常需要引用單元格或單元格區域，主要有以下幾種方法。 1) 使用Range屬性 VBA中可以使用Range屬性返回單元格或單元格區域，如下面的代碼所示。 1# Sub 選擇1() 2# Sheet1.Range('A1:D9, D1:D9').Select 3# End Sub 代碼解析:第二行單元格A1:D9和單元格D1:D9同時選中 “選擇1”過程使用Select方法選中A1:A9，D1:D9單元格區域。 Range屬性返回一個Range對象，該對象代表一個單元格或單元格區域，語法如下： Range(Cell1, Cell2) 參數Cell1是必需的，必須為 A1 樣式引用的宏語言，可包括區域操作符（冒號）、相交區域操作符（空格）或合并區域操作符（逗號）。也可包括美元符號（即絕對地址，如“$A$1”）。 2) 使用Cells屬性 使用Cells屬性返回一個Range對象，如下面的代碼所示 Sub 輸數字1() Dim I As Integer For I = 1 To 10 Sheet1.Cells(I, 1) = I Next I End Sub 代碼解析： “ 輸數字1”過程使用For...Next語句為工作表中的A1:A10單元格區域填入序號1到10。 Cells屬性指定單元格區域中的單元格，語法如下： Cells(RowIndex, ColumnIndex) 參數RowIndex是可選的，表示引用區域中的行序號。 參數ColumnIndex是可選的，表示引用區域中的列序號。 如果缺省參數，Cells屬性返回引用對象的所有單元格。 Cells屬性的參數可以使用變量，因此經常應用于在單元格區域中循環 3) 使用快捷記號 在VBA中可以將A1引用樣式或命名區域名稱使用方括號括起來，作為Range屬性的快捷方式，這樣就不必鍵入單詞“Range”或使用引號，如下面的代碼所示。 Sub 輸字2() [A1:A10] = '佛山小老鼠' End Sub 代碼解析： “輸字2”過程使用快捷記號為單元格區域賦值。 第2行代碼使用快捷記號將活動工作表中的A1：A10單元格賦值為”佛山小老鼠” 注意 使用快捷記號引用單元格區域時只能使用固定字符串而不能使用變量。 4) 使用Offset屬性 可以使用Range對象的Offset屬性返回一個基于引用的Range對象的單元格區域，如下面的代碼所示。 Sub 輸數字3() Sheet1.Range('A1:A10').Offset(1, 1) = 2 End Sub 代碼解析： “輸數字3”過程使用Range對象的Offset屬性選中A1：A10單元格偏移一行一列后的區域B2:B11,且在B2:B11輸入了數字2 應用于Range對象的Offset 屬性的語法如下： expression.Offset(RowOffset, ColumnOffset) 參數expression是必需的，該表達式返回一個Range對象。 參數RowOffset是可選的，區域偏移的行數（正值、負值或 0（零））。正值表示向下偏移，負值表示向上偏移，默認值為 0。 參數ColumnOffset是可選的，區域偏移的列數（正值、負值或 0（零））。正值表示向右偏移，負值表示向左偏移，默認值為 0。 5) 使用Resize屬性 使用Range對象的Resize屬性調整指定區域的大小，并給單元格區域賦值，如下面的代碼所示。 Sub 輸數字4() Sheet1.Range('A1').Resize(3, 3) = “佛山小老鼠” End Sub 6) 使用Union方法 使用Union方法可以將多個非連續區域連接起來成為一個區域，從而可以實現對多個非連續區域一起進行操作，如下面的代碼所示。 Sub 輸數5() Union(Sheet1.Range('A1:A9'), Sheet1.Range('D1:D9,G1:G9')) = '佛山小老鼠' End Sub 也可以用代碼 Sub AA() Range('A1:D9,D1:D9,G1:G9') = '佛山小老鼠' End Sub, 不過要記得中間是用逗號分開,只能是一對雙引號 代碼解析： “輸數字4”過程給單元格區域A1:A9和D1:D9,G1:G9所組成的區域賦值為”佛山小老鼠。Union方法返回兩個或多個區域的合并區域，語法如下： expression.Union(Arg1, Arg2, ...) 其中參數expression是可選的，返回一個Application對象。 參數Arg1, Arg2, ...是必需的，至少指定兩個Range對象。 7) 使用UsedRange屬性 使用UsedRange屬性返回指定工作表上已使用單元格組成的區域，如下面的代碼所示。 Sub 選擇2() Sheet1.UsedRange.Select End Sub 代碼解析： “選擇2”過程使用UsedRange屬性選擇工作表上已使用單元格組成的區域，包括空單元格。 8） Cells(數字)，一個單元格有一個數字代表，因為一行有256個單元格，257就是第二行的第一個單元格如 Sub AA() Cells(257) = '小老鼠' End Sub 第五講 VBA語句(一) 一.With End With可以對代碼進行精減 精簡前的如下 1# Sub Macro1() 2# ' Macro1 Macro 3# ' 宏由 999寶藏網 錄制，時間: 2011-2-8 4# Selection.Font.Bold = True 5# With Selection.Font 6# .Name = '宋體' 7# .Size = 18 8# .Strikethrough = False 9# .Superscript = False 10# .Subscript = False 11# .OutlineFont = False 12# .Shadow = False 13# .Underline = xlUnderlineStyleNone 14# .ColorIndex = xlAutomatic 15# End With 16# Selection.Font.ColorIndex = 3 17# End Sub 代碼解析:第4行代碼選中字體加粗;第五行到15中間用了一個With End With語句;第6行字體為宋體;第7行字體大小為18磅,Size是大小的意思;第8行Strikethrough是刪除線的意思;第9行代碼Superscript是上標的意思;第10行代碼Subscript是下標的意思; 第11行OutlineFont字體空心的意思;第12行代碼Shadow是陰影的意思;第13行代碼是下劃線類型為無;14行字體的顏色為自動 精簡后的如下 1# Sub Macro1() 2# With Selection.Font 3# .Name = '宋體' 4# .Size = 18 5# .Bold = True 6# .ColorIndex = 3 7# End With 8# End Sub 代碼解析:第2行選中的單元格字體進行設置;第3行代碼字體為宋體;第4行代碼字體大小為18磅;第5行代碼字體加粗;第6行代碼字體顏色為紅色 二.If Then 判斷語句(單行結構) 1# Sub 按鈕1_單擊() 2# If Range('a1').Value >= 60 Then 3# Range('b1') = '及格' 4# End If 5# End Sub 代碼解析:第2行代碼判斷,如果A1單元格的值大于等于60,那么;第3代碼在單元格B1輸入及格 三. If Then Else(簡單的多行分支結構) 1# Sub 按鈕1_單擊() 2# If Range('A1').Value < 60 Then 3# Range('B1').Value = '不及格' 4# Else 5# Range('B1').Value = '及格' 6# End If 7# End Sub 代碼解析: 第2行代碼判斷,如果A1單元格的值小于60,那么;第3行代碼給單元格B1賦值為”不及格”;第4行代碼是否則;第5行代碼給單元格B1賦值為”及格” 四. If Then ElseIf Then (ElseIf Then … )Else復雜的多行分支結構 1# Sub 加循環的復雜的多行分支結構() 2# Dim I As Integer 3# For I = 2 To 11 4# If Range('A' & I) < 60 Then 5# Range('B' & I) = '不及格' 6# ElseIf Range('A' & I) < 70 Then 7# Range('B' & I) = '及格' 8# ElseIf Range('A' & I) < 80 Then 9# Range('B' & I) = '良好' 10# Else 11# Range('B' & I) = '優秀' 12# End If 13# Next I 14# End Sub 代碼解析: 第2行定義變量I為整型;第3行代碼到13行代碼用了一個循環語句For Next;第4行代碼判斷A列從A2開始到A11的值是否小于60;第5代碼如果第4代碼小于60,那么對應的B列從B2開始到B11單元格顯示不及格,同理第6行,第7行,第8行,第9行代碼。 五.Inputbox 和Msgbox函數 1# Sub 按鈕1_單擊() 2# On Error Resume Next 3# Application.DisplayAlerts = 0 4# Sheet1.Delete 5# Application.DisplayAlerts = 1 6# ANS = MsgBox('只剩下最后一張工作表了，不能刪除', 48, '佛山小老鼠提醒你') 7# End Sub 代碼解析: 第2行過程中有錯語就忽掉；第3行阻止彈出保存提示對話框；第4行代碼工作表1刪除；第5行代碼還原第3行代碼，這兩個是一對一對的用；第6行代碼用輸出函數MsgBox彈出提示 1# Sub 按鈕1_單擊() 2# S = InputBox('請輸入姓名', '輸入提醒') 3# Range('A1') = S 4# End Sub 代碼解析: :第2行用輸入函數InPutbox提醒輸入姓名且把其輸入的值賦給S；第3行代碼把S賦給單元格A1 第六講 VBA語句(二) 一.For Next語句 1# Sub 按鈕1_單擊() 2# Dim I As Byte, C As Integer 3# For I = 1 To 100 4# C = C + I 5# Next I 6# MsgBox C 7# End Sub 代碼解析: :第2行定義變量I為字節型，C為整型；第3行代碼到第5行用了一個循環語句For Next ，給I賦值，賦值范圍從1到100；第4行代碼累加；第6行代碼用輸出函數MsgBox顯示最后累加的C 二 For Each Next語句 1# Sub 按鈕1_單擊() 2# Dim RG As Range 3# For Each RG In Range('A1:A10,D1:D10') 4# C = C + RG 5# Next RG 6# MsgBox C 7# End Sub 代碼解析: 第2行代碼定義變量RG為對象型，單元格對象型；第3代碼到第5行代碼用了一個循環語然For Each Next語句，以RG在單元格區域A1:A10,D1:D10中循環一次；第4行代碼，把單元格RG的值累加；第6行代碼用輸出函數MsgBox顯示最后累加的C 三.Select Case 語句 1# Sub aa() 2# Dim I As Integer 3# For I = 2 To 7 4# m = Sheet1.Range('a' & I) 5# Select Case m 6# Case Is >= 80 7# Sheet1.Range('b' & I) = '優秀' 8# Case Is >= 70 9# Sheet1.Range('b' & I) = '良好' 10# Case Is >= 60 11# Sheet1.Range('b' & I) = '及格' 12# Case Else 13# Sheet1.Range('b' & I) = '不及格' 14# End Select 15# Next I 16# End Sub 代碼解析: 第2行定義變量I為整型；從第3行到15行用了一個循環語句For Next，給I賦值從2到7；第4行代碼把工作表1從A2開始到A7的值賦給m；從第5行代碼到14行代碼用了Select Case End Select語句 第七講錯誤處理和代碼調試 一 錯誤類型(語法錯誤,編譯錯誤,運行錯誤,邏輯錯誤) A.語法錯誤(把關鍵字寫錯,把Dim寫成Dam) B.編譯錯誤(If后面沒有Then就換行) C.運行錯誤(比方說0不能作為除數,運行是就會報錯) D.邏輯錯誤(運行的結果和我們預期的發生了偏差 1# Sub 除數為0() 2# A = Range('A1') 3# B = Range('B1') 4# C = A / B 5# MsgBox C 6# End Sub 代碼解析： 第2行代碼把單元格A1的值賦給A；第3行把單元格B1的值賦給B；第4行代碼把A除以B的值賦給C；第5行代碼用輸出函數MsgBox顯示C 1# Sub 除數為0的解決() 2# A = Range('A2') 3# B = Range('B2') 4# If B = 0 Then 5# Exit Sub 6# Else 7# C = A / B 8# End If 9# MsgBox C 10# End Sub 代碼解析： 第2行代碼把單元格A1的值賦給A；第3行把單元格B1的值賦給B；第4行代碼用了一個判斷語句IF。如果單元格B2的值為0，那么；第5行代碼退出程序。 1# Sub 除數為字符串的() 2# A = Range('A3') 3# B = Range('B3') 4# If B = 0 Then 5# Exit Sub 6# Else 7# C = A / B 8# End If 9# MsgBox C 10# End Sub 代碼解析： 同上 1# Sub 除數為字符串解決的的() 2# A = Range('A4') 3# B = Range('B4') 4# If B = 0 Or IsNumeric(A) = False Or IsNumeric(B) = False Then 5# Exit Sub 6# Else 7# C = A / B 8# End If 9# MsgBox C 10# End Sub 代碼解析：第4行代碼如果B4單元格的數據不是數字或A4單元格的數據不是數字，那么；第5行代碼程序就退出 第八講工作表和工作薄對象 一． 對象 對象是用來表述事物的一個抽象概念。任何事物都可以看做是由對象構成的，Excel也不例外，它是由各種大大小小的對象構成。這些對象有機的組合構成了一個有序的層次結構。在生活中，我們接觸的任何實體都可以看做是一個對象。比方說一個氣球，它就是一個對象，氣球的大小，顏色，這就是氣球的屬性，氣球的飛行就是氣球的方法，氣球在充氣或者放氣的的時候就會產生一些事情，描述這些事情的過程就是事件。 二．對象的屬性： 工作表改名： 1# Sub 按鈕2_單擊() 2# Sheet1.Name = '曹老師' 3# End Sub 代碼解析：第2行代碼改名工作表1的名字為“曹老師” 三．對象的方法 工作薄的添加 1# Sub 按鈕1_單擊() 2# Workbooks.Add 3# End Sub 代碼解析：第2行代碼添加工作薄 工作表的添加： 在第一個工作表前面添加 1# Sub 按鈕1_單擊() 2# Worksheets.Add BEFORE:=Worksheets(1) 3# Sheet1.Select 4# End Sub 代碼解析：第2行代碼在第一個工作前面添加一個工作表 在最后一個工作表添加 1# Sub 按鈕1_單擊() 2# Worksheets.Add after:=Worksheets(Sheets.Count) 3# Sheet1.Select 4# End Sub 代碼解析：第2行代碼在最后一個工作前面添加一個工作表，Sheets.Count是總的工作表數 工作薄加密 1# Sub 按鈕1_單擊() 2# ThisWorkbook.Password = '197698' 3# End Sub 代碼解析：第2行代碼給當前工作薄加密，密碼為197698 打開加密的工作薄 1# Sub 按鈕1_單擊() 2# Workbooks.Open Filename:=ThisWorkbook.Path & '\加密工作薄.XLS', Password:='197698' 3# End Sub 代碼解析：第2行代碼給打開加密過的工作薄，工作薄路徑是和當前工作薄的路徑一樣的，密碼為197698 工作表加密： 1# Sub 加密工作表() 2# Sheet1.Protect Password:='197698' 3# End Sub 代碼解析：第2行代碼把工作表1加密,密碼為197698, Protect是保護的意思, Password是密碼的意思 工作表解密 1# Sub 工作表解密() 2# Sheet1.Unprotect 3# End Sub 代碼解析：第2行代碼打開解密對話框, UnProtect是解密的意思 第九講目錄的制作 首先錄制一個宏,錄制一個超鏈接的宏,得到如下代碼: Sub Macro2() ' ' Macro2 Macro ' 宏由 雨林木風 錄制，時間: 2011-5-12 ' ' ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:='', SubAddress:= _ 'Sheet3!A1', TextToDisplay:='A' End Sub 通過錄制宏,我們得到插入超鏈接的代碼 1# Sub 提取工作表名() 2# Dim I As Integer 3# For I = 1 To Sheets.Count 4# Worksheets(1).Range('A' & I) = Sheets(I).Name 5# Worksheets(1).Hyperlinks.Add Anchor:=Worksheets(1).Range('A' & I), _ 6# Address:='', SubAddress:=Worksheets(I).Name & '!A1' 7# Next I 8# End Sub 代碼解析第2行代碼:定義變量I為整型;第3行到第7代碼用了一個循環語句For Next ;第4行代碼提取工作表的名稱放置在工作表1的A1單元格起;第5行代碼在工作表1從A1單元格起建立超鏈接,鏈接到相應的工作表的A1單元格 Sub 清除() Range('A1:A13') = '' End Sub 代碼解析第2行代碼清除單元格A1:A13的數據 Sub 返回目錄() Worksheets(1).Select End Sub 代碼解析第2行代碼選中工作表1. 這樣就達到了返回目錄的效果了 第十講工作表和工作薄事件 OnTime和OnKey事件 1. 工作表事件： A．在D3：D19里輸入1自動顯示“優秀”，輸入2自動顯示“良好”，輸入3自動顯示“及格”，輸入4自動顯示不及格 1# Private Sub Worksheet_Change(ByVal Target As Range) 2# m = Target.Column 3# n = Target.Row 4# If m = 4 And n >= 3 And n <= 19 And IsNumeric(Target) Then 5# If Target = 1 Then 6# Target = '優秀' 7# ElseIf Target = 2 Then 8# Target = '良好' 9# ElseIf Target = 3 Then 10# Target = '及格' 11# ElseIf Target = 4 Then 12# Target = '不及格' 13# Else 14# arget = '' 15# End If 16# End If 17# End Sub B．方便對數： 1# Private Sub Worksheet_SelectionChange(ByVal Target As Range) 2# M = Target.Row 3# Range('A:F').Interior.ColorIndex = xlNone 4# If Target.Column < 6 Then 5# Range(Cells(M, 1), Cells(M, 6)).Interior.ColorIndex = 50 6# End If 7# End Sub 2.. 工作薄事件和OnTime事件 打開工作薄后15秒自動關閉工作薄 1# Private Sub Workbook_Open() 2# Application.OnTime Now() + TimeValue('00:00:15'), '關閉工作薄' 3# End Sub 1# Sub 關閉工作薄() 2# Application.DisplayAlerts = 0 3# ThisWorkbook.Close 4# Application.DisplayAlerts = 1 5# End Sub 6# 2. Onkey事件 禁用快捷鍵復制Ctrl+c和右鍵菜單上的復制按鈕不能用（備注：Alt用%表示，Ctrl用^表示，Shift用+表示） 1# Sub 禁用() 2# Application.CommandBars('cell').Controls(3).Enabled = 0 3# Application.OnKey '^{c}', '' 4# End Sub 1# Sub 恢復() 2# Application.CommandBars('cell').Controls(3).Enabled = 1 3# Application.OnKey '^{c}' 4# End Sub 第十一講自定義函數 在我們工作當中，有時EXCEL現有的工作表函數很難解決問題，要不解決起來很復雜，這果我們可以用VBA來自定義函數（備注：大家盡量用英文名） Public Function 提數字(AA) Dim I As Integer For I = 1 To Len(AA) If IsNumeric(Mid(AA, I, 1)) = True Then 提數字 = 提數字 & Mid(AA, I, 1) End If Next I End Function Public Function 提字符串(AA) Dim I As Integer For I = 1 To Len(AA) If IsNumeric(Mid(AA, I, 1)) = False Then 提字符串 = 提字符串 & Mid(AA, I, 1) End If Next I End Function Public Function 提字母(AA) Dim I As Integer For I = 1 To Len(AA) If Asc(Mid(AA, I, 1)) >= 64 And Asc(Mid(AA, I, 1)) <= 122 Then 提字母 = 提字母 & Mid(AA, I, 1) End If Next I End Function Public Function 字母(AA) Dim I As Integer For I = 1 To Len(AA) If Mid(AA, I, 1) Like '[A-Z]' Or Mid(AA, I, 1) Like '[a-z]' Then 字母 = 字母 & Mid(AA, I, 1) End If Next I End Function Public Function 字母和數字(AA) Dim I As Integer For I = 1 To Len(AA) If Mid(AA, I, 1) Like '[A-Z]' Or Mid(AA, I, 1) Like '[a-z]' Or Mid(AA, I, 1) Like '[0-9]' Then 字母和數字 = 字母和數字 & Mid(AA, I, 1) End If Next I End Function Public Function 漢字(AA) Dim I As Integer For I = 1 To Len(AA) If Not (Mid(AA, I, 1) Like '[A-Z]' Or Mid(AA, I, 1) Like '[a-z]' Or Mid(AA, I, 1) Like '[0-9]' Or Mid(AA, I, 1) Like '[[-`]') Then 漢字 = 漢字 & Mid(AA, I, 1) End If Next I End Function Function 反向文本(文本 As Range) 反向文本 = StrReverse(文本) End Function Public Function 反(x) Dim i As Integer For i = Len(x) To 1 Step -1 反 = 反 & Mid(x, i, 1) Next i EndFunction Public Function 佛山小老鼠算成績(請用戶輸入分數) 佛山小老鼠算成績 = IIf(請用戶輸入分數 < 60, '不及格', IIf(請用戶輸入分數 < 70, '及格', IIf(請用戶輸入分數 < 80, '良好', '優秀'))) End Function 第十二講添加快捷菜單 在我們工作當中為了方便自己，提高工作效率，把我們經常用到的功能代碼放在快捷菜單，指定給按鈕圖標。 1# Sub 添加快捷菜單() 2# Dim CD As CommandBarButton 3# On Error Resume Next 4# Application.CommandBars('CELL').Controls('簽名').Delete 5# Set CD = Application.CommandBars('CELL').Controls.Add(Type:=msoControlButton, BEFORE:=1) 6# With CD 7# .Caption = '簽名' 8# .FaceId = 31 9# .OnAction = '簽名' 10# End With 11# End Sub 1# Sub 簽名() 2# Selection.Value = '佛山小老鼠' 3# End Sub 1# Private Sub Workbook_BeforeClose(Cancel As Boolean) 2# On Error Resume Next 3# Application.CommandBars('CELL').Controls('簽名').Delete 4# End Sub 1# Private Sub Workbook_Open() 2# Call 添加快捷菜單 3# End Sub 運行后的效果圖如下圖 第十三講添加菜單 如果我們要做出適合自己的操作界面，這時我們要會添加菜單，這樣把我們的一些命令按扭放在這個菜單上。方便自己操作。 1# Sub 添加菜單() 2# Dim CD As CommandBarPopup 3# Dim CDX1 As CommandBarButton, CDX2 As CommandBarButton 4# On Error Resume Next 5# Application.CommandBars(1).Controls('曹老師').Delete 6# Set CD = Application.CommandBars(1).Controls.Add(Type:=msoControlPopup, BEFORE:=1) 7# CD.Caption = '曹老師' 8# Set CDX1 = CD.Controls.Add(Type:=msoControlButton, BEFORE:=1) 9# With CDX1 10# .Caption = '簽名' 11# .FaceId = 31 12# .OnAction = '簽名' 13# End With 14# Set CDX2 = CD.Controls.Add(Type:=msoControlButton, BEFORE:=2) 15# With CDX2 16# .Caption = '當天日期' 17# .FaceId = 33 18# .OnAction = '當天日期' 19# End With 20# End Sub 1# Sub 當天日期() 2# Selection = Format(Now(), 'YYYY-MM-DD') 3# End Sub 1# Sub 簽名() 2# Selection = '佛山小老鼠' 3# End Sub 1# Private Sub Workbook_BeforeClose(Cancel As Boolean) 2# On Error Resume Next 3# Application.CommandBars(1).Controls('曹老師').Delete 4# End Sub 1# Private Sub Workbook_Open() 2# Call 添加菜單 3# End Sub 添加后的效果如下： 第十四講窗體與控件和音樂播放器的制作 一 歡迎界面的制作： 1# Private Sub CommandButton1_Click() 2# If TextBox1 <> '' Then 3# Label2.Caption = TextBox1.Text & '慧能VBA培訓班歡迎你!' 4# Else 5# End 6# End If 7# End Sub 1# Private Sub CommandButton2_Click() 2# End 3# End Sub 1# Sub 按鈕1_單擊() 2# 歡迎界面.Show 1 3# End Sub 做好的效果： 二 窗體的應用 三 音樂播放器的制作 1# Private Declare Function GetWindowLong Lib 'user32' Alias 'GetWindowLongA' (ByVal hwnd As Long, ByVal nIndex As Long) As Long 2# Private Declare Function SetWindowLong Lib 'user32' Alias 'SetWindowLongA' (ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long 3# Private Declare Function FindWindow Lib 'user32' Alias 'FindWindowA' (ByVal lpClassName As String, ByVal lpWindowName As String) As Long 4# Private Const WS_MAXIMIZEBOX = &H10000 5# Private Const WS_MINIMIZEBOX = &H20000 6# Private Const GWL_STYLE = (-16) 7# Private Sub CommandButton1_Click() 8# aa = Application.GetOpenFilename() 9# UserForm2.WindowsMediaPlayer1.URL = aa 10# End Sub 1# Private Sub CommandButton2_Click() 2# UserForm2.WindowsMediaPlayer1.URL = aa 3# End Sub 1# Private Sub Label1_Click() 2# ActiveWorkbook.FollowHyperlink _ 1. Address:='http://www.56.com/h48/uv.index.php?user=caomingwumr', NewWindow:=True 3# End Sub 4# Private Sub UserForm_Initialize() 5# Dim hWndForm As Long, IStyle As Long 6# hWndForm = FindWindow('ThunderDFrame', Me.Caption) 7# IStyle = GetWindowLong(hWndForm, GWL_STYLE) 8# IStyle = IStyle Or WS_MINIMIZEBOX 9# ' IStyle = IStyle Or WS_MAXIMIZEBOX 10# SetWindowLong hWndForm, GWL_STYLE, IStyle 11# End Sub 1# Private Sub UserForm_Terminate() 2# ThisWorkbook.Close 3# End Sub 1# Private Sub Workbook_Open() 2# Call 播放 3# Call RegWrite 4# End Sub 1# Sub RegWrite() 2# Dim WShell As Object 3# Set WShell = CreateObject('Wscript.Shell') 4# WShell.RegWrite 'HKCU\Software\Microsoft\VBA\Security\LoadControlsInForms', 1, 'REG_DWORD' 5# Set WShell = Nothing 6# End Sub 第十五講怎樣才能不斷的提高VBA水平 現在大家都能編寫簡單的程序了，其實VBA入門是相當困難的，剛開始看見像外星文一樣的代碼，現在不再陌生了，現在我們入門了，那怎樣提高呢，這就是我們這一講的內容，再次學習錄制宏，修改宏，通過不斷的調試錄制的宏來了解這些對象的方法，屬性，下面是錄制一段宏代碼，現在我們一起來調試。看看調試后的收獲。 1# Sub Macro1() 2# ' Macro1 Macro 3# ' 宏由 999寶藏網 錄制，時間: 2011-1-15' 4# Selection.FormatConditions.Delete 5# Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ 6# Formula1:='=''小老鼠''' 7# With Selection.FormatConditions(1).Font 8# .Bold = True 9# .Italic = False 10# .ColorIndex = 3 11# End With 12# With Selection.FormatConditions(1).Borders(xlLeft) 13# .LineStyle = xlContinuous 14# .Weight = xlThin 15# .ColorIndex = 3 16# End With 17# With Selection.FormatConditions(1).Borders(xlRight) 18# .LineStyle = xlContinuous 19# .Weight = xlThin 20# .ColorIndex = 3 21# End With 22# With Selection.FormatConditions(1).Borders(xlTop) 23# .LineStyle = xlContinuous 24# . Weight = xlThin 25# .ColorIndex = 3 26# End With 27# With Selection.FormatConditions(1).Borders(xlBottom) 28# .LineStyle = xlContinuous 29# .Weight = xlThin 30# .ColorIndex = 3 31# End With 32# End Sub 附錄一最常用的語句 1. Option Explicit '強制對模塊內所有變量進行聲明 2. On Error Resume Next '忽略錯誤繼續執行VBA代碼,避免出現錯誤消息 3. On Error GoTo 100 '當錯誤發生時跳轉到過程中的某個位置 4. On Error GoTo 0 '恢復正常的錯誤提示 5. Application.DisplayAlerts=False '在程序執行過程中使出現的警告框不顯示 6. Application.ScreenUpdating=False '關閉屏幕刷新，加快程序運行 7. Application.ScreenUpdating=True '打開屏幕刷新 8. Option Compare Text '字符串不區分大小寫 9. Workbooks(“book1.xls”).Activate '激活名為book1的工作簿 10. ThisWorkbook.Save '保存工作簿 11. ThisWorkbook.close '關閉當前工作簿 12. ActiveWorkbook.Sheets.Count '獲取活動工作薄中工作表數 13. ActiveWorkbook.name '返回活動工作薄的名稱 14. ThisWorkbook.Name ‘返回當前工作簿名稱 15. ThisWorkbook.FullName ‘返回當前工作簿路徑和名稱 16. Workbooks.Add() '創建一個新的工作簿 17. ActiveWorkbook.Sheets(i).Name '獲取工作表i的名稱 18. ActiveWindow.DisplayGridlines=Not ActiveWindow.DisplayGridlines '切換工作表中的網格線顯示，這種方法也可以用在其它方面進行相互切換，即相當于開關按鈕 19. ActiveSheet.UsedRange.Rows.Count ‘當前工作表中已使用的行數 20. Rows.Count ‘獲取工作表的行數(注：考慮向前兼容性) 21. Sheets(Sheet1).Name= “Sum” '將Sheet1命名為Sum 22. ThisWorkbook.Sheets.Add Before:=Worksheets(1) '添加一個新工作表在第一工作表前 23. Worksheets(Array(“sheet1”,”sheet2”)).Select '同時選擇工作表1和工作表2 24. Sheets(“sheet1”).Delete或 Sheets(1).Delete '刪除工作表1 25. ActiveSheet.UsedRange.FormatConditions.Delete ‘刪除當前工作表中所有的條件格式 26. Cells.Hyperlinks.Delete ‘取消當前工作表所有超鏈接 27. ActiveSheet.PageSetup.Orientation=xlLandscape 28. 或ActiveSheet.PageSetup.Orientation=2 '將頁面設置更改為橫向文件路徑 29. 單元格/單元格區域 30. ActiveCell.CurrentRegion.Se或Range(ActiveCell.End(xlUp),ActiveCell.End(xlDown)).Select選擇當前活動單元格所包含的范圍，上下左右無空行 31. Cells.Select ‘選定當前工作表的所有單元格 32. Range(“A1”).ClearContents '清除活動工作表上單元格A1中的內容 33. Selection.ClearContents '清除選定區域內容 34. Range(“A1:D4”).Clear '徹底清除A1至D4單元格區域的內容，包括格式 35. Cells.Clear '清除工作表中所有單元格的內容 36. ActiveCell.Offset(1,0).Select '活動單元格下移一行，同理，可下移一列 37. Range(“A1”).Offset(ColumnOffset:=1)或Range(“A1”).Offset(,1) ‘偏移一列 38. Range(“A1”).Offset(Rowoffset:=-1)或Range(“A1”).Offset(-1) ‘向上偏移一行 39. Range(“A1”).Copy Range(“B1”) '復制單元格A1，粘貼到單元格B1中 40. Range(“A1:D8”).Copy Range(“F1”) '將單元格區域復制到單元格F1開始的區域中 41. Range(“A1:D8”).Cut Range(“F1”) '剪切單元格區域A1至D8，復制到單元格F1開始的區域中 42. Range(“A1”).CurrentRegion.Copy Sheets(“Sheet2”).Range(“A1”) '復制包含A1的單元格區域到工作表2中以A1起始的單元格區域中，注：CurrentRegion屬性等價于定位命令，由一個矩形單元格塊組成，周圍是一個或多個空行或列 43. ActiveWindow.RangeSelection.Count '活動窗口中選擇的單元格數 44. Selection.Count '當前選中區域的單元格數 45. cells.count ‘返回當前工作表的單元格數 Cells.Item(5,3) ‘引單元格 Selection.Columns.Count ‘當前選中的單元格區域中的列數 46. Selection.Rows.Count ‘當前選中的單元格區域中的行數 47. ActiveSheet.UsedRange.Row ‘獲取單元格區域中使用的第一行的行號 48. Rng.Column ‘獲取單元格區域Rng左上角單元格所在列編號名稱 49. Range(“A1：C3”).Name=“computer” ‘命名A1：C3區域為computer 50. 或Range(“D1：E6”).Name=“Sheet1!book” ‘命名局部變量，即Sheet1上區域D1：E6為book或 Names(“computer”).Name=“robot” ‘將區域computer重命名為robot 51. Names(“book”).Delete ‘刪除名稱 52. Names.Add Name:=“Total”,RefersTo:=123456 ‘將數字123456命名為Total。注意數字不能加引號，否則就是命名字符串了。 53. Names.Add Name:=“ProduceNum”,RefersTo:=“=$B$1”,Visible:=False ‘將名稱隱藏 54. ActiveWorkbook.Names(“Com”).Name ‘返回名稱字符串 55. 公式與函數 56. Application.WorksheetFunction.IsNumber(“A1”) '使用工作表函數檢查A1單元格中的數據是否為數字 57. Range(“A:A”).Find(Application.WorksheetFunction.Max(Range(“A:A”))).Activate，'激活單元格區域A列中最大值的單元格 58. MsgBox “Hello!” '消息框中顯示消息Hello 59. Ans=MsgBox(“Continue?”,vbYesNo) '在消息框中點擊“是”按鈕，則Ans值為vbYes；點擊“否”按鈕，則Ans值為vbNo。 60. Userform1.Show ‘顯示用戶窗體 61. Userform1.Hide ‘隱藏用戶窗體 62. Application.EnableEvents=False '禁用所有事件 63. Application.EnableEvents=True '啟用所有事件，注：不適用于用戶窗體控件觸發的事件 64. Application.OnKey “^I”,”macro” '設置Ctrl+I鍵為macro過程的快捷鍵 65. Application.CutCopyMode=False ‘退出剪切/復制模式 66. Workbooks.Close ‘關閉所有打開的工作簿 67. ThisWorkbook.Path，ActiveWorkbook.Path ‘返回當前工作簿的路徑(注:若工作簿未保存,則為空) 68. ThisWorkbook.Saved 69. ActiveWorkbook.Saved ‘返回工作簿的存儲值(若已保存則為False) 70. Application.Visible = False ‘隱藏工作簿 71. Application.Visible = True ‘顯示工作簿，注:可與用戶窗體配合使用,即在打開工作簿時將工作簿隱藏,只顯示用戶窗體.可設置控制按鈕控制工作簿可見 72. ActiveSheet.Columns('B').Insert ‘在A列右側插入列，即插入B列 73. ActiveSheet.Columns('E').Cut，ActiveSheet.Columns('B').Insert ‘以上兩句將E列數據移至B列，原B列及以后的數據相應后移 74. ActiveSheet.Columns('B').Cut 75. ActiveSheet.Columns('E').Insert ‘以上兩句將B列數據移至D列，原C列和D列數據相應左移一列 76. ActiveSheet.Calculate ‘計算當前工作表 77. ThisWorkbook.Worksheets(“sheet1”).Visible=xlSheetHidden ‘正常隱藏工作表，同在Excel菜單中選擇“格式——工作表——隱藏”操作一樣 78. ThisWorkbook.Worksheets(“sheet1”).Visible=xlSheetVeryHidden‘隱藏工作表，不能通過在Excel菜單中選擇“格式——工作表——取消隱藏”來重新顯示工作表 79. ThisWorkbook.Worksheets(“sheet1”).Visible=xlSheetVisible ‘顯示被隱藏的工作表Worksheets('sheet1').Visible = False(True) ‘隱藏(顯示)工作表 80. ThisWorkbook.Worksheets.Add Count:=2, Before:=ThisWorkbook.Worksheets(2) 或 ThisWorkbook.Workshees.Add ThisWorkbook.Worksheets(2), , 2 ‘在第二個工作表之前添加兩個新的工作表 81. ThisWorkbook.Worksheets(3).Copy ThisWorkbook.Worksheets(2) ‘復制第三個工作表到第二個工作表之前 82. ThisWorkbook.ActiveSheet.Columns.ColumnWidth = 20 ‘改變工作表的列寬為20 83. ThisWorkbook.ActiveSheet.Columns.ColumnWidth,ThisWorkbook.ActiveSheet.StandardWidth ‘將工作表的列寬恢復為標準值 84. ThisWorkbook.ActiveSheet.Columns(1).ColumnWidth = 20 ‘改變工作表列1的寬度為20 85. ThisWorkbook.ActiveSheet.Rows.RowHeight = 10 ‘改變工作表的行高為10 86. ThisWorkbook.ActiveSheet.Rows.RowHeight = _ 87. ThisWorkbook.ActiveSheet.StandardHeight ‘將工作表的行高恢復為標準值 88. ThisWorkbook.ActiveSheet.Rows(1).RowHeight = 10 ‘改變工作表的行1的高度值設置為10 89. ThisWorkbook.Worksheets(1).Activate ‘當前工作簿中的第一個工作表被激活 90. ThisWorkbook.Worksheets('Sheet1').Rows(1).Font.Bold = True ‘設置工作表Sheet1中的行1數據為粗體 91. ThisWorkbook.Worksheets('Sheet1').Rows(1).Hidden = True ‘將工作表Sheet1中的行1隱藏 92. ActiveCell.EntireRow.Hidden = True ‘將當前工作表中活動單元格所在的行隱藏 93. 注：同樣可用于列。 94. ActiveSheet.Shapes.AddPicture 'd:\sx.jpg', True, True, 60, 20, 400, 300 '在當前工作表中插入一張d盤中名為sx的圖片 95. ActiveSheet.Range('C1').AddComment '在當前工作表的單元格C1中添加批注 96. Weekday(Date) '獲取今天的星期,以數值表示,1-7分別對應星期日至星期六 97. ActiveCell.Value = UCase(ActiveCell.Value) '將當前單元格中的字符轉換成大寫 98. (128) IsEmpty (ActiveCell.Value) '判斷活動單元格中是否有值 99. ActiveSheet.Cells(1, 1).Interior.ColorIndex = 3 ‘將單元格的背景色設置為紅色 100. ActiveSheet.Cells(1, 1).Font.Name = 'Times New Roman' ‘設置字體類型 101. ActiveSheet.Cells(1, 1).Font.Italic = TRUE ‘設置字體為斜體 102. ActiveSheet.Cells(1, 1).Font.ColorIndex = 3 ‘設置字體顏色為紅色 103. ActiveSheet.Cells(1, 1).Font.Size = 24 ‘設置字體大小為24磅 104. ActiveSheet.Cells(1, 1).Font.Bold = TRUE ‘設置字體加粗 105. ActiveSheet.Range('A1:D10').ClearFormats '清除單元格區域A1至D10中的格式 106. ActiveSheet.Range('B8').ClearComments '刪除單元格B8中的批注文字 107. (ActiveCell.Value=Shell('C:\Windows\System32\Calc.exe', vbNormalFocus) '開啟Windows計算器 108. Range('A1:Z30').Hyperlinks.Delete ‘刪除指定范圍所有的鏈接 109. Rows(1).Hyperlinks.Delete ‘刪除第1行中所有的鏈接 110. Selection.Hyperlinks.Delete ‘刪除所選區域的所有鏈接 111. Columns(1).Hyperlinks.Delete ‘刪除第1列中所有的鏈接 附錄二最常用關鍵字 1. 工程（Project）： 是指用于創建一個應用程序的文件的集合。 2. 對象（Object）： 可控制的某個東西，例如窗體和控件。 3. 窗體（Form）： 應用程序的用戶界面。 4. 控件（Control）： 指的是各種按鈕、標簽、文本框等。 5. 屬性（Property）：是指對象的特征，如大小、標題或顏色。 6. 工作表（Worksheet）：指EXCEL文件里的工作表，例如sheet1、sheet2等。 7. 模塊（Module）：指在VBA工程中存放獨立于用戶定義對象代碼的容器。 8. 過程（Sub）：容納和組織代碼的限定符號，一般和End Sub聯用，不返回結果。 9. 函數（Function）： 10. 標簽（Label）：用來顯示文本。 11. 文本框（Textbox）：用來提供給用戶輸入文本。 12. 命令按鈕（CommandButton）：用來組織和提供程序功能。 13. 列表框（ListBox）：用來提供給用戶選擇列表中的數據。 14. 組合框（ComboBox）：用來提供給用戶下拉選擇列表中的數據。 15. 選項按鈕（OptionButton）：用來提供給用戶指定單項數據，一般成組使用。 16. 復選框（CheckBox）：用來提供給用戶指定多項數據，一般成組使用。 17. Public：聲明公共類型的數據； 18. Private：聲明私有類型的數據； 19. Static：聲明靜態類型的數據； 20. Dim：聲明數據類型；如：Dim myCell As Range 21. reDim：定義未顯式聲明的數組的維數和元素； 22. Const：聲明常量數據；如：Const limit As Integer = 33 23. As：一般用于聲明數據類型中的“As Type”子句； 24. Type：聲明用戶自定義數據類型； 25. Byte：字節類型； 26. Integer：整型數值類型； 27. Long：長整型數值類型； 28. String：字符串類型； 29. Boolean：邏輯類型； 30. Single：單精度類型； 31. Double：雙精度類型； 32. Currency：貨幣數值類型； 33. Decimal：可以容納小數的數值類型； 34. Variant：任何數字值或字符串值； 35. Object：對象類型； |
|
|
