P1: (120 pts) Write a VBA program to do the following, being sure to include hig

Question

P1: (120 pts) Write a VBA program to do the following, being sure to include high-quality comments throughout. Please ensure that each sub-part of this homework assignment is its clearly delineated piece of code (i.e., don't wrap two sub-parts into one loop!) (10) Create a 30 x 75" 2-D array containing random numbers of type single between 51.0 and 99.9 *Unless otherwise specified, assume that 2-D arrays are always specified row x column 1. 2. (40) Write a function that receives a type single, and using a standard A - F grading scheme (90 for A, >80 for B, etc., no plusses or minuses), returns a string letter grade (This is basically what you did for ICP22A). Write a loop that calls this function for every value in the original array, and that fills a second array of the same dimensions, but of type string, with the letter grades that correspond to the numerical grades in the first arra 3. (10) Prompt the user to enter a low and high numerical score (e.g., 65, then 75). If the low is below 55.0, set it to 55.0. If the high is above 99.0, set it to 99.0 4. (20) Count the number of grades in the type-single array that are within the specified range and report the result in a well-formatted message box. 5. (30) Write the letter grades to the spreadsheet (30 rows, 75 columns), putting a grey background on any cell associated with a numerical grades outside of the range specified by the user in part 3 6. (10) Use the countif function in Excel (not in VBA) to place on the spreadsheet, first empty row at the bottom of each column, a count of how many "A" grades are in the column.

Explanation / Answer

Sub GenerateRandomElements()
Dim Marks(30, 75) As Single
Dim intCounter1 As Integer
Dim intCounter2 As Integer

For intCounter1 = 1 To UBound(Marks, 1)
For intCounter2 = 1 To UBound(Marks, 2)

Marks(intCounter1, intCounter2) = Single ((99.9 - 51.0 + 1) * Rnd + 51.0)  

Next intCounter2
Next intCounter1

End Sub
  

Function calculateGrade(ByVal mark As Single)
Dim grade As String
  
If mark > 90 Then
grade = "A"
ElseIf mark > 80 Then
grade = "B"
ElseIf mark > 70 Then
grade = "C"
ElseIf mark > 60 Then
grade = "D"
ElseIf mark > 50 Then
grade = "E"
Else
grade = "F"
  
Return grade
End Function

Sub Main()
  
Dim Grades(30, 75) As String
Dim intCounter1 As Integer
Dim intCounter2 As Integer
Dim lowScore As Single
Dim highScore As Single
Dim count As Integer

'generate score array
GenerateRandomElements

'generate grade array
For intCounter1 = 1 To UBound(Marks, 1)
For intCounter2 = 1 To UBound(Marks, 2)

Grade(intCounter1, intCounter2) = calculateGrade Marks(intCounter1, intCounter2)

Next intCounter2
Next intCounter1

  
'get low and high score
lowScore = InputBox("Enter low score")
If lowscore < 55.0 Then
lowScore = 55.0

highScore = InputBox("Enter high score")
If highScore > 99.0 Then
highScore = 99.0

'count the elements in range
For intCounter1 = 1 To UBound(Marks, 1)
For intCounter2 = 1 To UBound(Marks, 2)

If (Marks(intCounter1, intCounter2) > lowScore And Marks(intCounter1, intCounter2) < highScore)
count = count + 1

Next intCounter2
Next intCounter1

'Go through the array, write into cell of spreadsheet and then mark the background as gray if score out of range
For intCounter1 = 1 To UBound(Marks, 1)
For intCounter2 = 1 To UBound(Marks, 2)
  
Worksheets("Grades").Range(Worksheets("Grades").Cells(intCounter1 + 1, intCounter2 + 1), Worksheets("Grades").Cells(intCounter1 + 1, intCounter2 + 1)) = Grade(intCounter1 ,intCounter2 )

If (Marks(intCounter1, intCounter2) < lowScore And Marks(intCounter1, intCounter2) > highScore)
Worksheets("Grades").Range(Worksheets("Grades").Cells(intCounter1 + 1, intCounter2 + 1), Worksheets("Grades").Cells(intCounter1 + 1, intCounter2 + 1)).Interior.Color = RGB(192,192,192)

Next intCounter2
Next intCounter1

End Sub

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.