A student marksheet is a practical Excel project that can be automated using VBA. Instead of manually entering totals and calculating results, VBA can automatically read student marks, calculate the total, percentage, and result, and place the information into the worksheet.
In this lesson, we will combine the VBA concepts learned so far, including Variables, Cells, Range, If...Then...Else, For...Next, Reading Values, Writing Values, and Formatting Cells.
A student marksheet is a worksheet that stores the marks and result information of students.
A simple marksheet may contain:
Calculating results manually for many students can be repetitive. VBA can automate these calculations.
VBA can automatically:
First, create the following headings in a worksheet named Marksheet.
| Column | Heading |
|---|---|
| A | Student ID |
| B | Student Name |
| C | English |
| D | Computer |
| E | Math |
| F | Total |
| G | Percentage |
| H | Result |
Enter some sample student data from row 2.
| Student ID | Student Name | English | Computer | Math |
|---|---|---|---|---|
| ST101 | Rahul | 75 | 82 | 68 |
| ST102 | Amit | 65 | 70 | 72 |
Columns F, G, and H will be calculated automatically.
Open the VBA Editor using:
Alt + F11Insert a standard module and create a procedure:
Sub GenerateMarksheet()
End SubThe marksheet automation code will be written inside this procedure.
First, create a worksheet variable.
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Marksheet")The variable ws now refers to the Marksheet worksheet.
We need variables for the marks and calculations.
Dim englishMarks As Double
Dim computerMarks As Double
Dim mathMarks As Double
Dim total As Double
Dim percentage As DoubleThese variables will temporarily store values during the calculation.
English marks are stored in column C. For the first student, read the value from C2.
englishMarks = ws.Cells(2, 3).ValueCells(2, 3) represents C2.
Computer marks are stored in column D.
computerMarks = ws.Cells(2, 4).ValueThis reads the Computer marks of the student in row 2.
Math marks are stored in column E.
mathMarks = ws.Cells(2, 5).ValueThis reads the Math marks from E2.
The total is calculated by adding the marks of all three subjects.
total = englishMarks + computerMarks + mathMarksIf the marks are 75, 82, and 68, the total is:
75 + 82 + 68 = 225The Total column is column F.
ws.Cells(2, 6).Value = totalThe calculated total is written into F2.
If there are three subjects and each subject has a maximum of 100 marks, the maximum total is 300.
percentage = (total / 300) * 100For a total of 225:
(225 / 300) * 100 = 75%The Percentage column is column G.
ws.Cells(2, 7).Value = percentageThe calculated percentage is written into G2.
Suppose a student must score at least 40 marks in every subject to pass.
If englishMarks >= 40 And _
computerMarks >= 40 And _
mathMarks >= 40 Then
ws.Cells(2, 8).Value = "Pass"
Else
ws.Cells(2, 8).Value = "Fail"
End IfThe result is written into column H.
The percentage can be displayed with two decimal places.
ws.Cells(2, 7).NumberFormat = "0.00"For example, 75 becomes 75.00.
Instead of processing only row 2, a For loop can process many students.
Dim i As Long
For i = 2 To 10
'Process student
Next iThe variable i represents the current student row.
The current row can be used to read the marks.
englishMarks = ws.Cells(i, 3).Value
computerMarks = ws.Cells(i, 4).Value
mathMarks = ws.Cells(i, 5).ValueThe program reads marks from columns C, D, and E for every student.
The total can be calculated for every student.
total = englishMarks + computerMarks + mathMarks
ws.Cells(i, 6).Value = totalThe result is written into column F of the current student's row.
percentage = (total / 300) * 100
ws.Cells(i, 7).Value = percentageEach student's percentage is calculated and written into column G.
If englishMarks >= 40 And _
computerMarks >= 40 And _
mathMarks >= 40 Then
ws.Cells(i, 8).Value = "Pass"
Else
ws.Cells(i, 8).Value = "Fail"
End IfThis checks every subject before assigning the final result.
Instead of always processing rows 2 to 10, we can automatically find the last student row.
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).RowThis finds the last used row based on column A.
For i = 2 To lastRow
englishMarks = ws.Cells(i, 3).Value
computerMarks = ws.Cells(i, 4).Value
mathMarks = ws.Cells(i, 5).Value
total = englishMarks + computerMarks + mathMarks
percentage = (total / 300) * 100
ws.Cells(i, 6).Value = total
ws.Cells(i, 7).Value = percentage
If englishMarks >= 40 And _
computerMarks >= 40 And _
mathMarks >= 40 Then
ws.Cells(i, 8).Value = "Pass"
Else
ws.Cells(i, 8).Value = "Fail"
End If
Next iThe heading row can be formatted using a With block.
With ws.Range("A1:H1")
.Font.Bold = True
.Font.Color = vbWhite
.Interior.Color = RGB(0, 112, 192)
.HorizontalAlignment = xlCenter
.Borders.LineStyle = xlContinuous
End WithThis creates a professional-looking header.
The following program combines the main concepts into a complete student marksheet generator.
Sub GenerateMarksheet()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim englishMarks As Double
Dim computerMarks As Double
Dim mathMarks As Double
Dim total As Double
Dim percentage As Double
Set ws = ThisWorkbook.Worksheets("Marksheet")
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
For i = 2 To lastRow
englishMarks = ws.Cells(i, 3).Value
computerMarks = ws.Cells(i, 4).Value
mathMarks = ws.Cells(i, 5).Value
total = englishMarks + computerMarks + mathMarks
percentage = (total / 300) * 100
ws.Cells(i, 6).Value = total
ws.Cells(i, 7).Value = percentage
If englishMarks >= 40 And _
computerMarks >= 40 And _
mathMarks >= 40 Then
ws.Cells(i, 8).Value = "Pass"
Else
ws.Cells(i, 8).Value = "Fail"
End If
Next i
With ws.Range("A1:H1")
.Font.Bold = True
.Font.Color = vbWhite
.Interior.Color = RGB(0, 112, 192)
.HorizontalAlignment = xlCenter
.Borders.LineStyle = xlContinuous
End With
With ws.Range("A1:H" & lastRow)
.Borders.LineStyle = xlContinuous
End With
ws.Columns("A:H").AutoFit
MsgBox "Marksheet Generated Successfully"
End SubYou can use different formatting for Pass and Fail results.
If ws.Cells(i, 8).Value = "Pass" Then
ws.Cells(i, 8).Interior.Color = vbGreen
ws.Cells(i, 8).Font.Color = vbWhite
Else
ws.Cells(i, 8).Interior.Color = vbRed
ws.Cells(i, 8).Font.Color = vbWhite
End IfThis makes the result easier to identify visually.
A complete automated marksheet follows these steps:
Read Marks
↓
Calculate Total
↓
Calculate Percentage
↓
Check Result
↓
Write Result
↓
Format MarksheetCreating a student marksheet is an excellent practical exercise for learning Excel VBA because it combines many programming concepts in one project.
The program reads marks:
englishMarks = ws.Cells(i, 3).Value
computerMarks = ws.Cells(i, 4).Value
mathMarks = ws.Cells(i, 5).ValueThen it calculates the total and percentage:
total = englishMarks + computerMarks + mathMarks
percentage = (total / 300) * 100Then it determines the result:
If englishMarks >= 40 And _
computerMarks >= 40 And _
mathMarks >= 40 Then
ws.Cells(i, 8).Value = "Pass"
Else
ws.Cells(i, 8).Value = "Fail"
End IfFinally, VBA can format the complete marksheet automatically.
This project prepares you for more advanced Excel VBA applications such as student result systems, automatic reports, data-entry forms, and complete management projects.
Question: If three subjects each have a maximum of 100 marks, what formula can be used to calculate percentage?