A Lecture 15 Takeshi Tokuyama Tohoku University Graduate

情報基礎A　　 Lecture 15 Takeshi Tokuyama Tohoku University Graduate School of Information Sciences System Information Sciences Design and Analysis of Information Systems

PROGRAMMING VBA DATA PROCESSING PLUS

Chart Insertion 1 Sub chart() 2 3 Worksheets(“Statistics”). Activate 4 5 Charts. Add 6 Active. Chart. Type = xl. Column. Stacked 100 7 Active. Chart. Set. Source. Data Source : = Sheets(“Statistics”). Range(“A 2: G 8”), Plot. By : = xl. Rows 8 Active. Chart. Location Where : = xl. Location. As. Object, Name : = “Statistics" 9 End Sub

Procedure Call • Calling procedure from different procedure – Procedure already written • • • Sub sum_ave_6 sub() Sub grade_6 sub() Sub student_ave() Sub stat_pass_fail() Sub stat_grade() Sub chart() – Instead of executing each procedure, write a new procedure to call of them

Procedure Call • Write procedures to execute in Sub score() – Call “Procedure name” • Six procedures are executed in order 1 Sub score() 2 3 Call sum_ave_6 sub 4 Call grade_6 sub 5 Call student_ave 6 Call stat_pass_fail 7 Call stat_grade 8 Call chart 9 10 End Sub

PROCESS 20 FILES AT ONCE

Preparation • Download “hoge”

20 Data File and Statistics File

Exercise • Score data processing for 20 classes – Execute six process below for each file • • • Sub sum_ave_6 sub() Sub grade_6 sub() Sub student_ave() Sub stat_pass_fail() Sub stat_grade() Sub chart() – Procedure name: score() • Output statistics of 20 classes into score. xls

File Operation • Open method 1 Workbooks. Open Filename : = “class 1. xls” • Open 20 files in order – Utilize number in filename as a variable 1 2 3 For i= a To 20 Workbooks. Open Filename : = “class” & i & “. xls” Next i • Save and close active file 1 Active. Workbook. Save 2 Active. Window. Close

Accessing the other file: Copy data to a file • Copy the number of student who got “A” for Japanese on sheet “Statistics” to score. xls>sheet: 2011 ->Japanese->A – Copy a cell B 4 on sheet “Statistics” of active file to a cell B 3 on score. xls 1 Workbooks("seiseki. xls"). Worksheets("平成 21年"). Cells(3, 2) = Worksheets("統計"). Cells(4, 2)

1 Sub score() Dim i As Integer 2 Dim m As Integer 3 Dim n As Integer 4 Dim x As Integer 5 Workbooks. Open 6 Filename: =“\netsrv 22c 90 a 1 rluMy. Documentsfoobarbaz. xls" 7 For i = 1 To 20 8 Workbooks. Open Filename: ="\netsrv 22c 90 a 1 rluMy. Documentsfoobarclass" & i & ". xls" 9 10 Call goukei_6 kamoku 11 Call hyouka_6 kamoku 12 Call kojin_heikin 13 Call toukei_gouhi 14 Call toukei_hyoka 15 Call graph 16 Workbooks(“baz. xls"). Worksheets(“ 2011”). Cells(52, i + 1) = Worksheets(“Statistics”). Cells(12, 2) 17 Workbooks(“baz. xls"). Worksheets(“ 2011”). Cells(53, i + 1) = Worksheets(“Statistics”). Cells(13, 2) 18 x = 0 19 For m = 1 To 6 For n = 2 To 6 20 Workbooks("seiseki. xls"). Worksheets(“ 2011"). Cells(n+1+x, i+1)=Worksheets(“Statistics"). Cells(n+2, m+1) 21 Next n 22 x=x+8 23 24 Next m 25 Active. Workbook. Save 26 Active. Window. Close 27 Next i 28 Workbooks("seiseki. xls"). Save 29 End Sub

Copy a path \netsrv 22c 90 a 1 rluMy. Documentsfoobarbaz. xls

Submit