vba homework help

Boost your journey with 24/7 access to skilled experts, offering unmatched vba homework help

Trusted by 1.1 M+ Happy Students

4.2Trust Pilot

4.4Edu Reviewer

5App Review

4.5Student

Recently Asked vba Questions

Expert help when you need it

Q1: 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. See Answer
Q2: Work should be justified by providing statements in the code itself. (Use the # sign to write logic, arguments and your understanding as done in the lab). Feel free to use other ways of providing justification. You should always stand behind your work – if you are unwilling to do so, why should anyone believe you? Insufficient validation (-3 to -5) depending on the amount of work No validation (-5)See Answer
Q3: Create a dictionary that consists of keys from Flight num list. No dictionary definition (-2 8 keys are not located in dictionary correctly (-1 each)See Answer
Q4: 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)See Answer
Q5: Passenger_info() function to fill the information of passengers. Function is not taking (3) inputs from user (first name, last name,flight number) (-1 each) Function doesn't have a loop to get ID (-1) Function is not asking all ID types (State ID, TIN, Driver license) ina decision statement with a loop (-1 each) Function does not first name, last name, flight number and ID (-1each)See Answer
Q6: 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)See Answer
Q7: Design of the Jupyter file Design is easily understandable, explanatory notes are included ifnecessarv. Use your resources efficiently. Basically, use the file that is provided toyou. a) Not clear, haphazard (- 5)See Answer
Q8: Update_passenger() function to create "Allowed_passenger"dictionary based on the rules. Function is not checking flight number, ID, first name and lastname and Covid-19 status with a nested if statement (-1.5 each) Function is not checking Covid-19 status by calling previousfunctions (-1.25 each) :) Function is not warning when luggage weight is exceeded andCovid-19 status is positive (-1.25 each) Function is not returning correct “Allowed_passenger" dictionary(-0 to -2.5) depends on the correctness of the dictionarySee Answer
Q9: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.See Answer
Q10: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 OutputSee Answer
Q11: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.See Answer
Q12:CHEN 1310 Option Explicit Sub Iteration () Dim i As Integer, xinit As Double, x As Double, n As Integer xinit = InputBox ("Please enter initial guess.") x = xinit n - 10 For i=1 To n x = sqr(x^(1/3) + 5 + x) Next i MsgBox Format Number (x, 3) End Sub Lab #08 You may have noticed the FormatNumber function on the second to the last line. This formats the variable x to 3 places to the right of the decimal point (thousandths place). What is x after 10 iterations? Make sure your cursor is on any line within the Iteration subroutine and step through your sub using F8. Also, verify that your locals window is open (if not, select View -> Locals Window). Using an initial guess of 4, fill in the following table with the values of x for the first 5 iterations (the first iteration has been filled in for you): Iteration (i in your code) 1 2 3 4 5 X Page 2 4.65 Change n in your code to be equal to 20 iterations. What is x after 20 iterations?/n2. In the first part of this workshop, we will be solving an equation of the form x = f(x) using iterative solving. Computers are good at doing things over and over and iterative solving uses several to many iterations. First, save your file as a macro-enabled workbook and name it "FirstName_LastName_Lab_08.xlsm". We would like to solve for a root of the following equation: x =VX1/3+5x You should all be familiar with iterative solving; the function value (right side of equation above) is used as the x-value for the next iteration, etc. If you are unfamiliar with iterative solving, please refresh your memory by viewing the following screencast: https://vimeo.com/87810968 A general flowchart for iterative solving is shown below: output x end N begin input X₁ i>n ? X = Xo n = 10 i=1 i=i+1 x = f(x) In the VBE, create code for this flowchart and name your sub Iteration. You should Dim the variables xinit (initial guess), x, i, and n. Use a value of 10 for the number of iterations (n, this can be changed later if you'd like). Use the function above. IMPORTANT: The next page has the solution, it is way better if you try to do this on your own before peaking at the solution!!! Ask your TA if you need help, give it your best shot before looking at the next page! Here is one way to write this code in the VBE:See Answer
Q13:3. In our Iteration sub, we are doing a fixed number of iterations (.e., n llerations- no more, no less). Sometimes, our ferative process may require fewer iterations or more iterations and we want to make sure that our sub can be adaptable. Now, we are going to adapt our Iteration sub such that it will keep iterating until a certain tolerance is met. The flowchart for this is shown on the next page. CHEN 1310 BIO begin inputx Lab 900 Tel-0.001 xnew-x) Er-x- E Tol? And the code for this new flowchart is shown here: xxnow Loop Until Err Pol MogBox FormatNumber (3) End Sub option Explicit Sub Iteration () Din xinit As Double, x As Double Din Err As Double, Tol As Double, new As Double xinit InputBox("Please enter initial guess.") x-xinit 701 = 0.001 Do xnow = sqr(x (1/3) + 5*x) Err- Abs (xnew- x) output x and Page 3 Adapt your code to that shown above. Notice that we have a Do...Loop Until in which we calculate an error (Err) and compare it to a tolerance (Tol). Tol is a value that can be set by the user-we iterate until (and hence the Loop Until)-the difference between subsequent x-values is less than or equal to Tol. Note that we also need to add Dim statements for Err, Tol, and xnew. We have eliminated in and I and the Dim statements for n and I because they are no longer needed. Now would be a good time to save!/nStep through (using FB) your code to make sure it is working. Enter 4 as the initial guess. You should have gotten the same answer as you did before using a fixed number of iterations. The code is a bit more involved, but why do you think implementing the loop might be more advantageous than a fixed number of terations? CHEN 1310 Lab 905 Page 4 What is the significance of xnew? In other words, why do we need to define xnew as a separate variable?See Answer
Q14: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 BSee Answer
Q15: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 81See Answer
Q16:2. An interesting function is shown below, and this pattern continues for all x. For x between 0 and 4, the function takes on these values (pattern repeats itself indefinitely): f(x) 20 10 f(x)=0 for 0 < x < 1 f(x) = 10 for 1 ≤ x < 2 f(x) = 20 for 2 <x<3 f(x) = 10 for 3 ≤ x < 4 ... and so on. 1 2 3 4 5 6 7 8 X Pattern continues Write a VBA function called Periodic that returns the value f(x) of the function above for a given value of x. If a negative value for x is input, the function should just be the same (mirror image about the y-axis). Importantly, x should not only be constrained to integers, but x can take on any real value. Strategy. When faced with a repeating pattern, as is in this problem, it can be valuable to utilize the Mod operator in VBA, which yields the remainder when two numbers are divided./nCHEN 1310 Homework #6 Page 3 result = number1 Mod number2 The modulus, or remainder, operator divides number1 by number2 (rounding floating-point numbers to integers) and returns only the remainder as result. In this problem, there is a pattern that repeats itself every 4 units of x. In other words, f(x)=f(x+4.n), where n is a positive integer. So, a statement of the form x Mod 4 will convert any x > 4 to a "transformed" 0<x< 4. Then, f(x) can easily be determined. The only other caveat is the fact that the Mod operator first converts number1 to an integer (rounding to the nearest integer); therefore, 7.5 Mod 4 is not equal to 3.5 nor 3, but rather is equal to 0 (the 3.5 is first rounded up to 4 so when divided by 4 there is no remainder). This problem can be circumvented by prudent use of the Int function, which returns the integer value of a number, e.g., Int(3.5) = 3. Create the function and verify that it is working properly. What is f(2873)?See Answer
Q17: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.See Answer
Q18:Purpose: develop VBA procedures to manipulate ranges of cells on the spreadsheet write a series of short procedures to manipulate arrays in VBA • • create an array function that returns more than a single value to the spreadsheet • work with flowchartsSee Answer
Q19:1. The following shows an example of tax code (this is from a number of years ago, so it probably is not still valid). a. Single Taxable income $0 to $9,275 $9,276 to $37,650 $37,650 to $91,150 $91,150 to $190,150 $190,150 to $413,350 $413,350 to $415,050 $415,050 or more Married Filing Jointly Taxable income $0 to $18,550 $18,550 to $75,300 $75,300 to $151,900 $151,900 to $231,450 $231,450 to $413,350 $413,350 to $466,950 $466,950 or more Tax rate 10% $927.50 plus 15% of the amount over $9,275 $5,183.75 plus 25% of the amount over $37,650 $18,558.75 plus 28% of the amount over $91,150 $46,278.75 plus 33% of the amount over $190,150 $119,934.75 plus 35% of the amount over $413,350 $120,529.75 plus 39.6% of the amount over $415,050 Tax rate 10% $1,855.00 plus 15% of the amount over $18,550 $10,367.50 plus 25% of the amount over $75,300 $29,517.50 plus 28% of the amount over $151,900 $51,791.50 plus 33% of the amount over $231,450 $111,818.50 plus 35% of the amount over $413,350 $130,578.50 plus 39.6% of the amount over $466,950 Create a user-defined VBA function called tax(<income>,<status>) that outputs the tax given the taxable income (in dollars) and status (1 for single and 2 for married filing jointly). As an example, if a married couple earned $125,000 (taxable income), then the tax would be: $10,367.50 +0.25*($125,000-$75,300) = $22,792.50/nworking function in Excel: =tax(125000,2) 22792.5 If you make $75,000 in taxable income your first year working as an engineer, what will your taxes be? Assume that you are single. Also, what percentage of your overall taxable income will you be paying in taxes? b. Plot tax as a function of taxable income from $0 to $500,000 for a single taxpayer. Remove any markers and present the result as a smoothed line. c. You overhear a very wealthy couple (married filing jointly, who you know make $500,000 a year in taxable income) complain, "40% of what we make we have to pay back in taxes!" Yes, the money that is earned between $466,950 and $500,000 is taxed at a nearly 40% tax rate (39.6%), but really, how much (looking for a percentage) of their total taxable income do they have to pay in taxes? 2. The exponential function can be approximated as the following Taylor series: e* = 1 + x + +.... =Σ** n! n=0 Create a VBA function called myexp(x) that will sum the first 100 terms of the above infinite series. A summation sign is a good indicator that you will need to use a For...Next loop. Show a few examples and compare to the exp(x) function in Excel. Are they the same?/n3. Create a VBA function called prime(n) that outputs TRUE if n is a prime number and FALSE if n is not a prime number. The flowchart on the next page depicts the algorithm for determining if n is prime or not. Let's assume most of you will live another 60 years (good assumption). For the years 2023 through 2083, how many of those years are prime number years? Document in Excel. HINT #1: The mod function in VBA will help you determine whether a number x is divisible by y (if x Mod y is equal to zero, that means x is divisible by y with no remainder). HINT #2: The ROUNDDOWN function in Excel will provide you with the integer part (written in the flow chart as int) of a number. For example, int(√37) is 6 and in VBA you can use ROUNDDOWN(SQR(37,0)), which is 6. You could also use the int function.See Answer
Q20:2. Create a VBA array function called ShiftVector(rng.n) with arguments for a range (mg) and in that will shift the elements of an (m x 1) vector up by n rows. The first n rows of the range rng will "wrap around" and appear at the bottom of the resulting vector. IMPORTANT: Your array function should be capable of accommodating strings and data types other than just numbers. 1 2 hi 6789&wN- 3 4 hey 5 8 yo 1-ShiftVector(A1:A9,3) /output by END 5 3 9 7 2 import START input n ✓ Count rows nr Ctri-Shift-Enter Here is a flow chart that you can implement in your ShiftVector function: -rag tis(nr-n)) + b(:) = rng(i+1) is ar i-nr-1-1 5 3 OF 3 1234SSTE a yo 2 2 hi 4 hey 5 6 8 yo 1 hey #example if 5 9 7 yo hi В 7 2 1 6(nr_n+1 →nr) = [6(79)] this will become b(1-ar-n). =[6(1+6)] + 6(1) = (ng(i-nr +n) "Remember that if you don't have Office 365, you'll need to press Ctrl-Shift-Enter when you enter this function in Excel!See Answer

