two excel vba small programs

CME 281 – COMPUTATIONAL METHODS Homework #9 Due Tuesday, March 28 th 1) Return to the six -sided -number cube simulation program from Assignment 6. Recall that when the user pressed the “Run” button, the program would simulate the number of rolls listed in the “Total Rolls” cell. Note that each time the “Run” button was pressed, the values from the previous simulation are erased. This program will add the functio nality that will retain data from a simulation, and use this data to continually update the simulation each time the “Run” button is pressed. In the example below, the user has entered 1000 for the number of rolls in a batch, meaning that the program will simulate 1000 rolls once the “Run” button is pressed. The resulting data is stored in the arrays and displayed in the “Batch” colu mns. All of the above is contained in your existing solution from Assignment 6. (Note that “Batch rolls” data i n this assignment serves the same purpose as “Total rolls” in Assignment 6.) Your program will require two new arrays (or a single two dimensional array) that will store the overall number of rolls, and average percentage of each outcome. Each time the user presses the “Run” button, the new data will be adde d to these arrays and displayed in the “Overall ” section under “Total ” and “pct.” columns. In the example below, the most recent batch is 1000 rolls, while a total of 4000 overall rolls have been performed . The program inputs the number of rolls for a batch from the worksheet, and outputs the total rolls to the worksheet in the indicated cells. When the ‘Clear’ button is pressed, the Overall “Total rolls ” and “Total” column will be reset to zero. 2) Create a program that sorts data in a two dimensional array and attach the program to action buttons. An example interface is shown below. Procedures will be attached to buttons Generate Data First, populate a two -dimensional array with a set of random integers between 9 and 66. Output this data to the spreadsheet in the columns under the heading “Raw Data ”. Your interface should resemble the interface in the examples. Sort by Level Using the array created in the ‘Generate’ section, sort the level data from lowest to highest using a sort ing algorithm. The corresponding concentrations must also be correctly carried along with the sorted levels. Clear This button will clear all data (both raw and so rted) from the spreadsheet. 3) You are working for a fitness magazine, and you gain a promotion after your award winning article: Drink Six -Packs for Six -Pack Abs. Your new assignment will be to study the cholesterol levels of urban populations. Consequently, you will need to find a city with generally high cholesterol (and generally poor health). Luckily, your research department has cholesterol data for several major cities. Each data set contains the cholesterol from 175 people (chosen at ran dom). You need to write a computer program that will input the cholesterol data from these files into a pre - arranged spreadsheet that performs a statistical analysis on the data. Relevant statistics calculated in the spreadsheet will then be appended to the data file. Your spreadsheet should resemble the following: a) Input Data The input data button will input the cholesterol data from the given data file into an array. The user enters the path for the data file into the highlighted cells in the spreadsheet. (The TA will be using another data file to test your spreadsheet so they need to be able to easily enter a different file path). b) Output Data The Output button will send the data from the input array to the spreadsheet. It will also calculate the maximum and minimum values and display them on the spreadsheet. Lastly, it will append the maximum and minimum values to the original input file, as shown below.