????A Lecture 9

1
????A Lecture 9

• Takeshi TokuyamaJinhee ChunTohoku University
  Graduate School of Information SciencesSystem
  Information SciencesDesign and Analysis of
  Information Systems

2
PROGRAMMING VBAARRAY, FOR - NEXT
3
Array

• Collection of the same data type
• For large amount of data or sequential reading of
  data

e
f
a
d
c
Integers
b
x(0)
x(1)
x(2)
x(3)
x(4)
x(5)
Array of Integer
Index
Name of Array
Element
4
Array Declaration
x(1)
x(2)
x(3)
x(4)
x(5)
x(6)
Index starts at 0
Six boxes to store Integer type variables Box
name x, Index 0 to 5
x(0)
x(1)
x(2)
x(3)
x(4)
x(5)
Dim x(5) As Integer
x(5) create six boxes named x which allows to
contain Integer
Array name
Data type
Maximum index
5
Array Declaration
StudentID 1 Score 100
StudentID 3 Score 76
StudentID 5 Score 61
StudentID 2 Score 65
StudentID 4 Score 87
StudentID 6 Score 99
score(0)
score(1)
score(2)
score(3)
score(4)
score(5)
score(0) 100 score(1) 65 score(2)
76 score(3) 87 score(4) 61 score(5) 99
6
Array Declaration with Index
x(1)
x(2)
x(3)
x(4)
x(5)
x(6)
Six boxes to store Integer type variables Box
name x, Index 1 to 6
Element Box x(1), x(2), , x(6)
Array name
Index
Dim x(1 to 6) As Integer
Array name
Data type
Index Range
7
Array Declaration
1 Sub array1()
2 Dim score(5) As Integer
3 score(0) 100
4 score(1) 65
5 score(2) 76
6 score(3) 87
7 score(4) 61
8 score(5) 99
9
10 MsgBox score(0)
11 MsgBox score(1)
12 MsgBox score(2)
13 MsgBox score(3)
14 MsgBox score(4)
15 MsgBox score(5)
16 End Sub
Student ID Score
1 100
2 65
3 76
4 87
5 61
6 99
8
Array Declaration
1 Sub array2()
2 Dim score(5) As Integer
3 Dim id As Integer variable to put StudentID
4
5 score(0) 100
6 score(1) 65
7 score(2) 76
8 score(3) 87
9 score(4) 61
10 score(5) 99
11
12 id InputBox(Enter StudentID(1 to 6) to see the score.)
13 MsgBox StudentID id , Score score(id -1)
14
15 End Sub
Student ID Score
1 100
2 65
3 76
4 87
5 61
6 99
9
For - Next

• Execute code repeatedly for defined times
• Counter variable to define the range of iteration
• Counter variable i

Counter initial value
i Counter name
Counter maximum value
i0
overwrite i with i1
For i 0 To 5 Step 1 Next i
ii1
Action1
ilt5
Action1
TRUE
6 times
FALSE
Counter increment step
10
Counter
0

• What is i i 1 ?
• Store i 1 to i
• Increment i by 1

i
Dim i As Integer
Counter name(arbitrary name) i
i
i
1
11
01
1
2
3
i i 1
i
i
1
i i 1
11
i
i
1
i i 1
21
i
1
i
12
1 Sub array3()
2 Display StudentID and score of all student using For - Next
3 score array name, i counter name
4 Dim score(5) As Integer
5 Dim i As Integer
6
7 score(0) 100
8 score(1) 65
9 score(2) 76
10 score(3) 87
11 score(4) 61
12 score(5) 99
13
14 For i 0 To 5 Step 1
15 MsgBox StudentID id , Score score(i - 1)
16 Next i
17 End Sub
Student ID Score
1 100
2 65
3 76
4 87
5 61
6 99
13
14 name(0) Koji Tanaka
15 name(1) Hiroshi Abe
16 name(2) Akiko Ito
17 name(3) Ichiro Suzuki
18 name(4) Takako Kato
19 name(5) Junpei Kimura
20
21 For i 0 To 5 Step 1
22 MsgBox StudentID i , Name name(i) , Score score(i)
23 Next I
24 End Sub
Student ID name Score
1 Koji Tanaka 100
2 Hiroshi Abe 65
3 Akiko Ito 76
4 Ichiro Suzuki 87
5 Takako Kato 61
6 Junpei Kimura 99
14
Exercise

• Change array4() program to display if a student
  is Pass or Fail
• Program name
• Grading()
• Ex.
• StudentID 1, Name Koji Tanaka, Score 100, Pass
• Display in order of StudentID (six students)
• Grading
• Pass if score is equal or more than 79, Fail
  otherwise
• Hints
• Apply For-Next in array4() and If-Then-Else
  in grade1()
• Put If-Then-Else into For-Next

15
Sum of Scores

• Calculate the sum of score for all students

score(0)
score(2)
score(3)
score(4)
score(1)
score(5)
sum
i
i0, sum0
ii1
ilt5
sumsumscore(i)
TRUE
FALSE
When i0 sum sumscore(0)
16
Sum of Scores
100 score(0)
76 score(2)
87 score(3)
61 score(4)
65 score(1)
99 score(5)
0 sum
0 i
i0
0 sum
100 sum
1 i
100 score(0)
i1
100 sum
165 sum
2 i
65 score(1)
i2
17
Sum of Scores
165 sum
241 sum
3 i
76 score(2)
i3
389 sum
488 sum
6 i
99 score(1)
i6
18
1 Sub sum()
2 Calculate the sum of score for all student using For - Next
3 score array name, i counter, sum variable for sum
4 Dim score(5) As Integer
5 Dim i As Integer
6 Dim sum As Integer
7 sum 0
8 score(0) 100
9 score(1) 65
10 score(2) 76
11 score(3) 87
12 score(4) 61
13 score(5) 99
14 For i 0 To 5 Step 1
15 xxxxxxxxxxxxxxx
16 Next i
17 MsgBox Sum of score for i1 students is sum
18
19 End Sub
19
1 Sub sum()
2 Calculate the sum of score for all student using For - Next
3 score array name, i counter
4 sum variable for sum, ave variable for average
5 Dim score(5) As Integer
6 Dim i As Integer
7 Dim sum As Integer
8 Dim ave As Single
9 sum 0
10 ave 0.0
11 score(0) 100
12 score(1) 65
13 score(2) 76
14 score(3) 87
15 score(4) 61
16 score(5) 99
17 For i 0 To 5 Step 1
18 xxxxxxxxxx
19 Next i
20 ave yyyyyyy
21 MsgBox Sum of score for i1 students is sum
22 MsgBox Average is ave
23 End Sub