Free Microsoft Excel Training Videos

Project in Excel using macro

How to enter data and calculate grades automatically

Excel Project
As soon as the Excel macro is run the headers appear.
Then the user gets an inputbox to enter the name of the student.
After entering the name, the user is asked to enter the marks of the student in 5 subjects of 100 marks each using the 'for' loop.
Applying standard calculation code and 'if' function the total, percentage and grade of the student is calculated.
If the student achieves an 'A' grade, this is formatted to highlight his achievement in red color. In fact, a custom form has been created for the user in Excel to automate grade calculations.
The entire code has been reproduced below.
Watch the video below to see the macro in action:






Macro Code:
Sub calculating_grades()
 Range("A3").Value = "Name"
 Range("B3").Value = "Marks1"
 Range("C3").Value = "Marks2"
 Range("D3").Value = "Marks3"
 Range("E3").Value = "Marks4"
 Range("F3").Value = "Marks5"
 Range("G3").Value = "Total"
 Range("H3").Value = "Percentage"
 Range("I3").Value = "Grade"
 Range("A3:I3").Font.Bold = True
 Range("A3:I3").Font.ColorIndex = 5
 Range("A3:I3").Interior.ColorIndex = 6
 Range("A3:I3").Columns.AutoFit
 
 question = "y"
 Do While question = "y"
 erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
question = Application.InputBox("Do you wish to enter data? Type 'n'end program or 'y' to continue", "Question")
 If question = "n" Or question = "no" Then End
 StudentName = Application.InputBox("Enter the student's name", "Student's Name")
 
 Cells(erow, 1).Value = StudentName
 'assume 5 subjects
 'initialize marks, total
 marks = 0
 total = 0
 For counter = 2 To 6
 marks = Application.InputBox("Enter the student's marks in the 5 subjects", "Marks")
 Cells(erow, counter).Value = marks
MsgBox "You entered marks" & counter & " " & 5 - counter & " to go"
 total = total + marks
 Cells(erow, 7).Value = total
 Dim percentage As Single
 percentage = 0
 percentage = total / 5
 Cells(erow, 8).Value = percentage
 If percentage >= 90 Then
 Grade = "A"
 Cells(erow, 8).Font.Bold = True
 Cells(erow, 9).Font.ColorIndex = 5
 Cells(erow, 9).Interior.ColorIndex = 6
 ElseIf percentage >= 80 Then
 Grade = "B"
 ElseIf percentage >= 70 Then
 Grade = "C"
 ElseIf percentage >= 60 Then
 Grade = "D"
 Else
 Grade = "Work Harder!"
 End If
 Cells(erow, 9).Value = Grade
 Next counter
Loop

End Sub

Further Reading Excel Macro Examples
Project in Excel using macro