Please write in VBA code for excel
Description: The following description was provided to you in Assignment 3, and is repeated here for convenience. You can reuse your solutions to assignment 3 for tasks 1 and 2, or improve on them here. In a hypothetical class, if a student takes a comprehensive final and gets a better score than the higher of the two midterm scores, then: (1) the lowest score would be replaced by the comprehensive final's score, (2) the higher score will be increased by the difference between the higher and lower scores, AND the student will get an extra 3 points added to the comprehensive score. If the student scores higher than only one of the two midterm scores, only that particular midterm score is replaced by the final. The final score will then be calculated by averaging the three test scores. Calculate the final score using the given data presented in the "Question1" worksheet. Note: for this question all your procedures (subs and/or functions) should be stored in the Question1_Module. Tasks: Task 1: Code a function that requires test 1 score, test 2 score, and comprehensive final as inputs and calculates the final score per the description above Task 2: Code a subroutine that uses the function coded in part (a) to fill column F of the worksheet, and link this subroutine to the "Perform Calculations" button in the worksheet. Task 3: In Question2_Module, Create a Record Type "Student" that contains the following fields i. StudentID ii. FinalScore iii. Comprehensive Taker iv. Student GPA Task 4: Declare a Public variable with the name "CEEStudent", Size 20, and Type "Student" Task 5: Write a procedure that reads and stores all student IDs, FinalScores, and StudentGPAs from Question1 sheet in the CEEStudent variable. Set Comprehensive Taker to 1 if the student took the comprehensive, and 0 otherwise (i.e., if no score is shown in the "comprehensive final" column. In Question2_Form, Write a suitable procedure that would do the following when the user clicks the "Retrieve" Button Task 6: Read the entered student number and display the corresponding final score and student GPA. Task 7: If the student number does not exist, change the form background color to red and set the test grade to blank. Task 8: If a student's grade is greater than or equal to 95, change the form background color to green. Task 9: If a student's grade is less than 95, but their GPA is greater than 3.5, change the form background color to blue. Task 10: The form background should otherwise be grey (hint: use RGB (224,224,224)) Description: The following description was provided to you in Assignment 3, and is repeated here for convenience. You can reuse your solutions to assignment 3 for tasks 1 and 2, or improve on them here. In a hypothetical class, if a student takes a comprehensive final and gets a better score than the higher of the two midterm scores, then: (1) the lowest score would be replaced by the comprehensive final's score, (2) the higher score will be increased by the difference between the higher and lower scores, AND the student will get an extra 3 points added to the comprehensive score. If the student scores higher than only one of the two midterm scores, only that particular midterm score is replaced by the final. The final score will then be calculated by averaging the three test scores. Calculate the final score using the given data presented in the "Question1" worksheet. Note: for this question all your procedures (subs and/or functions) should be stored in the Question1_Module. Tasks: Task 1: Code a function that requires test 1 score, test 2 score, and comprehensive final as inputs and calculates the final score per the description above Task 2: Code a subroutine that uses the function coded in part (a) to fill column F of the worksheet, and link this subroutine to the "Perform Calculations" button in the worksheet. Task 3: In Question2_Module, Create a Record Type "Student" that contains the following fields i. StudentID ii. FinalScore iii. Comprehensive Taker iv. Student GPA Task 4: Declare a Public variable with the name "CEEStudent", Size 20, and Type "Student" Task 5: Write a procedure that reads and stores all student IDs, FinalScores, and StudentGPAs from Question1 sheet in the CEEStudent variable. Set Comprehensive Taker to 1 if the student took the comprehensive, and 0 otherwise (i.e., if no score is shown in the "comprehensive final" column. In Question2_Form, Write a suitable procedure that would do the following when the user clicks the "Retrieve" Button Task 6: Read the entered student number and display the corresponding final score and student GPA. Task 7: If the student number does not exist, change the form background color to red and set the test grade to blank. Task 8: If a student's grade is greater than or equal to 95, change the form background color to green. Task 9: If a student's grade is less than 95, but their GPA is greater than 3.5, change the form background color to blue. Task 10: The form background should otherwise be grey (hint: use RGB (224,224,224))
