Lesson 12 : Using If.....Then....Else

����[Lesson 11] [Home] [Lesson 13]

In this lesson, we shall learn how to create Excel VBA using If..Then...ElseIf statement to control program flow and enables decision making based on certain conditions. To control program flow, we use the If...Then...ElseIf structure together with conditional and logical operators.

12.1 Conditional and Logical Operators

To control the Visual Basic program flow, we can use various conditional operators. Basically, they resemble mathematical operators. Conditional operators are very powerful tools, they let the VB program compare data values and then decide what action to take, whether to execute a program or terminate the program and more. These operators are shown in Table 2.1.

Table 12.1: Conditional Operators

Operator	Meaning
=	Equal to
>	More than
<	Less Than
>=	More than or equal
<=	Less than or equal
<>	Not Equal to

��

Table 12.2:Logical Operators

Operator	Meaning
And	Both sides must be true
or	One side or other must be true
Xor	One side or other must be true but not both
Not	Negates truth

---

12.2 Using If.....Then.....Else   Statements  with Operators

To control the Visual Basic program flow, we shall use If...Then...Else statement together with the conditional operators and logical operators.

* any If..Then..Else statement must end with End If. Sometime it is not necessary to use Else.

Example 12.1

In this example, you place the command button1 on the MS Excel spreadsheet and go into the VB editor by clicking on the button. At the Excel VB editor, key in the program codes as shown on the left.

I use randomize timer and the RND function to generate random numbers. In order to generate random integers between 0 and 100, I combined the syntax Int(Rnd*100). For example, when Rnd=0.6543, then Rnd*100=65.43, and Int(65.43)=65. Using the statement cells(1,1).Value=mark will place the value of 65 into cell(1,1).

Now, based on the mark in cells(1,1), I use the If.......Then....Elseif statements to put the corresponding grade in cells(2,1). So, when you click on command button 1, it will put a random number between 1 and 100 in cells(1,1) and the corresponding grade in cells(2,1).

---

The Interface

---

The Code

Private Sub CommandButton1_Click()
Dim mark As Integer
Dim grade As String
Randomize Timer
mark = Int(Rnd * 100)
Cells(1, 1).Value = mark
If mark < 20 And mark >= 0 Then
grade = "F"
Cells(2, 1).Value = grade
ElseIf mark < 30 And mark >= 20 Then
grade = "E"
Cells(2, 1).Value = grade
ElseIf mark < 40 And mark >= 30 Then
grade = "D"
Cells(2, 1).Value = grade
ElseIf mark < 50 And mark >= 40 Then
grade = "C-"
Cells(2, 1).Value = grade
ElseIf mark < 60 And mark >= 50 Then
grade = "C"
Cells(2, 1).Value = grade
ElseIf mark < 70 And mark >= 60 Then
grade = "C+"
Cells(2, 1).Value = grade
ElseIf mark < 80 And mark >= 70 Then
grade = "B"
Cells(2, 1).Value = grade
ElseIf mark <= 100 And mark >=80 Then
grade = "A"
Cells(2, 1).Value = grade
End If
End Sub

---

 

����[Lesson 11] [Home] [Lesson 13]

Copyright ® 2008 Dr.Liew Voon Kiong . All rights reserved 

Contact: admin@excelvbatutor.com [Privacy Policy]