2012-01-24 18:58

[轉載] EXCEL 巨集與VBA介紹

轉載自：EXCEL 巨集與VBA介紹

巨集：一連串的執行指令所構成，可以利用Visual Basic程式指令、也可以利用錄製巨集的方式來錄寫指令。

如何錄製巨集：
  1. 如果要執行巨集，則需要更改「EXCEL選項」\「信任中心」\「信任中心設定」\「巨集設定」
  2. 在「檢視」、「巨集」/「錄製巨集」
  3. 設定「巨集名稱」、快速鍵(Ctrl+英文鍵)，將巨集儲存位置
  4. 開始錄製相關動作(錄製是以絕對位址方式來錄製，如果要以相對位址來錄製則要選「以相對位置錄製」)
  5. 停止錄製
  6. 查看巨集程式碼，並作必要的修正
  7. 執行巨集(可以利用「執行巨集」或快速鍵、或利用表單按鈕來執行)，如果要編修表單時，可以按下Ctrl+該物件，進行修改。


範例：(錄製巨集)
  1. C6至C12的數值格式設定「"進貨" #,##0;"出貨" #,##0」
  2. 「檢視」、「巨集」、「開始錄製」，並開始執行下列指令
  3. 選取範圍C6至C12，並執行「複製」
  4. 選取範圍B6至B12，並按下「選擇性貼上」，選擇貼上「值」與運算「加」
  5. 選取範圍C6至C12，並按下「Del」，清除儲存格內容
  6. 在儲存格C6按一下
  7. 停止錄製巨集
  8. 在工作表中，產生一個按鈕，並指定該按鈕執行該巨集，並將其按鈕文字改為異動
  9. 每次輸入異動資料(正的表示進貨，負的表示出貨)，按下按鈕即可執行巨集


VBA簡介：
Visual Basic for Applications，利用VB來延申Office的能力。開啟EXCEL 顯示開發人員(在「EXCEL選項」/「常用」中勾選)，再撰寫或修改VBA程式。

VBA主要的組成要件：物件，其中包括
  1. 屬性：對物件狀態的描述，可以定義物件的特性(大小、顏色、狀態等)。
  2. 方法：物件的某些特定動作，可以指定動作的細別內容。其主要結構如下：
    物件.方法 指定引數1:=xl常數1, 指定引數2:=xl常數2,....

    指定引數設定為某些內建常數，每個內建常數前會有前綴字連接。
    • EXCEL物件的常數會以xl開始。
    • VB的陳述式及函數的常數會以vb開始。
    • Office物件模式的常數會以mso開始。
  3. 事件：物件的觸發反應。


