In Excel, total marks and percentage are common calculations in student marksheets and result systems. VBA can automatically calculate these values for one student or for many students.
In this lesson, we will learn how to read subject marks, calculate the total, calculate the percentage, write the results into Excel cells, and process multiple student records using VBA.
Total marks are the sum of marks obtained in all subjects.
For example, if a student gets:
The total is:
80 + 75 + 90 = 245Percentage represents the marks obtained compared with the maximum possible marks.
For three subjects with a maximum of 100 marks each:
Percentage = (Total / 300) * 100If the total is 245:
(245 / 300) * 100 = 81.67%Create a worksheet named Marks with the following columns:
| Column | Heading |
|---|---|
| A | Student ID |
| B | Student Name |
| C | English |
| D | Computer |
| E | Math |
| F | Total |
| G | Percentage |
Enter sample data from row 2.
| ID | Name | English | Computer | Math |
|---|---|---|---|---|
| ST101 | Rahul | 80 | 75 | 90 |
| ST102 | Amit | 65 | 70 | 78 |
Columns F and G will contain the automatically calculated Total and Percentage.
Open the VBA Editor using:
Alt + F11Insert a standard module and create a procedure:
Sub CalculateTotalPercentage()
End SubDim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Marks")The ws variable now refers to the Marks worksheet.
Dim englishMarks As Double
Dim computerMarks As Double
Dim mathMarks As Double
Dim total As Double
Dim percentage As DoubleThese variables store subject marks, total, and percentage.
English marks are stored in column C.
englishMarks = ws.Cells(2, 3).ValueThis reads the value from C2.
Computer marks are stored in column D.
computerMarks = ws.Cells(2, 4).ValueThis reads the value from D2.
Math marks are stored in column E.
mathMarks = ws.Cells(2, 5).ValueThis reads the value from E2.
Add the marks of all three subjects.
total = englishMarks + computerMarks + mathMarksFor example:
80 + 75 + 90 = 245Column F is used for Total.
ws.Cells(2, 6).Value = totalThe calculated total is written into F2.
For three subjects with a maximum of 100 marks each:
percentage = (total / 300) * 100If total is 245:
percentage = (245 / 300) * 100
percentage = 81.67Column G is used for Percentage.
ws.Cells(2, 7).Value = percentageThis writes the calculated percentage into G2.
You can display the percentage with two decimal places.
ws.Cells(2, 7).NumberFormat = "0.00"For example, 81.666666 becomes 81.67.
englishMarks = ws.Cells(2, 3).Value
computerMarks = ws.Cells(2, 4).Value
mathMarks = ws.Cells(2, 5).Value
total = englishMarks + computerMarks + mathMarks
percentage = (total / 300) * 100
ws.Cells(2, 6).Value = total
ws.Cells(2, 7).Value = percentageThis performs both calculations for the student in row 2.
To process multiple students, find the last used row.
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).RowColumn A is used to identify the last student record.
A For...Next loop can process every student.
Dim i As Long
For i = 2 To lastRow
'Calculation code
Next iThe variable i represents the current row.
englishMarks = ws.Cells(i, 3).Value
computerMarks = ws.Cells(i, 4).Value
mathMarks = ws.Cells(i, 5).ValueThe current student's marks are read from columns C, D, and E.
total = englishMarks + computerMarks + mathMarks
ws.Cells(i, 6).Value = totalThe total is calculated and written into column F for every student.
percentage = (total / 300) * 100
ws.Cells(i, 7).Value = percentage
ws.Cells(i, 7).NumberFormat = "0.00"Each student's percentage is automatically calculated and stored.
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
Next iThis processes all student rows automatically.
Before calculating, you can check whether the student ID exists.
If ws.Cells(i, 1).Value <> "" Then
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
End IfThis prevents calculations on completely blank student rows.
Marks should normally be between 0 and 100.
If englishMarks < 0 Or englishMarks > 100 Then
MsgBox "Invalid English marks in row " & i
Exit Sub
End IfThe same validation can be applied to the other subjects.
The following program calculates Total and Percentage for all students.
Sub CalculateTotalPercentage()
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("Marks")
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
For i = 2 To lastRow
If ws.Cells(i, 1).Value <> "" Then
englishMarks = ws.Cells(i, 3).Value
computerMarks = ws.Cells(i, 4).Value
mathMarks = ws.Cells(i, 5).Value
If englishMarks < 0 Or englishMarks > 100 Then
MsgBox "Invalid English marks in row " & i
Exit Sub
End If
If computerMarks < 0 Or computerMarks > 100 Then
MsgBox "Invalid Computer marks in row " & i
Exit Sub
End If
If mathMarks < 0 Or mathMarks > 100 Then
MsgBox "Invalid Math marks in row " & i
Exit Sub
End If
total = englishMarks + computerMarks + mathMarks
percentage = (total / 300) * 100
ws.Cells(i, 6).Value = total
ws.Cells(i, 7).Value = percentage
ws.Cells(i, 7).NumberFormat = "0.00"
End If
Next i
MsgBox "Total and Percentage Calculated Successfully"
End SubThe formula must be changed when the number of subjects changes.
For five subjects with a maximum of 100 marks each:
percentage = (total / 500) * 100For six subjects:
percentage = (total / 600) * 100Always use the correct maximum total for your marksheet.
You can format the calculated columns to make the result easier to read.
With ws.Range("F1:G" & lastRow)
.HorizontalAlignment = xlCenter
.Borders.LineStyle = xlContinuous
End With
ws.Range("G2:G" & lastRow).NumberFormat = "0.00"The complete automation process is:
Read Marks
↓
Validate Marks
↓
Calculate Total
↓
Calculate Percentage
↓
Write Results
↓
Format ResultsAutomatic calculation of Total and Percentage is one of the most useful applications of Excel VBA. Once the subject marks are available in the worksheet, VBA can calculate the results without requiring the user to manually enter formulas.
The basic total calculation is:
total = englishMarks + computerMarks + mathMarksThe percentage calculation for three subjects is:
percentage = (total / 300) * 100The calculated values can then be written into Excel:
ws.Cells(i, 6).Value = total
ws.Cells(i, 7).Value = percentageA For...Next loop allows the same calculations to be performed for many students. Validation can be added to prevent invalid marks, and formatting can be applied to make the final result easy to read.
This technique forms an important part of automated student marksheets, result systems, examination reports, and other Excel VBA projects.
Question: If three subjects have a maximum of 100 marks each, which VBA formula correctly calculates percentage?