Excel VBA Jun5,00
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
程序種類 Function Event Property
程序種類 Function Event Function 優利貸 (x) Function 平方和 (a,b) x=a*a y=b*b 平方和 =x+y End Fuction Property
程序種類 Function Event Sub 分頁 () Sub SayHello() Msgbox "Hello!" End Sub Property
程序種類 Function Event Property Property Something()... End Property
宣告變數 語法 Dim 變數名稱 As 資料類型 資料型態 String Integer, Long, Single, Double Boolean 其他 (Date,Currency,Object,Variant) ex:dim i, j, k As Integer
宣告常數 語法 Const 常數名稱 = 表示式 Const 常數名稱 As 資料類型 = 表示式 ex: Const i= Const 圓周 As Integer=(/7)**R Const i As Integer=, j As String=" 端午節 " i=
宣告陣列 語法 Dim 陣列名稱 ( 行數, 列數 )As 資料類型 ex: AA(,7) As Integer Code(0) As String CC() As Single Note 陣列變數大小以 0 為起算的基底數細, 所以 Code(0) As String 代表從 0 到 0, 共有 個元素
流程控制 決策控制 GoTo 強制改變流程
流程控制 決策控制 單一選擇結構 雙向選擇結構 巢狀結構 4 多向選擇 SelectCase GoTo 強制改變流程 If A>B Then A=B+0 If A>B Then A=A+0 B=0 End If
流程控制 決策控制 單一選擇結構 雙向選擇結構 巢狀結構 4 多向選擇 SelectCase GoTo 強制改變流程 If A>B Then A=A+0 Else B=B+0 If MyAge>0 Then MsgBox " 可以抽菸 " Else MsgBox " 抓起來關 " End If
流程控制 決策控制 單一選擇結構 雙向選擇結構 巢狀結構 4 多向選擇 SelectCase GoTo 強制改變流程 If 分數 >=90 Then 考績 =" 優 " ElseIf 分數 >=80 Then 考績 =" 甲 " ElseIf 分數 >=70 Then 考績 =" 乙 " Else 考績 =" 丙 " End If MsgBox 考績
流程控制 決策控制 單一選擇結構 雙向選擇結構 巢狀結構 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
流程控制 決策控制 GoTo 強制改變流程 GoTo F F0: FontSize=0 Range("A")=" 字型 0pt" GoTo F6 F: FontSize= Range("A")=" 字型 pt" GoTo F0 F6: FontSize=6 Range("A")=" 字型 6pt"
For...Next ForEach...Next Do...Loop 4 Exit DoWhile...Loop Do...LoopWhile DoUntil...Loop Do...LoopUntil
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
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
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
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
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
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
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
For...Next ForEach...Next Do...Loop 4 Exit DoWhile...Loop Do...LoopWhile DoUntil...Loop Do...LoopUntil Exit Do Exit For Exit Sub
活頁簿 Workbooks.Add Workbooks.Open("C:/Surpro/test.xls") Workbooks("test.xls").Activate ActiveWorkbook.Save AcitveWorkbok.Close
工作表 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
儲存格 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
其他 篩選 :AutoFilter 排序 :Sort 最適大小 : AutoFit 複製特殊可見儲存格 : SpecialCells(xlCellTypeVisible).Copy