Final Excel VBA Project – Complete Student Management System

The final project is a complete student management application created using Excel and VBA.

The system will contain:

• Student data entry
• Automatic calculations
• Grade calculation
• Pass or fail result
• Student search
• Student update
• Student deletion
• Automatic reports
• Data validation
• Formatted worksheets

Create an Excel workbook with the following worksheets:

• Students – main student database.
• Search – student search results.
• Report – generated student report.
• Dashboard – summary information.

Keeping different functions in separate worksheets makes the application easier to manage.

Create the main database with the following headings:

A1 = Student ID
B1 = Student Name
C1 = Father Name
D1 = Course
E1 = English
F1 = Computer
G1 = Mathematics
H1 = Total
I1 = Percentage
J1 = Grade
K1 = Result
L1 = Mobile
M1 = Date

All student records will be stored in this worksheet.

Create a UserForm to enter student information.

The form can contain:

• Student ID
• Student Name
• Father Name
• Course
• English Marks
• Computer Marks
• Mathematics Marks
• Mobile Number

Add these buttons:

• Save
• Search
• Update
• Delete
• Clear
• Close

Give the controls meaningful names.

txtStudentID
txtStudentName
txtFatherName
txtCourse
txtEnglish
txtComputer
txtMath
txtMobile

cmdSave
cmdSearch
cmdUpdate
cmdDelete
cmdClear
cmdClose

Meaningful names make the VBA project easier to maintain.

The Save button will store a new student record in the Students worksheet.

Private Sub cmdSave_Click()

    Dim ws As Worksheet
    Dim nextRow As Long

    Set ws = ThisWorkbook.Worksheets("Students")

    nextRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1

End Sub

The next available row is automatically identified.

Important fields should not be left empty.

If Trim(txtStudentID.Value) = "" Then
    MsgBox "Please enter Student ID."
    Exit Sub
End If

If Trim(txtStudentName.Value) = "" Then
    MsgBox "Please enter Student Name."
    Exit Sub
End If

If Trim(txtCourse.Value) = "" Then
    MsgBox "Please enter Course."
    Exit Sub
End If

Trim() removes unnecessary spaces from the beginning and end of the entered text.

Marks should be between 0 and 100.

If Val(txtEnglish.Value) < 0 Or Val(txtEnglish.Value) > 100 Then
    MsgBox "English marks must be between 0 and 100."
    Exit Sub
End If

If Val(txtComputer.Value) < 0 Or Val(txtComputer.Value) > 100 Then
    MsgBox "Computer marks must be between 0 and 100."
    Exit Sub
End If

If Val(txtMath.Value) < 0 Or Val(txtMath.Value) > 100 Then
    MsgBox "Mathematics marks must be between 0 and 100."
    Exit Sub
End If

The total marks are calculated by adding the three subjects.

Dim total As Double

total = Val(txtEnglish.Value) + _
        Val(txtComputer.Value) + _
        Val(txtMath.Value)

The maximum total is 300.

Percentage can be calculated from the total marks.

Dim percentage As Double

percentage = (total / 300) * 100

The percentage can then be stored in the worksheet.

Use If...Then...ElseIf to calculate the student's grade.

Dim grade As String

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 student can be marked Pass or Fail according to the percentage.

Dim result As String

If percentage >= 40 Then
    result = "Pass"
Else
    result = "Fail"
End If

The calculated information and form values can now be stored.

ws.Cells(nextRow, 1).Value = txtStudentID.Value
ws.Cells(nextRow, 2).Value = txtStudentName.Value
ws.Cells(nextRow, 3).Value = txtFatherName.Value
ws.Cells(nextRow, 4).Value = txtCourse.Value
ws.Cells(nextRow, 5).Value = Val(txtEnglish.Value)
ws.Cells(nextRow, 6).Value = Val(txtComputer.Value)
ws.Cells(nextRow, 7).Value = Val(txtMath.Value)
ws.Cells(nextRow, 8).Value = total
ws.Cells(nextRow, 9).Value = percentage
ws.Cells(nextRow, 10).Value = grade
ws.Cells(nextRow, 11).Value = result
ws.Cells(nextRow, 12).Value = txtMobile.Value
ws.Cells(nextRow, 13).Value = Date

The Search button can find a student using Student ID.

