Select Page

Homework week 5
Problem 1
Concepts – VBA’s built-in functions, relative references in VBA, Option Explicit
Complete this problem on Sheet1.
The user will select a cell on Sheet1 and input a positive number (you do not know which cell will be selected
ahead of time). This cell automatically becomes the active cell. Next, create a macro named problem1 that
will perform the following tasks:
• Obtain the value in the active cell and store it in a variable.
• In the cell to the right of the original active cell, output 5 plus the value in the original active cell
• In the cell above the original active cell, output one-half the value in the original active cell
• In the cell below the original active cell, output the square root of the value in the original active cell
Create a run button on Sheet1 that executes problem1.
Your macro must work for any positive number input into any cell. For example, if you type 36 in cell B3 and
then click the run button, the number 41 will appear in C3, the number 18 will appear in B2, and the number
6 will appear in B4. Then if you click on cell D5, type 25, and click the run button, the number 30 will appear
in E5, the number 12.5 will appear in D4, and the number 5 will appear in D6.
You can assume the user will not input the value in the first or last row, nor the last column (Think about what
will happen if the value is input into one of those cells. Or better yet, try doing it and see what happens.).
Assume the user will input a value in the active cell before clicking the run button.
Use the Option Explicit statement for this macro and in all future macros that you create. This will
force you to declare your variables.
Hint: When the run button is clicked, whatever cell is selected at that time automatically becomes the active
cell. You can move to the right, above, and below the active cell by using relative references in your program.
This will require you to use ActiveCell.Offset() three times.
Problem 2
Concept – Calling multiple Sub procedures
Complete this problem on Sheet2.
d
L
The surface area and volume of a cylinder of length L and diameter d are given by the following formulas:
π 
SA = π d L + 2 d 2 
4 
,
π 
Vol = L d 2 
4 
The value of π is approximately 3.141592654.
In large programs it is common practice to use one Sub procedure to obtain input for the program from files or
worksheets, one or more other Sub procedures to perform calculations, and one final Sub procedure to write
the output to files or worksheets. The macro that contains all these Sub procedures is often named main.
Input the length of cylinder into cell A2 and the diameter of a cylinder into cell A3. Create a run button on
Sheet2 that executes a macro named main. This macro performs the following tasks:
• Call a Sub procedure named getvals that obtains the length and diameter from cells A2 and A3, and
stores them in variables.
• Then call a Sub procedure named calcSAVol that calculates the surface area and volume of the cylinder
and stores them in variables. Inside calcSAVol create a constant named pi that stores the value
of π.
• Finally call a Sub procedure named writeout that outputs the values of the surface area and volume to
cells B6 and B7.
This problem will require you to create four different Sub procedures (main, getvals, calcSAVol, and
writeout). You will call getvals, calcSAVol, and writeout from inside main. Write all four Sub
procedures in one module.
Highlight and label the cells where the user must input data and the cells where data will be output to the
worksheet. Assume the user will input all necessary values before clicking the run button.
Use the Option Explicit statement for this macro.
Problem 3
Concept – Reusing a Sub procedure
Complete this problem on Sheet3.
The user travels from point 1 to point 2, then point 2 to point 3, then point 3 to point 1, and wants to know
the total distance travelled. Three sets of coordinates will be entered into Sheet3.
The x-, y-, and z-coordinate of point 1 in cells B2, B3, and B4
The x-, y-, and z-coordinate of point 2 in cells C2, C3, and C4
The x-, y-, and z-coordinate of point 3 in cells D2, D3, and D4
Create a run button in Sheet3 that executes a macro named problem3. This macro performs the following
• Obtain the x, y, and z values of all three points from the worksheet and store them in variables.
• Call a Sub procedure named calcdist to calculate the distance between points 1 and 2. Here is an
example of how to write this Call statement:
Call calcdist(x1, y1, z1, x2, y2, z2, d12)
• Call calcdist a second time to calculate the distance between points 2 and 3.
• Call calcdist a third time to calculate the distance between points 1 and 3.
• Output the three distances to cells B6, B7, and B8.
• Calculate the total distance travelled and output this value to cell B9.
This problem will require you to create two Sub procedures (problem3 and calcdist). You only need to
write the instructions for calcdist once, but you will be calling it three times within problem3. Write
both Sub procedures in one module.
Highlight and label the cells where the user must input data and the cells where data will be output to the
worksheet. Assume the user will input all necessary values before clicking the run button.
Use the Option Explicit statement for this macro.

Purchase answer to see full
attachment

#### Why Choose Us

• 100% non-plagiarized Papers
• 24/7 /365 Service Available
• Affordable Prices
• Any Paper, Urgency, and Subject
• Will complete your papers in 6 hours
• On-time Delivery
• Money-back and Privacy guarantees
• Unlimited Amendments upon request
• Satisfaction guarantee

#### How it Works

• Click on the “Place Order” tab at the top menu or “Order Now” icon at the bottom and a new page will appear with an order form to be filled.
• Fill in your paper’s requirements in the "PAPER DETAILS" section.
• Fill in your paper’s academic level, deadline, and the required number of pages from the drop-down menus.
• Click “CREATE ACCOUNT & SIGN IN” to enter your registration details and get an account with us for record-keeping and then, click on “PROCEED TO CHECKOUT” at the bottom of the page.
• From there, the payment sections will show, follow the guided payment process and your order will be available for our writing team to work on it.