More exercises

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.

Upload your workbook.

Hint:

dwarvesNeeded = snowWhites * 7
dwarvesChange = dwarvesNeeded - dwarves

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
8.     'Input
9.     getInputs pandas, lambs, bunnies, inputOK
10.     If Not inputOK Then
11.         'There was a problem with the input.
12.         End
13.     End If
15.     'Compute the total price.
16.     computePrice pandas, lambs, bunnies, totalPrice
18.     'Output the total prie.
19.     outputPrice totalPrice
21. 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?

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.

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.

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.

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.

Upload your solution.

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.

Upload your solution.

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.

Upload your solution.

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.

Upload your Excel workbook.

Some scientists were messing around in the lab. They cloned a cute bunny. What could possibly go wrong?

They created Bun Bun, a giant sentient bunny, with superpowers. Bun Bun saw how bunnies were being treated. He swore vengeance on humans, and become Evil Bun Bun the Destroyer. He's forced the scientists to work for him, preparing a clone bunny army.

Write a program to help Bun Bun work out how much food he'll need. You can download a starting worksheet.

There are two types of clones. Subbuns are his lieutenants. Minbuns are his foot soldiers. Here's what the worksheet looks like at the start.

Users type in the number of subbuns and minbuns, and the price per kilo of carrots. Each subbun needs 39 kilos of carrots. Each minbun needs 7.3 kilos.

Here's some sample output:

Validate all of the input. All inputs should be numbers that are greater than zero.

• Show errors to the right of each input cell, in red.
• If there is more than one error, show them all at once.
• Clear the errors and output each time the program runs.

For example:

Here some more errors.

You must use the flag pattern.

The usual coding standards apply.

Upload your worksheet.

Your company buys Stuff from wholesalers, and sells it retail. Work out the optimal order quantity. That is, how many Stuffs you should order each time to minimize cost.

Download the start worksheet, and add your code.

Here's how the worksheet starts:

Users type data into four cells. Validate them.

• Sales (units per year). Must be a number that is one or greater.
• Ordering cost ($). The cost of placing one order. Must be a number that is not negative.
• Carrying cost ($/unit/year). Cost of carrying one unit per year. Storage, insurance, like that. Must be a number that is not negative.
• Whether Smurfs are present. After trimming spaces, the first character should be Y or N, though case doesn't matter. So "Y", " n", " No, dude", "Yeah", and " Negatory, Nellie" are all OK. "Don't see none, Wilbur", " Seen them", "Banjo Bill wiped 'em out" are invalid.

Show appropriate error messages. Here's a sampling:

All applicable error messages should show, in red, next to the cells they apply to. Use the flag pattern.

Don't show output if there are any errors. As in the screenshot above.

Here are some more errors.

If all of the data is OK, compute the optimal order quantity, and show it. The formula from Wikipedia is:

Here's what the variables in the formula are:

If there are Smurfs, add one to the order quantity. They always steal just one.

Here's some output.

You don't have to use Subs for this. Though you can, if you want.

Upload your workbook.

The sleepy town of Fall's End, Montana, has been invaded by evil aliens! They have turned the townsfolk into lava lamps. There are roughly forty aliens, though the exact number is not known.

Poor research led the aliens to disguise themselves as clowns. They thought they would blend in with the locals.

Hansen has discovered that felines are the only creatures unaffected by the aliens' transmogrifiers. Write an Excel worksheet with some VBA to help him figure out how many leopards, lions, and tigers to hire. You can download a starter worksheet.

Here's what your solution should look like to start with:

Validate the three inputs. They must be numeric, and cannot be less than zero. In addition, leopards cannot be more than six, lions cannot be more than five, and tigers cannot be more than three.

Here are some error messages:

If there are any input errors, show all of the error messages at once. Hint: use the flag pattern.

Clear all output and error messages each time the program runs.

You can use subs for this, but you don't have to.

Compute and output the cost, and the number of clowns eaten. Each leopard $4,000, and will eat two clowns. Each lion costs $8,500, and will eat four clowns. Each tiger costs $12,800, and will eat seven clowns.

Hint: use singles for all numbers. It's just easier that way.

Here is some output:

Hansen has $90,000 in his wallet. If the total cost is more than $90,000, show a message along with the output, like this:

The usual coding standards apply.

Upload your worksheet.

Galatrax is a tourism company, in an obscure region of an obscure arm of an obscure spiral galaxy. They offer probing tours of backwater planets. Soil probing, weather probing, and other kinds of probing.

Galatrax is concerned about their notoriety on one planet, Grobo. If their notoriety levels get too high, Groboian authorities might start paying attention. That normally wouldn't concern Galatrax, except that the locals are so primitive that they have nuclear weapons. They'll kill themselves eventually, of course, but Galatrax doesn't want to hasten their apocalypse with an alien scare, and lose a fun tourist destination.

Make a workbook to help Galatrax estimate the notoriety levels generated by a tour. It should start like this:

There are three tour levels: gold, silver, and bronze. Each tour can have up to 20 regular probes, and 10 long probes. Check that user input is valid. Error messages should be in red text, next to the appropriate input cell. E.g.:

Check that the level is valid. Case of the level doesn't matter, so Silver and silver are both correct. Spaces before and after the level are OK.

The probe counts should be numeric, zero or more, but no more than the maximum allowed for that probe type.

Show all error messages at the same time. If there are input errors, there should be no output.

Here's another example:

Output the notoriety estimate for the tour, and a text interpretation. Here's an example:

For a gold tour, each regular probe adds 12 to the notoriety score. Each long probe adds 22.

For a silver tour, each regular probe adds 9 to the notoriety score. Each long probe adds 15.

For a bronze tour, each regular probe adds 7 to the notoriety score. Each long probe adds 11.

Here's another output example:

There are rules for the text interpretation. If the notoriety score is below 210, output "Low level" in green. If the notoriety score is 210 or more, but below 370, output "Warning: medium level" in magenta. If the notoriety score is 370 or more, output "Warning: high level" in red.

You must use the flag pattern.

You do not have to use subroutines, though you can if you want.

The usual coding standards apply.

Add your code to the starting worksheet. Upload your solution to this site.