範例結果

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 ( 列數 , 欄數 )

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

(1) 語法:
非所有函數皆可使用,請參照線上說明。(連結

Application.WorksheetFunction.工作表函數

(2) 範例使用函數

六、合併儲存格

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,範圍全部合併為單一儲存格

AcrossTrue:以列為單位進行儲存格合併
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