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:

Start

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.

Upload your spreadsheet.

(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:

Start

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

Errors

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.

Output

Output

Output

Output

Output

Upload your workbook.

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:

Output

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:

Error

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:

Sample

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:

Error

Error

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:

Sample

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:

Error

Error

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:

Start

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:

Error

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:

Valid order

Valid order

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:

Start

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:

Error

Error

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

Output

Use the IPO pattern, with at least one sub for each. 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: 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:

MT

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

Sample output

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

Errors

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.

Attachments

cricket-score-start.xlsm

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

Exercise: Jessica's bad day
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:

Start

(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:

Errors

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:

Output

Here is some more:

More output

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

Upload your solution.

(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:

Start state

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:

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:

Errors

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:

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.

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

Exercise: Evil Bun Bun
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.

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:

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:

Errors

Here some more errors.

More errors

You must use the flag pattern.

The usual coding standards apply.

Upload your worksheet.

Attachments

bunbun-start.xlsm

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

Exercise: Optimal order quantity
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:

Start

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:

Errors

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.

Errors

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

Formula

Here's what the variables in the formula are:

Variables in formula

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

Here's some output.

Output

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

Upload your workbook.

Attachments

optimal-order-quantity-start.xlsm

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

Exercise: Clown clearance
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.

Attachments

clown-clearance-start.xlsm

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