Excel homework

ME209 – Summer 2015 - Tarn

Homework #2

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

for each of these problems. Submit all of your work in a macro-containing Workbook (*.xlsm)

electronically to Moodle, and print your worksheets and code modules (landscape, please) to turn

in when this assignment is due at the beginning of class on June 24.

1. Using Cell direct access. Read Chapra Chapter 5 (through Section 5.1) and complete Exercise 5.1.1 (Kick Calculator). Then follow the instructions for Chapra Problem 5.1 (p. 52)

to modify the Sub you created in the exercise (Challenge questions are NOT required).

2. Writing a function used on a worksheet. Consider a column with a square cross-section, loaded with a force as shown in the figure below. The critical buckling load, F, which will

cause the column to buckle, is given by the formula shown. The parameter E is the elastic

modulus of the material, I is the area moment of inertia of the beam, and L is the length of the

beam. Parameter K is known as the dimensionless effective length factor, which takes into

account the supports of the column at each end.

A. Create the following tables on the user interface worksheet to contain the values of the effective length factors and elastic modulus of the materials steel, aluminum, and wood

(look up these values on the Internet). Create named input cells for the beam length (2 m)

and square cross-section dimension (3 mm), and properly label these user input cells.

End fixtures K value

Pinned 1

Fixed 0.5

Fixed-pinned 0.699

Fixed-free 2.0

Material Elastic Modulus (GPa)

Steel

Aluminum

Wood

B. Create the following table on the user interface worksheet to contain the resulting critical buckling load (F) for each case of material and effective length factor.

C. Write a VBA Function to calculate the critical buckling load for a column, with all of the required inputs passed as function arguments. All calculations will be done by your

function. Research the equation for the area moment of inertia of a square cross-section

beam, and use that equation in your function.

D. Use your function in each of the critical buckling load table cells, using cell relative referencing wisely so that your worksheet formulas entered in the top row can be copied

and pasted to the second and third rows without having to manually correct cell

references. Add the correct units of the resulting critical buckling load to your table of

results.

F