two excel vba small programs

CME 281 – COMPUTATIONAL METHODS

Homework #9

Due Tuesday, March 28th

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 functionality 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”

columns. All of the above is contained in your existing solution from Assignment 6.

(Note that “Batch rolls” data in 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 added 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 sorting algorithm. The corresponding concentrations must also be

correctly carried along with the sorted levels.

Clear

This button will clear all data (both raw and sorted) 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 random). 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.