Dim studentID As String
Dim lastRow As Long
Dim i As Long

studentID = InputBox("Enter Student ID:")

If studentID = "" Then Exit Sub

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

For i = 2 To lastRow

    If CStr(ws.Cells(i, 1).Value) = studentID Then

        MsgBox "Student Found"

        Exit For

    End If

Next i

After finding the student, the existing values can be loaded into the UserForm.

txtStudentID.Value = ws.Cells(i, 1).Value
txtStudentName.Value = ws.Cells(i, 2).Value
txtFatherName.Value = ws.Cells(i, 3).Value
txtCourse.Value = ws.Cells(i, 4).Value
txtEnglish.Value = ws.Cells(i, 5).Value
txtComputer.Value = ws.Cells(i, 6).Value
txtMath.Value = ws.Cells(i, 7).Value
txtMobile.Value = ws.Cells(i, 12).Value

This allows the user to view and edit an existing record.

The Update button can modify an existing student record.

First, find the row containing the Student ID. Then replace the existing values with the updated information.

ws.Cells(i, 2).Value = txtStudentName.Value
ws.Cells(i, 3).Value = txtFatherName.Value
ws.Cells(i, 4).Value = txtCourse.Value
ws.Cells(i, 5).Value = Val(txtEnglish.Value)
ws.Cells(i, 6).Value = Val(txtComputer.Value)
ws.Cells(i, 7).Value = Val(txtMath.Value)
ws.Cells(i, 12).Value = txtMobile.Value

After changing marks, total, percentage, grade, and result should also be recalculated.

total = Val(txtEnglish.Value) + _
        Val(txtComputer.Value) + _
        Val(txtMath.Value)

percentage = (total / 300) * 100

ws.Cells(i, 8).Value = total
ws.Cells(i, 9).Value = percentage

This keeps the calculated information synchronized with the marks.

The Delete button can remove a selected student record.

If MsgBox("Delete this student?", _
    vbYesNo + vbQuestion) = vbYes Then

    ws.Rows(i).Delete

    MsgBox "Student record deleted."

End If

The confirmation message helps prevent accidental deletion.

The Clear button can reset all form controls.

Private Sub cmdClear_Click()

    txtStudentID.Value = ""
    txtStudentName.Value = ""
    txtFatherName.Value = ""
    txtCourse.Value = ""
    txtEnglish.Value = ""
    txtComputer.Value = ""
    txtMath.Value = ""
    txtMobile.Value = ""

End Sub

The Close button can close the UserForm.

Private Sub cmdClose_Click()

    Unload Me

End Sub

Unload Me removes the current UserForm from memory and closes it.

The Report worksheet can display selected information from the Students worksheet.

A1 = Student Management Report
A3 = Student ID
B3 = Student Name
C3 = Course
D3 = Total
E3 = Percentage
F3 = Grade
G3 = Result

VBA can copy all student records into this report automatically.

Use a loop to copy the records.

Dim wsReport As Worksheet
Dim reportRow As Long

Set wsReport = ThisWorkbook.Worksheets("Report")

wsReport.Cells.Clear

reportRow = 4

For i = 2 To lastRow

    wsReport.Cells(reportRow, 1).Value = ws.Cells(i, 1).Value
    wsReport.Cells(reportRow, 2).Value = ws.Cells(i, 2).Value
    wsReport.Cells(reportRow, 3).Value = ws.Cells(i, 4).Value
    wsReport.Cells(reportRow, 4).Value = ws.Cells(i, 8).Value
    wsReport.Cells(reportRow, 5).Value = ws.Cells(i, 9).Value
    wsReport.Cells(reportRow, 6).Value = ws.Cells(i, 10).Value
    wsReport.Cells(reportRow, 7).Value = ws.Cells(i, 11).Value

    reportRow = reportRow + 1

Next i

Format the report automatically after generating it.

With wsReport.Range("A3:G3")
    .Font.Bold = True
End With

With wsReport.Range("A3:G" & reportRow - 1)
    .Borders.LineStyle = xlContinuous
End With

wsReport.Columns("A:G").AutoFit

This makes the report easier to read.

A simple Dashboard worksheet can display summary information.

For example:

A1 = Student Management Dashboard

A3 = Total Students
A4 = Passed Students
A5 = Failed Students
A6 = Average Percentage

