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.)