Elizabeth

The exam.

Exercise

Receivables

Cthulhu does business with companies in the Midwest, selling them goating supplies. Most companies pay their bills on time, but some accounts are delinquent. Write an Excel VBA program that analyzes the data.

Cthulhu has given you a CSV file, with data on companies with delinquent accounts. Only delinquent accounts are in the file. The data is like this:

  • "Runbam","MI",20179
  • "Joyzarcity","IN",18891
  • "Code-holding","MI",24465

The first field is the name of the company. The second is the state the company is in. The third is the balance owed.

There might be errors in the data. Exclude records with errors from the analysis. Here are the rules:

  • Name: cannot be blank.
  • State: must be one of IA, IL, IN, MI, or OH. Allow for case differences, and extra spaces at the start and end. So " iA " is OK.
  • Balance: a number greater than zero.

Your worksheet should start like this:

Start

The user is supposed to type in a file name, before pressing the button. If they do not, show an error message and stop the program:

Missing file name error

Here's what the output should be:

Output

(Hint: make sure error messages from prior runs are removed.)

The output shows the number of valid and invalid records, the total balances from each state, and the grand total. The totals only include data from valid records.

Cthulhu also wants to know the name of the company with the highest balance, and what that balance is. Why does it want to know? Best not to ask.

Goat

Wouldn't want to be them. Just sayin.

Upload your workbook. The usual standards apply.