VBA (Visual Basic For Applications) Assignments | Online Homework Help
The goal of this assignment is to give you a chance to create a VBA application from start to finish. You will have data and a brief description of the functionality. Details on how you are going to implement this are very limited. With the exception of some “must haves” listed below, you have to make all of the design and coding decisions.
The Problem
It seems like almost daily the news has some story of teacher strikes and teacher salary is often cited as being part of the problem. School data is available on the web for Colorado and is compiled by the Colorado Department of Education (CDE).
For this “Final Exam” I would like you to take the role of data analyst for the CDE. While you may have Tableau, Power BI, and/or pivot table skills, you have been asked to create a prototype VBA application for policymakers. This prototype won’t use all of the data or answer all possible question. You are asked to focus on teacher salary. The point of this prototype is to show off your skills with just a limited analysis.
Your users would be school superintendents, legislators, or other interested parties, e.g., workers in the Governor’s office, the press, or “think tank” organizations. You should not assume that this group of users is very proficient with Excel or other analytic software. The following functionality is requested:
Summary information: Teacher salary by district.
Teacher salary by “Setting.”
Teacher salary by Charter/NonCharter.
Provide output that enables the user to answer this question: Are teacher salary and graduation rates related? You should probably at least separate this by Elementary, Middle, and High School, but might what other categorizing variables (moderators). Note: You don’t give the answer, but you give the user a way to answer the question themselves.
Provide analysis of one additional area that you identify.
I got the data from the following websites:
https://www.cde.state.co.us/cdereval/gradratecurrent
(https://www.cde.state.co.us/cdereval/gradratecurrent)
https://www.cde.state.co.us/cdereval (https://www.cde.state.co.us/cdereval)
https://www.cde.state.co.us/schoolview (https://www.cde.state.co.us/schoolview)
Here are the spreadsheets that I used and should be enough to earn an A-level score. You may not even need all of these worksheets.
2017-18 Average Salaries for Teachers Updated with Charter Status.xlsx 2017-18 graduation rates, school-level, race & gender.xlsx
2017-18 Teachers by Gender and Race Updated.xlsx
school building codes-en-us.xlsx Note: this is 2019 data. You might have to reconcile this list of schools with the previous files.
You should not do pre-processing of data by hand, your code should do this. (Pre-processing would be things like moving columns into one “Data” sheet, deleting missing data, sorting or other categorizations, etc. (The exception to this is Tables and Graphs.) Find a way to deal with “missing data” and make this transparent in the output. You may put full worksheets in one workbook. You should build your code so that extra rows can be added. You can assume that the columns will not change.
Things that you need to include:
Use at least one array
Code is modularized into several subs Pass variables between subs as needed.
Error prevention, error capture, and/or error checking with re-dos for the user
At least one user form and provide the user choices, e.g., give me salaries of rural high schools
While some query results can be displayed with message boxes, you have to provide at least one table and one graph with navigation. You can (should) design your table(s) and graph(s) by hand and then just populate them with VBA.
I will be testing your code with different data. It will have the same numeric scope as the given data. (I won’t use data that is in the 100s if the given data is in the 1000s.)
Please review some of the applications in the second part of the book to understand the scope of what I am looking for. Starting on an explanation sheet is good. Provide navigation so that the user can get to the sheets needed. You can hide and unhide sheets as needed. Be sure to review the rubric.