VBA can calculate these values from the Students worksheet.

The total number of students can be calculated from column A.

Dim totalStudents As Long

totalStudents = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row - 1

Worksheets("Dashboard").Range("B3").Value = totalStudents

The subtraction of 1 excludes the heading row.

We can use CountIf to count Pass and Fail results.

Dim passedStudents As Long
Dim failedStudents As Long

passedStudents = WorksheetFunction.CountIf( _
    ws.Range("K2:K" & lastRow), "Pass")

failedStudents = WorksheetFunction.CountIf( _
    ws.Range("K2:K" & lastRow), "Fail")

These values can be displayed on the Dashboard.

The average percentage can be calculated using WorksheetFunction.

Dim averagePercentage As Double

averagePercentage = WorksheetFunction.Average( _
    ws.Range("I2:I" & lastRow))

Worksheets("Dashboard").Range("B6").Value = _
    averagePercentage

This gives a quick overview of student performance.

The following code combines the major operations required to save a student record.

Private Sub cmdSave_Click()

    Dim ws As Worksheet
    Dim nextRow As Long
    Dim total As Double
    Dim percentage As Double
    Dim grade As String
    Dim result As String

    If Trim(txtStudentID.Value) = "" Then
        MsgBox "Please enter Student ID."
        Exit Sub
    End If

    If Trim(txtStudentName.Value) = "" Then
        MsgBox "Please enter Student Name."
        Exit Sub
    End If

    If Trim(txtCourse.Value) = "" Then
        MsgBox "Please enter Course."
        Exit Sub
    End If

    If Val(txtEnglish.Value) < 0 Or Val(txtEnglish.Value) > 100 Then
        MsgBox "English marks must be between 0 and 100."
        Exit Sub
    End If

    If Val(txtComputer.Value) < 0 Or Val(txtComputer.Value) > 100 Then
        MsgBox "Computer marks must be between 0 and 100."
        Exit Sub
    End If

    If Val(txtMath.Value) < 0 Or Val(txtMath.Value) > 100 Then
        MsgBox "Mathematics marks must be between 0 and 100."
        Exit Sub
    End If

    Set ws = ThisWorkbook.Worksheets("Students")

    nextRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1

    total = Val(txtEnglish.Value) + _
            Val(txtComputer.Value) + _
            Val(txtMath.Value)

    percentage = (total / 300) * 100

    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

    If percentage >= 40 Then
        result = "Pass"
    Else
        result = "Fail"
    End If

    ws.Cells(nextRow, 1).Value = txtStudentID.Value
    ws.Cells(nextRow, 2).Value = txtStudentName.Value
    ws.Cells(nextRow, 3).Value = txtFatherName.Value
    ws.Cells(nextRow, 4).Value = txtCourse.Value
    ws.Cells(nextRow, 5).Value = Val(txtEnglish.Value)
    ws.Cells(nextRow, 6).Value = Val(txtComputer.Value)
    ws.Cells(nextRow, 7).Value = Val(txtMath.Value)
    ws.Cells(nextRow, 8).Value = total
    ws.Cells(nextRow, 9).Value = percentage
    ws.Cells(nextRow, 10).Value = grade
    ws.Cells(nextRow, 11).Value = result
    ws.Cells(nextRow, 12).Value = txtMobile.Value
    ws.Cells(nextRow, 13).Value = Date

    MsgBox "Student record saved successfully."

End Sub

Test every feature before considering the project complete.

Test the following:

• Add a new student.
• Enter valid marks.
• Try invalid marks such as 105.
• Search for an existing student.
• Search for a non-existing student.
• Update student information.
• Delete a student record.
• Clear the form.
• Generate the report.
• Check Dashboard statistics.

Testing each feature helps identify errors before the application is used with real data.

You have completed a complete Excel VBA learning path and built the foundation of a practical Student Management System.

The final project combines:

• Excel Macros
• VBA Editor
• Variables
• Data Types
• Conditions
• Loops
• Workbook and Worksheet objects
• Range and Cells
• UserForms
• Input validation
• Calculations
• Search functionality
• Update functionality
• Delete functionality
• Automatic reports
• Dashboard summaries

You can further expand this project with attendance management, fee management, certificate generation, printing, charts, login systems, and other business automation features.