目錄
一、指定運算
二、數值運算
三、布林運算
四、位元運算
五、字串運算
六、特殊運算
一、指定運算
1-1 指定運算子(Assignment Operator)
# |
運算子 |
意義 |
VBA |
1 |
variable = expr |
指定運算(Assignment Operator) |
✔ |
Sub Arithmetic_Operator()
result = (4 * 3) / 2 + 1
End Sub
1-2 複合指定運算子(Compound Assignment Operators)
# |
運算子 |
意義 |
VBA |
1 |
variable += expr |
加法指定運算(Addition Assignment Operator) |
✘ |
2 |
variable -= expr |
減法指定運算(Subtraction Assignment Operator) |
✘ |
3 |
variable *= expr |
乘法指定運算(Multiplication Assignment Operator) |
✘ |
4 |
variable /= expr |
除法指定運算(Division Assignment Operator) |
✘ |
5 |
variable <<= expr |
左移指定運算(Left-shift Assignment Operator) |
✘ |
6 |
variable >>= expr |
右移指定運算(Right-shift Assignment Operator) |
✘ |
二、數值運算
2-1 四則運算(Arithmetic Operators)
# |
運算子 |
意義 |
VBA |
1 |
expr1 + expr2 |
加法運算子(Addition Operator) |
✔ |
2 |
expr1 - expr2 |
減法運算子(Subtraction Operator) |
✔ |
3 |
expr1 * expr2 |
乘法運算子(Multiplication Operator) |
✔ |
4 |
expr1 / expr2 |
除法運算子(Division Operator) |
✔ |
5 |
expr1 expr2 |
地板除法運算子(Floor Division Operator) |
✔ |
6 |
expr1 Mod expr2 |
餘數運算子(Remainder Operator) |
✔ |
7 |
expr1 ^ expr2 |
乘冪運算子(Power Operator) |
✔ |
Sub Arithmetic_Operators()
expr1 = 3
expr2 = 2
Debug.Print expr1 + expr2
Debug.Print expr1 - expr2
Debug.Print expr1 * expr2
Debug.Print expr1 / expr2
Debug.Print expr1 \ expr2
Debug.Print expr1 Mod expr2
Debug.Print expr1 ^ expr2
End Sub
2-2 遞增、遞減運算(Increment Operators)
# |
運算子 |
意義 |
VBA |
1 |
++expr |
前置遞增運算子(Prefix Increment Operator) |
✘ |
2 |
--expr |
前置遞減運算子(Prefix Decrement Operator) |
✘ |
3 |
expr++ |
後置遞增運算子(Postfix Increment Operator) |
✘ |
4 |
expr-- |
後置遞增運算子(Postfix Increment Operator) |
✘ |
2-3 正、負運算(Plus and Negation Operators)
# |
運算子 |
意義 |
VBA |
1 |
+expr |
一元正運算子(Unary Plus Operator) |
✔ |
2 |
-expr |
一元負運算子(Unary Negation Operator) |
✔ |
Sub Plus_and_Negation_Operators()
expr = -2
Debug.Print +expr
Debug.Print -expr
End Sub
三、布林運算
3-1 比較運算子(Comparison Operators)
# |
運算子 |
意義 |
VBA |
1 |
expr1 = expr2 |
相等運算子(Equality Operator) |
✔ |
2 |
expr1 <> expr2 |
不等運算子(Inequality Operator) |
✔ |
3 |
expr1 >= expr2 |
大於等於運算子("Greater than or equal" Relational Operator) |
✔ |
4 |
expr1 > expr2 |
大於運算子("Greater than" Relational Operator) |
✔ |
5 |
expr1 <= expr2 |
小於等於運算子("Less than or equal" Relational Operator) |
✔ |
6 |
expr1 < expr2 |
小於運算子("Less than" Relational Operator) |
✔ |
7 |
string Like pattern |
萬用字元字串比對運算子 |
✔ |
Sub Comparison_Operators()
expr1 = 3
expr2 = 2
Debug.Print expr1 = expr2
Debug.Print expr1 <> expr2
Debug.Print expr1 >= expr2
Debug.Print expr1 > expr2
Debug.Print expr1 < expr2
Debug.Print expr1 <= expr2
End Sub
Sub Like_Operator_With_Question_Mark()
test = "aBa"
Pattern = "a?a"
Debug.Print test Like Pattern
End Sub
Sub Like_Operator_With_Asterisk()
test = "aBBBa"
Pattern = "a*a"
Debug.Print test Like Pattern
End Sub
Sub Like_Operator_With_Number_Sign()
test = "a7a"
Pattern = "a#a"
Debug.Print test Like Pattern
End Sub
Sub Like_Operator_With_Brackets()
test = "aBa"
Pattern = "a[A-Z]a"
Debug.Print test Like Pattern
End Sub
Sub Like_Operator_With_Exclamation_Point()
test = "aBa"
Pattern = "a[!A-Z]a"
Debug.Print test Like Pattern
End Sub
3-2 邏輯運算子(Logical Operators)
# |
運算子 |
意義 |
VBA |
1 |
Not expr |
邏輯 NOT 運算(Logical NOT Operator) |
✔ |
2 |
expr1 And expr2 |
邏輯 AND 運算子(Logical And Operator) |
✔ |
3 |
expr1 Or expr2 |
邏輯 OR 運算子(Logical OR Operator) |
✔ |
4 |
expr1 Xor expr2 |
邏輯 XOR 運算子(Logical XOR Operator) |
✔ |
5 |
expr1 Eqv expr2 |
邏輯 XNOR運算子(Logical XNOR Operator) |
✔ |
6 |
expr1 Imp expr2 |
邏輯 IMPLY 運算子(Logical IMPLY Operator) |
✔ |
6 |
expr1 AndAlso expr2 |
條件式邏輯 AND 運算子(Conditional Logical And Operator) 最小化求值 AND 運算子("Short-circuiting" AND Operator) |
✘ |
7 |
expr1 OrAlso expr2 |
條件式邏輯 OR 運算子(Conditional Logical OR Operator) 最小化求值 OR 運算子("Short-circuiting" OR Operator) |
✘ |
Sub Logical_NOT_Operator_For_Boolean()
Debug.Print Not False
Debug.Print Not True
End Sub
Sub Logical_AND_Operator_For_Boolean()
Debug.Print False And False
Debug.Print False And True
Debug.Print True And False
Debug.Print True And True
End Sub
Sub Logical_OR_Operator_For_Boolean()
Debug.Print False Or False
Debug.Print False Or True
Debug.Print True Or False
Debug.Print True Or True
End Sub
Sub Logical_XOR_Operator_For_Boolean()
Debug.Print False Xor False
Debug.Print False Xor True
Debug.Print True Xor False
Debug.Print True Xor True
End Sub
Sub Logical_XNOR_Operator_For_Boolean()
Debug.Print False Eqv False
Debug.Print False Eqv True
Debug.Print True Eqv False
Debug.Print True Eqv True
End Sub
Sub Logical_Imply_Operator_For_Boolean()
Debug.Print False Imp False
Debug.Print False Imp True
Debug.Print True Imp False
Debug.Print True Imp True
End Sub
四、位元運算
4-1 位元運算子(Bitwise Operators)
# |
運算子 |
意義 |
VBA |
1 |
~expr |
位元補數運算(Bitwise Complement Operator) |
✘ |
2 |
expr1 << int |
位元左移運算(Bitwise Left-shift Operator) |
✘ |
3 |
expr1 >> int |
位元右移運算(Bitwise Right-shift Operator) |
✘ |
4-2 邏輯運算子(Logical Operators)
# |
運算子 |
意義 |
VBA |
1 |
Not expr |
邏輯 NOT 運算(Logical NOT Operator) |
✔ |
2 |
expr1 And expr2 |
邏輯 AND 運算子(Logical And Operator) |
✔ |
3 |
expr1 Or expr2 |
邏輯 OR 運算子(Logical OR Operator) |
✔ |
4 |
expr1 Xor expr2 |
邏輯 XOR 運算子(Logical XOR Operator) |
✔ |
5 |
expr1 Eqv expr2 |
邏輯 XNOR運算子(Logical XNOR Operator) |
✔ |
6 |
expr1 Imp expr2 |
邏輯 IMPLY 運算子(Logical IMPLY Operator) |
✔ |
Sub Logical_Operators_For_Bitwise()
expr1 = 12
expr2 = 10
Debug.Print Not expr1
Debug.Print expr1 And expr2
Debug.Print expr1 Or expr2
Debug.Print expr1 Xor expr2
Debug.Print expr1 Eqv expr2
Debug.Print expr1 Imp expr2
End Sub
五、字串運算
5-1 串連運算子(Concatenation Operators)
# |
運算子 |
意義 |
VBA |
1 |
string1 + string2 |
串接字串 |
✔ |
2 |
expr1 & expr2 |
串接各種型別的資料 |
✔ |
Sub Concatenation_Operators()
Debug.Print "string:" + "abc"
Debug.Print "string:" & "abc" & _
" ,double:" & 1.1 & _
" ,bool:" & True & _
" ,int:" & 123
End Sub
六、特殊運算
6-1 條件運算子(Conditional Operator)
# |
運算子 |
意義 |
VBA |
1 |
(條件式) ? 成立 : 不成立 |
條件運算子(Conditional Operator) |
✘ |
可用IIf(條件式, 成立, 不成立)
代替
Sub Conditional_Operator()
i = 2
j = 3
result = IIf(i > j, i, j)
Debug.Print result
End Sub