Excel VBA Mini Project – Student Management System

Our project will be a basic student management system created completely inside Microsoft Excel using VBA.

The system will provide features such as:

• Student data entry
• Student record storage
• Student search
• Total marks calculation
• Percentage calculation
• Grade calculation
• Pass or fail result
• Automatic report generation

We can organize the workbook into different worksheets.

• Students – stores student information and marks.
• Search – displays searched student information.
• Report – contains automatically generated reports.

This separation makes the project easier to manage.

Create a worksheet named Students.

Use the following headings:

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

This worksheet will act as the main student database.

Create a UserForm for entering student information.

The form can contain:

• Student ID TextBox
• Student Name TextBox
• Course TextBox
• English Marks TextBox
• Computer Marks TextBox
• Mathematics Marks TextBox
• Save button
• Clear button
• Close button

Use meaningful names for the controls.

txtStudentID
txtStudentName
txtCourse
txtEnglish
txtComputer
txtMath
cmdSave
cmdClear
cmdClose

Meaningful names make VBA code easier to understand.

The Save button needs to work with the Students worksheet.

Dim ws As Worksheet

Set ws = ThisWorkbook.Worksheets("Students")

The variable ws now represents the Students worksheet.

Each new student should be saved below the previous record.

Dim nextRow As Long

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

This finds the next available row in column A.

Before saving, make sure the Student ID has been entered.

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

This prevents an empty Student ID from being stored.

We can also check the student's name.

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

Similar validation can be added for the course and marks.

Marks should normally 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

The same type of validation can be used for Computer and Mathematics.

Student information can be written into the worksheet using the Cells property.

ws.Cells(nextRow, 1).Value = txtStudentID.Value
ws.Cells(nextRow, 2).Value = txtStudentName.Value
ws.Cells(nextRow, 3).Value = txtCourse.Value

These statements save the Student ID, Name, and Course.

The three subject marks can be saved in columns D, E, and F.

ws.Cells(nextRow, 4).Value = Val(txtEnglish.Value)
ws.Cells(nextRow, 5).Value = Val(txtComputer.Value)
ws.Cells(nextRow, 6).Value = Val(txtMath.Value)

Val() converts the entered text into a numeric value.

The total marks can be calculated by adding the three subjects.

Dim total As Double

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

The result can then be saved in column G.

ws.Cells(nextRow, 7).Value = total

Since three subjects have a maximum of 300 marks, the percentage can be calculated as follows.

Dim percentage As Double

percentage = (total / 300) * 100

ws.Cells(nextRow, 8).Value = percentage

We can use If...Then...ElseIf to calculate the 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 grade can be stored in column I.

A simple result condition can be created using percentage.

Dim result As String

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

The result can be saved in column J.

The current date can be automatically saved when the record is added.

ws.Cells(nextRow, 11).Value = Date

This stores the current date in column K.

After saving the complete record, display a confirmation message.

MsgBox "Student record saved successfully."

This provides feedback to the user.

After saving, clear the TextBoxes so another student can be entered.

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

The project can include a Student ID search feature. VBA can loop through the Students worksheet and compare the entered Student ID.

For i = 2 To lastRow

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

        MsgBox "Student Found"

        Exit For

    End If

Next i

This allows the user to find an existing student record.

Create another worksheet named Search.

The searched student's information can be displayed in this sheet.

A1 = Student Search Result
A3 = Student ID
A4 = Student Name
A5 = Course
A6 = Total
A7 = Percentage
A8 = Grade
A9 = Result

Create a worksheet named Report.

The report can contain:

• Student ID
• Name
• Course
• Total Marks
• Percentage
• Grade
• Result

VBA can generate this report automatically.

A For loop can copy the required information from Students to the Report worksheet.

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, 3).Value
    wsReport.Cells(reportRow, 4).Value = ws.Cells(i, 7).Value
    wsReport.Cells(reportRow, 5).Value = ws.Cells(i, 8).Value
    wsReport.Cells(reportRow, 6).Value = ws.Cells(i, 9).Value
    wsReport.Cells(reportRow, 7).Value = ws.Cells(i, 10).Value

    reportRow = reportRow + 1

Next i

The report should be formatted so that it is easy to read.

With wsReport.Range("A1:G1")
    .Font.Bold = True
End With

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

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

This creates a simple professional-looking report.

A Clear button can remove all values from the form.

Private Sub cmdClear_Click()

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

End Sub

This allows the user to quickly prepare the form for a new record.

The Close button can close the UserForm.

Private Sub cmdClose_Click()

    Unload Me

End Sub

Unload Me closes the currently active UserForm.

The following code combines validation, calculations, and saving the 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 txtStudentID.Value = "" Then
        MsgBox "Please enter Student ID."
        Exit Sub
    End If

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

    If 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 = txtCourse.Value
    ws.Cells(nextRow, 4).Value = Val(txtEnglish.Value)
    ws.Cells(nextRow, 5).Value = Val(txtComputer.Value)
    ws.Cells(nextRow, 6).Value = Val(txtMath.Value)
    ws.Cells(nextRow, 7).Value = total
    ws.Cells(nextRow, 8).Value = percentage
    ws.Cells(nextRow, 9).Value = grade
    ws.Cells(nextRow, 10).Value = result
    ws.Cells(nextRow, 11).Value = Date

    MsgBox "Student record saved successfully."

End Sub

Test the project by entering sample student information.

For example:

Student ID: 101
Name: Rahul
Course: ADCA
English: 80
Computer: 90
Mathematics: 85

The system should calculate:

• Total = 255
• Percentage = 85%
• Grade = A
• Result = Pass

This mini project combines many concepts learned throughout the Excel VBA course.

• Variables
• Data types
• UserForms
• TextBoxes
• CommandButtons
• Worksheet objects
• Cells property
• For loops
• If...Then...ElseIf
• Input validation
• Calculations
• Automatic reports

You have now created the basic design of an Excel VBA Student Management System.

The project can:

• Accept student information through a UserForm.
• Save records into an Excel worksheet.
• Calculate total marks.
• Calculate percentage.
• Calculate grades.
• Determine pass or fail.
• Store the entry date.
• Search student records.
• Generate reports.

This project provides a practical foundation for the final Excel VBA project in the next lesson.