Excel VBA Excel Visual Basic for Application

Size: px

Start display at page:

Download "Excel VBA Excel Visual Basic for Application"

韬程隗
6 years ago
Views:

1 Excel VBA Jun5,00

2 Sub 分頁 () Dim i As Integer Dim Cname As String Dim Code As Variant Set score=thisworkbook.sheets("sheet") Code=Array(" 專北一 "," 專北二 "," 專北三 "," 專桃園 "," 專桃竹 "," 專中苗 ", " 專台中 "," 專台南 "," 專高一 "," 專高屏 "," 專東台 ") For i= to Step - Cname=code(i-) Sheets.Add after:=score ActiveSheet.Name=Cname score.activate Range("A").Select Range("A").AutoFilter field:=, Criteria:=Cname Range("A:G0").SpecialCells(xlCellTypeVisible).Copy Destination:=Worksheets(Cname).Range("A") Worksheets(Cname).Activate Cells.EntireColumn.AutoFit Next End Sub

3 程序種類 Function Event Property

4 程序種類 Function Event Function 優利貸 (x) Function 平方和 (a,b) x=a*a y=b*b 平方和 =x+y End Fuction Property

5 程序種類 Function Event Sub 分頁 () Sub SayHello() Msgbox "Hello!" End Sub Property

6 程序種類 Function Event Property Property Something()... End Property

7 宣告變數語法 Dim 變數名稱 As 資料類型資料型態 String Integer, Long, Single, Double Boolean 其他 (Date,Currency,Object,Variant) ex:dim i, j, k As Integer

8 宣告常數語法 Const 常數名稱 = 表示式 Const 常數名稱 As 資料類型 = 表示式 ex: Const i= Const 圓周 As Integer=(/7)**R Const i As Integer=, j As String=" 端午節 " i=

9 宣告陣列語法 Dim 陣列名稱 ( 行數, 列數 )As 資料類型 ex: AA(,7) As Integer Code(0) As String CC() As Single Note 陣列變數大小以 0 為起算的基底數細, 所以 Code(0) As String 代表從 0 到 0, 共有個元素

10 流程控制決策控制 GoTo 強制改變流程

11 流程控制決策控制單一選擇結構雙向選擇結構巢狀結構 4 多向選擇 SelectCase GoTo 強制改變流程 If A>B Then A=B+0 If A>B Then A=A+0 B=0 End If

12 流程控制決策控制單一選擇結構雙向選擇結構巢狀結構 4 多向選擇 SelectCase GoTo 強制改變流程 If A>B Then A=A+0 Else B=B+0 If MyAge>0 Then MsgBox " 可以抽菸 " Else MsgBox " 抓起來關 " End If

13 流程控制決策控制單一選擇結構雙向選擇結構巢狀結構 4 多向選擇 SelectCase GoTo 強制改變流程 If 分數 >=90 Then 考績 =" 優 " ElseIf 分數 >=80 Then 考績 =" 甲 " ElseIf 分數 >=70 Then 考績 =" 乙 " Else 考績 =" 丙 " End If MsgBox 考績

14 流程控制決策控制單一選擇結構雙向選擇結構巢狀結構 4 多向選擇 SelectCase GoTo 強制改變流程 Select Case 分數 Case 90 to 00 Range("A")=" 優 " Case 80 to 89 Range("A")=" 甲 " Case 70 to 79 Range("A")=" 乙 " Case Else Range("A")=" 丙 " End Select

15 流程控制決策控制 GoTo 強制改變流程 GoTo F F0: FontSize=0 Range("A")=" 字型 0pt" GoTo F6 F: FontSize= Range("A")=" 字型 pt" GoTo F0 F6: FontSize=6 Range("A")=" 字型 6pt"

16 For...Next ForEach...Next Do...Loop 4 Exit DoWhile...Loop Do...LoopWhile DoUntil...Loop Do...LoopUntil

17 For...Next ForEach...Next Do...Loop 4 Exit DoWhile...Loop Do...LoopWhile DoUntil...Loop Do...LoopUntil Sub ForNext() Dim i, j As Integer j=0 For i= to 0 j=j+i Next MsgBox j End Sub

18 For...Next ForEach...Next Do...Loop 4 Exit DoWhile...Loop Do...LoopWhile DoUntil...Loop Do...LoopUntil Sub ForNext() Dim k As Integer For k=0 to step - Cells(k,k)=k Next End Sub

19 For...Next ForEach...Next Do...Loop 4 Exit DoWhile...Loop Do...LoopWhile DoUntil...Loop Do...LoopUntil Sub ForEach() For Each Worksheet In Worksheets Worksheet.Activate Range("A") = 5 Next End Sub

20 For...Next ForEach...Next Do...Loop 4 Exit DoWhile...Loop Do...LoopWhile DoUntil...Loop Do...LoopUntil Sub DoLoop() i= Do While i<=0 Cells(i,)=i i=i+ Loop End Sub

21 For...Next ForEach...Next Do...Loop 4 Exit DoWhile...Loop Do...LoopWhile DoUntil...Loop Do...LoopUntil Sub DoLoop() i= Do Cells(i,)=i i=i+ Loop While i<=0 End Sub

22 For...Next ForEach...Next Do...Loop 4 Exit DoWhile...Loop Do...LoopWhile DoUntil...Loop Do...LoopUntil Sub DoLoop() i= Do Until i= Cells(i,)=i i=i+ Loop End Sub

23 For...Next ForEach...Next Do...Loop 4 Exit DoWhile...Loop Do...LoopWhile DoUntil...Loop Do...LoopUntil Sub DoLoop4() i= Do Cells(i,4)=i i=i+ Loop Until i= End Sub

24 For...Next ForEach...Next Do...Loop 4 Exit DoWhile...Loop Do...LoopWhile DoUntil...Loop Do...LoopUntil Exit Do Exit For Exit Sub

25 活頁簿 Workbooks.Add Workbooks.Open("C:/Surpro/test.xls") Workbooks("test.xls").Activate ActiveWorkbook.Save AcitveWorkbok.Close

26 工作表 WorkSheets("Sheet").Activate Sheets("Sheet").Copy After:=sheets("Sheet") Sheets("Sheet").Move Before:=Sheets("Sheet") Sheets.Add Worksheets("Sheet").Delete Sheets(" 周報 ").Visible=False Sheets(" 周報 ").Visible=xlVeryHidden

27 儲存格 Range("A")=45 Range("A:C")=45 Range("B:B")=45 Range(":7")=45 Range("B:C5,D6:E9")=45 Cells()=45 Cells(,)=45 Cells(,"C")=45 Cells(i,j)=45 Range(Cells(,),Cells(5,))=45

28 其他篩選 :AutoFilter 排序 :Sort 最適大小 : AutoFit 複製特殊可見儲存格 : SpecialCells(xlCellTypeVisible).Copy

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