EXCEL常用的物件
  1. Workbook 活頁簿
  2. Workbooks 活頁簿集合
  3. Workbooks("filename") 檔名為filename的活頁簿
  4. ActiveWorkbook 正在作用中的活頁簿
  5. Sheets 活頁簿中所有工作表
  6. Sheets(n) 活頁簿中第n張工作表
  7. Worksheet 工作表
  8. Worksheets 所有工作表(包括圖表)
  9. Worksheets("sheet") 指表名為sheet工作表
  10. ActiveSheet 正在作用中的工作表
  11. Columns("c1:c2") c1至c2欄(其中c1,c2為A~Z或AA~XFD等欄名)
  12. Rows("r1:r2") r1至r2列(其中r1,r2為1~1048576等列名
  13. Range("x1:x2") x1至x2間的儲存格(其中x1,x2為儲存格位址名稱)
  14. cells(i,j) 儲存格(第i列、第j行)
  15. ActiveCell 目前的儲存格
  16. Selection 目前所選取的物件
範例：
  1. Workbooks("Book1").Sheets("Sheet1").Range("A1:D5").Font.Bold = True 
  2. Worksheets("Sheet1").Cells.ClearContents 
  3. Worksheets("Sheet1").Rows(1).Font.Bold = True 
  4. Range("1:1,3:3,8:8") 
  5. Worksheets("Sheet1").Cells(6, 1).Value = 10 
  6. Worksheets("Sheet1").[A1:B5].ClearContents 
  7. ActiveCell.Offset(1, 3).Font.Underline = xlDouble  


活頁簿常用屬性：
  • ActiveWorkBook.Name 目前活頁簿的名稱
  • ActiveWorkBook.Save 儲存目前的活頁簿
  • ActiveWorkBook.SaveAs Filename := "filename" 另儲新檔
  • WorkBooks.Add 新增活頁簿
  • WorkBooks(i).Close [SaveChange, Filename, RouteWorkbook] 關閉指定的第i個活頁簿
    • SaveChange := True 改變儲存
    • SaveChange := False 不會改變儲存
    • SaveChange 省略時，會出現對話方塊
    • filename := "檔名"
  • WorkBooks.Open "filename" 開啟一個活頁簿
  • Application.Windows 所有活頁簿視窗
  • WorkBooks.Count 活頁簿的數量
  • WorkBooks.Item(Index) 傳回單一活頁簿，由索引值指定


工作表常用屬性：
  • Worksheets.Add [Before, After, Count, Type] 新增工作表
    • Before := Worksheets(n) 出現於某工作表之前
    • After := Worksheets(n) 出現於某工作表之後
    • Count := n 新增工作表數量
    • Type := xlWorksheet (工作表) 或 xlChart (圖表)
  • WorkSheets.Name 工作表名稱
  • WorkSheets("Sheet1").Activate 設定工作表為目前作用的功作表


儲存格常用屬性：
  • Rows.RowHeight 指定範圍內的所有列高
  • Columns.ColumnsWidth：指定範圍內的所欄寬
  • expression.NumberFormatLocal 以本地的數字格式
  • Range.CurrentRegion 目前區域是指以任意空白列及空白欄的組合為邊界的範圍
    範例：
    1. Worksheets("Sheet1").Activate 
    2. ActiveCell.CurrentRegion.Select 
  • expression.Address(RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo) 以參照的方式
    • RowAbsolute 為True，則用列的絕對位址
    • ColumnAbsolute 為True，則用欄的絕對位址
    • ReferenceStyle 預設值為xlA1，如為xlR1C1則為R1C1的表達方式
  • expression.count 傳回範圍的數量(可以是欄數、列數或儲存格數量)
  • expression.Item(RowIndex, ColumnIndex) 代表相對於指定之範圍某個位移距離的範圍。
  • expression.value 傳回或設定物件的值
  • expression.Formula 傳回或設定物件的公式，代表 A1 樣式註解以及巨集語言中的物件公式。
    範例：Worksheets("Sheet1").Range("A1").Formula = "=$A$4+$A$10"
  • expression.FormulaR1C1 傳回或設定物件的公式，並以巨集語言中的 R1C1 樣式標記法表示
    範例：Worksheets("Sheet1").Range("B1").FormulaR1C1 = "=SQRT(R1C1)"
  • expression.Text 傳回或設定物件的文字
    範例：
    1. Set c = Worksheets("Sheet1").Range("B14") 
    2. c.Value = 1198.3 
    3. c.NumberFormat = "$#,##0_);($#,##0)" 
    4. MsgBox c.Value 
    5. MsgBox c.Text 


常用方法：
  • Range.Select方法/Selection屬性 設定目前選取的範圍/使用目前所選取的範圍
    範例：
    1. Sub Macro1() 
    2.    Sheets("Sheet1").Select 
    3.    Range("A1").Select 
    4.    ActiveCell.FormulaR1C1 = "Name" 
    5.    Range("B1").Select 
    6.    ActiveCell.FormulaR1C1 = "Address" 
    7.    Range("A1:B1").Select 
    8.    Selection.Font.Bold = True 
    9. End Sub 
  • expression.Copy 將目前所選取的物件復製至剪貼簿
  • expression.Cut 將目前所選取的物件剪下
  • expression.Delete 將目前所選取的物件刪除
  • expression.Paste 將剪貼簿的內容貼上
    範例：
    1. Sub CopyRow() 
    2.    Worksheets("Sheet1").Rows(1).Copy 
    3.    Worksheets("Sheet2").Select 
    4.    Worksheets("Sheet2").Rows(1).Select 
    5.    Worksheets("Sheet2").Paste 
    6. End Sub 
  • expression.RasteSpecial(Paste,Operation, SkipBlanks, Transpose)
    範例：
    1. With Worksheets("Sheet1") 
    2.    .Range("C1:C5").Copy 
    3.    .Range("D1:D5").PasteSpecial _ 
    4.        Operation:=xlPasteSpecialOperationAdd 
    5. End With 
  • Range.Activate 目前的儲存格
  • Range.Clear 清除資料
  • Range.ClearContents 清除資料內容
  • Range.ClearFormats 清除資料格式
  • Range.ClearComments 清除註解
  • expression.AutoFit 自動調整列高和欄寬
  • Range.FillDown、Range.FillUp、Range.FillLeft、Range.FillRight 填滿
  • Range.Offset(RowOffset, ColumnOffset) 指定區域的位移列與行
    範例：
    1. Sub MoveActive() 
    2.    Worksheets("Sheet1").Activate 
    3.    Range("A1:D10").Select 
    4.    ActiveCell.Value = "Monthly Totals" 
    5.    ActiveCell.Offset(0, 1).Activate 
    6. End Sub 


程式語法：

  • Dim 陳述式(變數)
    1. Dim varname [ As [New] type] 
    2. type 包括 Byte、Boolean、Integer、Long、Single、Double、Date、String、Object等 
    Set 陳述式(物件)
    1. Set objectvar = {[New] objectexpression | Nothing} 
    2. 例：Set RangeA = Range("A1:B2") 
    範例：
    1. Sub Random() 
    2.    Dim myRange As Range 
    3.    Set myRange = Worksheets("Sheet1").Range("A1:D5") 
    4.    myRange.Formula = "=RAND()" 
    5.    myRange.Font.Bold = True 
    6. End Sub 
    With 多種屬性設定
    1. With 物件 
    2.    .屬性1 = 設定值 
    3.    .屬性2 = 設定值 
    4.    .... 
    5. End With 
    範例：
    1. Sub AddNew() 
    2. Set NewBook = Workbooks.Add 
    3.    With NewBook 
    4.        .Title = "All Sales" 
    5.        .Subject = "Sales" 
    6.        .SaveAs Filename:="Allsales.xls" 
    7.    End With 
    8. End Sub 
    Array 陣列
    1. Array(Range1, Range2, ....) 
    範例：
    1. Sub Several() 
    2.    Worksheets(Array("Sheet1", "Sheet2", "Sheet4")).Select 
    3. End Sub 
    InputBox 函數
    1. InputBox("文字說明",[,title][,default][,xpos][,ypos][,helpfile, context]) 
    MsgBox 函數
    1. MsgBox "文字說明" 
    Union 將多個範圍合併成單一Range物件
    1. Union(Range1, Range2, ...) 
    範例：
    1. Sub MultipleRange() 
    2.    Dim r1, r2, myMultipleRange As Range 
    3.    Set r1 = Sheets("Sheet1").Range("A1:B2") 
    4.    Set r2 = Sheets("Sheet1").Range("C3:D4") 
    5.    Set myMultipleRange = Union(r1, r2) 
    6.    myMultipleRange.Font.Bold = True 
    7. End Sub 
    For... Next 陳述式
    1. For counter = start to end [ step stepvalue] 
    2.    [statements] 
    3.    [Exit For] 
    4.    [statements] 
    5. Next [counter] 
    範例：
    1. Sub CycleThrough() 
    2.    Dim Counter As Integer 
    3.    For Counter = 1 To 20 
    4.        Worksheets("Sheet1").Cells(Counter, 3).Value = Counter 
    5.    Next Counter 
    6. End Sub 
    For Each... Next 陳述式
    1. For Each element In group  
    2.    [statements] 
    3.    [Exit For] 
    4.    [statements] 
    5. Next [element] 
    範例：
    1. Sub ApplyColor() 
    2.    Const Limit As Integer = 25 
    3.    For Each c In Range("MyRange") 
    4.        If c.Value > Limit Then 
    5.            c.Interior.ColorIndex = 27 
    6.        End If 
    7.    Next c 
    8. End Sub 
    Do ... Loop 陳述式
    1. Do [{While | Until} condition] 
    2.    [statements] 
    3.    [Exit Do] 
    4.    [statements] 
    5. Loop 
    或
    1. Do 
    2.    [statements] 
    3.    [Exit Do] 
    4.    [statements] 
    5. Loop [{While | Until} condition] 
    If ... Then ... Else ... 陳述式
    1. If condition Then [statements][Else elsestatements] 
    或
    1. If condition Then 
    2.    [statements] 
    3. [ElseIf condition-n Then 
    4.    [elseifstatements]... 
    5. [Else 
    6.    [elsestatements]] 
    7. End If 



範例：(VBA程式範例)
原始碼
  1. Sub pmt_title() 
  2.    Dim rate As Single 
  3.    Dim nper, i As Integer 
  4.    Dim pv, totali, totalp As Double 
  5.    Dim start As Date 
  6.    Dim color1 As Variant 
  7.  
  8.    start = Range("C2").Value 
  9.    pv = Range("C3").Value 
  10.    rate = Range("C4").Value 
  11.    nper = Range("C6").Value 
  12.  
  13.    '清除所有有明細表 
  14.    Range("A11:E65536").Clear 
  15.  
  16.    With Cells(11, 1) 
  17.        .Value = 0 
  18.        .HorizontalAlignment = xlCenter 
  19.        .Interior.Color = RGB(255, 255, 255) 
  20.    End With 
  21.  
  22.    With Cells(11, 2) 
  23.        .Value = start 
  24.        .HorizontalAlignment = xlCenter 
  25.        .NumberFormat = "ge年mm月dd日" 
  26.    End With 
  27.  
  28.    Cells(11, 5) = pv 
  29.    pv1 = pv 
  30.  
  31.    For i = 1 To nper 
  32.        If i Mod 2 = 1 Then 
  33.            color1 = RGB(255, 255, 150) 
  34.        Else 
  35.            color1 = RGB(255, 255, 255) 
  36.        End If 
  37.  
  38.        With Cells(11 + i, 1) 
  39.            .Value = i 
  40.            .HorizontalAlignment = xlCenter 
  41.            .Interior.Color = color1 
  42.        End With 
  43.  
  44.        With Cells(11 + i, 2) 
  45.            .Value = DateAdd("m", i, start) 
  46.             .HorizontalAlignment = xlCenter 
  47.             .Interior.Color = color1 
  48.            .NumberFormatLocal = "ge年mm月dd日" 
  49.        End With 
  50.  
  51.        With Cells(11 + i, 3) 
  52.            .Value = -IPmt(rate / 12, i, nper, pv) 
  53.            .Interior.Color = color1 
  54.            .NumberFormat = "_-$* #,##0.00_-" 
  55.        End With 
  56.        totali = totali + Cells(11 + i, 3) 
  57.  
  58.        With Cells(11 + i, 4) 
  59.            .Value = -PPmt(rate / 12, i, nper, pv) 
  60.            .Interior.Color = color1 
  61.            .NumberFormat = "_-$* #,##0.00_-" 
  62.        End With 
  63.        totalp = totalp + Cells(11 + i, 4) 
  64.  
  65.        With Cells(11 + i, 5) 
  66.            .Value = pv - totalp 
  67.            .Interior.Color = color1 
  68.            .NumberFormat = "_-$* #,##0.00_-" 
  69.        End With 
  70.    Next i 
  71.  
  72.  
  73.    With Range(Cells(10, 1), Cells(11 + nper, 5)).Borders 
  74.        .LineStyle = xlContinuous 
  75.        .Weight = xlThin 
  76.        .Color = RGB(0, 0, 0) 
  77.    End With 
  78.    Cells(12 + nper, 1) = "合計" 
  79.  
  80.    With Range(Cells(12 + nper, 1), Cells(12 + nper, 2)) 
  81.        .MergeCells = True 
  82.        .HorizontalAlignment = xlCenter 
  83.        .Interior.Color = RGB(255, 200, 255) 
  84.    End With 
  85.  
  86.    With Cells(12 + nper, 3) 
  87.        .Value = totali 
  88.        .Interior.Color = RGB(255, 200, 255) 
  89.        .NumberFormat = "_-$* #,##0.00_-" 
  90.    End With 
  91.  
  92.    With Cells(12 + nper, 4) 
  93.        .Value = totalp 
  94.        .Interior.Color = RGB(255, 200, 255) 
  95.        .NumberFormat = "_-$* #,##0.00_-" 
  96.    End With 
  97.  
  98.    With Range(Cells(12 + nper, 1), Cells(12 + nper, 4)).Borders 
  99.        .LineStyle = xlContinuous 
  100.        .Weight = xlThin 
  101.        .Color = RGB(0, 0, 0) 
  102.    End With 
  103.  
  104. End Sub 
  105.  
  106. '=================================================================== 
  107. Sub clearall() 
  108.    Range("A11:E65536").Clear 
  109. End Sub 
分類： , , 1 回應
訂閱： 文章 (Atom)