Centigrade to Fahrenheit Converter
Temperature can 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, calculates the equivalent temperature in degrees Fahrenheit using the above equation, and returns the result as the function value. Initially, name your function C2F.
Are you having any trouble using C2F as the name of your function? You should be, because of an idiosyncrasy of Excel. Excel thinks that you meant to type the address of worksheet cell CF2 (second row, 85th column), and wants to correct it for you. Worse yet, Excel won't take "No" for an answer. Frustrating, isn't it? There are many such idiosyncrasies in Excel. When having trouble, one of the things to try is changing the name of your procedure or variable; it may be conflicting with something reserved by Excel or VBA.
To get your function to work, rename it to something like Cent2F. Try your function in worksheet cells A1, A2, and A3 using C = -40°, C = 20°, and C = 100°, respectively.
Column Buckling
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 type of column support condition 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. Use the Internet to find appropriate values for the elastic modulus. Note that most property tables list these values in units of GPA (gigapascals, or 10^9 N/m^2).
| End fixtures |
K value |
| Pinned |
1 |
| Fixed |
1 |
| Fixed-Pinned |
1 |
| Fixed-Free |
2 |
| Material |
Elastic Modulus (GPa) |
| Steel |
(look up this value on the Internet) |
| Aluminum |
(look up this value on the Internet) |
| Wood |
(look up this value on the Internet) |
B. Create two named input cells for the beam length, L (in meters), and square cross-section dimension, d (in millimeters), and properly label these input cells. Use L = 2 m and d=3 mm as initial inputs for these parameters.
C. Create the following results table on the user interface worksheet to contain the resulting critical buckling load (F) for each case of material and effective length factor.
|
Critical buckling load iF) |
| Material |
Pinned |
Fixed |
Fixed-Pinned |
Fixed-Free |
| steel |
|
|
|
|
| aluminum |
|
|
|
|
| wood |
|
|
|
|
D. Write a VBA Function to calculate the critical buckling load for a column, with the four required inputs (K, E, L, and d) passed from the worksheet as function arguments. Research the equation for the area moment of inertia of a square cross-section beam, and use that equation in your function to calculate I.
E. Use your function in each of the critical buckling load table cells. Use relative vs. absolute cell referencing wisely, so that cell references passed to your function can be copied (Ctrl-C) and pasted (Ctrl-V) without having to manually correct them.
F. Add the correct units of the resulting critical buckling load to the results table header.
Incandescent Space Heating
When energy is added to a fluid (liquid or gas), the temperature of the fluid increases. An equation describing this phenomenon is:
Q = MCp ΔT
Q is the amount of energy added (joules)
M is the mass of the fluid (kg)
Cp is the heat capacity of the fluid (joules/kg-°C)
ΔT is the change in temperature (°C)
A garage (24 ft x 24 ft x 10 ft) is illuminated by six 60-W incandescent bulbs. It is estimated that 90% of the energy consumed by an incandescent bulb is dissipated as heat. Question: If the bulbs are on for 3 hours, how much would the temperature in the garage increase because of the light bulbs? Assume that no energy is lost through the walls, floor, or ceiling of the garage.
Create a User Interface Worksheet like the one shown below, using the input/output units shown.
Wisely name the input/output cells so they can be easily referenced using the Range() method in VBA.
Create a Sub procedure that does the following:
1. Declares all local variables using appropriate data types.
2. Reads the user inputs entered in the "Specified Information" input section of the worksheet, using the cell range names that you defined, and stores the values in local variables.
3. Performs all necessary calculations.
4. Writes out the results to the "Calculated Information" section of the worksheet. Mind your units!
Add user instructions and a printable "Run" button to the worksheet, for your user to use when running your Sub procedure
