 # Lesson 4: Using Operators

Operators are used to calculate values, perform certain operations, make comparisons and more. The operators can be divided into three main categories:

• Arithmetic
• Comparison
• Logical

### 4.1 Arithmetic Operators

Arithmetic operators are used to perform mathematical operations in Excel VBA.

Table 4.1 Arithmetic Operators
Operator Mathematical function Example

^

Exponential MsgBox 2^4 gives a value of 16

*

Multiplication MsgBox 4*3 gives a value of 12,

/

Division MsgBox 12/4 gives a value of 3

Mod

Modulus (returns the remainder from an integer division) MsgBox 15 Mod 4 gives value of 3

\

Integer Division(discards the decimal places) MsgBox 19\4 gives a value of 4

+ or &

String concatenation MsgBox "Excel"&"VBA 2010" or "Excel"+"VBA 2010" produces a new string "Excel VBA 2010"

MsgBox is a built-in function of excel VBA that displays a message. We shall learn more about functions in next lesson. Note that MsgBox 1+"VBA" will produce a type mismatch error whereas MsgBox 1&"VBA" will not result in an error, it gives a concatenated string 1VBA. We shall engage the usage of arithmetic operators in Excel VBA code writing in future lessons.

### 4.2 Comparison Operators

Comparison operators are often used in writing code that require decisions making. For example,

```If mark>50 then
MsgBox "Pass"
Else
MsgBox "Fail"
Endif
```

comparison operators are shown in Table 4.2.

Table 4.2 Comparison Operators

Operator

Meaning Example

<

Less than MsgBox 2<3 returns true while MsgBox 4>5 returns false

<=

Less than or equal to MsgBox 3<=4 returns true

>

Greater than MsgBox 5>4 returns true

>=

Greater than or equal to MsgBox 10>=9 returns true

=

Equal to MsgBox 10=10 returns true

<>

Not Equal to MsgBox 9<>10 returns true

* For letters, the hierarchy is A>B>C>..........>Z Therefore MsgBox A>B returns true

### 4.3 Logical Operators

Logical operators are also used in writing decision making codes by comparing values or expressions.

Table 4.3 Logical Operators

Operator

Meaning Example

And

Logical Conjunction If A>=80 And B<101 thenGrade="A"

Or

Logical Disjunction If income>5000 or car>2 thenStatus="Rich"

Not

Logical negation MsgBox Not (3 > 4)returns true

Xor

Similar to Or, except that it returns False if both camparison values are true MsgBox 4 > 3 Xor 5 >2 returns false