Purpose:

12.6 VBA: Loops HW Create a new macro-enabled workbook and save it as (lastname-firstname]Loops.xlsm. The two macros in this homework should be assigned to separate buttons and should be located on the same worksheet. When you are done, upload your saved workbook below. Be sure to write your code in a module in the workbook you will submit, not in your personal workbooks or sheets. To access the correct module, go to your "Visual Basic," right-click "Microsoft Excel Objects" in the workbook you are submitting, and then click "Insert" and "Module. Problem 1: Write a Sub that prints out (in the cells, not debug window) numbers 1 through 25 In an Excel worksheet using any type of Loop (e.g., Do Until, Do While, For). Problem 2: Create a nested-loop program (you can use any type of loop you would like). This nested loop should take the names input (below) and print out every possible combination of first and middle names. These input names will need to appear in your workbook somewhere. Then, output every possible combination of first and middle names somewhere in the worksheet. Your program should work correctly with more or less first names and middle names. Below is an example of input and output. To save space, only the names associated with the first two first names are shown in the example output. Input First names Middle names Penelope Jill Gwyneth Diane Alice Heidi Anna Lilly Victoria Example Output

Code creates required (6) lists required (Please use these names for lists:Flight_num, Last_name, First_name, State_ID, TIN, Driver_license). Read each column and put them in the correct list by loop statement. Code is not reading the Credential.csv file (-3) Loop statement is not able to create the list (-2 each)

1. Get a dataset for 12 months average temperatures (any country of your choice). Name the sheet as "Data" and keep your main data on this sheet. Do the following tasks: Create a button of macro on the main sheet and when this button is clicked, the following task should be performed. o A new sheet is created after the "data" sheet and named as "analysis". o Average of the whole data is calculated and copied to the analysis sheet. o A line graph is created on the analysis sheet. Next do the following: Name the full range of dataset as "monthly data" Using macros, find max and min of the above range. Add value of "10" in each data point using looping in macros and generate a new dataset on the "analysis" sheet. Create a button in macro which when clicked "highlights this new dataset. Take an input from user for a value. If this value is in the updated dataset (highlighted), display "YES" else display "NO" in the output message.

1. Larger and Smaller Create an application that allows the user to enter two integers on a form similar to the one shown in Figure 4-33. The application should determine which value is larger than the other, or it should determine that the values are equal. Before com-paring the numbers, use the Try Parse method to verify that both inputs are valid integers. If an error is found, display an appropriate message to the user. Use a Label control to display all messages. The Exit button should close the window.

Covid-19 status and luggage weight functions Function doesn't have a binary variable to take decision (-1.5each) Function doesn't take the input from passenger (-2 each) No decision statement in the functions (-2 each) No return value in the function (-2 each)

work with flow charts 1. Launch Excel and go through the typical setup procedure for VBA program development: display the Developer tab set Macro security to enable all macros set Require Variable Declaration in the Visual Basic Editor Launch the VBE ( Alt-F11) and insert a module into the project associated with your Excel workbook.

Solve the following problems using Excel/VBA. Use a different module in the VBE for each problem. 1. Body mass index (BMI) is a measure of obesity. In standard units, it is calculated by the formula: W BMI = 703- H² where W is weight in pounds and H is height in inches. a. Create a VBA sub named BodyMassIndex that will ask the user for their weight and height in separate input boxes then display the BMI in a message box. Place a button on your spreadsheet that will run the sub. Your TA will test this to make sure it is working. b. Adapt your solution to part A to be a user defined function "BMI(W,H)" with arguments for weight (W) and height (H). Show an example of your function's use in a random cell on your spreadsheet.

3. A pipeline is to transfer crude oil from a tanker docking area to a large oil refinery. The power required to pump the oil, P in hp, is determined from P=4x10-¹3- w² p'd³ where w: oil flow rate in lb/hr p: density of the oil in lb/ft³ d: pipe diameter in ft The cost incurred, in dollars, is given by C = 10000d² +170P where the first term on the right-hand side represents the capital cost of the pipeline and the second term represents the cost of the pump and its operation. Power in the cost equation can be calculated using the equation for power above. a. Create a user-defined function called pumppower with arguments w, rho, and d that determines the power requirements (P) to pump oil. Use your function in Excel to determine the power requirements when the oil flow rate is 107 lb/hr, the density is 50 lb/ft³, and pipe diameter is 2.0 feet [i.e., calculate "=pumppower(1e7,50,2.0)"]. b. Create a user-defined function called cost with arguments d and P that calculates the cost of a pipeline. Use your answer from part a along with a diameter of 2.0 ft. to calculate the cost of a corresponding pipeline [i.e., calculate "=cost(2.0,< result from part a >)]. c. Now, use the Solver tool in Excel along with your pumppower and cost functions to adjust the diameter to determine the minimum cost for a pipeline pumping 107 lb/hr of oil with a density of 50 lb/ft³. You may want to generate a quick plot of cost as a function of diameter to verify your solution.

