EXCEL VBA語句集300

• 2020 年 3 月 5 日
• 筆記

        訂製模組行為 (1) Option Explicit 『強制對模組內所有變數進行聲明 Option Private Module 『標記模組為私有，僅對同一工程中其它模組有用，在宏對話框中不顯示  Option Compare Text 『字元串不區分大小寫  Option Base 1 『指定數組的第一個下標為1 (2) On Error Resume Next 『忽略錯誤繼續執行VBA程式碼,避免出現錯誤消息 (3) On Error GoTo ErrorHandler 『當錯誤發生時跳轉到過程中的某個位置 (4) On Error GoTo 0 『恢復正常的錯誤提示 (5) Application.DisplayAlerts=False 『在程式執行過程中使出現的警告框不顯示 (6) Application.ScreenUpdating=False 『關閉螢幕刷新 Application.ScreenUpdating=True 『打開螢幕刷新 (7) Application.Enable.CancelKey=xlDisabled 『禁用Ctrl+Break中止宏運行的功能  工作簿 (8) Workbooks.Add() 『創建一個新的工作簿 (9) Workbooks(「book1.xls」).Activate 『激活名為book1的工作簿 (10) ThisWorkbook.Save 『保存工作簿 (11) ThisWorkbook.close 『關閉當前工作簿 (12) ActiveWorkbook.Sheets.Count 『獲取活動工作薄中工作表數 (13) ActiveWorkbook.name 『返回活動工作薄的名稱 (14) ThisWorkbook.Name 『返回當前工作簿名稱 ThisWorkbook.FullName 『返回當前工作簿路徑和名稱 (15) ActiveWindow.EnableResize=False 『禁止調整活動工作簿的大小 (16) Application.Window.Arrange xlArrangeStyleTiled 『將工作簿以平鋪方式排列 (17) ActiveWorkbook.WindowState=xlMaximized 『將當前工作簿最大化  工作表 (18) ActiveSheet.UsedRange.Rows.Count 『當前工作表中已使用的行數 (19) Rows.Count 『獲取工作表的行數(註：考慮向前兼容性) (20) Sheets(Sheet1).Name= 「Sum」 『將Sheet1命名為Sum (21) ThisWorkbook.Sheets.Add Before:=Worksheets(1) 『添加一個新工作表在第一工作表前 (22) ActiveSheet.Move After:=ActiveWorkbook. _ Sheets(ActiveWorkbook.Sheets.Count) 『將當前工作表移至工作表的最後 (23) Worksheets(Array(「sheet1」,」sheet2」)).Select 『同時選擇工作表1和工作表2 (24) Sheets(「sheet1」).Delete或 Sheets(1).Delete 『刪除工作表1 (25) ActiveWorkbook.Sheets(i).Name 『獲取工作表i的名稱 (26) ActiveWindow.DisplayGridlines=Not ActiveWindow.DisplayGridlines 『切換工作表中的網格線顯示，這種方法也可以用在其它方面進行相互切換，即相當於開關按鈕 (27) ActiveWindow.DisplayHeadings=Not ActiveWindow.DisplayHeadings 『切換工作表中的行列邊框顯示 (28) ActiveSheet.UsedRange.FormatConditions.Delete 『刪除當前工作表中所有的條件格式 (29) Cells.Hyperlinks.Delete 『取消當前工作表所有超鏈接 (30) ActiveSheet.PageSetup.Orientation=xlLandscape 或ActiveSheet.PageSetup.Orientation=2 『將頁面設置更改為橫向 (31) ActiveSheet.PageSetup.RightFooter=ActiveWorkbook.FullName 『在頁面設置的表尾中輸入文件路徑 ActiveSheet.PageSetup.LeftFooter=Application.UserName 『將用戶名放置在活動工作表的頁腳  單元格/單元格區域 (32) ActiveCell.CurrentRegion.Select 或Range(ActiveCell.End(xlUp),ActiveCell.End(xlDown)).Select 』選擇當前活動單元格所包含的範圍，上下左右無空行 (33) Cells.Select 『選定當前工作表的所有單元格 (34) Range(「A1」).ClearContents 『清除活動工作表上單元格A1中的內容 Selection.ClearContents 『清除選定區域內容 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) 『偏移一列 Range(「A1」).Offset(Rowoffset:=-1)或Range(「A1」).Offset(-1) 『向上偏移一行 (38) Range(「A1」).Copy Range(「B1」) 『複製單元格A1，粘貼到單元格B1中 Range(「A1:D8」).Copy Range(「F1」) 『將單元格區域複製到單元格F1開始的區域中 Range(「A1:D8」).Cut Range(「F1」) 『剪切單元格區域A1至D8，複製到單元格F1開始的區域中 Range(「A1」).CurrentRegion.Copy Sheets(「Sheet2」).Range(「A1」) 『複製包含A1的單元格區域到工作表2中以A1起始的單元格區域中 註：CurrentRegion屬性等價於定位命令，由一個矩形單元格塊組成，周圍是一個或多個空行或列 (39) ActiveWindow.RangeSelection.Value=XX 『將值XX輸入到所選單元格區域中 (40) ActiveWindow.RangeSelection.Count 『活動窗口中選擇的單元格數 (41) Selection.Count 『當前選中區域的單元格數 (42) GetAddress=Replace(Hyperlinkcell.Hyperlinks(1).Address,mailto:,」」) 『返回單元格中超級鏈接的地址並賦值 (43) TextColor=Range(「A1」).Font.ColorIndex 『檢查單元格A1的文本顏色並返回顏色索引 Range(「A1」).Interior.ColorIndex 『獲取單元格A1背景色 (44) cells.count 『返回當前工作表的單元格數 (45) Selection.Range(「E4」).Select 『激活當前活動單元格下方3行，向右4列的單元格 (46) Cells.Item(5,」C」) 『引單元格C5 Cells.Item(5,3) 『引單元格C5 (47) Range(「A1」).Offset(RowOffset:=4,ColumnOffset:=5) 或 Range(「A1」).Offset(4,5) 『指定單元格F5 (48) Range(「B3」).Resize(RowSize:=11,ColumnSize:=3) Rnage(「B3」).Resize(11,3) 『創建B3：D13區域 (49) Range(「Data」).Resize(,2) 『將Data區域擴充2列 (50) Union(Range(「Data1」),Range(「Data2」)) 『將Data1和Data2區域連接 (51) Intersect(Range(「Data1」),Range(「Data2」)) 『返回Data1和Data2區域的交叉區域 (52) Range(「Data」).Count 『單元格區域Data中的單元格數 Range(「Data」). Columns.Count 『單元格區域Data中的列數 Range(「Data」). Rows.Count 『單元格區域Data中的行數 (53) Selection.Columns.Count 『當前選中的單元格區域中的列數 Selection.Rows.Count 『當前選中的單元格區域中的行數 (54) Selection.Areas.Count 『選中的單元格區域所包含的區域數 (55) ActiveSheet.UsedRange.Row 『獲取單元格區域中使用的第一行的行號 (56) Rng.Column 『獲取單元格區域Rng左上角單元格所在列編號 (57) ActiveSheet.Cells.SpecialCells(xlCellTypeAllFormatConditions) 『在活動工作表中返回所有符合條件格式設置的區域 (58) Range(「A1」).AutoFilter Field:=3,VisibleDropDown:=False 『關閉由於執行自動篩選命令產生的第3個欄位的下拉列表  名稱 (59) Range(「A1：C3」).Name=「computer」 『命名A1：C3區域為computer 或Range(「D1：E6」).Name=「Sheet1!book」 『命名局部變數，即Sheet1上區域D1：E6為book或 Names(「computer」).Name=「robot」 『將區域computer重命名為robot (60) Names(「book」).Delete 『刪除名稱 (61) Names.Add Name:=「ContentList」，_ RefersTo:=「=OFFSET(Sheet1!A2,0,0,COUNTA(Sheet2!$A:$A))」 『動態命名列 (62) Names.Add Name:=「Company」,RefersTo:=「CompanyCar」 『命名字元串CompanyCar (63) Names.Add Name:=「Total」,RefersTo:=123456 『將數字123456命名為Total。注意數字不能加引號，否則就是命名字元串了。 (64) Names.Add Name:=「MyArray」,RefersTo:=ArrayNum 『將數組ArrayNum命名為MyArray。 (65) Names.Add Name:=「ProduceNum」,RefersTo:=「=$B$1」,Visible:=False 『將名稱隱藏 (66) ActiveWorkbook.Names(「Com」).Name 『返回名稱字元串  公式與函數 (67) Application.WorksheetFunction.IsNumber(「A1」) 『使用工作表函數檢查A1單元格中的數據是否為數字 (68) Range(「A:A」).Find(Application.WorksheetFunction.Max(Range(「A:A」))).Activate 』激活單元格區域A列中最大值的單元格 (69) Cells(8,8).FormulaArray=「=SUM(R2C[-1]:R[-1]C[-1]*R2C:R[-1]C)」 『在單元格中輸入數組公式。注意必須使用R1C1樣式的表達式  圖表 (70) ActiveSheet.ChartObjects.Count 『獲取當前工作表中圖表的個數 (71) ActiveSheet.ChartObjects(「Chart1」).Select 『選中當前工作表中圖表Chart1 (72) ActiveSheet.ChartObjects(「Chart1」).Activate ActiveChart.ChartArea.Select 『選中當前圖表區域 (73) WorkSheets(「Sheet1」).ChartObjects(「Chart2」).Chart. _ ChartArea.Interior.ColorIndex=2 『更改工作表中圖表的圖表區的顏色 (74) Sheets(「Chart2」).ChartArea.Interior.ColorIndex=2 『更改圖表工作表中圖表區的顏色 (75) Charts.Add 『添加新的圖表工作表 (76) ActiveChart.SetSourceData Source:=Sheets(「Sheet1」).Range(「A1:D5」), _ PlotBy:=xlColumns 『指定圖表數據源並按列排列 (77) ActiveChart.Location Where:=xlLocationAsNewSheet 『新圖表作為新圖表工作表 (78) ActiveChart.PlotArea.Interior.ColorIndex=xlNone 『將繪圖區顏色變為白色 (79) WorkSheets(「Sheet1」).ChartObjects(1).Chart. _ Export FileName:=「C：MyChart.gif」,FilterName:=「GIF」 『將圖表1導出到C盤上並命名為MyChart.gif  窗體 (80) MsgBox 「Hello!」 『消息框中顯示消息Hello (81) Ans=MsgBox(「Continue?」,vbYesNo) 『在消息框中點擊「是」按鈕，則Ans值為vbYes；點擊「否」按鈕，則Ans值為vbNo。 If MsgBox(「Continue?」,vbYesNo)<>vbYes Then Exit Sub 『返回值不為「是」，則退出 (82) Config=vbYesNo+vbQuestion+vbDefaultButton2 『使用常量的組合，賦值組Config變數，並設置第二個按鈕為預設按鈕 (83) MsgBox 「This is the first line.」 & vbNewLine & 「Second line.」 『在消息框中強制換行，可用vbCrLf代替vbNewLine。 (84) MsgBox "the average is :"&Format(Application.WorksheetFunction.Average(Selection),"#,##0.00"),vbInformation, "selection count average" & Chr(13) 『應用工作表函數返回所選區域的平均值並按指定格式顯示 (85) Userform1.Show 『顯示用戶窗體 (86) Load Userform1 『載入一個用戶窗體,但該窗體處於隱藏狀態 (87) Userform1.Hide 『隱藏用戶窗體 (88) Unload Userform1 或 Unload Me 『卸載用戶窗體 (89) (影像控制項).Picture=LoadPicture(「影像路徑」) 『在用戶窗體中顯示圖形 (90) UserForm1.Show 0 或 UserForm1.Show vbModeless 『將窗體設置為無模式狀態 (91) Me.Height=Int(0.88*ActiveWindow.Height) 『窗體高度為當前活動窗口高度的0.88 Me.Width=Int(0.88*ActiveWindow.Width) 『窗體寬度為當前活動窗口高度的0.88 事件 (92) Application.EnableEvents=False 『禁用所有事件  Application.EnableEvents=True 『啟用所有事件 註：不適用於用戶窗體控制項觸發的事件  對象 (93) Set ExcelSheet = CreateObject("Excel.Sheet") 『創建一個Excel工作表對象 ExcelSheet.Application.Visible = True 『設置 Application 對象使 Excel 可見 ExcelSheet.Application.Cells(1, 1).Value = "Data" 『在表格的第一個單元中輸入文本 ExcelSheet.SaveAs "C:TEST.XLS" 『將該表格保存到C:test.xls 目錄 ExcelSheet.Application.Quit 『關閉 Excel Set ExcelSheet = Nothing 『釋放該對象變數 (94) 『聲明並創建一個Excel對象引用 Dim xlApp As Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.WorkSheet Set xlApp = CreateObject("Excel.Application") Set xlBook = xlApp.Workbooks.Add Set xlSheet = xlBook.Worksheets(1) (95) 『創建並傳遞一個 Excel.Application 對象的引用 Call MySub (CreateObject("Excel.Application")) (96) Set d = CreateObject(Scripting.Dictionary) 『創建一個 Dictionary 對象變數 (97) d.Add "a", "Athens" 『為對象變數添加關鍵字和條目  其他 (98) Application.OnKey 「^I」,」macro」 『設置Ctrl+I鍵為macro過程的快捷鍵 (99) Application.CutCopyMode=False 『退出剪切/複製模式 (100) Application.Volatile True 『無論何時工作表中任意單元格重新計算，都會強制計算該函數 Application.Volatile False 『只有在該函數的一個或多個參數發生改變時，才會重新計算該函數  訂製模組行為 (101) Err.Clear 『清除程式運行過程中所有的錯誤 ******************************************************* 工作簿 (102) ThisWorkbook.BuiltinDocumentProperties(「Last Save Time」) 或Application.Caller.Parent.Parent.BuiltinDocumentProperties(「Last Save Time」) 『返回上次保存工作簿的日期和時間 (103) ThisWorkbook.BuiltinDocumentProperties("Last Print Date") 或Application.Caller.Parent.Parent.BuiltinDocumentProperties(「Last Print Date」) 『返回上次列印或預覽工作簿的日期和時間 (104) Workbooks.Close 『關閉所有打開的工作簿 (105) ActiveWorkbook.LinkSources(xlExcelLinks)(1) 『返回當前工作簿中的第一條鏈接 (106) ActiveWorkbook.CodeName ThisWorkbook.CodeName 『返回工作簿程式碼的名稱 (107) ActiveWorkbook.FileFormat ThisWorkbook.FileFormat 『返回當前工作簿文件格式程式碼 (108) ThisWorkbook.Path ActiveWorkbook.Path 『返回當前工作簿的路徑(注:若工作簿未保存,則為空) (109) ThisWorkbook.ReadOnly ActiveWorkbook.ReadOnly 『返回當前工作簿的讀/寫值(為False) (110) ThisWorkbook.Saved ActiveWorkbook.Saved 『返回工作簿的存儲值(若已保存則為False) (111) Application.Visible = False 『隱藏工作簿 Application.Visible = True 『顯示工作簿 注:可與用