Excel VBA 四 宣告變數、陣列、If、迴圈、Select Case

※宣告變數

Dim x As Integer
'x = "d"
x = 1 + 2
MsgBox (7 + x)
MsgBox ("x=" & x)

※雖然不用宣告也可以執行，但如果不宣告，註解那行是可以執行的

※類型
Byte (1)：無符號，0-255
Integer (2)
Long (4)
Double (8)
Boolean (2)
Decimal (14)
String

Date (8)
Currency (8)
Single (4)
Object (4)
Variant （根据分配确定）

數字預設是 Integer

※陣列

Dim a(6 To 10) As String
a(6) = "a"
a(8) = "b"
a(10) = "c"
MsgBox a(6) 'a
MsgBox a(7) '

※如果不在範圍內 (6-10)，如 a(5) 會報 Subscript out of range

．二維陣列

Dim b(1 To 3, 2 To 4) As String
b(1, 2) = "kkk"
MsgBox b(1, 2) 'kkk

※If

Dim i As Byte
i = 7
If i = 1 Then
  MsgBox (1)
ElseIf i = 2 Then
  MsgBox (2)
ElseIf i = 7 Then
  MsgBox (7)
Else
  MsgBox ("hahaha")
End If

※不等於用 <>


Dim i As Byte
i = 7
If i = 0 Then
  MsgBox (0)
ElseIf i >= 1 & i <= 10 Or i = 100 Then
  MsgBox ("1~10 or 100")
Else
  MsgBox ("hahaha")
End If

※& 和 Or 可以更進一步的判斷

※迴圈

※Do ~ Loop

Dim i As Integer
'i = 10
Do While i < 10
  i = i + 1
Loop
MsgBox (i)

※至少會跑一次

※For ~ Next

Dim i As Integer
Dim sum As Long

For i = 1 To 10 'Step 1
  sum = sum + i
Next i
MsgBox (sum)

※Step 預設就是 1 了


For i = 1 To 9
  For j = 1 To 9
    Cells(i, j).Value = i & "x" & j & "=" & i * j
  Next j
Next i

※可以嵌套

※For Each ~ Next

For i = 1 To 5 Step 1
    Cells(i, "a").Value = i * 9
Next i

For Each j In Range("A1: A5")
  If j = 36 Then
    GoTo xx
    'Exit For
  End If
  MsgBox (j)
  xx:
Next j

※Exit For 就是 break

※GoTo 到一個標籤，模擬 continue

※Select Case

n = -9
Dim str As String

Select Case n
  Case 1
    n = "a"
  Case 2, 3
    n = "b"
  Case 4 To 6
    n = "c"
  Case Is > 6
    n = "d"
  Case Else
    n = "other"
  End Select
MsgBox (n)