"成績データベース" 

"出力例"

InputBoxメソッドを使ってデータを入力させ、簡易成績データベースを作成しろ

入力項目は、氏名、学籍番号、中間点数、期末点数

"サンプルマクロ01"

"ヒント・解説01"

Public Function JGrade(score As Integer) As String

Select Case score

Case 90 To 100
    JGrade = "AA"
Case 80 To 89
    JGrade = "A"
Case 70 To 79
    JGrade = "B"
Case 60 To 69
    JGrade = "C"
Case 0 To 59
    JGrade = "F"
Case Else
    JGrade = "I"
    
End Select

End Function


Public Sub Createtable01()

Dim n As Boolean
Dim i As Integer, j As Integer, k As Integer
Dim res As Byte, nrow As Long
Dim h(8) As Variant, s As Variant, t As Variant

n = True '終了フラグ
s = Array("No.", "氏名", "学籍番号", "中間点数", "期末点数", "合計", "平均", "判定")
t = Array(1, 2, 2, 1, 1, 1, 1, 2) 'InputBox()の入力タイプ 1(数値),2(文字)

With Range("A1:H1") '1行目タイトル

    .MergeCells = True
    .Value = "成績データベース"
    
End With

For i = 0 To UBound(s) '2行目項目

    Cells(2, i + 1).Value = s(i)

Next i

Do While n = True

    For j = 1 To 4 '必要な情報を入力させる
    
        h(j) = Application.InputBox(s(j) & "を入力してください", "成績データベース", "100", , , , , t(j))
        
        If VarType(h(j)) = vbBoolean Then 'キャンセルを押すと終了させる
            MsgBox "キャンセルしました", vbInformation
            Exit Sub
        End If
            
    Next j
    
    nrow = Range("A1").CurrentRegion.Rows.Count + 1 '最終行の次の行
    
    h(0) = nrow - 2
    h(5) = Application.WorksheetFunction.Sum(h(3), h(4))
    h(6) = Application.WorksheetFunction.Average(h(3), h(4))
    h(7) = JGrade(CInt(h(6)))
    
    For k = LBound(s) To UBound(s) '各項目の値をセルに代入する
    
        Cells(nrow, k + 1).Value = h(k)
    
    Next k
    
    res = MsgBox("続けて入力しますか？", vbQuestion + vbYesNo)
    
    If res = vbNo Then 'いいえを押すと終了させる
        MsgBox "終了します", vbInformation
        n = False
    End If
    
Loop

End Sub

    

  boolean型の変数nで終了フラグをつくる

  InputBox、MsgBox関数の引数ごとに処理を変える