範例結果
0、開始
VBA執行程式的單位
命名規則:第1個字不能使用數字,另【空白】、【.】、【!】、【@】、【$】、【#】 等特殊符號
程序類型 | 說明 |
Sub | 通常使用Sub程序 |
Function | 執行後,會傳回值的程序 常用在使用者定義函數上 |
事件程序 | 因動作事件而執行的程序 如:按下按鈕、打開活頁簿或使用者定義表單 |
提供撰寫程序的地方
※ 內含巨集時,活頁簿的副檔名為 . xlsm
模組 | 說明 |
Excel 物件 | 工作表或活頁簿等物件的事件程序 |
模型 | 使用 Sub 或 Function 程序 |
表單 | 使用者定義表單的事件程序 |
物件類型模組 | 製作獨立類別時所需要的程序 |
一、宣告變數
Sub 報表生成() 'S1 宣告變數 Dim wht As Worksheet, rng As Range Dim n As Long, k As Long End Sub
(1) 語法:
Dim 變數名稱 As 資料型態
(2) 列舉常用項目:( 參考連結)
資料型態 | 範圍 | |
Byte | 位元組 | 0 到 255 |
Boolean | 布林 | True 或 False |
Integer | 整數 | -32,768 到 32,767 |
Long | 長整數 | -2,147,483,648 到 2,147,483,647 |
Single | 單精度浮點數 | 負數: -3.402823E38 到 -1.401298E-45 正數: 1.401298E-45 到 3.402823E38 |
Double | 雙精度浮點數 | 負數: -1.79769313486231E308 到 -4.94065645841247E-324 正數: 4.94065645841247E-324 到 1.79769313486232E308 |
Currency | 貨幣 | -922,337,203,685,477.5808 到 922,337,203,685,477.5807 |
Date | 日期 | January 1, 100 到 December 31, 9999 |
String | 字串 | 0 ~2G |
Object | 物件 | 任何 Object 引用 |
(3) 範例
Dim myName As String Dim myHeight As Single Dim myDate As Date, myAge As Integer myName = "呂大明" myDate = #1/1/2000# myAge = Range("A1").Value myHeight = 190
(1) 語法:
Dim 變數名稱 As 物件型態
Set 變數名稱 = 物件
(2) 範例:
'宣告 變數 rng 為Range物件,使用 Set 敍述,將儲存格範圍指定 rng 變數 Dim rng As Range Set rng = Range("A1:C3") '宣告 變數 sht 為 工作表 物件,使用 Set 敍述,將 ActiveSheet 作用中工作表 指定 sht 變數 Dim sht As Worksheet Set sht = ActiveSheet
(1) 語法:
Const 常數名 As 資料型態 = 值
(2) 範例:
Const aa As Double = 20
(1) 語法:
Dim 變數名稱 As Variant
變數 = Array ( 元素1 , 元素2 , 元素3 , …)
(2) 範例:
Dim myarray As Variant myarray = Array("呂大明", #1/1/2000#, "A") Range("B2") = myarray(0) Range("B3") = myarray(1) Range("B4") = myarray(2)
二、複製工作表
'S2 複製目前工作表 並更名 ActiveSheet.Copy after:=ActiveSheet ActiveSheet.Name = "結果" Set wht = ActiveSheet
(1) 語法:
Dim 變數名稱 As 物件型態
Set 變數名稱 = 物件
(2) 範例:
Sub 參照工作表() With Worksheets("工作表1") Range("B1").Value = Sheets.Count '顯示活頁簿所有種類的資料表數 Range("B2").Value = Worksheets.Count '顯示工作表數 Range("B3").Value = Charts.Count '顯示圖表工作表數 End With MsgBox ActiveSheet.Name '訊息顯示【目前作用工作表】 End Sub
顯示結果
(3) 說明
(1) 語法:
Worksheets 物件.Count
(2) 範例:
Sub 工作表數() Worksheets(Array(1, 3)).Select MsgBox "工作表數:" & Worksheets.Count & Chr(10) & _ "選取的工作表數:" & ActiveWindow.SelectedSheets.Count End Sub
顯示結果
(1) 語法:
Worksheets 物件.Name = 工作表名
(2) 範例:
Sub 確認工作表() 'S1:取得第1張工作表的名稱 MsgBox Worksheets(1).Name 'S2:設定作用中的工作表名稱 ActiveSheet.Name = "工作表更名" End Sub
範例結果
A. 複製工作表
語法:
Worksheet.Copy [Before := 工作表名 , After := 工作表名 ]
B. 新增工作表
(1) 語法:
Worksheet.Add [Before := 工作表名 , After := 工作表名 , Count : = 個數 ]
(2) 範例:
Sub 新增工作表() Dim i As Integer ' Do Until ... Loop 設定直到工作表的個數為3 Do Until Worksheets.Count = 3 i = Worksheets.Count Worksheets.Add after:=Worksheets(i) ActiveSheet.Name = i + 1 & "月" Loop End Sub
顯示結果
C. 移動工作表
(1) 語法:
Worksheet.Move [Before := 工作表名 , After := 工作表名 ]
(2) 範例:
Sub 移動工作表() '將第1張工作表,移動到最後一張工作表的後面 Worksheets(1).Move after:=Worksheets(Worksheets.Count) End Sub
顯示結果
三、進行數據排序
A. 課程步驟
A-1 錄製巨集情形
Sub 錄1_排序() ' 錄1_排序 巨集 ActiveWorkbook.Worksheets("結果").Sort.SortFields.Clear ActiveWorkbook.Worksheets("結果").Sort.SortFields.Add2 Key:=Range("C2:C187"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal ActiveWorkbook.Worksheets("結果").Sort.SortFields.Add2 Key:=Range("D2:D187"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal ActiveWorkbook.Worksheets("結果").Sort.SortFields.Add2 Key:=Range("B2:B187"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets("結果").Sort .SetRange Range("A1:F187") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Sub
A-2 修改錄製步驟
'S3 進行數據排序 依 經銷商>商品為順序 With wht.Sort .SortFields.Clear .SortFields.Add Key:=Range("C2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal .SortFields.Add Key:=Range("D2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal .SortFields.Add Key:=Range("B2"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal .SetRange Range("A:F") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With
說明:相同範圍連續處理,可以使用With 省略物件,直接以【.】開始撰寫處理程序
語法:
With 範圍
程式處理步驟
End With
(1) 語法
With Worksheet 物件.Sort
.SortFields.Clear
.SortFields.Add (Key , SortOn , Order , CustomOrder , DataOption )
.SetRange 排序範圍
.Header = 第一列是否為標題
.MatchCase = 是否區分大小寫
.Orientation = 排序方向
.SortMethod = 使用筆畫或注音順序
.Apply
End With
(2) 說明
方法 | 內容 | 設定值 | |
.Clear | 清除原先所有排序 | ||
.SortFields.Add | .SortFields:取得.SortFields集合 .Add:建立排序欄位,傳回SortFields物件 | 再說明 | |
.SetRange | 排序範圍 | ||
.Header | 第一列是否為標題 | xlNone:預設值,不設定標題列 | |
xlYes:設定標題列、 | xlGuess:Excel 自動判斷 | ||
.MatchCase | 是否區分大小寫 | True:區分 | False:不區分 |
.Orientation | 排序方向 | xlLeftToRight:欄 | xlTopBotton:列 |
.SortMethod | 使用筆畫或注音順序 | xlPinYin:筆畫順序 | xlStroke:注音順序 |
.Apply | 執行排序 |
(1) 語法
.SortFields.Add (Key , SortOn , Order , CustomOrder , DataOption )
(2) 說明
引數 | 內容 | 設定值 | |
key | 排序關鍵欄位 | 指定欄位範圍 或 範圍起始儲存格 | |
SortOn | 排序基準 | xlSortOnValues:值 | xlSortOnCellColor:儲存格色彩 |
xlSortOnIcon:儲存格圖示 | xlSortOnFontColor:文字色彩 | ||
Order | 排序順序 | xlAscending:遞增 | xlDescending:遞減 |
CustomOrder | 使用者定義排序 | 以字串指定排序順序。 例: .CustomOrder:= ″台北,台中,高雄″ | |
DataOption | 文字排序方法 | xlSortNormal:預設值,數字和文字 個別排序 | |
xlSortTextAsNumbers:數字和文字 當作數值來排序 |
四、找出最後一列的列號
'S4 找出報表中的最後一列列號 n = wht.Cells(Rows.Count, 1).End(xlUp).Row MsgBox "Rows.Count:" & Rows.Count & Chr(10) & _ "n值,報表最後列號:" & n
A. 範圍 表示方法
(1) 語法:
Cells ( 列號 , 欄名 )
(2) 範例:
Sub 參照儲存格() Cells(2, 3) = "今天日期" '在儲存格 C2 位置 Cells(2, "D").Value = Date ' 在儲存格 D2 位置,顯示今天日期 End Sub
(1) 語法:
Range ( 儲存格範圍 )
Range ( 起始儲存格 , 終點儲存格
(2) 範例:
參照方法 | 範例 | 說明 |
單一儲存格 | Range (″A1″) | 儲存格 A1 |
儲存格範圍 | Range ( ″A3 : G7″ ) | 儲存格 A3 ~ G7 |
Range (″A3″ , ″G7″ ) | ||
多儲存格範圍 | Range (″A3 , G7″ ) | 儲存格 A3、G7 |
Range (″A3:G7″ , ″A7″) | 儲存格 A3 ~ G7、A7 | |
整欄 | Range ( ″A :G″ ) | 整欄 A ~ G |
整列 | Range ( ″3 :7 ″ ) | 整列 3 ~ 7 |
定義名稱範圍 | Range ( ″表名稱″ ) | 定義名稱為【表名稱】的範圍 |
(1) 語法:
Range 物件.End ( 方向 )
(2) 範例:
Sub 移動儲存格位置() Range("B2").Select Selection.End(xlToRight).Select Selection.End(xlDown).Select Selection.End(xlToLeft).Select Selection.End(xlUp).Select End Sub
顯示結果
(3) 說明:
位置 | 說明 |
XlDown | 向下 |
XlUp | 向上 |
XlToRight | 向右 |
XlToLeft | 向左 |
語法:
Range 物件.Offset ( 列移動 , 欄移動 )
語法:
Range 物件.Resize ( 列數 , 欄數 )
語法:
Range 物件.CurrentRegin
A. 取得所選儲存格的 列號 / 欄號
說明:取得指定的儲存格的 第列號 / 第欄號
語法:
Range 物件.Row
Range 物件.Column
B. 參照 列號 / 欄號
說明:參照儲存格範圍的 第列號 / 第欄數
語法:
Range 物件.Rows (列)
Range 物件.Column (欄)
C. 儲存格範圍的 列數 / 欄數
說明:參照所有儲存格範圍,接著使用 Count屬性 算出 總列數 / 總欄數
語法:
Range 物件.Row.Count
Range 物件.Column.Count
D. 參照儲存格範圍的 整列 / 整欄
說明:參照到指令的儲存格範圍內的 整列 / 整欄 範圍
語法:
Range 物件.EntireRow
Range 物件.EntireColumn
B. 選取範圍 列舉
選取方法 列舉:
參照方法 | 範例 |
選取範圍,Range 與 Select 配合 | Range(″範圍″).Select |
選取整個工作表範圍 | Cells.Select |
選取 基準儲存格 所在連續的整個【表格】範圍 | 基準儲存格.CurrentRegion.Select |
選取 目前儲存格 所在連續的整個【表格】範圍 | ActiveCell.CurrentRegion.Select |
選取 目前單一儲存格 | ActiveCell.Select |
目前選取範圍.執行工作 | Selection.執行工作 例如: Selection.CleanContents >>目前範圍清除內容 |
以 基準儲存格/範圍 開始, 相對平移位置 同大小範圍 | ActiveCell.Offset ( 列移動數 , 欄移動數 ).Select Section.Offset ( 列移動數 , 欄移動數 ).Select |
五、各項計算
'S5 各項計算 Range("G1:J1") = Array("產品" & Chr(10) & "筆數", "產品" & Chr(10) & "總金額", "經銷商" & Chr(10) & "筆數", "經銷商" & Chr(10) & "總金額") For k = 2 To n '經銷商C 筆數 及 金額 'Range("I" & k) = Application.WorksheetFunction.CountIf(Range("C:C"), Range("C" & k)) Cells(k, "I") = Application.WorksheetFunction.CountIf(Columns("C"), Cells(k, "C")) Cells(k, "J") = Application.WorksheetFunction.SumIf(Columns("C"), Cells(k, 3), Columns("F")) '同經銷商C 同產品D 之下 數量 及 金額 Cells(k, "G") = Application.WorksheetFunction.CountIfs(Columns("C"), Cells(k, "C"), Columns("D"), Cells(k, "D")) Cells(k, "H") = Application.WorksheetFunction.SumIfs(Columns("F"), Columns("C"), Cells(k, "C"), Columns("D"), Cells(k, "D")) '序號A 加入 Cells(k, 1) = k - 1 Next
(1) 語法:
Dim 變數
For 變數 = 初始值 To 終值 Step 增加值
重複處理工作
Next 變數
(2) 範例
Sub For重複() Dim i As Integer For i = 2 To Range("A1").End(xlDown).Row step 1 ActiveSheet.Copy after:=ActiveSheet Worksheets(i).Name = Cells(i, 1).Value Next End Sub
六、合併儲存格
A. 課程步驟
A-1 依需求 進行合併儲存格
'S6 同經銷商 及 同經銷商 同產品,進行合併儲存格 For k = n To 2 Step -1 '同經銷商 同產品 If Range("C" & k) & Range("D" & k) = Range("C" & k - 1) & Range("D" & k - 1) Then Range("D" & k - 1).Resize(2, 1).Merge Range("G" & k - 1).Resize(2, 1).Merge Range("H" & k - 1).Resize(2, 1).Merge End If '同經銷商 If Range("C" & k) = Range("C" & k - 1) Then Range("C" & k - 1).Resize(2, 1).Merge Range("I" & k - 1).Resize(2, 1).Merge Range("J" & k - 1).Resize(2, 1).Merge End If Next
A-2 設定是否顯示確認訊息
Sub 報表生成() Application.DisplayAlerts = False 'S1 宣告變數 Dim wht As Worksheet, rng As Range Dim n As Long, k As Long ... ... Application.DisplayAlerts = True End Sub
(1) 語法:
If 條件式 Then
符合條件式時,處理工作
End If
(1) 語法:
Range 物件.Merge [Across:= True / False]
Across:True:以列為單位進行儲存格合併,
False:省略時為False,範圍全部合併為單一儲存格
Across | True:以列為單位進行儲存格合併 False:省略時為False,範圍全部合併為單一儲存格 |
(2) 範例:
Sub 合併儲存格() Application.DisplayAlerts = False Range("B3:B7").Merge Range("C8:E10").Merge across:=True Application.DisplayAlerts = True End Sub
語法:
True:預設值,顯示 、 False:不顯示
※巨集設定False不顯示訊息後,記得再巨集結束後回復為True
Application.DisplayAlerts = True 或 False
七、儲存格美化
A. 課程步驟
A-1 錄製巨集情形
Sub 錄2_美化格式() ' 錄2_美化格式 巨集 'S7-1 全部表格 > 儲存格水平置中 > 加藍色框線 > 行高20 Range("A1").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext End With Selection.Borders(xlDiagonalDown).LineStyle = xlNone Selection.Borders(xlDiagonalUp).LineStyle = xlNone With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Color = -4165632 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Color = -4165632 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Color = -4165632 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Color = -4165632 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideVertical) .LineStyle = xlContinuous .Color = -4165632 .TintAndShade = 0 .Weight = xlThin End With With Selection.Borders(xlInsideHorizontal) .LineStyle = xlContinuous .Color = -4165632 .TintAndShade = 0 .Weight = xlThin End With Selection.RowHeight = 20 'S7-2 交易日期 B 欄 ,設定西元日期格式 yyyy/mm/dd Columns("B:B").Select Selection.NumberFormatLocal = "yyyy/mm/dd" 'S7-3 金額 F、H、J 欄,設定格式為 #,##0 及儲存格水平靠右對齊 Range("F:F,H:H,J:J").Select Range("J1").Activate Selection.NumberFormatLocal = "#,##0" With Selection .HorizontalAlignment = xlRight .VerticalAlignment = xlCenter .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext End With 'S7-4 表格標題,將 序號 A1 格式複製到 A1~J1 > 設定自動換行 Range("A1").Select Selection.Copy Range("A1:J1").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.CutCopyMode = False Selection.Rows.AutoFit With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = True .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With 'S8 設定儲存格最後停留位置 Range("A1").Select End Sub
A-2 修改錄製步驟
'S7 格式美化 'S7-1 全部表格 > 儲存格水平置中 > 加藍色框線 > 行高20 ActiveCell.CurrentRegion.Select With Selection .HorizontalAlignment = xlCenter .Borders.LineStyle = xlContinuous .Borders.Color = -4165632 .Borders.Weight = xlThin .RowHeight = 20 End With 'S7-2 交易日期 B 欄 ,設定西元日期格式 yyyy/mm/dd Columns("B:B").Select Selection.NumberFormatLocal = "yyyy/mm/dd" 'S7-3 金額 F、H、J 欄,設定格式為 #,##0 及儲存格水平靠右對齊 Range("F:F,H:H,J:J").Select With Selection .NumberFormatLocal = "#,##0" .HorizontalAlignment = xlRight End With 'S7-4 表格標題,將 序號 A1 格式複製到 A1~J1 > 設定自動換行 Range("A1").Copy With Range("A1:J1") .PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False .Rows.AutoFit .WrapText = True End With 'S8 設定儲存格最後停留位置 Range("A1").Select