Exercises

Many of these were exams (exam 2) in the past.

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.

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 a unit per year. Storage, insurance, like that. Must be a number that is greater than zero.
  • Whether Smurfs are present. After trimming spaces, should be Y or N, though case doesn't matter.

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

Another example:

More output

Upload your workbook. The usual coding standards apply.

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.

Marcella has discovered that felines are the only creatures unaffected by the aliens' transmogrifiers. Write an Excel worksheet with some VBA to help her figure out how many leopards, lions, and tigers to rent.

Here's what your solution should look like to start with:

Start

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:

Errors

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.

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:

Output

Marcella has $90,000 in her wallet. If the total cost is more than $90,000, show a message along with the output, like this:

Output

The usual coding standards apply.

Upload your worksheet.

Exercise

What's the cricket score?

Cricket is a popular sport in British Commonwealth countries, like Australia, and India. Each match has two sides. 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

Here's some more sample output.

More sample output

All input values must be numeric and zero or more. If there are errors, show messages next to each erroneous cell, in red. Show all applicable error messages at once. 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.

The usual coding standards apply.

Upload your solution.

Exercise

Goat milk

Estimate the amount of goat milk you'll get per day, based on the number of goats of different breeds you have, and whether you love them or not.

The worksheet starts like this:

Start

For each breed, the user enters the number of goats (a number zero or more), and whether they love the goats. They can can enter y, yes, n, or no. Case and extra spaces shouldn't matter.

If they enter invalid data, they see all applicable error messages, like this:

Errors

If the data's OK, compute the estimated milk production for that scenario. For example:

Output

Here production data, in gallons per day per goat:

Breed Loved Unloved
Alpine 2.2 1.7
Nubian 2.8 2.1
Saanen 1.9 1.5

Here's some more output:

More output

Be sure to clear errors and output at the start of each run.

Upload your workbook. The usual standard apply.

Exercise

Goat fuel

Cthulhu sponsors goat races every month. The course varies in length, and whether there are hurdles or not. Before each race, Cthulhu feeds the goats, so they have the energy they'll need. Write a program to work out the number of kilocalories needed for a race.

You can download a start worksheet. It looks like this:

Start

The user types:

  • The number of goats (must be a number more than zero)
  • The course length (must be a number at least 10)
  • Whether there are hurdles (must be y or n, allow upper- or lowercase, and extra spaces)

Validate the data, showing all appropriate error messages. For example:

Error messages

Make sure error messages and output are cleared each time the program runs.

Some sample output:

Output

Kilocalories is goats times length times 100 divided by 1,000. If there are hurdles, multiply kilocalories times two.

If more than 100 kilocalories are needed, Cthulhu wants to bring in Euphonites, his goat nutritionist. Show a message to that effect:

More output

Upload your workbook. The usual coding standards apply.

Exercise

Goat shirts

Goat

Paula Poundstone is selling shirts for goats on her website. The shirts are made of a remarkably soft tri-poly blend that goats adore. Write a VBA program to compute the cost of an order. The worksheet starts like this (you can download) it):

Start

Customers enter the number of small, medium, and large goat shirts they want. The values must be numeric, and greater than zero. If they aren't, show error messages, like this:

Output

Of course, once an error is fixed, its message should go away. Only show the output if all input is valid.

Shipping can be done by Bonnie, or Toni. Bonnie is cheaper, but her attention wanders. Toni is more likely to get the job done quickly.

The user should enter B or T in the shipping field. Case and extra spaces don't matter. If the user enters something else, show an error message, as shown.

If all the data is OK, compute the total cost. Here are some examples.

Output

Output

Large shirts sell for $28 each, medium for $25, and small for $22. Shipping is $2 per shirt for Bonnie, and $3 per shirt for Toni.

Upload your solution. The usual coding standards apply.

Exercise

Your validation program

