Last Updated on January 11, 2023 by token
Types of operators in Excel VBA
In our scripts we will use both arithmetic expressions and logical expressions. For this purpose, we will be able to use operators in Excel VBA, we can put them into 3 groups:
- arithmetic
- logical
- comparison
VBA operators: arithmetic
Arithmetic operators serve as the name suggests the implementation of operations in math. When creating a procedure containing mathematical operations, it may also be necessary to use parentheses. We write them in the standard way: “()”.
| Operator | Name | Example |
| + | Addition | Dim DblResult As Double DblResult = 9 + 7 MsgBox DblResult |
| – | Subtraction | Dim DblResult As Double DblResult = 9 – 7 MsgBox DblResult |
| * | Multiplication | Dim DblResult As Double DblResult = 9 * 7 MsgBox DblResult |
| / | Division | Dim DblResult As Double DblResult = 9 / 7 MsgBox DblResult |
| \ | Division without rest | Dim DblResult As Double DblResult = 9 \ 7 MsgBox DblResult |
| ^ | exponentiation | Dim DblResult As Double DblResult = 9 ^ 7 MsgBox DblResult |
| mod | Modulo (the rest from sharing) | Dim DblResult As Double DblResult = 9 mod 7 MsgBox DblResult |
| + and & | Adding strings | Dim DblResult As Double DblResult = “string_1” & “string_2” MsgBox DblResult |
As you can see, the “+” operator can also be used to connect strings to each other. For the same purpose, you can also use the operator: “&”. The example is described below.
'OfficeInside.Org
Sub SumOperators()
MsgBox "string_1 " & "string_2"
MsgBox "string_1 " + "string_2"
End Sub
If you want to use the minority and majority operators in relation to letters, the procedure will return true or false depending on which place the given letter is in the alphabet.
'OfficeInside.Org
Sub LogOperators()
MsgBox "A" < "B" 'returns True
MsgBox "A" = "B" 'returns False
End Sub
VBA operators: comparison
Comparison operators are used to compare selected data. Their description and application example are below. Using the following construction, the result will be True or False each time.
| Operator | Name | Example |
| = | equal | MsgBox 100 = 200 |
| <> | uneven | MsgBox 100 <> 200 |
| > = | Bigger than | MsgBox 100 >= 200 |
| <= | Smaller than | MsgBox 100 <= 200 |
| > | Bigger | MsgBox 100 > 200 |
| < | Smaller | MsgBox 100 < 200 |
VBA operators: logical
The basis for a good script is correctly logic. We will use logical operators to save it.
| Operator | Name | Example |
| and | Conjunction | MsgBox (1 = 1) And (2 <> 3) ‘will return True |
| Or | Alternative | MsgBox (1 = 1) Or (2 = 3) ‘will return True |
| xor | An alternative with one choice. Returns false only if more than one condition is true | MsgBox (1 = 1) Xor (2 <> 3) ‘will return False |
| not | Negation | MsgBox Not (0 = 1) ‘will return true |
Tasks (You can save the result in a comment)
- Record any action using all the arithmetic operators at the same time. The operation should give in result 100.
More examples of Excel VBA basics can be found in the Excel VBA Course section and Excel VBA How To section. If you have any questions about this chapter, you can post them on the forum without logging in.
