USA: +1(669)289-8683 
Sign in
Processing Step1435
1436
1438
1535
1635
Inspection Time (seconds per wafer)
50,1
55,0
62,2
69,2
50,9
Re-Inspect Time (seconds per wafer)
125,4
147,5
189,1
204,8
120,3
% Re-Inspect
5,3%
4,2%
4,7%
1,2%
6,7%
Load/Unload
load (per lot)
unload (per lot)
load (per wafer)
unload (per wafer)
Time (seconds)
20,4
25,3
1,1
0,95
•
•
•
Excellent (everything works; solution produces valid and consistent results).
Good (graphical user interface is good; solution produces inconsistent results).
Bad (graphical user interface is poor; solution produces invalid results).
.
MAIN DELIVERABLES
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.
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,
–
–
DELIVERABLE TWO
You must submit a single macro enabled Excel file
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 inspection time on a particular tool,
referred to as tool IN-Spect (pictured below in figure 1), in the factory.
Figure 1. Example of semiconductor inspection tool similar to tool IN-Spect. Photo source:
Material in the factory is processed on “wafers”. Wafers are grouped into “lots” of up to 25 wafers. INSpect performs defect inspections on wafers during the semiconductor manufacturing process. The
inspection steps occur multiple times throughout the processing of wafers. Different inspections take
different amounts of time depending on the step being processed and the number of wafers in the lot.
When a lot arrives at IN-Spect, it is loaded into the tool. Then individual wafers move to the inspection
area inside the tool one at a time. Each wafer in the lot is inspected individually. Once the inspection is
complete for a wafer, that wafer is removed and a new wafer can be inspected. Once all the wafers in the
lot have been inspected and the lot passes inspection, they are unloaded from the tool. Sometimes a lot
fails an initial inspection and a re-inspection needs to occur. In this case, the lot remains in the tool for a
longer more detailed inspection.
The tool has storage spaces on it allowing lots to 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 inspected.
When this occurs 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 step need to be inspected
to get a resulting inspection times for the entire lot, including regular inspection time, re-inspection time
and expected inspection time. Multiple lots can be entered in the report. The basic report will show
inspection times for individual lots entered. A summary report will also be available to calculate the
inspection times 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
“InspectionTimes”. The worksheet “InspectionTimes” 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 “InspectionTimes” must always be hidden from the user. The
worksheet “InspectionTimes” . 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”:
o The worksheet “Report” must be displayed.
o 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”:
o 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:
o 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.
o Four rectangular shape buttons labeled “ADD LOT”, “CALCULATE TOTAL”, “CLEAR FORM”,
and “BACK TO WELCOME”.
§ (2 pts) Rectangular shape buttons must be configured manually so that they do not shift when
calculations are performed.
§ (2 pts) 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:
o The rectangular shape with the instructions, and
o 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”:
o 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.
o 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 “InspectionTimes”.
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 “InspectionTimes”.
§ 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.
o The user will be prompted via an input box to specify what the lot identifier is. Any entry is valid
including a blank entry. This is for the employee to put in specific lot ID’s if desired.
o 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 is shown below in table 1.
Table 1. Example of report table headers.
o
o
Once the user provides a valid lot, 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 wafers and step ID must have consistent formatting and display exactly as
entered by the user.
§ The inspection times 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 inspection 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”:
o 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”:
o All data (i.e., inputs, results) and data formats must be cleared from the worksheet “Report”.
o The button labeled “CALCULATE TOTAL” must be hidden.
o 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 “BACK TO WELCOME”.
The following functionality must be provided when the user presses the button “BACK TO
WELCOME”.
o The worksheet “Welcome” must be displayed.
o The worksheet “Report” must be hidden.
o 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:
o Explicitly declaring all variables.
o Properly and consistently indenting your code so that it is easier to read.
o Adding extensive comments to your code.
CALCULATIONS REQUIRED
Inspection time for a single lot is calculated using the following method:
•
1.
2.
3.
4.
5.
To compute the inspection time for a single lot, the sum of five parts are needed.
Load Time
Per wafer load time – a load time applied for each wafer in the lot
Inspection time – an inspection time applied for each wafer in the lot
Per wafer unload time – an unload time applied for each wafer in the lot
Unload time
To calculate re-inspection time, the inspection time calculate above is added to the inspection time that
is re-inspection time applied for each wafer in the lot.
To calculate the expected time, it is the weighted average of the two times above based on the chance
that re-inspection will occur.
Processing time for multiple lots run back to back (BTB) 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. The per wafer load time is only applicable to the first lot, and the per wafer unload time is only
application to the last lot.
PROGRAM PLAN
“SIMULATING THE ROLLING OF A DIE”
Simulating the
Roll of a Die
Welcome Page
Provide General
Instructions to
User
Allow User to
Move to Next
Page
Simulation Page
Allow the User
to Roll the Die
Show Results
Clear Worksheet
to Restart
Simulation
Close
Application and
Save Workbook
