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.4Trust Pilot

4.4Edu Reviewer

5App Review

4.8Student

Place An Orderand save time

^*Get instant homework help from top tutors—just a WhatsApp message away. 24/7 support for all your academic needs!

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: 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
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: 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
Q5: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
Q6: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
Q7: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
Q8: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
Q9: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
Q10: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
Q11:Please name your file Firstname Lastname Homework 08.xlsm IMPORTANT: Remember that I encourage you to work in groups, but each student must submit his/her own homework spreadsheet. Every student's homework assignment should look significantly different from all other students (ie., each student's work should be a representation of their own work). You are responsible for keeping copies of all work you submit for grading. Also, please plan to submit your homework in advance - there will be no exceptions in case your computer and/or internet connection are not working. Please plan to submit assignments at least a few hours early so that any connectivity or computer problems can be fixed or another computer/facility can be used. Solve the following problems using Excel/VBA. Please place each problem on a new worksheet in your single workbook. Label areas of the spreadsheet to make it clear where each part of the problem is worked. You may consider separating various parts of problems by a border. NOTE: You may have to search for various bits of information somewhere, like on the web (?)! 1. (Problem 2, Homework 4 revisited.) For the ladder problem described in Problem 2 of Homework 4. develop a VBA function called Ladder(a,b,w1,w2) that has input arguments a, b, w₁, and w, and returns the length of the longest ladder that will fit around a given corner. Your function should use the Golden Search method (20 rounds) to find the minimum L (see diagram in Homework 4 assignment) and it is this minimum value of L that is the length of the longest ladder that will fit around the comer. The variables a and b are the initial low and high values used in the Golden Search method. The TA should be able to type into a cell on your spreadsheet "Ladder(0,10,2,3)", for example, and the answer for the longest ladder that fits around the corresponding corner will be output in that cell (no input boxes or message boxes!). Your solution must utilize the Golden Search method. HINT#1: If I were you, I would set this up in Excel first (similar to bisection-the solution is found in the Homework 4 Solutions online!) so that you have something to check your function return value against (I've also got an example below). HINT #2: See the Learning Module "Implementing targeting and optimization algorithms in VBA subroutines in the Class 16 module. This screencast shows how to do the Golder Search method in a subroutine, but you'll have to implement a function here. Example Output: A 1 Problem 1 2 3 4 5 6779 b w1 W2 B C 0 10 2 D Length: Ladder(C3,C4,C5, C6) 1 Problem 1 2345 6 7 B a b w1 w2 B Length: C 0 10 2 3 7.023482See Answer
Q12: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.See Answer
Q13:2. Write a VBA function (not a macro) to implement the moving average method that takes the observations of a time series and the order parameter k as input and outputs the forecast for the next time period in the series. This has to be implemented and a built-in excel function is not acceptable. (15 marks)See Answer
Q14:3. Case Study A small company has commissioned you to develop a useful spreadsheet modelling tool to support their demand forecasting on a weekly basis. The tool will be used by the Operations Manager who needs forecasts of demand for 1-4 weeks ahead to help him plan production. He also needs a useful way of monitoring the performance of forecasts that he can use in weekly and monthly management reports since this information will help inform inventory policies, for example. Data and Information To build, test and demonstrate the use of your modelling tool, the Operations Manager has made available the data in the spreadsheet "MS924 resit assignment data and can be downloaded from myplace page folder.See Answer

TutorBin Testimonials

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

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.