Reference no: EM131898991

Computational Methods for Industrial Engineering Term Project

DELIVERABLE ONE - Prepare a document in Microsoft (MS) Word that includes the following:

A program plan. The program plan must be developed electronically in MS Visio or a similar program. An example of a program plan is available on Canvas for Module #5.

Pseudocode for the CalculateTotal() sub procedure. (Writing the sub procedure will be easier if you do this before writing the code, though the due date is the same. I recommend doing this for AddLot() as well, though it does not need to be turned in.)

DELIVERABLE TWO - You must submit a single macro enabled Excel file via Canvas by the due date listed above.

PROJECT OBJECTIVE -

In partial fulfillment of the requirements of this course, you are required to develop an Excel VBA application to represent a decision support system (DSS). The application is intended for employees working at a semiconductor manufacturing plant to calculate the total processing time on a particular tool, referred to as ToolX (pictured below in figure 1), in the factory.

Figure 1. Example of semiconductor processing tool similar to ToolX.  Photo courtesy of Applied Materials.

Material in the factory is processed on "wafers". Wafers are grouped into "lots" of up to 25 wafers. ToolX processes wafers individually in process chambers. An individual ToolX can have up to 4 processing chambers working at one time in it. Different steps in the manufacturing process require use of this tool for different purposes, therefore processing could take a different amount of time depending on the step being processed and the number of wafers in the lot.

When a lot arrives at ToolX, it is loaded into the tool. Then individual wafers go to an open chamber for processing. A chamber can only process one wafer at a time, but if there are multiple chambers available, they can all be processing at the same time. Once the processing is complete for a wafer, that wafer is removed and a new wafer can use the chamber. Once all the wafers in the lot have been processed, they are unloaded from the tool.

An additional feature of ToolX, is that lots can be run back-to-back (BTB) on the tool. This means that the unloading of one lot or the loading of the next lot can be happening while wafers are being processed in the chambers. This means that time does not need to be added to the total processing time for loading and unloading of lots when they are running B2B. Only the first load time and last unload time need to be considered for a group of lots run BTB.

The DSS you develop will allow a user to specify how many wafers of a particular processing step need to be processed to get a resulting process time for the entire lot based on the current number of chambers that the tool is operating with. Multiple lots can be entered in the report. The basic report will show processing time for individual lots entered. A summary report will also be available to calculate the processing time of all lots if they are run B2B.

Employees working in the factory frequently need to plan activities for doing upgrades and routine maintenance on tools carefully. This DSS will help them do that.

SOLUTION REQUIREMENTS -

Your Excel VBA solution must include three worksheets named "Welcome", "Report", and "ProcessingTimes". The worksheet "ProcessingTimes" contains the processing times for each process based on an ID number (time is given per wafer), as well as the load and unload times (time given is per load or unload activity performed). The worksheet "ProcessingTimes" must always be hidden from the user. The worksheet "ProcessingTimes" is available on Canvas in the term project module. The specific actions to be performed in worksheets "Welcome" and "Report" are described below.

WORKSHEET "Welcome"

• When the Excel workbook first opens, only the worksheet "Welcome" must be visible to the user.
• The worksheet "Welcome" must display a rectangular shape with a brief explanation of the purpose of the Excel VBA application.
• The worksheet "Welcome" must not display gridlines. This functionality must be implemented via Excel VBA code.
• The worksheet "Welcome" must display two rectangular shape buttons labeled "PROCEED" and "EXIT APPLICATION".

Button "PROCEED"

Create a sub procedure named GoReportSheet() and assign it to the button "PROCEED". The following functionality must be provided when the user presses the button "PROCEED":

• The worksheet "Report" must be displayed.
• The worksheet "Welcome" must be hidden.

Button "EXIT APPLICATION"

Create a sub procedure named CloseApp() and assign it to the button "EXIT APPLICATION". The following functionality must be provided when the user presses the button "EXIT APPLICATION": Your application must display a message box asking the user if they wish to exit. The message box must have a yes and a no button to choose from.