Write a program about whatever you like, that has:

  • Input from the worksheet.
  • At least one string, and one numeric input.
  • Output to the worksheet.
  • Validation of all input. Numeric and range check (numeric input), and normalization (string input).
  • All error messages shown on the worksheet.
  • Old output and error messages cleared at start.
  • Output depends on input values, tested with one or more Ifs.

See the other exercises on this page if you need inspiration. Silly is OK.

Upload your workbook. The usual coding standards apply.

Exercise

Exploration expeditions

Write a program to estimate the cost of a goat exploration expedition. You can download a starter workbook. It starts like this:

Start

Users enter the number of goats of different sizes. The values must be numeric, and cannot be less than zero. If there are errors, display error messages all at once, and don't output anything. For example:

Validation errors

Don't forget to erase old error messages, and the output, the next time the program runs.

Estimated days should be a number, at least four. Valid values for terrain are shown in the screen shot. Let users type lowercase if they want, and add extra spaces if they want. Here's another sample screen:

More errors

The terrain is OK, since lowercase and extra spaces are allowed.

Here's some output:

Output

For planning purposes, add three extra days in your calculations. Don't change the user's input, though. For example, if the user estimated six days, do your calculations based on nine days, though leave the cell the user typed in (B8) at six.

Large goats need two ration packs per day. Medium goats need one per day, and small goats need half a pack per day.

Ration packs cost $8 each, unless the expedition is going into jungle. Jungle packs cost $10 each.

Goats need one water jug each, unless they're going into the desert. Then they need two each. Water jugs cost $17 each.

Panniers let goats carry supplies. Here's a small goat with a pannier:

Pannier

Panniers can hold 16 ration packs, or four water jugs. Round up the number of panniers, like this:

  • sPanniersCount = Application.RoundUp(sPanniersCount, 0)

Large and medium goats can carry two panniers each. Small goats can carry one.

Panniers cost $42 each.

There are two warnings you should show, if applicable:

  • When there are more panniers than the goats can carry. Show: You have more panniers than you can carry.
  • Estimated days (the user input) is more than 14. Show: Please check with Tough Toni.

If both warnings are needed, they should be together. For example:

More output

Some more output:

Output

Upload your solution here, not to Moodle. The usual programming standards apply.

Exercise

Bookie bookie book club

