轉載自：
EXCEL 巨集與VBA介紹
巨集：一連串的執行指令所構成，可以利用Visual Basic程式指令、也可以利用錄製巨集的方式來錄寫指令。
如何錄製巨集：
- 如果要執行巨集，則需要更改「EXCEL選項」\「信任中心」\「信任中心設定」\「巨集設定」
- 在「檢視」、「巨集」/「錄製巨集」
- 設定「巨集名稱」、快速鍵(Ctrl+英文鍵)，將巨集儲存位置
- 開始錄製相關動作(錄製是以絕對位址方式來錄製，如果要以相對位址來錄製則要選「以相對位置錄製」)
- 停止錄製
- 查看巨集程式碼，並作必要的修正
- 執行巨集(可以利用「執行巨集」或快速鍵、或利用表單按鈕來執行)，如果要編修表單時，可以按下Ctrl+該物件，進行修改。
範例：(錄製巨集)
- C6至C12的數值格式設定「"進貨" #,##0;"出貨" #,##0」
- 「檢視」、「巨集」、「開始錄製」，並開始執行下列指令
- 選取範圍C6至C12，並執行「複製」
- 選取範圍B6至B12，並按下「選擇性貼上」，選擇貼上「值」與運算「加」
- 選取範圍C6至C12，並按下「Del」，清除儲存格內容
- 在儲存格C6按一下
- 停止錄製巨集
- 在工作表中，產生一個按鈕，並指定該按鈕執行該巨集，並將其按鈕文字改為異動
- 每次輸入異動資料(正的表示進貨，負的表示出貨)，按下按鈕即可執行巨集
VBA簡介：
Visual Basic for Applications，利用VB來延申Office的能力。開啟EXCEL 顯示開發人員(在「EXCEL選項」/「常用」中勾選)，再撰寫或修改VBA程式。
VBA主要的組成要件：物件，其中包括
- 屬性：對物件狀態的描述，可以定義物件的特性(大小、顏色、狀態等)。
- 方法：物件的某些特定動作，可以指定動作的細別內容。其主要結構如下：
物件.方法 指定引數1:=xl常數1, 指定引數2:=xl常數2,....
指定引數設定為某些內建常數，每個內建常數前會有前綴字連接。
- EXCEL物件的常數會以xl開始。
- VB的陳述式及函數的常數會以vb開始。
- Office物件模式的常數會以mso開始。
- 事件：物件的觸發反應。
EXCEL常用的物件
Workbook 活頁簿Workbooks 活頁簿集合Workbooks("filename") 檔名為filename的活頁簿ActiveWorkbook 正在作用中的活頁簿Sheets 活頁簿中所有工作表Sheets(n) 活頁簿中第n張工作表Worksheet 工作表Worksheets 所有工作表(包括圖表)Worksheets("sheet") 指表名為sheet工作表ActiveSheet 正在作用中的工作表Columns("c1:c2") c1至c2欄(其中c1,c2為A~Z或AA~XFD等欄名)Rows("r1:r2") r1至r2列(其中r1,r2為1~1048576等列名Range("x1:x2") x1至x2間的儲存格(其中x1,x2為儲存格位址名稱)cells(i,j) 儲存格(第i列、第j行)ActiveCell 目前的儲存格Selection 目前所選取的物件
範例：
Workbooks("Book1").Sheets("Sheet1").Range("A1:D5").Font.Bold = True
Worksheets("Sheet1").Cells.ClearContents
Worksheets("Sheet1").Rows(1).Font.Bold = True
Range("1:1,3:3,8:8")
Worksheets("Sheet1").Cells(6, 1).Value = 10
Worksheets("Sheet1").[A1:B5].ClearContents
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 目前區域是指以任意空白列及空白欄的組合為邊界的範圍
範例：Worksheets("Sheet1").Activate
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 傳回或設定物件的文字
範例：Set c = Worksheets("Sheet1").Range("B14")
c.Value = 1198.3
c.NumberFormat = "$#,##0_);($#,##0)"
MsgBox c.Value
MsgBox c.Text
常用方法：
Range.Select方法/Selection屬性 設定目前選取的範圍/使用目前所選取的範圍
範例：Sub Macro1()
Sheets("Sheet1").Select
Range("A1").Select
ActiveCell.FormulaR1C1 = "Name"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Address"
Range("A1:B1").Select
Selection.Font.Bold = True
End Sub
expression.Copy 將目前所選取的物件復製至剪貼簿expression.Cut 將目前所選取的物件剪下expression.Delete 將目前所選取的物件刪除expression.Paste 將剪貼簿的內容貼上
範例：Sub CopyRow()
Worksheets("Sheet1").Rows(1).Copy
Worksheets("Sheet2").Select
Worksheets("Sheet2").Rows(1).Select
Worksheets("Sheet2").Paste
End Sub
expression.RasteSpecial(Paste,Operation, SkipBlanks, Transpose)
範例：With Worksheets("Sheet1")
.Range("C1:C5").Copy
.Range("D1:D5").PasteSpecial _
Operation:=xlPasteSpecialOperationAdd
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) 指定區域的位移列與行
範例：Sub MoveActive()
Worksheets("Sheet1").Activate
Range("A1:D10").Select
ActiveCell.Value = "Monthly Totals"
ActiveCell.Offset(0, 1).Activate
End Sub
程式語法：
- Dim 陳述式(變數)
Dim varname [ As [New] type]
type 包括 Byte、Boolean、Integer、Long、Single、Double、Date、String、Object等
- Set 陳述式(物件)
Set objectvar = {[New] objectexpression | Nothing}
例：Set RangeA = Range("A1:B2")範例：Sub Random()
Dim myRange As Range
Set myRange = Worksheets("Sheet1").Range("A1:D5")
myRange.Formula = "=RAND()"
myRange.Font.Bold = True
End Sub
- With 多種屬性設定
With 物件
.屬性1 = 設定值
.屬性2 = 設定值
....
End With範例：Sub AddNew()
Set NewBook = Workbooks.Add
With NewBook
.Title = "All Sales"
.Subject = "Sales"
.SaveAs Filename:="Allsales.xls"
End With
End Sub
- Array 陣列
Array(Range1, Range2, ....)
範例：Sub Several()
Worksheets(Array("Sheet1", "Sheet2", "Sheet4")).Select
End Sub
- InputBox 函數
InputBox("文字說明",[,title][,default][,xpos][,ypos][,helpfile, context])- MsgBox 函數
MsgBox "文字說明"
- Union 將多個範圍合併成單一Range物件
Union(Range1, Range2, ...)
範例：Sub MultipleRange()
Dim r1, r2, myMultipleRange As Range
Set r1 = Sheets("Sheet1").Range("A1:B2")
Set r2 = Sheets("Sheet1").Range("C3:D4")
Set myMultipleRange = Union(r1, r2)
myMultipleRange.Font.Bold = True
End Sub
- For... Next 陳述式
For counter = start to end [ step stepvalue]
[statements]
[Exit For]
[statements]
Next [counter]範例：Sub CycleThrough()
Dim Counter As Integer
For Counter = 1 To 20
Worksheets("Sheet1").Cells(Counter, 3).Value = Counter
Next Counter
End Sub
- For Each... Next 陳述式
For Each element In group
[statements]
[Exit For]
[statements]
Next [element]範例：Sub ApplyColor()
Const Limit As Integer = 25
For Each c In Range("MyRange")
If c.Value > Limit Then
c.Interior.ColorIndex = 27
End If
Next c
End Sub
- Do ... Loop 陳述式
Do [{While | Until} condition]
[statements]
[Exit Do]
[statements]
Loop或Do
[statements]
[Exit Do]
[statements]
Loop [{While | Until} condition]- If ... Then ... Else ... 陳述式
If condition Then [statements][Else elsestatements]
或
If condition Then
[statements]
[ElseIf condition-n Then
[elseifstatements]...
[Else
[elsestatements]]
End If
範例：(VBA程式範例)
Sub pmt_title()
Dim rate As Single
Dim nper, i As Integer
Dim pv, totali, totalp As Double
Dim start As Date
Dim color1 As Variant
start = Range("C2").Value
pv = Range("C3").Value
rate = Range("C4").Value
nper = Range("C6").Value
'清除所有有明細表
Range("A11:E65536").Clear
With Cells(11, 1)
.Value = 0
.HorizontalAlignment = xlCenter
.Interior.Color = RGB(255, 255, 255)
End With
With Cells(11, 2)
.Value = start
.HorizontalAlignment = xlCenter
.NumberFormat = "ge年mm月dd日"
End With
Cells(11, 5) = pv
pv1 = pv
For i = 1 To nper
If i Mod 2 = 1 Then
color1 = RGB(255, 255, 150)
Else
color1 = RGB(255, 255, 255)
End If
With Cells(11 + i, 1)
.Value = i
.HorizontalAlignment = xlCenter
.Interior.Color = color1
End With
With Cells(11 + i, 2)
.Value = DateAdd("m", i, start)
.HorizontalAlignment = xlCenter
.Interior.Color = color1
.NumberFormatLocal = "ge年mm月dd日"
End With
With Cells(11 + i, 3)
.Value = -IPmt(rate / 12, i, nper, pv)
.Interior.Color = color1
.NumberFormat = "_-$* #,##0.00_-"
End With
totali = totali + Cells(11 + i, 3)
With Cells(11 + i, 4)
.Value = -PPmt(rate / 12, i, nper, pv)
.Interior.Color = color1
.NumberFormat = "_-$* #,##0.00_-"
End With
totalp = totalp + Cells(11 + i, 4)
With Cells(11 + i, 5)
.Value = pv - totalp
.Interior.Color = color1
.NumberFormat = "_-$* #,##0.00_-"
End With
Next i
With Range(Cells(10, 1), Cells(11 + nper, 5)).Borders
.LineStyle = xlContinuous
.Weight = xlThin
.Color = RGB(0, 0, 0)
End With
Cells(12 + nper, 1) = "合計"
With Range(Cells(12 + nper, 1), Cells(12 + nper, 2))
.MergeCells = True
.HorizontalAlignment = xlCenter
.Interior.Color = RGB(255, 200, 255)
End With
With Cells(12 + nper, 3)
.Value = totali
.Interior.Color = RGB(255, 200, 255)
.NumberFormat = "_-$* #,##0.00_-"
End With
With Cells(12 + nper, 4)
.Value = totalp
.Interior.Color = RGB(255, 200, 255)
.NumberFormat = "_-$* #,##0.00_-"
End With
With Range(Cells(12 + nper, 1), Cells(12 + nper, 4)).Borders
.LineStyle = xlContinuous
.Weight = xlThin
.Color = RGB(0, 0, 0)
End With
End Sub
'===================================================================
Sub clearall()
Range("A11:E65536").Clear
End Sub