1. Create a VBA sub that will do the following (see example below): a. A cell within an array is selected by the user prior to running the sub. b. When the sub is started (using an on-sheet button!), an input box asks the user "By how many rows do you wish to offset?" c. The user inputs an integer. d. A second input box asks the user "By how many columns do you wish to offset?" e. The user inputs another integer. f. The sub squares the number that is in the original active cell and offsets the result in a cell that is offset by the number of rows and columns specified above by the user. g. Finally, the original array (cells A1:C5 in the example below) is highlighted yellow (color code = 65535). HINT: Record a macro to see the syntax for selecting a block of cells using the "Ctrl-*" (Ctrl-Shift- 8") key combination. This selects an entire block of cells. While the macro is still recording, select the "Text Highlight Color" tool (yellow) in the "Font" frame of the "Home" menu of the ribbon. Stop the macro and use this syntax at the end of your sub./nCHEN 1310 1 2 3 4 5 N 37964 -7 8 14 B -5 2 9 13 in 5 C 32 1 7 24 16 D Microsoft Excel By how many columns do you wish to offset? GO Homework #6 OK Cancel Microsoft Excel By how many rows do you wish to offset? WN 4 5 6 7 A 3 -7 8 6 14 529 13 5 32 1 7 24 16 Page 2 D X ОК Cancel 81

4. We are now going to adapt our iteration sub such that it can act on any function f(x) (and not just the one we've used in our example on the previous pages). We'll do this two different ways, the second way is really slick! Subs and functions can "call other subs and functions from within them. We are going to create a separate function fund that we can call from within our Iteration sub. Adapt your code for Iteration to be the following and also add a new function func (can be in the same module, just below Iteration, or could be in a separate module but in the same workbook). option Explicit Sub Iteration () Dim xinit As Double, x As Double Din Err As Double, Tel As Double, new As Double xinit = InputBox("Please enter initial guess.") x-xinit Tol = 0.001 Do znow=fune (x) Err-Aba (xnew-x) x = xnow Loop Ustil Er - Tol MagBox FormatNumber(x, 3) End Sub Function rane (z) fune- sqr(z (1/3) + 5* 23 End Function Place your cursor somewhere within the Iteration sub and step through using FB. What do you notice when you step through the "xnew=func(x)" Ine? Where does the cursorhighlight go to? Let's make a few more (very cooll) final modifications to our Iteration code. Delete the func function and modify iteration to be the following (okay, I admit, these are not minor but are somewhat major modifications):/nSubIteration() Din xinit As Double, As Double in E A Double, Tel As Double, now as Double Dima As String, Here As String on EXEGGUTO BOLO - Inputic (anter a function just as you would in Excel.VECELE(1) InputBox("Please enter initial gas.") xinit x-xit Tol-0.001 Evaluate Replace (Fm, "", 201 Ereb-x) Loop Ostil Err-701 MB TOK TOCRatmusivΣ (Χ, 3) "A GESr has occurred, please try again." Place your cursor somewhere within the Iteration sub and step through using F8. When the first Input box pops up, enter the following (note that we are using Excel syntax for the square root function, not VBA syntax): Microsoft Excel Enter a function 100 just as you would in Excel (Fox) x^(1/2)+3%) OK Cur Then press OK and enter an Intial guess of 4. Continue stepping through (FB) and make sure everything is working properly. You should get a result that is the same as what you got in the previous versions of neration. Please consult your TA if you are having problems, or go back and follow these instructions carefully. What does the function "Evaluate(Replace(Fxn, "x", x]" do? You will have to use this type of equation entry in the next couple weeks so this will help you on your homework! At this point, it is worthwhile to explain a few lines in the code that you may be unfamiliar with. First, I have Dimmed Fxn As String. The function that is input into the InputBox is stored as a String and used in the Replace function (see above). Second, you may notice the statement "VbCrLf. This is essentially a carriage return (Visual basic Carriage return Left formatted-from the old typewriter Ingol) and allows the "(Form: x = f(x))"Ine to be on a new line. Third, you may have noticed the lines "On Error GoTo Here" and "Here". We will now address these two statements and you will discover what they do! Run your code again using F8 so you can step through the code. This time, enter the following into the input DOK:/nCHEN 1310 Microsoft Excel Interaction you wil PINKS Lab 805 Carca Enter an initial guess of 4. Keep pressing F8 to cycle through iterations. Notice what is happening to the value of x. What is happening to x? Page 6 You will reach a point (If you press FB too quickly you will miss it!- this happens then you'll have to start over) when the cursor/highlight skips to the Ine "MsgBox "An error has occurred, please try again.": Exit Bub Here: MsgBox "An error has occurred, please try again." End Sub Make sure you save your file now. What has happened here is that the solution is veraing (not converging like we'd want for iterative solving) A point comes when x is just too large to be stored in VBA and an error occurs internally in the VBE. Based upon the line "On Error GoTo Here", what is happening when there is an Internal error? What is the Importance/significance of the line "Here:"7 Finally, you notice the line "Exit Sub" before the line "Here". What do you think this does and why is it needed? (If you are unsure, temporarily delete or "comment out the line by using an apostrophe at the beginning of the line to see what happens if Exit Sub is not present-you will want to use a function that converges.) Submission Instructions: When you have completed this workshop, upload your completed (and saved) "Lab 5 Worksheet" and your file "<FirstName_LastName> Lab_08.xlsm. These should be submitted to Canvas in the "Lab 5 module. For instructions on submitting assignments to Canvas, see the document How to Submit Assignments to Canvas". Leave Excel and return to Windows. Copy your workbook file to your USB drive or email to yourself. Log out. End of Lab Workshop B