More exercises

Lots of exercises here. For the last few, you might want to work with another human.

Exercise: Euros to USD
Make a workbook that converts euros to USD, using the current exchange rate. The worksheet looks like this to start:

Input

The user types in the number of euros and presses the button. The equivalent number of USD appears. For example:

Output

Assume that the user makes no input mistakes.

Upload your Excel file.

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

Exercise: Parade length
Write a program to help small towns estimate the length of parades, like their July 4th parade.

Parades have floats and marching bands. Each float needs 30 meters. Each band needs 100 meters.

The worksheet starts like this:

Start

The user types the number of floats and bands, and presses Run.

Add validation. Make sure that floats and bands are numbers, and zero or more. If the data is bad, show an error message in a MsgBox, and end the program. For example:

Error

If the input is OK, compute and output the result.

Output

Follow the usual coding standards for indenting and such.

Upload your worksheet.

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

Exercise: Stock price weekly change
Find the percentage change in a stock’s price, to one decimal place. Make sure input is numeric.

Here is what the worksheet looks like before the program runs.

Input

Here’s the output:

Output

Here’s what happens if the user doesn’t enter numbers.

Error

Be sure to check both inputs.

Upload your Excel file.

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

Exercise: Wrapping popcorn
You have big can of gourmet popcorn you want to wrap. You have a roll of wrapping paper. How much of the paper will you measure out and cut so you can wrap the can?

r = radius of the can (inches)

h = height of the can (inches)

w = width of the paper roll (inches) – often 36

p = length of paper needed from roll (inches)

\[p = \frac{2 \pi r^2 + 2 \pi rh + 2h+4 \pi r}{w}\]

The last two terms in the numerator allow for paper overlap for taping.

Upload your Excel file.

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

Exercise: Ulma’s Ungulates
Write a program to compute the price of renting an animal from Ulma’s Ungulates. You can rent either a llama, or a camel. The price depends on:

  • Which animal you choose.
  • How far you want to ride the aminal.
  • Whether you want to rent a saddle as well.

The worksheet starts like this:

Start

The user fills in the three fields above the Run button, and clicks Run. Here’s some output:

Output

Here’s another sample:

Output

Validate all the input, using the following rules:

  • The animal input must be either L or C. Case doesn’t matter. Use trim() to remove unwanted spaces.
  • The number of miles must be numeric, and between 3 and 50 inclusive.
  • Saddle is either Y or N. Case doesn’t matter. Use trim() to remove unwanted spaces.

If there are any input errors, show a message, and end the program. For example:

Error

Error

Output the full name of the animal, the warning, and the cost.

  • Llamas cost $20 per mile.
  • Llama saddles rent for $120.
  • Camels cost $12 per mile.
  • Camel saddles rent for $180.

The usual coding standards apply.

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

Exercise: Place setting items
You’re having a dinner party. You’re inviting couples, their children, and dogs. You’re not sure who you’ll invite yet.

Write a program to work out how many place setting items you will need, depending on the number of adults, children, and dogs you invite.

Each adult gets a salad plate, a salad fork, a dinner plate, a dinner knife, a dinner fork, a dessert bowl, a dessert spoon, a water glass, a wine glass, and a napkin.

Each child gets a dinner plate, a dinner knife, a dinner fork, a dessert bowl, a dessert spoon, a water glass, and two napkins.

Each dog gets four napkins, a food bowl, a water bowl, and two dog treats.

An extra four napkins are added, as spares.

Compute totals, as in the following example:

Example

Write everything in VBA. Do not use formulas in the worksheet.

Upload your Excel file.

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

Exercise: Santa’s security devices order
The only way Santa can hit his production targets is to enslave elves. Currently, he has 16,384 slaves. To keep order, Santa formed the Greater Elf Security and Tactical Police, aka the Gestapo. The Gestapo has 512 overseers, 16 captains, and General Huggy Buns, the head of the force.

Every year, Santa resupplies the Gestapo with uniforms, whips, and tasers. Every Gestapo member gets one new uniform every year, except for General Huggy Buns, who gets three.

