Excel VBA Lesson 4: Operators in Excel VBA

<<Lesson 3>> [Contents] <<Lesson 5>>

The Operators are important in writing Excel VBA program code. They 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

We shall learn more about writing decision-making code in future lessons.




The 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

 

<<Lesson 3>> [Contents] <<Lesson 5>>

Excel 2010 VBA Lesson 4: Operators

[Lesson 3]<<[Table of Contents]>>[Lesson 5]

Operators are important in writing Excel 2010 VBA program code. They are used to compute 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 performing mathematical operations in Excel VBA 2010.

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”

*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 use of 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:

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.

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



[Lesson 3]<<[Table of Contents]>>[Lesson 5]