Excecl VBA Classic Excel VBA 2010 Excel VBA 365 Excel VBA Examples About Us

Lesson 4 Operators


Operators are used to compute values, perform some 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 for performing mathematical operations.

Table 4.1 Arithmetic Operators
Operator Mathematical Functions Example
^ Exponential 2^4 gives a value of 16
* Multiplication 4*3 gives a value of 12
/ Division 12/4 gives a value of 3
Mod Modulus (returns the remainder from an integer division) 15 Mod 4 gives value of 3
\ Integer Division(discards the decimal places) 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”

*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 learn more about arithmetic operators in Excel 2010 VBA code writing in future lessons.

4.2 Comparison Operators

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

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

Here is a list of comparison operators:

Table 4.2 Comparison Operators
Operator Meaning Example
< Less Than 2<3 returns true while 5<4 returns false
<= Less than or equal to 3<=4 returns true
> Greater than 5>4 returns true
>= Greater than or equal to 10>=9 returns true
= Equal to 10=10 returns true
<> Not Equal to 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 then Grade="A"
Or Logical Disjunction If income>5000 or car>2 thenStatus="Rich"
Not Logical negation Not (3 > 4)returns true
Xor Similar to Or, except that it returns False if both camparison values are true 4 >3 Xor 5>2 returns false





Copyright ® 2008 Dr.Liew Voon Kiong . All rights reserved   [Privacy Policy]

Contact: Facebook Page