The number of whips and tasers Santa buys depends on the unrest forecast (UF). If UF is low, here’s how many items each Gestapo member gets:

Overseers Captains General
Whips 1 3 5
Tasers 1 2 4

If UF is moderate, here’s how many items each Gestapo member gets:

Overseers Captains General
Whips 2 5 9
Tasers 2 4 8

If UF is high, here’s how many items each Gestapo member gets:

Overseers Captains General
Whips 3 8 16
Tasers 3 7 15

This code gives unit prices for every item Santa buys:

  1. uniformOverseerUnitPrice = 129.5
  2. uniformCaptainUnitPrice = 289.25
  3. uniformGeneralUnitPrice = 1295.5
  4. whipUnitPrice = 87.5
  5. taserUnitPrice = 429
Write a program that takes the UF as input, and shows counts and totals, as shown in this example:

Example

Hints:

  • Divide your code into input, processing, and output.
  • Try doing just the uniforms to start with. Get that right, then add the other stuff.

Do everything with VBA, of course. No formulas in the worksheet.

Upload your Excel file.

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

Exercise: Price a computer lab
Write a program to price computer labs for schools. A lab will have a number of pods. They’re sets of tables that face each other, in a square, pentagon, or hexagon:

Pod

Each table ($90) will have one Chromebook ($300), and space for writing. There should be two spare Chromebooks and one spare table for the lab. There should be one chair ($60 each) per student, plus five extras.

Each pod will have a network hub ($30), with a 3 meter (about 10 feet) network cable from each computer to the hub ($5 per). The hubs will be connected by 20 meter cables ($8 per) to a lab router ($200). All cable will run under carpet tiles. There should be one spare hub, and three spare short cables.

There will be one power strip for each pod ($12 per), connected to a lab junction box ($225). Allow 20 meters of power cable ($7) for each pod. There should be two spare power strips.

Write a program that takes two inputs:

  • The number of Chromebooks per pod, either 4, 5, or 6.
  • The number of pods, from 1 to 20.

Like this:

Input

Validate the input data.

Item prices should be in a table in the worksheet:

Prices

This allows for easy price changes. You need not validate the price data.

Your program should output the number of each component, cost of those components, the total number of components, and total cost. It should also output the maximum number of students the lab can serve at once.

As usual: indenting, good variables names, comments.

Upload your spreadsheet.

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

Exercise: Toupee pricing
Write a program to estimate the price of toupees. There are three models: rug, mop, and silky. The price per unit depends on the order amount:

Model Quantity Price per unit
Rug 1 – 9 $89.95
Rug 10 – 29 $79.95
Rug 30 or more $64.95
Mop 1 – 9 $129.95
Mop 10 – 29 $109.95
Mop 30 or more $89.95
Silky 1 – 9 $229.95
Silky 10 – 29 $189.95
Silky 30 or more $149.95

Your worksheet starts like this:

Empty

The user enters a model, a quantity, and clicks Run.

Your program validates both inputs. Model should be R, M, or S. Your program should work for upper- and lowercase, and if there are spaces before or after the character (e.g., “ r “ is OK). Show error messages as needed.

Quantity must be a number that’s one or greater. Show error messages as needed.

The total is price per unit times number ordered.

The usual coding standards apply.

Submit your worksheet.

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

Exercise: Dogs playing poker
You’ve probably seen this before.

Painting

Write a program to compute the cost of throwing a dog poker party. It starts like this:

Start

The user fills in three cells, and clicks the Run button. Your program computes the cost, and shows it. For example:

Output

If the user types a non-numeric value for large dogs or small dogs, show an error message and end the program.

If the user types a number less than zero for large dogs or small dogs, show an error message and end the program. Zero is OK.

If the total number of dogs (large dogs plus small dogs) is more than seven, show an error message and end the program.

Remove any spaces from around the user’s beer input (B6). If the beer input is other than Y, N, y, or n, show an error message and end the program.

The cost is $40 for a large dog, and $30 for a small dog. If there is beer, add $8 for each dog.

Upload your spreadsheet. Be sure to follow the coding standards.

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