TutorBin Testimonials

I got my Vba homework done on time. My assignment is proofread and edited by professionals. Got zero plagiarism as experts developed my assignment from scratch. Feel relieved and super excited.

Joey Dip

I found TutorBin Vba homework help when I was struggling with complex concepts. Experts provided step-wise explanations and examples to help me understand concepts clearly.

Rick Jordon

TutorBin experts resolve your doubts without making you wait for long. Their experts are responsive & available 24/7 whenever you need Vba subject guidance.

Andrea Jacobs

I trust TutorBin for assisting me in completing Vba assignments with quality and 100% accuracy. Experts are polite, listen to my problems, and have extensive experience in their domain.

Lilian King

I got my Vba homework done on time. My assignment is proofread and edited by professionals. Got zero plagiarism as experts developed my assignment from scratch. Feel relieved and super excited.

Joey Dip

I found TutorBin Vba homework help when I was struggling with complex concepts. Experts provided step-wise explanations and examples to help me understand concepts clearly.

Rick Jordon

Popular Subjects for vba

You can get the best rated step-by-step problem explanations from 65000+ expert tutors by ordering TutorBin vba homework help.

TutorBin helping students around the globe

TutorBin believes that distance should never be a barrier to learning. Over 500000+ orders and 100000+ happy customers explain TutorBin has become the name that keeps learning fun in the UK, USA, Canada, Australia, Singapore, and UAE.