Automatic Grade Calculation in VBA

Grade calculation means assigning a grade to a student based on their percentage or marks.

For example:

• 90% or above → A+
• 80% to 89.99% → A
• 70% to 79.99% → B
• 60% to 69.99% → C
• 50% to 59.99% → D
• 40% to 49.99% → E
• Below 40% → F

When there are many students, calculating grades manually can be repetitive. VBA can calculate grades automatically.

Automation can:

• Save time
• Reduce repetitive work
• Apply the same grading rules consistently
• Process many students
• Update grades quickly

Create a worksheet named Grades.

Enter sample percentages into column C.

Column D will be filled automatically by VBA.

Open the VBA Editor using:

Alt + F11

Insert a standard module and create a procedure:

Sub CalculateGrade()

End Sub

Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets("Grades")

The variable ws now represents the Grades worksheet.

Dim percentage As Double
Dim grade As String

The percentage variable stores the student's percentage and the grade variable stores the calculated grade.

The percentage is stored in column C. For the first student, the percentage can be read from C2.

percentage = ws.Cells(2, 3).Value

The value from C2 is stored in the percentage variable.

If the percentage is 90 or above, assign A+.

If percentage >= 90 Then

    grade = "A+"

End If

If the percentage is at least 80 but below 90, assign A.

If percentage >= 80 And percentage < 90 Then

    grade = "A"

End If

When using ElseIf, the second condition can simply be:

ElseIf percentage >= 80 Then

    grade = "A"

A percentage of 70 or above can receive a B grade.

ElseIf percentage >= 70 Then

    grade = "B"

Because the conditions are checked from highest to lowest, values from 70 to 79.99 reach this condition.

ElseIf percentage >= 60 Then

    grade = "C"

This handles percentages from 60 to 69.99 when the conditions are checked from highest to lowest.

ElseIf percentage >= 50 Then

    grade = "D"

This handles percentages from 50 to 59.99.

ElseIf percentage >= 40 Then

    grade = "E"

This handles percentages from 40 to 49.99.

Any percentage below 40 can receive an F grade.

Else

    grade = "F"

The Else block handles all remaining values.

The complete grading structure is:

If percentage >= 90 Then

    grade = "A+"

ElseIf percentage >= 80 Then

    grade = "A"

ElseIf percentage >= 70 Then

    grade = "B"

ElseIf percentage >= 60 Then

    grade = "C"

ElseIf percentage >= 50 Then

    grade = "D"

ElseIf percentage >= 40 Then

    grade = "E"

Else

    grade = "F"

End If

The Grade column is column D.

ws.Cells(2, 4).Value = grade

The calculated grade is written into D2.

Select Case is another useful way to calculate grades.

Select Case percentage

    Case Is >= 90
        grade = "A+"

    Case Is >= 80
        grade = "A"

    Case Is >= 70
        grade = "B"

    Case Is >= 60
        grade = "C"

    Case Is >= 50
        grade = "D"

    Case Is >= 40
        grade = "E"

    Case Else
        grade = "F"

End Select

Both methods can be used to calculate grades.

For a simple percentage grading system, either approach can be used.

To calculate grades for multiple students, find the last used row.

Dim lastRow As Long

lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

Column A is used to find the last student record.

A For...Next loop can calculate the grade for every student.

Dim i As Long

For i = 2 To lastRow

    percentage = ws.Cells(i, 3).Value

    'Grade calculation

Next i

For i = 2 To lastRow

    percentage = ws.Cells(i, 3).Value

    If percentage >= 90 Then

        grade = "A+"

    ElseIf percentage >= 80 Then

        grade = "A"

    ElseIf percentage >= 70 Then

        grade = "B"

    ElseIf percentage >= 60 Then

        grade = "C"

    ElseIf percentage >= 50 Then

        grade = "D"

    ElseIf percentage >= 40 Then

        grade = "E"

    Else

        grade = "F"

    End If

    ws.Cells(i, 4).Value = grade

Next i

Before calculating the grade, check whether a Student ID exists.

