Moodle grading kerfuffle

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:

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

Data

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.
  • I controlled all the data operations. The data never left my hands after downloading.
  • 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.  
  3. Sub ComputeGrades()
  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
  11.     Dim sGradeMapRow As Single
  12.     Dim sScore As Single
  13.     Dim tGrade As String
  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.
  24.     Const GRADE_COLUMN = 11
  25.  
  26.     'Deets about the score-to-grade map data area.
  27.     'First row of the score-to-grade map.
  28.     Const SCORE_GRADE_MAP_FIRST_ROW = 3
  29.     'Last row of the score-to-grade map.
  30.     Const SCORE_GRADE_MAP_LAST_ROW = 13
  31.     'Column with the scores in the score-to-grade map.
  32.     Const SCORE_GRADE_MAP_SCORE_COLUMN = 15
  33.     'Column with the grades in the score-to-grade map.
  34.     Const SCORE_GRADE_MAP_GRADE_COLUMN = 14
  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.
  42.         For sGradeMapRow = SCORE_GRADE_MAP_FIRST_ROW To SCORE_GRADE_MAP_LAST_ROW
  43.             'Get the bottom of the score range for the grade category (e.g., 93 for A).
  44.             sBottomOfScoreRange = Cells(sGradeMapRow, SCORE_GRADE_MAP_SCORE_COLUMN)
  45.             'Did the student get above that?
  46.             If sScore >= sBottomOfScoreRange Then
  47.                 'Yes. Remember the grade.
  48.                 tGrade = Cells(sGradeMapRow, SCORE_GRADE_MAP_GRADE_COLUMN)
  49.                 'Exit the inner for loop immediately.
  50.                 Exit For
  51.             End If
  52.         Next sGradeMapRow
  53.         'Record the grade. Exit For takes you here.
  54.         Cells(sStudentScoreRow, GRADE_COLUMN) = tGrade
  55.     Next sStudentScoreRow
  56. End Sub

I didn't use Ranges here, just regular cell references. (Not sure why, ranges would be a little cleaner.) Note all the constants. It makes things easy to change, but it also adds to the documentation. The named constants make the purpose of each line they're in clearer. Easy to understand is good.

There's only one new statement. It's on line 50: Exit For. It ends the for loop it's in. If there's a nested loop, as there is here, only the loop with the Exit For is stopped, not any containing outer loops. Exit For made the code easier to write.

My main point here: this program solves a problem in RL (real life). Notice how similar it is to code you learn how to write in this course. If you do well in this course, you really do have a new skill. You should be proud of that.