Washington State University



MgtOp 470—Business Modeling with Spreadsheets

Washington State University

Spring 2018

Problem Set 3

Due: March 6, midnight

E-mail your completed files to lan.luo@wsu.edu. This assignment may be completed in a group of up to four people. Please state in the email message the names of all persons turning in this assignment. Please put “MgtOp 470 Homework 3” followed by the name of the person who sent the email in the Subject of the email.

Note: The file related to this assignment are available on our course website.

1. (6 points)

Write a sub for your school that does the following: (a) displays a Yes-No box that asks whether the student is a “full-time student;” (b) uses an input box to ask how many credits the student is going to take next semester; and (c) if the result of the Yes-No box in part (a) is vbYes (the built-in VBA constant that results from clicking the Yes button), use a new message box to inform that the status of the student is “Full-Time” and the tuition fee for the student is $4800; otherwise the message box should inform that the status of the student is “Part-Time.” Part-time students need to pay $400 per credit. Use a separate line to display the total amount due for the student (whether full-time or part-time).

2. (8 points)

Write a sub that does the following: 1) it asks the user to input a positive integer; 2) it enters all of the odd integers up to the input number in consecutive cells in column A, starting with cell A1; 3) it displays the product of all odd integers that were placed into the spreadsheet in the cell just below the last odd integer. Hint: Use For Loop.

3. (8 points)

Write a sub that does the following: 1) it asks the user to input a word; 2) it enters each character of the word in consecutive cells in row 2, starting with cell B2. Hint: Use a For Loop.

4. (8 points)

Starting with the Sales Data file from the course website (Chapter 7), write a sub uses a For Each loop to italicize each sales amount that is greater than $12,000 and change the interior color to gray for each region in column A where the annually sales exceeds $125,000 .

5. (6 points)

An Input Box statement returns a string—whatever the user enters into the box. However, it returns a blank string if the user enters nothing and clicks the OK or Cancel button (or the X button). Write a sub that uses an Input Box to ask the user for a product code. Embed this in a Do loop so that the user has to keep trying until the result is not a blank string.

6. (6 points)

Assume that you have a mailing list file. The file is currently the active workbook, and the active sheet of this workbook has full names in column A, starting in Cell A1, with last name last and everything in uppercase letters (such as BOB C. LEE). Write a sub that counts the number of names in the list with last name LEE and then displays this count in an MsgBox that is titled “Number of Lees” and has an Exclamation icon button. Note that there might be last names, such as KLEE, which should not be counted.

7. (8 points)

Moonlight Potato Company offers a quantity discount to its customers. The discount scheme is shown in the following table.

|Quantity (lbs.) |Price/lb. |

|< 4,000 |$0.06 |

|4,000−9,999.99 |$0.055 |

|10,000−15,999.99 |$0.05 |

|16,000−19,999.99 |$0.045 |

|>= 20,000 |$0.04 |

Manually insert the above table into Excel using three columns: (A) contains the minimum quantity breakpoint in a range, (B) contains the maximum quantity breakpoint in a range, and (C) contains the applicable price. Write a sub to ask the customer how many pounds of potatoes that he or she would like to order. Use the Case construction to compute the total amount due. Be sure to use cell references in the VBA code that refer to the table in Excel (so that changes in the quantity discount schedule could be manually entered into Excel without needing to change the VBA code). Display a message that informs the customer the amount of the total bill due to Moonlight Potato Company.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download