第３話課題
入力して
実行ボタンを押すと、
となるマクロをFunctionプロシージャを使って実現課題解答例
Private Sub CommandButton1_Click()
Dim a As Integer, b As Integer, c As Integer
a = Cells(6, 2)
b = Cells(7, 2)
c = Cells(8, 2)
Cells(9, 1) = "和"
Cells(9, 2) = wa(a, b, c)
Cells(10, 1) = "積"
Cells(10, 2) = seki(a, b, c)
End Sub
Private Sub CommandButton2_Click()
Range("B6:B10").Select
Selection.ClearContents
Cells(1, 1).Select
End Sub
Function wa(a As Integer, b As Integer, c As Integer)
Dim i As Byte
wa = 0
For i = a To b Step c
wa = wa + i
Next
End Function
Function seki(a As Integer, b As Integer, c As Integer)
Dim i As Byte
seki = 1
For i = a To b Step c
seki = seki * i
Next
End Function
第５話以下の課題です。
第７講第９話において作った成績一覧表ソフト（参考ダウンロードファイル成績一覧表社員版）
Private Sub CommandButton1_Click()
hyouji
datasakusei
syori
hyouka
sonota
End Sub
Private Sub CommandButton2_Click()
Rows("6:200").Select
Selection.ClearContents
Cells(1, 1).Select
End Sub
Sub hyouji()
Cells(6, 2) = "出席番号"
Cells(6, 3) = "国語"
Cells(6, 4) = "社会"
Cells(6, 5) = "数学"
Cells(6, 6) = "理科"
Cells(6, 7) = "英語"
Cells(6, 8) = "合計"
Cells(6, 9) = "平均"
Cells(6, 10) = "評価"
Cells(6, 11) = "3段階評価"
Cells(6, 12) = "4段階評価"
Cells(6, 13) = "5段階評価"
End Sub
Sub datasakusei()
Dim i As Byte
For i = 0 To 39
Cells(7 + i, 2) = i + 1
For j = 0 To 4
Cells(7 + i, 3 + j) = Int(100 * Rnd())
Next
Next
End Sub
Sub syori()
Dim i As Byte, j As Byte
For i = 0 To 4
w = 0
For j = 0 To 39
w = w + Cells(7 + j, 3 + i)
Next
Cells(47, 3 + i) = w
Cells(48, 3 + i) = w / 40
If w / 10 >= 50 Then
Cells(49, 3 + i) = "合格"
Else
Cells(49, 3 + i) = "不合格"
End If
Next
End Sub
Sub hyouka()
Dim i As Byte, j As Byte, w As Integer
For i = 0 To 39
w = 0
For j = 0 To 4
w = w + Cells(7 + i, 3 + j)
Next
Cells(7 + i, 8) = w
Cells(7 + i, 9) = w / 5
If w / 5 >= 50 Then
Cells(7 + i, 10) = "合格"
Else
Cells(7 + i, 10) = "不合格"
End If
If w / 5 >= 55 Then
Cells(7 + i, 11) = "優秀"
Else
If w / 5 >= 50 Then
Cells(7 + i, 11) = "普通"
Else
Cells(7 + i, 11) = "努力が必要"
End If
End If
If w / 5 >= 60 Then
Cells(7 + i, 12) = "天才級"
Else
If w / 5 >= 55 Then
Cells(7 + i, 12) = "秀才級"
Else
If w / 5 >= 50 Then
Cells(7 + i, 12) = "平凡"
Else
Cells(7 + i, 12) = "不出来"
End If
End If
End If
If w / 5 >= 65 Then
Cells(7 + i, 13) = "神"
Else
If w / 5 >= 60 Then
Cells(7 + i, 13) = "准超越者"
Else
If w / 5 >= 55 Then
Cells(7 + i, 13) = "人間"
Else
If w / 5 >= 45 Then
Cells(7 + i, 13) = "人造人間ベム・ベロ・ベラ級"
Else
Cells(7 + i, 13) = "ピノキオ以下の存在"
End If
End If
End If
End If
Next
End Sub
Sub sonota()
Dim i As Byte, w As Integer
w = 0
For i = 1 To 10
w = w + Cells(6 + i, 8)
Next
Cells(47, 8) = w
Cells(48, 8) = w / 40
Dim v As Single
v = 0
For i = 1 To 39
v = v + Cells(6 + i, 9)
Next
Cells(47, 9) = v
Cells(48, 9) = v / 10
Cells(47, 2) = "合計"
Cells(48, 2) = "平均"
Cells(49, 2) = "評価"
End Sub
について少しずつ改良していきます。
まず、
Private Sub CommandButton1_Click()
hyouji
datasakusei
syori
hyouka
sonota
End Sub
を
Private Sub CommandButton1_Click()
hyouji
datasakusei
Dim i As Byte
For i = 0 To 4
w = syori(i)
・
・
Next
hyouka
sonota
End Sub
と改良して同じ結果が出るようにしていただきたいのです。
syori(i)はFunctionプロシージャにして、縦合計のみを計算する任務のみに縮小します。
それ以外の仕事は、
For i = 0 To 4
w = syori(i)
・
・
Next
の省略されている部分で行ってください。
第３話へ 第５話へ
vc++講義へ
