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

Exercise: Happiness estimator
Make a worksheet like this:

Worksheet

Everyone's happiness index starts off at 50. Subtract their age. Older people realize how futile life is.

If they have a degree in accounting, their happiness index goes down by 20. If they have a degree in econ, their happiness goes down by 10. If they have a degree in MIS, their happiness index goes up by 20.

The effect of humor depends on their degree. If they have a degree is accounting, subtract the humor rating from the happiness index. A sense of humor makes accountants sadder. If they have a degree in another field, add the humor rating.

Output the result. Here's an example:

Output

Validate the degree. Make sure the user enters Acc, Econ, or MIS. Case doesn't matter (mis and ACC are OK). You can assume that all other input data is correct.

EXTRA: Validate the rest of the input.

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

Exercise: Emotional baggage shipping cost
EBS is a company that ships emotional baggage. Write a program that computes the weight of someone's emotional baggage, and the cost of shipping it. The worksheet starts like this:

Start

Users fill in the customer's first name, age, and gender, and then click Run. If the data validates, the program outputs the baggage's weight (in emograms), and the shipping cost. For example:

Some output

Validate the three inputs, using the following rules:

  • A name must be entered. Case doesn't matter. jAN, Jan, JAN… all the same.
  • Age must be a number.
  • Gender must be M or F, either upper- or lowercase.

If there is an error, show an error message, and end the program. For example:

Error

Here are the rules for computing weight and cost.

  • Weight is 1.9 times age for women, and 1.4 times age for men.
  • People whose first name starts with "A" or "E" have an extra 32 emograms of emotional baggage. Nobody knows why.
  • Each emogram costs $26.30 to ship.

Hints:

You can check whether a string is empty like this:

  1. If thing = "" Then
That's two double-quotes together, with nothing between them.

You can get the first character of a string like this:

  1. Left(thing, 1)
Upload your worksheet.

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

Exercise: Caffeine
Write a program that predicts behavior, based on chocolate consumption. Download the attached spreadsheet, and modify it.

The program starts like this:

Start

The user enters data, and clicks Run. Validate the input, using the following rules.

  • Name must be given.
  • Gender must be F or M. Case doesn't matter. Leading and trailing spaces don't matter.
  • Dark must be a number. It cannot be negative.
  • Milk must be a number. It cannot be negative.

If there is an input error, show a message, and stop the program. For example:

Error

Output the name, caffeine eaten, and behavior prediction. Output the name without leading or trailing spaces. You do not need to round the caffeine eaten.

There are 20 mg of caffeine in 100 grams of milk chocolate, and 43 mg of caffeine in 100 grams of dark chocolate.

The behavior prediction depends on gender.

For women:

Caffeine (mg) Behavior
< 200 No change
>= 200 and < 400 Hyper
>= 400 Sarcasm overload

For men:

Caffeine (mg) Behavior
< 150 No change
>= 150 and < 300 Jerk
>= 300 The defendant pleads…

For example:

Output

Upload your workbook.

Attachments

caffeine-start.xlsm

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

Exercise: Tony's travel tracker
Tony runs a "fishing" boat in the Mediterranean. He doesn't catch fish, though. He transports high-value cargo, discreetly. Write a program to help Tony estimate his costs for a trip. Download the attached worksheet to save time.

The worksheet starts like this:

Start

There are three inputs: trip length (km), cargo weight (kg), and heat level. Heat level is the amount of attention the cargo would attract from local authorities, if it was discovered. Heat is either high, medium, or low.

Validate the input, using these rules.

  • Length and cargo must be numbers greater than zero.
  • Length cannot be more than 1,000.
  • Weight cannot be more than 2,000.
  • Heat must be H, M, or L. Case doesn't matter. Extra spaces don't matter.

If input is not valid, show an appropriate error message. For example:

Error

Costs are:

  • Fuel.
  • "Crew" to protect the cargo.
  • "Gifts" to local officials to, er, expedite shipping.
  • Overhead: an extra charge Tony throws in.

Fuel is €8 for each kilometer.

The other three depend on the heat level.

  • High: Crew costs €89 for each kg. Gifts are €4,000. Overhead is €44 for each kg, plus €31 for each km.
  • Medium: Crew costs €58 for each kg. Gifts are €2,000. Overhead is €25 for each kg, plus €19 for each km.
  • Low: Crew costs €38 for each kg (just Tony's cousin Stretchy Raoul). Gifts are €1,200. Overhead is €27 for each kg, plus €11 for each km.

Output these values, and the total. For example:

Output

Hint: Use longs or singles, not integers.

Upload your workbook.

Attachments

tony-start.xlsm

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

Exercise: Larry and the Lions
Larry is director and lead dancer of Larry and the Lions, a famous pole dancing troupe. Larry is starting an off-Broadway revival of the musical Cats, but with pole dancing. Naturally, it will be called Pole Cats. A dream come true!

Larry's friend Ben, a well-known architect, has an idea for a new theater design. Theaters usually separate the performers and the audience, like this:

Normal theater

For Pole Cats, Ben wants to replace the stage and the seats with ten microstages, scattered around the space.

Theater design for _Pole Cats_

The audience will be free to wander around, as the story unfolds on the microstages.

Some of the microstages will have poles, and some will not, as the drawing shows (poles are not to scale). Those with poles will also have tip jars.

Most aspects of the design are nailed down, but Larry and Ben aren't sure how many microstages should have poles, and how many should not. Write an Excel worksheet to help them figure out what to do. You can download a starter worksheet. Remember to save it on your PC before you open it.

The worksheet estimates profit for each performance. It starts like this:

Start

There are three inputs you should validate:

  • Number of microstages with poles.
  • Number of microstages without poles.
  • Number of tickets sold.

All must be numbers that are zero or more. If there's an error, show a message and stop the program. Here's an example:

Error

There are ten microstages, so the number with poles, plus the number without poles, must add up to ten.

Another error

The theater is small, so the number of tickets sold cannot be more than 700.

The worksheet should show four outputs, like this:

Output

Another example:

Output

Tickets are $42 each.

Estimated tips depend on the number of people in the audience (that is, the number of tickets sold), and the number of microstages with poles. Larry estimates that there will be $4 in tips for each ticket sold, for each pole-enhanced stage. So, the code is something like:

tips = ticketsSold * withPoles * 4

Each pole has to be cleaned, straightened, and anchored after each performance. That costs $300 per pole.

Production costs are $15,800 per performance.

So, profit is ticket revenue, plus tips, minus pole cost, minus production costs.

Write the code, and upload your solution.

Hint: make all numbers single, rather than integer.

Attachments

larry-start.xlsm

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