• If the user presses the button labeled "Yes", the Excel workbook must be saved and closed.
• If the user presses the button labeled "No", the Excel workbook must remain open.
• The button labeled "No" must be setup as the default button of the message box.

WORKSHEET "Report"

The worksheet "Report" must include the following components:

• A rectangular shape with instructions about how to interact with the four buttons available in this worksheet.
• The rectangular shape must be configured manually to prevent it from shifting when calculations are performed.
• Four rectangular shape buttons labeled "ADD LOT", "CALCULATE TOTAL", "CLEAR FORM", and "BACK TO WELCOME".
• Rectangular shape buttons must be configured manually so that they do not shift when calculations are performed.
• The rectangular shape button labeled "CALCULATE TOTAL" must become visible only after there are at least two lots in the table.
• The worksheet "Report" must not display gridlines. This functionality must be implemented via Excel VBA code.
• All data (i.e., inputs, results) and data formats must be cleared from the worksheet "Report" every time the worksheet "Report" is accessed from the worksheet "Welcome".

The only objects that must always be visible when the worksheet "Report" is first displayed or cleared are:

• The rectangular shape with the instructions, and
• The rectangular shape buttons "ADD LOT", "CLEAR FORM", and "BACK TO WELCOME".

Button "ADD LOT"

Create a sub procedure named AddLot() and assign it to the button "ADD LOT". The following functionality must be provided when the user presses the button "ADD LOT":

The first time a user enters a lot in the table, the user will be prompted via an input box to specify how many chambers will be used for processing for all lots in the table. Valid inputs are 1, 2, 3 or 4.

• This input box must only appear for the first lot in a new table. The number of chambers entered will be valid for all calculations in the table.
• If the user enters an invalid number of chambers, a message box telling the user to enter a valid number of chambers must be displayed.
• If the user presses the "Cancel" button or presses the button "OK" without entering a value, the program must stop execution, ensure nothing additional is shown on the report, and variables have not been updated.

The user will be prompted via an input box to specify how many wafers will be processed. Valid inputs are integer values from 1 to 25.

• If the user enters an invalid number of wafers, a message box telling the user to enter a valid number of wafers must be displayed.
• If the user presses the "Cancel" button or presses the button "OK" without entering a value, the program must stop execution, ensure nothing additional is shown on the report, and variables have not been updated.

The user will be prompted via an input box to specify what step ID will be processed

• The process step entered must be validated by calling a function procedure named checkStep() to verify that the step ID entered exists in the worksheet "ProcessingTimes". Checking against this worksheet (your version of a local database) ensures that any new step ID's not originally in the worksheet will be allowed.
• A variable storing the step ID must be passed to the function procedure checkStep() as an argument. This variable must be used in the function checkStep() to compare against the table on "ProcessingTimes".
• If the user enters an invalid step ID a message box telling the user to enter a valid step ID must be displayed.
• If the user presses the "Cancel" button or presses the button "OK" without entering a value, the program must stop execution, ensure nothing additional is shown on the report, and variables have not been updated.

The user will be prompted via an input box to specify what the lot identifier is. Any entry is valid including a blank entry.

The first time a lot is added to the table, the column headers of the table that will organize the report must be displayed on the worksheet "Report" by calling a sub procedure named PrintTableHeader().

• A variable of type Range (set to a cell on the report worksheet of your choosing) must be passed to the sub procedure PrintTableHeader() as an argument. This cell must be used in PrintTableHeader() to set the values and formats of the cells of the table headers. You can arrange your report in any way you choose as long as it is clear. An example of what the table headers could be are shown below in table 1.

Lot Identifier

# of Processing Chambers to be used

# of Wafers in Lot

Processing Type

Processing Time (Minutes)

Table 1. Example of report table headers.

Once the user provides a valid lot that, a new row must be added to the table that will organize the output:

• Every cell in the new row must have a border.
• Rows added to the table must alternate their fill color to improve readability.
• The input values for chambers, wafers and step ID must have consistent formatting and display exactly as entered by the user.
• The processing time must be calculated using the values specified by the user and the method described for a single lot at the end of this document. How you implement this method is up to you but you must make it able to have new input values available to the user in the future (i.e. do not "hard code" different options).
• At least 1 global array must be used to store values that will be used in the calculateTotal() sub procedure.
• The output processing times must have consistent formatting and display 2 decimal points each.
• The table set up is up to you but it must be clear, easy to understand and read.

The sub procedure AddLot() must end by selecting cell A1.

Button "CALCULATE TOTAL"

Create a sub procedure named CalculateTotal() and assign it to the button "CALCULATE TOTAL". The following functionality must be provided when the user presses the button "CALCULATE TOTAL":

The total BTB processing time of the lots entered by the user must be calculated using the information stored in the global array(s) during the AddLot() sub procedure.

• The total BTB processing time must be calculated using all the values entered by the user for the lots and the method described at the end of the document for BTB lots. How you implement this method is up to you.
• Display a label and the output value. Where you display the value and the formatting are up to your choosing but make sure it is clear that this is something different from the rest of the table and that the total does not overwrite any part of the existing table.

Button "CLEAR FORM"

Create a sub procedure named ClearForm() and assign it to the button "CLEAR FORM". The following functionality must be provided when the user presses the button "CLEAR FORM":

• All data (i.e., inputs, results) and data formats must be cleared from the worksheet "Report".
• The button labeled "CALCULATE TOTAL" must be hidden.
• The sub procedure ClearForm() must end by selecting cell A1.

Button "WELCOME SHEET"

Create a sub procedure named GoBackWelcome() and assign it to the button "WELCOME SHEET". The following functionality must be provided when the user presses the button "WELCOME SHEET".

• The worksheet "Welcome" must be displayed.
• The worksheet "Report" must be hidden.
• The sub procedure GoBackWelcome() must end by selecting cell A1.

ADDITIONAL REQUIREMENTS

Except for the Excel VBA code needed to enable the functionality required when the Excel workbook first opens, all other Excel VBA code must be written in a single module (i.e., Module1).

Make sure your program does not experience excessive flickering when processing the input data or displaying the results

Make sure to follow good programming practices. In particular, the following will be considered heavily when grading your term project:

• Explicitly declaring all variables.
• Properly and consistently indenting your code so that it is easier to read.
• Adding extensive comments to your code.

CALCULATIONS REQUIRED -

Processing time for a single lot is calculated using the following method:

To compute the processing time for a single lot, two parts are needed.

1. Load/Unload Time.

a. The load time must be added once

b. The unload time must be added once.

2. Processing Chamber time.

a. The number of times the processing chambers are all used: the number of wafers must be divided by the number of chambers. The quotient is multiplied by the processing time per wafer.

b. The number of times the processing chambers are only partially used: if there is a remainder when dividing the number of wafers by the number of chambers that is greater than 0, then the processing time must be added again.

25 wafer example with 1 chamber: LoadTime + ProcTime * (25 + 0) + UnloadTime

25 wafer example with 2 chambers: LoadTime + ProcTime * (12 + 1) + UnloadTime

25 wafer example with 3 chambers: LoadTime + ProcTime * (8 + 1) + UnloadTime

25 wafer example with 4 chambers: LoadTime + ProcTime * (6 + 1) + UnloadTime

Processing time for multiple lots run back to back is calculated in a similar method to a single lot with two exceptions.

1. The load and unload time only occurs at the beginning and end of all the wafers run.

2. When the same process step (only when it is the same process step!) is run back to back, the wafers "fill up" all the chambers that previously were assumed to be left empty.

Two lot example: 25 wafers of step A then 25 wafers of step B with 3 chambers: LoadTime + ProcTimeA * (8 + 1) + ProcTimeB * (8 + 1) + UnloadTime

Two lot example: 25 wafers of step A then 25 wafers of step A with 3 chambers: LoadTime + ProcTimeA * (16 + 1) + UnloadTime

Attachment:- Assignment Files.rar