1 1 Excel VBA 說明 ( ) (_) STEP4 Excel 2 STEP5 A1 1 B2 2 C3 3 STEP6 A1 STEP7 > > 1-11

1-3 1-3-1 Excel VBA VBA OK CD DVD Excel VBA Excel VBA Excel Visual Basic A1 1 B2 2 C3 3 STEP1 Excel Ch01_VBA.xlsm 1 > > STEP2 Excel 1 2 STEP3 1-10

1 1 Excel VBA 說明 ( ) (_) STEP4 Excel 2 STEP5 A1 1 B2 2 C3 3 STEP6 A1 STEP7 > > 1-11

說明 Excel VBA 1 2 3 VBA 1-12

1-3-2 Excel VBA VBA Excel VBA STEP1 2 > > 1 Excel VBA STEP2 1 2 1-13

STEP3 Excel 2 A1 B2 C3 1 2 3 VBA > > Visual Basic + Visual Basic 1-14

1 Excel VBA STEP1 VBAProjcet STEP2 Module1 1 2 3 1-15

1-3-3 VBA VBA STEP1 Excel > > Visual Basic STEP2 Visual Basic > 1-16

STEP3 Microsoft Excel Excel 2 1 Excel VBA STEP4 Excel 3 > > STEP5 1 2 1 3 1-17

5 VB r 1-18

Chapter4 Excel VBA 4-1 處理 Excel 活頁簿 4-2 處理 Excel 工作表 4-3 儲存格的參照位址 4-4 Excel VBA 中儲存格的定義 4-5 選取儲存格範圍的技巧 4-6 應用程式物件集合 4-7 事件程序的重要應用

Excel VBA Excel Excel VBA 4-1 Excel Excel Excel VBA 4-1-1 Excel VBA Workbooks Workbooks Excel *.xls *. xlsx Add 範例 Sub ( ) Workbooks.Add 4-2

VBA VBA D:\ Excel VBA 4 範例 Sub _ () Dim wkb1 As Workbook Dim wkb2 As Workbook Set wkb1 = Workbooks.Add(Template:="D:\Test_.xlsx") Set wkb2 = Workbooks.Add(Template:="D:\Test_.xltx") Workbooks.Add ("D:\TAATCL\Test_.xlsx") 4-3

*.xlsx *.xltx VBA Test_ 1.xls Test_ 2.xls Test_ 3.xls VBA 範例 Sub Test( ) Workbooks.Open("D:\Test_.xlsx") 說明 範例 Sub Test( ) Dim Wkb = As Workbook Set Wkb = Workbooks.Open(FileName:= "D:\Test_.xlsx") VBA.Name.FullName 範例 4-4 Sub () Dim wkb As Workbook Set wkb = Workbooks.Open("D:\Test_.xlsx") MsgBox wkb.name MsgBox wkb.fullname MsgBox wkb.path

說明 Path \ Path \ Excel Excel VBA Excel VBA 4 範例 Sub ( ) Workbooks("Ch04_VBA.xlsm").Activate 4-1-2 Excel VBA VBA 範例 Sub () Dim Dim As String 4-5

= "D:\TAATCL\Test_.xlsx" If Dir( ) <> "" Then = True If Then MsgBox & " " Else MsgBox & " " End If VBA ChDir CurDir GetFile FileExists GetFolder Microsoft Visual Basic for Applications Sub () Dim wkb As Workbook Set wkb = Workbooks.Open("D:\TAATCL\Test_.xlsx") wkb.saveas Filename:="D: TAATCL\ANT Test_.xlsx" 4-6

Sub Test( ) ActiveWorkbook.Save Excel VBA ActiveWorkbook.Close 關閉檔案, 並出現對話方塊, 詢問您是否要儲存檔案 ActiveWorkbook.Close SaveChanges:=True 直接關閉檔案並儲存 ActiveWorkbook.Close SaveChanges:=False 直接關閉檔案, 不要儲存 Excel VBA 4 4-1-3 Microsoft Excel VBA Excel > > Excel VBA Windows (1) 1 Excel VBA Windows (" ") 4-7

5 1 VBA 4-8

Excel VBA Sub () Workbooks("Test_.xlsx").Activate Windows("Ch04_VBA.xlsm").Activate / ' ' Excel VBA WindowState Sub () Windows(1).WindowState = xlmaximized Windows(2).WindowState = xlminimized Windows(2).WindowState = xlnormal ' ' ' Excel VBA 4 Excel VBA Sub ( ) Windows(1).close Windows("Test_.xlsx").close Workbooks("Ch04_VBA.xlsm").close ' ' ' 4-9

4-2 Excel Excel VBA Sheets Charts 4-2-1 Excel 2016 1 2 3 > > > 2 1 Excel VBA 4-10

Excel Excel VBA Sub () Worksheets(" ").Activate Sheets(" ").Select Charts(" ").Select VB Name Excel VBA 4 Name Name 4-11

Excel VBA Excel Worksheets (1) Worksheets (Worksheets.Count) Sub A() Worksheets(1).Select MsgBox ActiveWorkbook.Worksheets(2).Name Sub B() For K = 1 To 4 MsgBox ActiveWorkbook.Worksheets(K).Name & _ " =" & ActiveWorkbook.Worksheets(K).Index Next K 4-12

1 Excel VBA 4 1 2 3 4-4-4 4 VB Sheet2 Sheet3 Sheet4 Sheet6 4-4-4 說明 Sheets WorkSheets 4-13

