YIELD ANALYSIS This documents presents a guide to develop an application for yield analysis. The application is developed in Excel by using Excel’s programming language, Visual Basic for Applications (VBA). This exercise is aimed at taking advantage of the familiarity that the students already have with Excel and of the capability of it as a modeling tool in order to achieve a better command of concepts learned in the course. The exercise purposes are only didactical and therefore it should not be expected to find an elegant or efficient program. LEARNING OBJECTIVES The learning objectives include: • •

To see how VBA can be used to develop a computer program in a simple manner. To develop an understanding of yield analysis for simple production systems.

To achieve the first of the objectives it is recommended to the student to follow the guide in the Appendix. For the second of the objectives, the student should study the course material (textbook and notes). OBJECTIVES OF THE APPLICATION The application is aimed at determined the required inputs for the production steps of a serial production system. BASIC DESIGN OF THE APPLICATION The application is to be capable of getting data from two sources: • •

An Excel Spreadsheet, containing the operation number on the first column, the percentage of values for the dependent variable on the second column, and the number of production steps - operations (Figure 1). The user. Who enters the desired system’s output through an input box (Figure 2).

Results are to be displayed on: • •

A message box showing the system’s required input (Figure 3). The Excel Spreadsheet, on which the operations’ required inputs are to be written (Figure 4).

The application should run at the click of a button. 1

TASKS TO PERFORM The application is to perform the following tasks: • • • •

Data reading. Determination of required inputs/outputs for each operation. Determination of required system input. Generation of outputs.

Table 1 shows the code to perform the above tasks.

2

TABLES AND FIGURES Table 1 Code Sub Yield() ' Variables Declaration Dim Steps As Integer Dim I() Dim O() Dim P() ' Data Reading ' From user (InputBox) Output = InputBox("Desired output: ") ' From Spreadsheet Steps = Cells(3, 6) 'Redimensioning ReDim I(Steps) ReDim O(Steps) ReDim P(Steps) For k = 1 To Steps P(k) = Cells(k + 3, 2) Next k ' Computations O(Steps) = Output For k = Steps To 1 Step -1 I(k) = O(k) / (1 - P(k)) O(k - 1) = I(k) Next k 'Rounding up Desired Input Inp = Int(I(1)) If Int(I(1)) I(1) Then Inp = Int(I(1)) + 1 ' Outputs Generation ' On Spreadsheet For k = 1 To Steps Cells(k + 3, 3) = I(k) Next k ' On Message Box MsgBox "Required input: " & Inp End Sub

3

Fig 1 Data on the Spreadsheet

Fig 2 Input box for the desired output

4

Fig 3 Message box for system’s required input

Fig 4 Operations’s required inputs displayed on the spreadsheet

5

APPENDIX: A GUIDE TO DEVELOP AN EXCEL-VBA APPLICATION (Adapted from: Albright, S.C., VBA for Modelers. Developing Decision Support Systems with Microsoft Excel. Duxbury, Pacific Grove, CA, 2001). Step 1: Open the file. Get into Excel and open the YieldAnalysis.xls file. It should contain the data as shown in Figure 1. Step 2: Get into the VBE. Press Alt-F11 to open the VBE (or equivalently, you can proceed from the Menu using Tools/Macro/Visual Basic Editor). Make sure the Project Explorer Window is visible. If it isn’t open it with the View/Project Explorer Menu item. Step 3: Add a module. In the Project Explorer window, make sure the YieldAnalysis.xls project is highlighted (click on it if necessary), and use the Insert/Module menu item to add a module (which automatically be named Module1) to this project. This module will hold your VBA code. Step 4: Start a Sub. Click anywhere in the Code window, type Sub Yield, and press Enter. You should immediately see the contents in Figure A-1. You have started a program called Regression. Including the keyword Sub informs VBA that you want to write a “subroutine,” so it adds empty parentheses next to the name Regression and adds the keywords End Sub at the bottom – two necessary elements of any subroutine. The rest of your code will be placed between the Sub and End Sub lines. (You can think of a subroutine as a section of code that performs a particular task of set of tasks). Step 5: Type the code. Type the code shown in Table 1 between the Sub and End Sub lines. It is important to indent properly for readability. To indent press the Tab key. Also, note that there is no “word wrap” in the VBE. To finish a line and go to the next line, you need to press the Enter key. Other than this, the Code window is essentially like a word processor. Be sure to check your spelling carefully and fix any errors before proceeding. (You’ll note that keywords such as Sub and End Sub are automatically colored blue by the VBE.) Step 6: Run the program from the VBE. There are several ways to run the program. For the first method make sure the cursor is anywhere within your subroutine and select the Run/Run Sub/UserForm menu item. (Alternatively, click on the “blue triangle” button on the Standard toolbar or press the F5 key.) If all goes well, you should see the input box in Figure 2, where you can enter the desired output. Next, you will see a message box such as the one in Figure 3, displaying the required system input. The required inputs for the operations will be displayed on the spreadsheet, as shown in Figure 4. This is exactly what we want the program to do! Step 7: Run the program with a button. This method allows the user to run the program directly from the Excel worksheet that contains the data. First, switch back to Excel (click on its button on the bottom taskbar of the screen). Then right-click on any

6

toolbar to show a list of Excel toolbars, and check the Forms toolbar’s box to make this toolbar visible. The fourth item on the Forms toolbar is for creating buttons to run macros (that is, subroutines). To create such a button, click on the toolbar’s button item and then drag a rectangular button somewhere on your worksheet, as shown in Figure 4. You will immediately be asked to assign a macro to this button – this is asked immediately because the only purpose of a button is to run a macro. Of course, you want to assign the Yield macro you just wrote to the button. Then you can type a more meaningful caption on the button itself. At this point, the button is “selected” (there is a dotted border around it). To deselect it, click anywhere else on the worksheet. Now your button is ready to go. To run your program, just click on the button. Step 8: Save the file. In case you haven’t done so already, save the file under the original (or a new) name. This will save your code and the button you created.

7

Fig A-1 Beginning lines of a subroutine

8