VBA - Operators
An Operator can be defined using a simple expression - 4 + 5 is equal to 9. Here, 4 and 5 are called operands and + is called operator. VBA supports following types of operators −
- Arithmetic Operators
- Comparison Operators
- Logical (or Relational) Operators
- Concatenation Operators
The Arithmatic Operators
Following arithmetic operators are supported by VBA.Assume variable A holds 5 and variable B holds 10, then −
| Operator | Description | Example |
|---|---|---|
| + | Adds the two operands | A + B will give 15 |
| - | Subtracts the second operand from the first | A - B will give -5 |
| * | Multiplies both the operands | A * B will give 50 |
| / | Divides the numerator by the denominator | B / A will give 2 |
| % | Modulus operator and the remainder after an integer division | B % A will give 0 |
| ^ | Exponentiation operator | B ^ A will give 100000 |
The Comparison Operators
There are following comparison operators supported by VBA.Assume variable A holds 10 and variable B holds 20, then −
| Operator | Description | Example |
|---|---|---|
| = | Checks if the value of the two operands are equal or not. If yes, then the condition is true. | (A = B) is False. |
| <> | Checks if the value of the two operands are equal or not. If the values are not equal, then the condition is true. | (A <> B) is True. |
| > | Checks if the value of the left operand is greater than the value of the right operand. If yes, then the condition is true. | (A > B) is False. |
| < | Checks if the value of the left operand is less than the value of the right operand. If yes, then the condition is true. | (A < B) is True. |
| >= | Checks if the value of the left operand is greater than or equal to the value of the right operand. If yes, then the condition is true. | (A >= B) is False. |
| <= | Checks if the value of the left operand is less than or equal to the value of the right operand. If yes, then the condition is true. | (A <= B) is True. |
The Logical Operators
Following logical operators are supported by VBA.Assume variable A holds 10 and variable B holds 0, then −
| Operator | Description | Example |
|---|---|---|
| AND | Called Logical AND operator. If both the conditions are True, then the Expression is true. | a<>0 AND b<>0 is False |
| OR | Called Logical OR Operator. If any of the two conditions are True, then the condition is true. | a<>0 OR b<>0 is true. |
| NOT | Called Logical NOT Operator. Used to reverse the logical state of its operand. If a condition is true, then Logical NOT operator will make false. | NOT(a<>0 OR b<>0) is false. |
| XOR | Called Logical Exclusion. It is the combination of NOT and OR Operator. If one, and only one, of the expressions evaluates to be True, the result is True. | (a<>0 XOR b<>0) is true. |
The Concatenation Operators
Following Concatenation operators are supported by VBA.Assume variable A holds 5 and variable B holds 10 then −
| Operator | Description | Example |
|---|---|---|
| + | Adds two Values as Variable. Values are Numeric | A + B will give 15 |
| & | Concatenates two Values | A & B will give 510 |
Assume variable A = "Microsoft" and variable B = "VBScript", then −
| Operator | Description | Example |
|---|---|---|
| + | Concatenates two Values | A + B will give MicrosoftVBScript |
| & | Concatenates two Values | A & B will give MicrosoftVBScript |
Note − Concatenation Operators can be used for both numbers and strings. The output depends on the context, if the variables hold numeric value or string value.
VBA - Decisions
Decision making allows the programmers to control the execution flow of a script or one of its sections. The execution is governed by one or more conditional statements.VBA provides the following types of decision making statements.
VBA - If Statement
If check the condition if condition is true then do something otherwise do nothing.
Syntax:-
If(boolean_expression) Then
Statement 1
.....
.....
Statement n
End If
Statement 1
.....
.....
Statement n
End If
Example:-
Private Sub if_demo_Click()
Dim x As Integer
Dim y As Integer
x = 234
y = 32
If x > y Then
MsgBox "X is Greater than Y"
End If
End Sub
Dim x As Integer
Dim y As Integer
x = 234
y = 32
If x > y Then
MsgBox "X is Greater than Y"
End If
End Sub
VBA - If-Else Statement
If check the condition if condition is true then do something otherwise do something else.
Syntax:-
If(boolean_expression) Then
Statement 1
.....
.....
Statement n
Else
Statement 1
.....
....
Statement n
End If
Statement 1
.....
.....
Statement n
Else
Statement 1
.....
....
Statement n
End If
Example:-
Private Sub if_demo_Click()
Dim x As Integer
Dim y As Integer
x = 234
y = 324
If x > y Then
MsgBox "X is Greater than Y"
Else
Msgbox "Y is Greater than X"
End If
End Sub
Dim x As Integer
Dim y As Integer
x = 234
y = 324
If x > y Then
MsgBox "X is Greater than Y"
Else
Msgbox "Y is Greater than X"
End If
End Sub
VBA - If Elseif - Else statement
If check the condition if condition is true then do something otherwise check the next condition.
Syntax:-
If(boolean_expression) Then
Statement 1
.....
.....
Statement n
ElseIf (boolean_expression) Then
Statement 1
.....
....
Statement n
ElseIf (boolean_expression) Then
Statement 1
.....
....
Statement n
Else
Statement 1
.....
....
Statement n
End If
Statement 1
.....
.....
Statement n
ElseIf (boolean_expression) Then
Statement 1
.....
....
Statement n
ElseIf (boolean_expression) Then
Statement 1
.....
....
Statement n
Else
Statement 1
.....
....
Statement n
End If
Example:-
Private Sub if_demo_Click()
Dim x As Integer
Dim y As Integer
x = 234
y = 234
If x > y Then
MsgBox "X is Greater than Y"
ElseIf y > x Then
Msgbox "Y is Greater than X"
Else
Msgbox "X and Y are EQUAL"
End If
End Sub
Dim x As Integer
Dim y As Integer
x = 234
y = 234
If x > y Then
MsgBox "X is Greater than Y"
ElseIf y > x Then
Msgbox "Y is Greater than X"
Else
Msgbox "X and Y are EQUAL"
End If
End Sub
VBA - Nested If Statement
If check the condition if condition is true then do something otherwise check the next condition.
Syntax:-
If(boolean_expression) Then
Statement 1
.....
.....
Statement n
If(boolean_expression) Then
Statement 1
.....
.....
Statement n
ElseIf (boolean_expression) Then
Statement 1
.....
....
Statement n
Else
Statement 1
.....
....
Statement n
End If
Else
Statement 1
.....
....
Statement n
End If
Statement 1
.....
.....
Statement n
If(boolean_expression) Then
Statement 1
.....
.....
Statement n
ElseIf (boolean_expression) Then
Statement 1
.....
....
Statement n
Else
Statement 1
.....
....
Statement n
End If
Else
Statement 1
.....
....
Statement n
End If
Example:-
Private Sub nested_if_demo_Click()
Dim a As Integer
a = 23
If a > 0 Then
MsgBox "The Number is a POSITIVE Number"
If a = 1 Then
MsgBox "The Number is Neither Prime NOR Composite"
ElseIf a = 2 Then
MsgBox "The Number is the Only Even Prime Number"
ElseIf a = 3 Then
MsgBox "The Number is the Least Odd Prime Number"
Else
MsgBox "The Number is NOT 0,1,2 or 3"
End If
ElseIf a < 0 Then
MsgBox "The Number is a NEGATIVE Number"
Else
MsgBox "The Number is ZERO"
End If
End Sub
Dim a As Integer
a = 23
If a > 0 Then
MsgBox "The Number is a POSITIVE Number"
If a = 1 Then
MsgBox "The Number is Neither Prime NOR Composite"
ElseIf a = 2 Then
MsgBox "The Number is the Only Even Prime Number"
ElseIf a = 3 Then
MsgBox "The Number is the Least Odd Prime Number"
Else
MsgBox "The Number is NOT 0,1,2 or 3"
End If
ElseIf a < 0 Then
MsgBox "The Number is a NEGATIVE Number"
Else
MsgBox "The Number is ZERO"
End If
End Sub
VBA - Switch Statement
It checks the condition choice wise.
Syntax:-
Select Case expression
Case expressionlist1
statement1
statement2
....
....
statement1n
Case expressionlist2
statement1
statement2
....
....
Case expressionlistn
statement1
statement2
....
....
Case Else
elsestatement1
elsestatement2
....
....
End Select
Case expressionlist1
statement1
statement2
....
....
statement1n
Case expressionlist2
statement1
statement2
....
....
Case expressionlistn
statement1
statement2
....
....
Case Else
elsestatement1
elsestatement2
....
....
End Select
Example:-
Private Sub switch_demo_Click()
Dim MyVar As Integer
MyVar = 1
Select Case MyVar
Case 1
MsgBox "The Number is the Least Composite Number"
Case 2
MsgBox "The Number is the only Even Prime Number"
Case 3
MsgBox "The Number is the Least Odd Prime Number"
Case Else
MsgBox "Unknown Number"
End Select
End Sub
Dim MyVar As Integer
MyVar = 1
Select Case MyVar
Case 1
MsgBox "The Number is the Least Composite Number"
Case 2
MsgBox "The Number is the only Even Prime Number"
Case 3
MsgBox "The Number is the Least Odd Prime Number"
Case Else
MsgBox "Unknown Number"
End Select
End Sub