Cthulhu is helping his goaty friends form bookie bookie book clubs. Each club has a name (like Bonnie's Ear Burns), and can order three different titles. Cthulhu will cover most of the cost.

You can download a start workbook.

Here's what the order form looks like to start.

Start

The goats enter a name for their club. The name is required. If there is no name, report an error. If the name is just spaces (some goats will try to trick your program), that counts as no name. Show an error message, and end the program.

Error

The goats enter the number of each title they want. The values must be numbers. Here's an error message:

Another error

The numbers can't be negative:

Yet another error

If all the data is OK, complete the computations, like this:

OK

Moby Dicks cost $12.95 each. Eats are $14.95. Fights are $11.95.

If the book cost is less than $50, shipping is 20% of the book cost. If the order is between $50 and $100, then shipping is 10%. Shipping for orders $100 or more is free.

Legal fees are 18% of book costs. There's an ongoing copyright infringement case over the bookie bookie book theme song.

Total cost is books plus shipping plus legal.

The goats pay $8. Cthulhu pays the rest.

Upload your solution. The usual standards apply.

Exercise

Kaba-a-a-a-a-ddi

Kaba-a-a-a-a-ddi is a fave game for goats. Two teams... well, it's hard to describe.

Download the start workbook. Write a program that works out who won a game.

The workbook starts like this:

Start

Users fill in the top part, following the rules given:

  • Goats must be a number, at least 5.
  • Raw score must be a number, at least 0.
  • Shirt color must be green, orange, or blue. Users are allowed to use any combination of upper- and lowercase, and spaces at the start and end of their input.

Make sure you:

  • Show all errors at once.
  • Clear all error messages and output each time the program runs.

Error sample:

Errors

The raw score is adjusted based on the number of players, and the color of their opponent's shirts.

  • For each player above 10, the score is adjusted down by one. For example, if a team had 15 players, 5 points would be subtracted from their raw score.
  • For each player below 10, the score is adjusted up by one. For example, if a team had 5 players, 5 points would be added to their raw score.
  • If a team wears blue shirts, their opponent's score is adjusted up by 5.
  • If a team wears orange shirts, their opponent's score is adjusted down by 5.
  • There is no score adjustment for green shirts.

The winner cell should show "Team 1", "Team 2", or "Draw".

Here's some output:

Output

Upload your solution. The usual standards apply.

Exercise

Mighty Mary's Smash Hut

Mary owns Mighty Mary's Smash Hut, a boxing school in Rochester Hills. She has a special offer, an intro to face smashing course, at a special price. Even cheaper if you have a Groupon. Write a program to compute the price of an order. You can download a starting workbook.

Here's the starting worksheet:

Start

Input:

  • People, from 1 to 4.
  • Groupon, y or n.
  • Rent headgear, y or n.
  • Rent body pads, y or n.

Make sure the user is allowed to type in upper- or lowercase letters for the Y/N fields, with extra spaces at the beginning and/or end of their input.

Validate the input, showing appropriate error messages, using the advanced validation pattern. That is, all errors should be shown onscreen at the same time, like this:

Errors

Make sure error messages and output are cleared when the program runs.

The price is computed as follows:

  • The base price per person is $99 with a Groupon, or $250 without one.
  • Headgear rental costs $20 per person, whether or not there is a Groupon.
  • Body pads rental cost $30 per person, whether or not there is a Groupon.

If the user says they don't want headgear and/or body pads (either or both), show a warning below the price.

Here's some output:

Output

Here's some more:

Output

The usual programming standards apply, including the secluded Cells rule.

Upload your solution to this site, not to Moodle.

Exercise

Ambassador Toni

Toni is producer and ambassador for Nobody Listens to Paula Poundstone. Her apartment has flooded, so she's staying in a hotel with her cat, Mr. Totes.

The hotel has a bar, called The Drunken Bishop, or something like that. Toni is supposed to visit the bar, promote the show, and get some new paid subscribers. (The show is free, but some people support it financially, to keep Toni in nodefridas. OK, maybe not, but it makes for a good programming task, so go with it.) She's supposed to wear her NLTPP sweatshirt.

One time at the Boozy Priest, or whatever it's called, Toni caught Randy's eye. He instantly fell in love, because of course he would.

Write a program to compute the profit or loss from Toni's visits. You can download a starting worksheet. It looks like this:

Start

Users fill in the green fields, and click Run. The inputs are:

  • Number of bar visits, numeric, one or more.
  • Cocktails per visit, numeric, minimum of three. (Not saying Toni has a problem, though Mr. Totes has his own opinion.)
  • Whether Toni wore her NLTPP sweatshirt. Y or N. Allow the user to type upper or lowercase, with leading and trailing spaces, if they want.
  • Number of visits Randy is present, numeric, minimum zero, maximum is the number of bar visits.

Costs

Cocktails cost $12 each.

If Toni wears her sweatshirt, add in laundry cost of $16. There will be stains.

Subscription revenue

If she wears her shirt, people will ask her about NLTPP, and revenue will be $208 per visit. If she doesn't wear it, revenue will be $22 per visit, since nobody knows she produces Nobody Knows, except for people she grabs and shakes after her third cocktail.

Income increases by $122 (old wrong value $175, which is OK too, use either value) each time Randy is there. He's really into Toni.

Here's some sample output:

Output

Output

Output

Output

Clear all output before the program runs. All relevant error messages should show simultaneously. There should be no revenue or profit output if there are any errors.

Upload your solution here, not to Moodle. The usual coding standards apply.

Exercise

Sheep yurt sizing

Since worm farming isn't profitable, Paula is getting into the yurting biz. There's a big demand for sheep yurts in Mongolia, Manitoba, and other M places.

Paula sells four models, and can make custom yurts, too. Here are the yurts:

Model Area
Yurtlet 25 sq meters
Rambler 64 sq meters
Homesteader 100 sq meters
X2000 Industrial 200 sq meters
Custom build More than 200 sq meters

Here are the areas one sheep needs at minimum:

Size Area
Big 1.15 sq meters
Medium 0.83 sq meters
Small 0.67 sq meters

When you put sheep into a yurt, you can pack them in three shapes.

  • Rectangular is the most efficient.
  • Circular is less efficient, requiring an extra 20%. So, a herd needing 100 sq meters when packed in a rectangle would need 120 sq meters when packed in a circle.
  • Triangular is the least efficient, requiring an extra 50% more than rectangular.

Write a program to help estimate customers' yurtal needs, based on the sizes of their sheep herds and their packing preference. Use this workbook to start. It opens like this:

Start

Users enter the number of big, medium, and small sheep they have, as well as the packing shape they want to use in their yurts. They click the button and, if there are no input errors, get the area needed, and the model needed. If they have a total of 35 or more sheep, they get a warning.

Here's how errors are reported:

Errors

Input requirements:

  • Data in the number fields must be numeric, and zero or more. Use the error messages shown. Error messages should be red.
  • The packing shape must be C, R, or T. However, allow users to enter lowercase, and to have spaces before and after the letter. So, an input of " c " is valid.

Clear the errors and output each time the program runs. Report all relevant errors when the user runs the program. Don't just report one error at a time.

Here are some output samples:

Output

Output

Notice the warning when there are 35 or more sheep.

Output

Upload your solution to this site. The usual programming standards apply.

Exercise

Sheldon's fundraiser

Sheldon is planning an online fundraiser for his fave podcast, Nobody Listens To Paula Poundstone. Help him estimate how much money he'll raise under various conditions. You can download a starting workbook.

The podcast has two types of fans: Nobodies, and Crinkle Heads. They like different aspects of the show. Sheldon thinks Nobodies will contribute an average of $12 each, with Crinkle Heads contributing $8 each.

Sheldon is thinking of having special content for the event:

  • A theme song. There are three possibilities. An original song, a song with original lyrics but a stolen tune, or no theme song.
  • A craft segment, teaching fans how to make cootie catchers.

Different fans react to content in their own way.

  • Nobodies like original theme songs, and will increase their donations by $4 each if there is one. They don't care for songs with stolen tunes. They'll decrease their donations by $2 each in that case. If there's no song, they'll increase their donations by $6.
  • Crinkle Heads are OK with original songs, and will increase their donations by $3 each if there is one. If there's a song with a stolen tune, they'll increase their donations by $6. They love stolen tunes. If there's no theme song, they'll donate $3 less each.
  • Nobodies like crafts, and will increase their donations by $4 each if there's a how-to-make-a-cootie-catcher video.
  • Crinkle Heads aren't so keen on crafts. They'll reduce their donations by $1 each if there's a cootie catcher video.

There'll be other expenses depending on the special content.

  • If there's a song with a stolen tune, there'll be a $1,200 legal fee.
  • If there's a craft video, there'll be $700 in editing costs, and $30 in supplies.

Here's the starting workbook:

Start

Sheldon enters four values. The Nobodies and Crinkle Heads must be numbers, zero or more, and cannot be greater than 10,000.

Theme song and Cootie catcher must be one of the values in the screenshot. Sheldon is allowed to type upper or lowercase, and add extra spaces if he likes.

Here are some error messages:

Errors

All applicable errors must be displayed at once, not one at a time. If there are errors, the output cells should be empty. Don't show error messages when Sheldon has fixed the problems.

Some output:

Output

Some more:

Output

Deduct extra expenses before showing funds raised.

Submit your solution here. The usual coding standards apply.