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

Exercise: Karla Krab Party Planner
Karla Krab is the bomb in the crab (and lobster) party scene. You want to see and be seen (and you're a crab (or lobster))? Go to one of Karla's legendary mix-it-up-like-a-blender-on-acid raves.

Karla

Karla, courtesy of Teagan Mathieson

Write a VBA program to help Karla plan a party. You can download a starting worksheet.

Karla has lost money in the past, when fewer crustaceans turned up than she had planned for. The room was too big (and expensive), and she had too much offal left over (crabs (and lobsters) eat offal). She wants your program to predict the effect on profits, if planned and actual attendance are different.

Your worksheet should start like this:

Start

The user fills in six values, above the Run button. Each one must be numeric, and 0 or more. If there's invalid data, show an appropriate error message, and stop the program. Like this:

Error

Crab ticket price is the cost of one crab ticket to the party, in clams. Lobster ticket price is the same, for lobsters. Planned attendance is the number of crabs (and lobsters) the party is planned for. Actual attendance is the number who show up.

Cost has two parts: shack rental, and offal cost. Shack rental depends on total (crabs plus lobsters) planned attendance. If there are fewer than 100, Karla can rent the small shack, for 1,100 clams. If planned attendance is 100 or more, but less than 300, Karla can rent the medium shack, for 1,500 clams. If planned attendance is 300 or more, but no more than 500, Karla can rent the large shack, for 1,900 clams. If planned attendance is more than 500, then show an error message, something like "Sorry, the total actual attendance must be 500 or less.", and stop the program.

Offal costs 10 clams for each crab, and 15 clams for each lobster. Total offal cost is based on planned attendance.

Revenue is based on actual crab (and lobster) attendance. The user has typed in the ticket prices.

Example:

Example

Requirement: Use the Cells() function only in the input and output parts of your programs. Use variables in the processing. So don't do something like this:

Cells(22, 33) = Cells(34, 23) * Cells(23, 44)

Instead, put the data into variables during input. Processing would just use variables, like this:

aVariable = anotherVariable * yetAnotherVariable

In output, put variables into cells.

Hints:

  • Copy-and-paste is your friend.
  • Write code for one input/validation first, and then test it, before you copy it.
  • Don't worry about the capacity of the room being exceeded.
  • You can use Single for all numeric variables, if you like.

Upload your workbook.

Attachments

crab-start.xlsm

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