# More exercises

Exercise: Annual Percentage Yield
From FinanceFormulas:

The Annual Percentage Yield (APY), referenced as the effective annual rate in Finance, is the rate of interest that is earned when taking into consideration the effect of compounding.

The formula is:

$APY = ( {1+{r \over n}} )^n-1$

r is the annual interest rate, and n is the number of compounding periods.

Create a worksheet that works like this:

Both inputs must be numeric. The interest rate must be greater than zero and less than one. The number of periods must be one or more.

If there are errors in both fields, show two error messages. For example:

Use the IPO pattern, with a sub for each.

Someone started writing the program, and got this far before being turned into a grue:

1. Private Sub cmdRun_Click()
2.     getInput r, n, dataOk
3.     If Not dataOk Then
4.         End
5.     End If
6.     computeAPY r, n, APY
7.     outputAPY APY
8. End Sub
Use this code in your solution. You can add to it, if you want.

(If you were logged in as a student, you could submit an exercise solution, and get some feedback.)

Exercise: More dwarves?
You have some Snow Whites and some dwarves. Each Snow White needs seven dwarves, but you might have more or dwarves than you need. Write a program to figure out how many dwarves to add, or get rid of.

Use IPO, with at least one Sub for each step (I, P, and O). Use more Subs if you want.

It starts like this:

Make sure that the two input values are numeric, and zero or more. Show errors in the worksheet:

Make sure that the errors and output from one run are cleared when the next run starts.

Here are output samples. Note that singular/plural is handled correctly.

Hint:

dwarvesNeeded = snowWhites * 7
dwarvesChange = dwarvesNeeded - dwarves

(If you were logged in as a student, you could submit an exercise solution, and get some feedback.)

Exercise: Pinata pricing
You sell three different types of pinatas. Pandas cost $14 each, lambs cost$12 each, and bunnies cost $9 each. Write a program that computes the total price of an order. Here’s a sample: The program validates the three input fields. They should all be numbers zero or more. If any of them is bad, show one error message, like this: Don’t create your own workbook. Download this one instead. It has some code already there: 1. Private Sub cmdRun_Click() 2. Dim pandas As Integer 3. Dim lambs As Integer 4. Dim bunnies As Integer 5. Dim inputOK As Boolean 6. Dim totalPrice As Integer 7. ‘Input 8. getInputs pandas, lambs, bunnies, inputOK 9. If Not inputOK Then 10. ‘There was a problem with the input. 11. End 12. End If 13. ‘Compute the total price. 14. computePrice pandas, lambs, bunnies, totalPrice 15. ‘Output the total prie. 16. outputPrice totalPrice 17. End Sub Don’t change this code! Don’t add or remove anything. Your job is to write the Subs. All the usual coding standards apply. Upload your completed workbook. In the text section of the submission form, please answer this question: what did you learn from this exercise? Attachments pinatas-start.xlsm (If you were logged in as a student, you could submit an exercise solution, and get some feedback.) Exercise: Paintball Halloween It’s Halloween. You hate Halloween. You decide to shoot unpleasant paintballs at people who enter your front gate. You have two laser-targeted paintball turrets by your door. You call them Stinker and Red. Stinker shoots balls filled with liquid artificial skunk stench. Red shoots balls filled with artificial blood. You decide every adult who enters your gate will get three skunkballs, and five bloodballs. Every teenage will get four skunkballs, and three bloodballs. Every tween will get one skunkball, and one bloodball. Younger kids won’t be shot at. Their turn will come. You use high-quality paintballs. Skunkballs cost you$0.25 each, and bloodballs $0.07 each. Write a program to compute the number of each ball type you’ll need and their total cost, given a number of adults, teens, and tweens. Here’s a screenshot: Round the cost to the neatest cent. It’s OK to leave off the last digit if it’s zero, as in the sample output above. Check the rounding numbers hint; it might be useful. All inputs should be numeric, and not less than zero. Your program should show an error message and stop when there’s an error. For example: Your program can stop immediately when it detects an error, or show all applicable error messages and then stop. Your choice. You must use subroutines. Use three at least: input, processing, and output. You may use more if you want. The usual coding standards apply. Upload your solution. (If you were logged in as a student, you could submit an exercise solution, and get some feedback.) Exercise: Petting zoo food cost Interstellar explorers find an Earth-like planet orbiting FH-3299. Some cute, harmless creatures have evolved there. You decide to set up a petting zoo, with three types of creatures: skwilks, zambos, and drufts. The creatures eat earthworms and paperclips. Each week: • Skwilks eat 1.8 kilos of earthworms, and 5.3 kilos of paperclips • Zambos eat 2.3 kilos of earthworms, and 6.2 kilos of paperclips • Drufts eat 3.9 kilos of earthworms, and 7.7 kilos of paperclips A kilos of worms costs$4.52. A kilo of paperclips costs $7.55. Write a program to compute the weekly cost of feeding your critters, given the number of each type of critter you have in the zoo. Here’s a screenshot: Round the cost to the neatest cent. It’s OK to leave off the last digit if it’s zero. Check the rounding numbers hint; it might be useful. All inputs should be numeric, and not less than zero. Your program should show an error message and stop when there’s an error. For example: Your program can stop immediately when it detects an error, or show all applicable error messages and then stop. Your choice. You must use subroutines. Use three at least: input, processing, and output. You may use more if you want. The usual coding standards apply. Upload your solution. (If you were logged in as a student, you could submit an exercise solution, and get some feedback.) Exercise: Catbots You start a company selling Catbots, cute robotic cats. You have two models:  Model Price Weight (kilos) Darth 1000$159 1.1 SayTen 44b $299 1.9 Write a program to compute the price of an order. It reports shipping cost, and total cost. The program starts like this: Customers enter the number of each bot they want. Check that the data is numeric, and between zero and five inclusive. Show an error message if the data is bad. For example: Shipping cost depends on the order. Orders for$1,000 or more (excluding shipping) are shipped for free. Otherwise, shipping is $4 per kilo. For example: Use the IPO pattern, that is, separate subs for input, processing, and output. Upload your worksheet. The usual coding standards apply. (If you were logged in as a student, you could submit an exercise solution, and get some feedback.) Exercise: Beligerent Unicycles Beligerent Unicycles makes top quality unicycles, with a difference. They cost more, but you get respect! They have three models. The FlameOut 5000 carries a napalm thrower. The Ratatat Special has a 0.30 cal machine gun. The Target 20 has no weapons. Here are the parts required to make each model:  Model Parts list FlameOut One frame, one steel wheel, one napalm thrower Ratatat One frame, one rubber wheel, one 0.30 cal MG Target One frame, one rubber wheel Here are parts costs:  Part Unit cost Frame$128.66 Rubber wheel $52.76 Steel wheel$228.55 Napalm thrower $1459 0.30 cal MG$2048

