Reference no: EM132866419

INFS 3150 Principles Of Structured Computer Programming And Problem Solving - University of Toledo

VBA Project

Problem Description: Download Project 6.xlsm file, rename the file to yourLastName_Project 6.xlsm and review the worksheets contained within it. This project requires you to demonstrate your understanding of Functions, Subroutines and Variable Scope. There are 2 parts to this project.

Part 1: Write a reusable Function to determine the letter grade based upon a score. Review the data on the worksheet named Grades. Each semester when I administer midterms and final exams, I must repeatedly write the logic to determine the students' letter grade. You are going to help me with that by writing a reusable function that receives a double value and returns a letter grade. Hint: 78.5% is actually .785 but formatted to display as a percent.

Part 2: Write an algorithm to process a list of transactions and update the Inventory quantities (maintained in an Excel worksheet).

The Inventory worksheet contains a complete list of all Inventory items and the current quantity on hand for each item.

The Transactions worksheet contains the ItemID, a transaction Code which is either an "R" - for inventory Received into the warehouse or an "I" for inventory Issued to the production floor, and the Qty received or issued.

The changes to inventory identified in the Transactions worksheet need to be processed and the Inventory worksheet must be updated to reflect the changes to an item's inventory as noted in a transaction. (Note: inventory received should INCREASE the Inventory QOH column and inventory issued should DECREASE the Inventory QOH column).

Write an algorithm that processes the data on the Transactions worksheet. (Assign the Button on the Transaction worksheet to your Sub procedure).

To process the transaction data:
• For each row on the Transaction worksheet
o Lookup the Transaction.ItemID on the Inventory Worksheet
o When the ItemID is found (meaning the ItemID on the Transaction worksheet equals the ItemID on the Inventory worksheet), update the Inventory.Item's QOH by adding the

transaction's Qty (for rows where the Transaction.Code = "R") OR subtracting the Qty (for rows where the Transaction.Code = "I").
o Also, highlight the QOH (on the Inventory worksheet) to yellow (to identify all items with a value change)
o Count each transaction processed to provide a count of how many Receipts were processed and how many Issues were processed.
• Process all transactions - updating the Inventory data as described above.
• After processing all transactions, provide a summary of the counts:

The updated Inventory Worksheet (after processing the data) would look like the screen shot below:

The worksheet, called SavedData, contains the original data, so that you can reset the data to its original values after each attempt (I am assuming you may not get it right with a single attempt). I have provided a macro to do the reset and a button that will execute the code for you.

We have worked with multiple worksheets in other assignments, but as a reminder: when working with two sheets simultaneously, you will need to reference the non-active sheet's Cells and/or Ranges by referencing them with the sheet name: Sheets("Inventory").Range("A"& row) or by using the object reference: Sheet2.Range("A" & row). Both of those examples allow you to reference the Inventory worksheet's cells. I have placed the button to execute your code on the Transaction worksheet (thus making it the active sheet to the code of the macro) so the samples I provided for you to reference the cells on the Inventory worksheet will be useful to you.

Required:

1) Solve the problem - write the algorithm to process all the inventory transactions (row 2 through row #N#) on the Transaction Worksheet:
a. Search the Inventory worksheet until you find the matching ItemID
b. Determine if the transaction is for an Issue of inventory or a Receipt of Inventory
c. Update the QOH on the Inventory worksheet for that ItemID
d. Highlight the QOH value to indicate that it was changed (which will help you validate whether your code is working without having to look at all 252 inventory items: You're Welcome!)
2) Write a solution that will work on any length of data sets - meaning rows could be added or deleted from either worksheet and your logic would still work. Do NOT hardcode the lastRow value for either list but use a solution that will work no matter how many rows are in either worksheet.

3) Assign the Sub to execute upon a click of the Transaction worksheet's Button.

4) Count of the number of inventory updates made by type (Issue / Receive) - and at the conclusion of the processing, pop up a single message box with the counts. (Because we have an excellent inventory management system, you can be confident that there are no transactions with an invalid ItemID.)

5) Desk check your work. That means to visually inspect your results. Your solution should report 76 updates (32 Receipts and 44 Issues). If your result doesn't match mine, look at your logic to deduce the problem.

6) When ready to submit the assignment, Reset your worksheet data.

7) Your algorithm MUST demonstrate a level of understanding on variable scope by utilizing at least two Sub procedures in your solution (the first Sub will be assigned to the worksheet Button and the second Sub must be called by the first Sub). You must also use at least one modular scoped variable in your solution - and your second Sub must have a signature that receives at least one value passed to it by the invoking statement.

Attachment:- VBA Project.rar