In the fall of 2023, Moodle calculated score %s by dividing by 107, rather than 100. Cause, you know, x/107 is how you calculate a %. ðŸ¤¨ I must have done something wrong, but I don't know what.

Poop.

So, instead of relying on automatic calculation, I downloaded the data, and wrote a VBA program to compute accurate grades. It's a good example of VBA in the wild.

Here is the worksheet, after grades were calculated. There are two data areas:

• A table mapping scores to grades, from the syllabus.

The second column in the grade mapping shows the lowest score in the grade category.

The data in column J was computed with a basic Excel formula, rather than validation code with a flag. Why?

• The data was from an automated data source. I trusted Moodle to at least store the right data.
• The data was easy to scan. I verified there was no missing data, and no bad data (e.g., no negative scores).

Often, you can't make those assumptions. You need to know how data is gathered and transmitted. If you can't be sure, add validation, so you know exactly what you are getting.

Here's the code:

1. Option Explicit
2.
4.
5.     'Compute a grade from a score.
6.     'There are two data areas:
7.     ' * The students' scores, and an MT grade column.
8.     ' * The score-to-grade map. One col for grade, another for minimum score for that grade.
9.
10.     Dim sStudentScoreRow As Single
12.     Dim sScore As Single
14.     Dim sBottomOfScoreRange As Single
15.
16.     'Deets about the scores data area.
17.     'First row of student data.
18.     Const SCORES_FIRST_ROW = 2
19.     'Last row of student data.
20.     Const SCORES_LAST_ROW = 48
21.     'Column student scores are in.
22.     Const SCORE_COLUMN = 10
23.     'Column student grades are in.
25.
27.     'First row of the score-to-grade map.
29.     'Last row of the score-to-grade map.
31.     'Column with the scores in the score-to-grade map.
35.
36.     'Loop over the students.
37.     For sStudentScoreRow = SCORES_FIRST_ROW To SCORES_LAST_ROW
38.         'Get a student's score.
39.         sScore = Cells(sStudentScoreRow, SCORE_COLUMN)
40.         'Look it up in the score-to-grade map.
41.         'Loop over the map, from A to F.
43.             'Get the bottom of the score range for the grade category (e.g., 93 for A).
45.             'Did the student get above that?
46.             If sScore >= sBottomOfScoreRange Then
49.                 'Exit the inner for loop immediately.
50.                 Exit For
51.             End If