Excel VBA Sub () Dim As Worksheet For Each In Worksheets If.Name = " " Then = True Next If = True Then MsgBox "*** ***" Else MsgBox " " End If For Each Next 4-2-2 Excel VBA Select Activate Sub ( ) Sheets(" ").Select Sheets(" ").Activate 4-14

4-2-3 > > > > > > > > Excel VBA 4 2 3 4 1 4-15

1 4 2 5 3 Excel VBA > > > > VBA Sub () Sheets(" ").Visible = False Sheets(" ").Visible = True Sheets(" ").Visible = xlveryhidden ' ' ' 4-16

說明 Sheets(" ").Visible = True ' Visible -1 0 2 1 2 Excel VBA 4 4-2-4 Excel Excel VBA Worksheet Copy Move Add Delete Copy Move Add Delete Sub () Worksheets(" 2").Copy after:=worksheets(1) Sheets(" 2").Move before:=sheets(1) Sheets.Add Worksheets(1).Delete 4-17

2 2 2 4-18

Excel VBA 4 Excel VBA Sub Test( ) Worksheets(Array(1,2,3)).Select Excel VBA Sub Test( ) Worksheets(Array(1,3,5)).Select 說明 Array(x,y,z) x,y,z > > Excel VBA 4-19

Sub ( ) Sheets(" ").Select Range("A2:E4").Locked = True ActiveSheet.Protect Password:="abc123",DrawingObjects:=True, _ Contents:=True,Scenarios:=True, _ UserInterfaceOnly:=True A2:E4 > > 4-20

說明 4-3 Excel VBA 4-3-1 Excel B 5 B5 Excel VBA 4 $ D4 F6 4-21

A1 D4 =E22+B27 D4 2 E22 3 B27 R1C1 R C R4C4=R[-2]C[1]+R[3]C[-2] (-) (+) D19 25 F21 370 D19 =E17+B22 F21 =G24+D29 $A1 A$1 4-22

D34 =E$32+B$37 F36 =G$32+D$37 4-3-2 R1C1 A1 A1 R1C1 VBA Excel VBA 4 C4:E8 * 3 C$3 B $B4 R1C1 C4:E8 =R3C*RC2 R3C 3 RC2 B 4-23

Excel VBA R1C1 A1 Formula A1 FormulaR1C1 R1C1 A1 1 1 R1C1 Formula 4-24

範例 R1C1 A1 Sub R1C1_A1 1() Sheets(" 2").Select Range("C3").Formula = "= A2 * B3" Range("C3").Select MsgBox ActiveCell.Formula & " " & ActiveCell.Value MsgBox ActiveCell.FormulaR1C1 & " " & ActiveCell.Value Sheets(" 2").Select Range("A1:D10").Select Range("C3").Select Range("C3").Range("D3").Select Excel VBA 4 Range("A1:D10").Select Range("C3").Select A1:D10 C3 Range A1 Range("C3").Range("D3").Select F4 Range("D2") C3 C 4 2 Range("F4").Range("A1").Select ActiveCell.Range("A1").Select 4-25

4-5-5 Excel Excel VBA Excel VBA 4 Sub () Sheets(" ").Select Range("C3").Select Selection.SpecialCells(xlCellTypeFormulas).Select 4-43

說明 SpecialCells XlCellType xlcelltypeallformatconditions xlcelltypeallvalidation xlcelltypeblanks xlcelltypecomments xlcelltypeconstants xlcelltypeformulas xlcelltypelastcell xlcelltypesameformatconditions xlcelltypesamevalidation xlcelltypevisible SpecialCells VBA 4-5-6 Excel VBA Columns Rows Sub () Columns("B:B").Select Columns("D:F").Select Rows("8:8").Select Rows("3:5").Select Range("a1").Value = Rows.Count 4-44

如 果 要 選 取 不 連 續 的 欄 或 列 儲 存 格 範 圍 可 以 配 合 Union 方 法 下 表 是 Columns 與 Rows 屬性常用到的參照方式 參照 含義 工作表的第 1 欄 Columns(3) 工作表的第 3 欄 Columns 工作表上的所有欄位 Rows(2) 工作表的第 2 列 Rows 工作表上的所有列數 4 4-5-7 Union Intersect 與 Areas Excel VBA 對於不連續的儲存格範圍 有許多屬性或方法無法執行 此時 可以使用 Union 方法 若要在多重範圍中尋找相互重疊的儲存格 則可以使用 Intersect 方法 Sub 聯集與交集 () Dim 聯集 As Range, 交集 As Range, 我不連續 As Range Set 聯集 = Union(Range("B2:C6"), Range("E2:F3")) 聯集.Interior.Color = vbgreen Set 交集 = Intersect(Range("B2:C6"), Range("C3:D6")) 交集.Interior.Color = vbblue 常用的程式 Excel VBA Columns("A") 執行結果 說明 若要針對多重範圍 分別執行設定或編輯工作 可先使用 Areas 屬性傳回集合物 件 再使用 For Each Next 陳述式處理 這部份的操作請參考 3-3-3 節 4-45 ACI0288_Excel2016VBA.indb 45 2016/4/25 下午 04:04:14

4-7 VBA Excel VBA 說明 VB 4-7-1 Excel VBA 4 STEP1 ThisWorkbook Workbook 2 1 4-49

STEP2 Activate STEP3 STEP4 Excel 4-50

4-7-2 STEP1 Sheet4( ) Worksheet 2 1 Excel VBA 4 STEP2 BeforeRightClick STEP3 4-51