If ws.Cells(i, 1).Value <> "" Then

    percentage = ws.Cells(i, 3).Value

    'Grade calculation

End If

This prevents unnecessary processing of completely blank rows.

A percentage normally should be between 0 and 100.

If percentage < 0 Or percentage > 100 Then

    MsgBox "Invalid percentage in row " & i
    Exit Sub

End If

This prevents an invalid percentage from being assigned a grade.

The following program calculates grades for all students in the Grades worksheet.

Sub CalculateGrades()

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long

    Dim percentage As Double
    Dim grade As String

    Set ws = ThisWorkbook.Worksheets("Grades")

    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    For i = 2 To lastRow

        If ws.Cells(i, 1).Value <> "" Then

            percentage = ws.Cells(i, 3).Value

            If percentage < 0 Or percentage > 100 Then

                MsgBox "Invalid percentage in row " & i
                Exit Sub

            End If

            If percentage >= 90 Then

                grade = "A+"

            ElseIf percentage >= 80 Then

                grade = "A"

            ElseIf percentage >= 70 Then

                grade = "B"

            ElseIf percentage >= 60 Then

                grade = "C"

            ElseIf percentage >= 50 Then

                grade = "D"

            ElseIf percentage >= 40 Then

                grade = "E"

            Else

                grade = "F"

            End If

            ws.Cells(i, 4).Value = grade

        End If

    Next i

    MsgBox "Grades Calculated Successfully"

End Sub

The Grade column can be formatted to make the result easier to read.

With ws.Range("D1:D" & lastRow)

    .HorizontalAlignment = xlCenter
    .Borders.LineStyle = xlContinuous

End With

You can also make the grade values bold.

ws.Range("D2:D" & lastRow).Font.Bold = True

VBA can apply different formatting depending on the grade.

Select Case ws.Cells(i, 4).Value

    Case "A+"
        ws.Cells(i, 4).Interior.Color = vbGreen

    Case "A"
        ws.Cells(i, 4).Interior.Color = RGB(146, 208, 80)

    Case "B"
        ws.Cells(i, 4).Interior.Color = vbYellow

    Case "C"
        ws.Cells(i, 4).Interior.Color = RGB(255, 192, 0)

    Case "D", "E"
        ws.Cells(i, 4).Interior.Color = RGB(255, 230, 153)

    Case "F"
        ws.Cells(i, 4).Interior.Color = vbRed

End Select

• Define the grading rules clearly before writing the VBA code.
• Check the highest percentage first.
• Use ElseIf or Select Case for multiple grade ranges.
• Validate percentages before calculating grades.
• Keep the original percentage unchanged.
• Store the calculated grade in a separate column.
• Use a loop to process multiple students.
• Use consistent formatting.
• Test boundary values such as 40, 50, 60, 70, 80, and 90.

The complete workflow is:

1. Prepare the student worksheet.
2. Read the student's percentage.
3. Validate the percentage.
4. Compare the percentage with grading ranges.
5. Assign the appropriate grade.
6. Write the grade into the worksheet.
7. Format the grade if required.
8. Repeat the process for all students.

Percentage
     ↓
Validation
     ↓
Grade Rules
     ↓
Grade
     ↓
Write to Excel
     ↓
Format

Automatic grade calculation is an important part of an Excel VBA result system. VBA can read the percentage of each student and automatically assign a grade according to predefined rules.

The basic grading structure is:

If percentage >= 90 Then
    grade = "A+"
ElseIf percentage >= 80 Then
    grade = "A"
ElseIf percentage >= 70 Then
    grade = "B"
ElseIf percentage >= 60 Then
    grade = "C"
ElseIf percentage >= 50 Then
    grade = "D"
ElseIf percentage >= 40 Then
    grade = "E"
Else
    grade = "F"
End If

The grade can then be written into Excel:

ws.Cells(i, 4).Value = grade

For multiple students, a For...Next loop can repeat the same process. Select Case can also be used when you want to organize grading ranges in a clear case-based structure.

This technique can be combined with automatic total, percentage, and result calculation to create a complete student result system.