excel

Homework #5

Homework submittal: Please use one Workbook, with a separate Worksheet and VBA Module

for each of these problems. Save your workbook to a file of type *.xlsm, and submit it

electronically (Moodle preferred).

General programming expectation: Always include type declarations and/or logic in your

code to protect it from “crashing.” Anticipate and catch errors in user inputs or other input

or process cases that may cause runtime errors. For example, if division by zero can occur,

test for that condition and code to handle it gracefully.

1- Temperatures may be converted from Celsius to Fahrenheit using the formula:

F=1.8C 32

Write a Function that takes the temperature in degrees Celsius as an input argument, and

returns the function value in degrees Fahrenheit. Call your function C2F. Try the function in

worksheet cells A1, A2, and A3 using C = -40°, C = 20°, and C = 100°, respectively.

(By the way, Excel has a conversion function called “convert”, I urge you to search the help

file to learn more about it).

2- 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 Sub program 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

degrees. Process the inputs according to this logic:

1. If the x and y inputs are both zero 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 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. Test your Sub 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.

3- If a liquid moves through a pipe at sufficiently slow velocity, the flow will be smooth, or laminar. As the velocity increases, there will come a point at which the flow will become

irregular, or turbulent. The Reynolds number (Re) provides a way to determine whether the

flow in a pipe is laminar or turbulent. It is calculated as

𝑅𝑒 = 𝐷𝑉

𝜈

where 𝐷 is the pipe’s diameter (m), 𝑉 is the velocity (m/s), and 𝜈 is kinematic viscosity (m2/s). If the Reynolds number is less than 2000 then the flow will be laminar. Write a Sub program

that computes the Reynolds number and identifies whether the flow is laminar or turbulent.

Test your program for engine oil at 20oC, for which 𝜈 = 9 𝑋 10−4 m2/s, flowing in a 0.75 m diameter pipe. Starting with a flow rate (Q) of 0.5 m3/s, use trial and error to determine the

value of Q that yields Re = 2000 (approximately). Note that the volumetric flow rate Q is

related to the flow velocity V by 𝑄 = 𝑉𝐴 where A is the cross sectional area of the pipe. The Sub will read the input kinematic viscosity, pipe diameter, and flow rate from worksheet cells,

and output the Reynolds number and a message identifying whether the flow is laminar or

turbulent in a message box.