Transcription of 第一個Excel VBA 程式 - epaper.gotop.com.tw
1 Excel VBA Excel VBA Excel VBA VBA Excel VBA Excel VBA 2-2 Excel VBA Excel VBA Excel VBA (Object) .. (Attribute) (Class) Excel Application(Excel ) Workbooks( ) Worksheets( ) Charts( ) Range( ) Cells ( ) Application Workbook Worksheet Range Workbooks( ) Worksheets( ) Excel VBA 2-6 . Cells Cells [R1C1] Cells ( ) B4 4 2(A 1 B ) Cells(4, 2) ' Range("B4") For Next Cells For Each Next Range Excel VBA FileName B1 Excel VBA 2-7.
2 1. 1 2. First Excel Excel . 1. 2. (M) Time (A) VBA Module1 Time 1234 Excel VBA 2-8 . 1. Sub Time() <Tab> range( Range range 2. range("B1"). <Tab> (IntelliSense) 3. range("B1").Value=now() .. 1 Excel VBA 2-9 4. "Range("B1").Value = Now()" Now() B1 B1 5. FileName: ( Module1 ) 01 Sub Time() 02 Range("B1").Value = Now() 03 End Sub.)
3 1. 2. Time 3. Command1 . 1. Clear Cells B1 [ ] Value ""( ) Excel VBA 2-10 Excel VBA Excel VBA Excel VBA Excel VBA (Visual Basic Editor) VBA <Alt> + <F11> Excel VBA . VBA Excel VBA 2-11 . VBA 1. Excel Excel 2. ( ) ( ) ( ) ( ) 3. (UserForm) 4. 5. Excel (Project) 1.
4 Excel Microsoft Excel Microsoft Excel ThisWorkbook( ) (WorkSheet) Excel VBA 2-12 (Module) Module1 Module2 ( UserForm) 2. () Name 1( 1) 3. ( ) .. ( ) .. 2 Excel 2 ( ) Excel Excel VBA Excel VBA 14-2 Excel Excel 1. ( ) Chart ChartObject ChartObject Chart ChartObject ChartObjects ChartObject 2.
5 Chart ChartObject Charts Sheets Charts 1. ChartObjects Add . (Left, Top, Width, Height) Left To p Width Height Chart 14-3 (points) 1 point = 1/72 ChartObjects Worksheets(1).ChartObjects(1) 1 Name ActiveChart < 1> (50, 100) 300 200 Dim co As ChartObject : Dim ch As Chart Set co = Worksheets(" 1"). (50, 100, 300, 200) Set ch = < 1> For Each co In Worksheets(" 1").ChartObjects Next 2. Charts Add ([Before ] [, After ] [, Count ]) Add Before After Count Charts (1) Excel VBA 14-4 Chart1 ("Chart1") ActiveChart Dim ch As Chart Set ch = (Before:=Worksheets( )) For i = 1 To (i).
6 Activate Next Add SetSourceData .SetSourceData(Source, [PlotBy]) 1. Source 2. PlotBy xlRows( ) xlColumns( ) 3. ch 1 A1:E4 Source:= Worksheets(" 1").Range("A1:E4"), PlotBy:=xlRows Chart 14-5 FileName A1:F5 A1:F5 300 200 ( ) ( ) ( ) 3 2 1 1~ 3 Excel VBA 14-6 1. AddChart 2. < 1> ActiveX 1. > 0 Delecte ws 2. Add ChartObject SetSourceData A1:F5 FileName: ( 1 ) 01 Private Sub cmdAdd_Click() 02 Dim ws As Worksheet ' ws 03 Dim co As ChartObject ' co ChartObject 04 Dim ch As Chart ' ch 05 Set ws = (" 1") ' ws 1 06 If > 0 Then ' ChartObject >0 07 ' 08 End If 09 ' A6 300 200 10 Set co = ( ("A6").)
7 Left, ("A6").Top, 300, 200) 11 Set ch = ' ch co 12 Source:= ("A1:F5") ' ch A1:F5 13 End Sub cmdAdd Chart 14-7 1. ChartType ChartType xlColumnClustered ( ) C ch = xlLine ch If = xlPie Then MsgBox " " Excel VBA 14-8 2. HasTitle/ChartTitle HasTitle True HasTitle True ChartTitle ChartTitle Text Left Top Font Interior ch = True = " " FileName xlColumnClustered( ) xl3 DBarClustered( ) xl3 DColumn( ) xlLine( ) Chart 14-9 1.
8 ChartType 2. < 1> ActiveX 1. Workbook_Open 2. Array aryType aryType ChartType aryType( ) 3. HasTitle True Text cboType txtTitle cmdAdd Excel VBA 14-10 FileName: (ThisWorkbook ) 01 Private Sub Workbook_Open() 02 Dim aryType As Variant 03 aryType = Array("xlColumnClustered", "xl3 DBarClustered", "xl3 DColumn", "xlLine") 04 With Sheets(" 1") 05 . = aryType ' 06 . = 0 ' 07 . = " " 08 End With 09 End Sub FileName: ( 1 ) 01 Private Sub cmdAdd_Click() 02 Dim ws As Worksheet ' ws 03 Dim ch As Chart ' ch 04 Set ws = (" 1") ' ws 1 05 If > 0 Then ' Charts >0 06 ' 07 End If 08 Set ch = 09 Dim aryType As Variant 10 aryType = Array(xlColumnClustered, xl3 DBarClustered, xl3 DColumn, xlLine) 11 Source:= ("A1:F5") ' ch A1:F5 12 = aryType( ) ' ChartType 13 = True ' 14 = ' Text 15 End Sub