Write a program that will compute the parts cost for an order. The worksheet starts like this:

Users enter values for each unicycle model, and click Run. Your program checks that each value is numeric, and between 0 and 50 inclusive. If not, it shows an error message. For example:

If the data is OK, the program computes and shows parts cost:

Use the IPO pattern, with at least one sub for each. The usual coding standards apply.

(If you were logged in as a student, you could submit an exercise solution, and get some feedback.)

Exercise: What’s the cricket score?
Cricket is a popular sport in British Commonwealth countries, like Australia, and India. Each match has two sides (teams), as usual. There are four components to a side’s score:

• Boundaries (sixes): 6 points each
• Boundaries (fours): 4 points each
• Singles: 1 point each
• Sundries: 1 point each

You can read more about cricket scoring, if you want to know more.

Write a VBA program to compute a score given the information above. When the worksheet opens:

The user enters four values, clicks Run, and the program computes the score. For example:

All input values must be numeric and zero or more. If there are errors, show messages next to each erroneous cell. For example:

Be sure to clear old error messages and output when the program runs.

Do not start from scratch. Download this worksheet, and add the code you need. Warning! Don’t click the link and open in Excel immediately. Click the link, save the file, and then open it as a separate step.

• Do not change any code in the sub cmdRun_Click. Do not add, change, or remove anything.
• Do not change the declarations of any sub.
• Put code in every sub given in the starting worksheet, and call every sub at least once.
• Put any code you want in the subs, apart from cmdRun_Click, which must remain unchanged.
• Add any new subs you want.

The usual coding standards apply.

Attachments

cricket-score-start.xlsm

(If you were logged in as a student, you could submit an exercise solution, and get some feedback.)

When Jessica has a bad day, she might kill and injure customers and coworkers. They deserve it, though. The only thing that will help is her friend Victoria. If Victoria is nearby, Jessica will just injure the people she would have killed.

Write a program to compute the cost of a Jessica rampage. Here’s how it starts:

(Yes, I accidentally put a blank line after the coworker heading. You can remove it.)

The user fills in the five inputs. The death and injury counts should be numeric, and zero or more. The cell at 12, 2 should be Y or N, with case and extra spaces not mattering. If there are errors, show them to the right of the inputs. Like this:

Important! Show all the error messages that apply, not just the first one your code detects. Hint: use the flag pattern. Another hint: clear old error messages and output each time the program runs.

The cost of each death or injury are in cells (20, 2) to (23, 2), as you can see.

Here is some output:

Here is some more:

You don’t have to use subs for this one, though you can if you want.

(If you were logged in as a student, you could submit an exercise solution, and get some feedback.)

Exercise: Candy throwing estimator
A strange professor throws candy at…, er, to students. Write a program to help him estimate the cost of candy for a semester.

The worksheet starts like this:

Assume pieces per student, and cost per piece are already in the worksheet, and are correct. You don’t have to validate them. They can be fractional.

Here is some sample output:

The user inputs three numeric values: students, weeks, and attendance percentage. Test that they are numeric, and one or greater. You can assume users only enter whole numbers. Percentage attendance must be 100 or less.

The user can enter a course number as well, so that it shows when the worksheet is printed. You don’t need to do any validation of the course number.

Your program should output total number of candy pieces needed (always a whole number), and total cost in dollars (could be fractional). You do not need to round or truncate the values.

Show error messages, like this:

NOTE: show all error messages that apply. Also notice that the output cells are blank when there are errors. Remember to clear old error messages the next time the program is run.

Here’s some more output:

You don’t have to use subroutines, though you can if you want.

Hints:

• Use the flag pattern.
• Candy pieces is a whole number, but it could be big, so Dim it as Long. In fact, just Dim all of the whole number variables as Long, instead of Integer. It will help you avoid subtle bugs.