Problem description
For this part, we’ve chosen an academic problem that assigns grades to the students according to their score, then calculates the total and the average score for the whole class.
Task
You are tasked with building VBA code to solve some sort of Excel problem that plagues you. This can be an academic problem or a work-related problem. On some level, it should be related to accounting, finance, or business.
At a minimum, your code must incorporate:
- Variables,
- Looping,
- Logic
Bonus points for more complexity.
You have to show me how your code works and then walk us through the code that you have written. It will be helpful to add comments throughout your code.
Solution:
1.1. Data description
Our data has the following columns:
1. Students: A string to describe the student.
2. Mark: an integer between 0 and 100.
3. Grade: a string that has the student grade that will be filled by the VBA program. As the following:
A | >85 |
B | >=85 and 75> |
C | >=75 and 65> |
D | >=65 and 50>= |
F | <50 |
4. The total row: an integer that represents the sum of the students’ scores.
5. The avg row: a double that represents the average of all the student scores.
1.2. Data example
The data has been consistently filled up. An example is given below:
Student | Marks (100) | GRADES |
Students 1 | 49 | F |
Students 2 | 80 | B |
Students 3 | 74 | C |
Students 4 | 73 | C |
Students 5 | 49 | F |
Students 6 | 90 | A |
Students 7 | 100 | A |
Students 8 | 20 | F |
Students 9 | 100 | A |
Students 10 | 49 | F |
Students20 | 51 | D |
TOTAL | ||
AVG |
1. VBA code review
In this part, we’ll present the code and explain each line and we will present how we have used the following:
● variables.
● looping.
● and logic.
The explanation is shown in the table below: