1 / 48

變數與常數

變數與常數. 宣告變數. 變數與常數. Dim (Variable Name) as (Type) Private Sub CommandButton1_Click() Dim new_date As Date ' 宣告變數並指定型態 new_date = Range("B2").Value ' 指定變數的值 MsgBox new_date ' 顯示變數的內容 End Sub. 變數與常數. Private Sub CommandButton2_Click()

bellini-fadden
Télécharger la présentation

變數與常數

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 變數與常數 宣告變數

  2. 變數與常數 Dim (Variable Name) as (Type) Private Sub CommandButton1_Click() Dim new_date As Date ' 宣告變數並指定型態 new_date = Range("B2").Value ' 指定變數的值 MsgBox new_date ' 顯示變數的內容 End Sub

  3. 變數與常數 Private Sub CommandButton2_Click() Dim msg As String * 5 ’宣告字串及長度 msg = "Excel2003 VBA" MsgBox msg End Sub

  4. 變數與常數 變數的作用範圍只限於程序內 Private Sub CommandButton2_Click() Dim msg As String * 5 ’宣告字串及長度 msg = "Excel2003 VBA" MsgBox msg End Sub Private Sub CommandButton3_Click() Dim msg As String * 3 ’宣告字串及長度 msg = “VBA2003" MsgBox msg End Sub

  5. 變數與常數 要在不同程序內使用相同變數必須於工作表中宣告

  6. 變數與常數 Optional Explicit 程序內任何變數 必須於工作表中宣告

  7. 變數與常數 常數 Public | Private] Constconstname [Astype] =expression Private Sub CommandButton1_Click() Const str_1 = "Excel 2003 VBA" ’宣告常數 Const num_1 As Integer = 1234 ’宣告並指定型態 MsgBox str_1 & vbCr & num_1 End Sub Example: Check Example Lec6.2-Msgbox.Sheet1

  8. 變數與常數 物件變數 Private Sub CommandButton1_Click() Set cell_1 = Workbooks("ex314_2.xls").Sheets(3).Range("b2") cell_1.Value = "物件變數的用法" cell_1.ColumnWidth = 18 cell_1.Font.Bold = True End Sub

  9. 變數與常數 Private Sub CommandButton1_Click() Dim r1 As Range ’宣告物件變數 Set r1 = Workbooks("ex314_2.xls").Sheets(3).Range("b2:c5") With r1 .Color = RGB(255, 0, 0) .Bold = True End With End Sub

  10. 變數與常數 Private Sub CommandButton1_Click() Set new_book = Workbooks.Add ’建立新的 　　　　　　　　　　　　活頁簿並設定物件變數 With new_book .Title = "物件變數的應用" .SaveAs Filename:="test1.xls" End With End Sub

  11. 陣列 陣列的宣告方法 Dim [Array_name(#)] as Type Private Sub CommandButton1_Click() Dim num(3) As Integer num(0) = 10 num(1) = 20 num(2) = 30 num(3) = 40 total = num(0) + num(1) + num(2) + num(3) MsgBox "總合 = " & total End Sub Optional Base 0 is Default We can try another base say 1

  12. 陣列 Private Sub CommandButton1_Click() Dim num(3 To 5) As Integer ’ 設定索 引的範圍 num(3) = 100 num(4) = 200 num(5) = 300 MsgBox "第三個元素 = " & num(5) End Sub

  13. 陣列 Private Sub CommandButton1_Click() Dim msg(3) As String * 5 msg(1) = "Word2003" msg(2) = "Excel2003" msg(3) = "PowerPoint2003" MsgBox msg(1) & vbCr & msg(2) & vbCr & msg(3) End Sub

  14. 陣列 Private Sub CommandButton1_Click() Dim num(9, 9) As Integer For i = 1 To 9 For j = 1 To 9 num(i, j) = i * j Next Next MsgBox "8 * 7 = " & num(8, 7) End Sub Two dimensional Array

  15. Private Sub CommandButton1_Click() Dim num(3) As Integer num(1) = 100 num(2) = 200 num(3) = 300 Dim num(4) As Integer num(4) = 400 MsgBox num(4) End Sub 陣列 不能重覆宣告Array

  16. 陣列 Private Sub CommandButton1_Click() Dim my_array() As Integer ' 動態的陣列 ReDim my_array(3) ' 指定維數 my_array(1) = 100 my_array(2) = 200 my_array(3) = 300 ReDim my_array(4) ' 重新調整長度 my_array(4) = 400 MsgBox my_array(4) End Sub 重覆宣告

  17. 算術運算

  18. 算術運算 Private Sub CommandButton1_Click() Dim num(1 To 4) As Integer num(1) = 1234 num(2) = 5678 num(3) = num(1) + num(2) ' 變數與變數的運算 num(4) = num(3) * 3 ' 變數與常數的運算 MsgBox "計算結果 = " & num(4) End Sub

  19. 比較運算

  20. 比較運算 Private Sub CommandButton1_Click() num = Range("b2").Value If num > 60 Then ’比較運算 MsgBox " 過 關 !!!" Else Msgbox “革命尚未成功” End If End Sub

  21. 邏輯運算

  22. 邏輯運算 Private Sub CommandButton1_Click() num_1 = Range("b2").Value num_2 = Range("c2").Value If (num_1 > 60) And (num_2 > 60) Then ’ 邏輯運算 MsgBox " 兩 科 都 及 格" End If End Sub

  23. 連結運算 Private Sub CommandButton1_Click() str_1 = "Excel" str_2 = " 2003" Range("c3").Value = str_1 & str_2 End Sub

  24. 連結運算 Private Sub CommandButton1_Click() str_1 = "Excel 2003" str_2 = "VBA與進階應用" Range("b2").Value = str_1 + vbCrLf + str_2 End Sub

  25. 連結運算

  26. 判斷式的設計方法 IF…Then ﹝語法﹞： If ＜條件式＞ Then 【程式一】 【程式二】 ﹝執行方法﹞： 如果＜條件式＞成立，就先執行【程式一】，再執行【程式二】。 如果＜條件式＞不成立，就忽略【程式一】，直接執行【程式二】。

  27. 判斷式的設計方法 Private Sub Worksheet_SelectionChange(ByVal Target As Range) msg = "及 格" num = Target.Value If num < 60 Then msg = "不及格" ’資料如果小於60，就改變字串 的內容 MsgBox msg End Sub

  28. 判斷式的設計方法 IF…Then…End If ﹝語法﹞： If ＜條件式＞ Then 【程式一】 End If 【程式二】 ﹝執行方法﹞： 程式的流程與上一節的指令相同，但是【程式一】代表一個區塊，可以輸入兩行以上的敘述。

  29. 判斷式的設計方法 Private Sub Worksheet_SelectionChange(ByVal Target As Range) msg = "及 格" num = Target.Value If num < 60 Then msg = "不及格" Target.Font.Color = RGB(255, 0, 0) Target.Interior.ColorIndex = 35 End If Range("b7").Value = msg End Sub

  30. 判斷式的設計方法 Private Sub Worksheet_SelectionChange(ByVal Target As Range) num = Target.Value msg = "及 格" If num < 60 Then’ 外層的判斷式 msg = "補 考" Target.Font.Color = RGB(255, 0, 0) Target.Interior.ColorIndex = 35 If num < 50 Then’ 內層的判斷式 msg = "死 當" End If End If Range("b7").Value = msg End Sub

  31. 判斷式的設計方法 IF…Then…Else…End If ﹝語法﹞： If ＜條件式＞ Then 【程式一】 Else 【程式二】 End If ﹝執行方法﹞： 先檢查＜條件式＞，如果成立就執行【程式一】，如果不成立就執行【程式二】。

  32. 判斷式的設計方法 Private Sub CommandButton1_Click() num = Range("b2").Value If num >= 30 Then num = 0 Else num = num + 1 End If Range("b2").Value = num End Sub

  33. 判斷式的設計方法 IF…Then…ElseIf…End If ﹝語法﹞： If ＜條件式１＞ Then 【程式一】 ElseIf ＜條件式２＞ Then 【程式二】 End If ﹝執行方法﹞： 先檢查＜條件式１＞，如果成立就執行【程式一】，然後跳到〝EndIf〞後面。如果＜條件式１＞不成立，就繼續檢查＜條件式２＞，再依據檢查的結果，來決定要不要執行【程式二】。

  34. 判斷式的設計方法 Private Sub Worksheet_SelectionChange(ByVal Target As Range) num = Target.Value If num >= 60 Then Target.Font.Color = RGB(0, 0, 255) ElseIf num < 50 Then Target.Font.Color = RGB(0, 255, 0) Else Target.Font.Color = RGB(255, 0, 0) End If End Sub

  35. 判斷式的設計方法 Select Case..End Select ﹝語法﹞： Select Case 變數名稱 Case 數值１ 【程式一】 Case 數值２ 【程式二】 　　　　． End Select ﹝執行方法﹞： 依據變數的值，來選擇要執行的程式。作用方法類似 If…ElseIf。

  36. 判斷式的設計方法 Private Sub Worksheet_SelectionChange(ByVal Target As Range) num = ActiveCell.Value Select Case num Case 1 msg = " ONE" Case 2 msg = " TWO" Case 3 msg = " THREE" Case 4 msg = " FOUR" End Select MsgBox msg End Sub

  37. 判斷式的設計方法 Private Sub Worksheet_SelectionChange(ByVal Target As Range) num = ActiveCell.Value Select Case num Case Is >= 60 msg = " 及 格" Case 50 To 59 msg = " 必需補考" Case Is < 50 msg = " 明年再見" End Select MsgBox msg End Sub

  38. 迴圈的控制方法 For…Next ﹝語法﹞： For 變數名稱 = 起始值 To 結束值 【程式區塊】 Next ﹝執行方法﹞： 先將變數設定成〝起始值〞，然後反覆地執行區塊中的指令，並依序將變數的值加１，直到變數的值大於〝結束值〞。

  39. 迴圈的控制方法 Private Sub CommandButton1_Click() total = 0 For k = 1 To 100 ’執行100次的迴圈 total = total + k Next MsgBox “1+2+3+…+100 =” & total End Sub

  40. 迴圈的控制方法 Private Sub CommandButton1_Click() Dim total As Integer For k = 1 To 100 Step 2 total = total + k Next MsgBox "奇數的總合 = " & total End Sub

  41. 迴圈的控制方法 Private Sub CommandButton1_Click() Dim total As Integer For k = 1 To 100000 total = total + k If total > 6000 Then ’總合如果大於6000 Exit For ’就結束迴圈的運作 End If Next MsgBox "次 數 = " & k & _ " 總 合 = " & total End Sub

  42. 迴圈的控制方法 巢狀的迴圈 For i = 1 To 12 ' 外層的迴圈 For j = 1 To 3 ' 內層的迴圈 . . Next Next

  43. 迴圈的控制方法 Private Sub CommandButton1_Click() Dim score(12, 3) As Integer ' 宣告陣列 For i = 1 To 12 ' 設定數值 For j = 1 To 3 score(i, j) = i * 100 + j * 10 Next Next MsgBox "score(5,3) = " & score(5, 3) '顯示一個元素 End Sub

  44. 迴圈的控制方法 Private Sub CommandButton1_Click() For i = 1 To 9 For j = 1 To 9 Cells(i, j).Value = i * j ’在儲存格裡面填入數字 Next Next End Sub

  45. 迴圈的控制方法 Private Sub CommandButton1_Click() For i = 1 To 9 If i Mod 2 = 1 Then ' 如果是奇數列 Rows(i).Interior.ColorIndex = 35 ' 就改變儲存格的顏色 End If Next End Sub

  46. 迴圈的控制方法 For Each…Next ﹝語法﹞： For Each 元素 In 群組的名稱 【程式區塊】 Next ﹝執行方法﹞： 先選取〝群組〞的第一個元素，再執行區塊中的程式，然後依序改成下一個元素，直到所有的元素都執行完畢。

  47. 迴圈的控制方法 Do While…Loop ﹝語法﹞： Do While ＜條件式＞ 【程式區塊】 Loop ﹝執行方法﹞： 如果＜條件式＞成立，就執行區塊內的程式，然後重新檢查＜條件式＞，直到＜條件式＞不成立為止。

  48. 迴圈的控制方法

More Related