excel Visual Basic Problem

ME209 Summer 2015 R. B. Tarn

Homework #3

Create a flowchart for each of these two homework problems; hand drawn is sufficient, or you

can use whichever drawing program you like. Use one workbook (save as file type *.xlsm) with

a User Interface Worksheet (UIW) and VBA module for each of these two homework problems.

Submit the flowcharts and workbook electronically via Moodle, and submit hardcopies of all

work (flowcharts, worksheets and VBA code modules) at the beginning of class on Wednesday,

July 8, or at the ME Department office by 3:30 PM on Wednesday, July 8. Be sure your name is

at the top of all pages.

General programming expectations: Include logic in your code to protect it from errors caused

by inappropriate or invalid user inputs, or other process cases that may cause runtime errors. For

example, if division by zero can occur, test for that condition and write code to handle it

gracefully. Message boxes are an appropriate means of alerting your user to inappropriate or

invalid inputs to Sub procedures. Never use Input boxes in homework or project assignments.

Hint for determining if a worksheet cell is blank (empty): If Cells(irow, icol) = "",

then the cell is blank.

Problem 1: Polar & Cartesian Coordinates Converter

Polar coordinates are related to Cartesian Coordinates by the following equations.

2 2 1 cos( ) sin( ) tan ( )

y x r y r r x y

x   

     

Write a well-structured Sub procedure that reads inputs for x, y, r, and θ from worksheet cells

B3, B4, B5, and B6, respectively. Input units of x, y, and r will be millimeters, and input units

of θ will be in degrees. Name these input cells and create appropriate labels for them.

Process the inputs according to this logic:

1. If the x and y inputs are both zero (or blank) AND the r and θ inputs are NOT both zero, then

calculate x and y outputs corresponding with the given r and θ, and write their values to cells B3

and B4, respectively.

2. If the r and θ inputs are both zero (or blank) AND the x and y inputs are NOT both zero, then

calculate the r and θ outputs corresponding with the given x and y, and write their values to cells

B5 and B6, respectively.

3. For all other conditions, do nothing.

Add a Run Button to the worksheet and assign it to your Sub procedure. Test your Sub

procedure with inputs x = 2.5mm and y = 7.1mm to compute r and θ. Then test your Sub with

inputs r = 5.5mm and θ = 49° to compute x and y. Ensure that your code calculates θ correctly,

even for the case when x=0.

Problem 2: Statistics

Write a well-structured Sub procedure to compute the average and standard deviation of data

entered by the user in column A of the user interface worksheet, beginning in row 1. Your user

may enter as many data values as he/she wants, so your code must determine how many data

values are entered. The end of the data values list will be determined by the first blank cell in

column A.

Design your Sub to read the values into an array, and then compute the average and standard

deviation of the data, using the values stored in the array. The formulas for the average and

standard deviation are:

Average Standard Deviation

1

n

i

i

y

y n

 

2

2 1

1

( )

1

n

in i

i

i y

y

y n

s n





     



 

 

Calculated results display: Write the number of input data values, and the resulting average and

standard deviation on the worksheet, in properly labeled and named cells.

If the user enters less than 2 data values, display a message in the cell for the standard deviation

result, alerting the user that at least two data values are required to calculate a standard deviation.

If any of the user’s data values is not numeric, use a Message Box to alert the user of this error,

and informing the user which line the error was found on. In this event, the cells displaying the

calculated results will be blank.

Create a “Run” button to execute your Sub procedure, and test your Sub by entering at least 20

data values.