UFDC Home  myUFDC Home  Help  RSS 
CITATION
THUMBNAILS
PAGE IMAGE
ZOOMABLE


Full Citation  
STANDARD VIEW
MARC VIEW


Full Text  
11 I 03o ETHNOGRAPHIC LINEAR PROGRAMMING WITH VISUAL BASIC A training manual for AEB 5167: Economic Analysis in Small Farm Livelihood Systems Fall Semester, 1999 VISUAL BASIC & EXCEL 97 Victor E. Cabrera and Peter E. Hildebrand Food and Resource Economics Department University of Florida Gainesville, FL 326110240 1999 Peter E. Hildebrand LINEAR PROGRAMMING WITH VISUAL BASIC PREFACE This set of learning exercises constitutes a manual designed for graduate students and others with linear programming literacy and familiarity with Microsoft Excel 97. It was developed for the course AEB 5167, Economic Analysis in Small Farm Livelihood Systems, in the Food and Resource Economics Department at the University of Florida. It is intended to make the ethnographic linear programming analysis more efficient in representing and respecting the diversity of the systems. The objective is to achieve individual household analyses with accuracy and speed. Recent computer development has made it possible to use linear programming on farm. Visual Basic could become an essential tool for updating and testing different situations working directly with the farmers. Visual Basic makes the simulation process user friendly. This manual is divided in eight sections and 10 excersises. The first two sections deal with the most simple operations of "macros" and with inserting Visual Basic Objects in a spreadsheet. The third and the seventh sections present common linear programming tasks as input and output tables, respectively, linked to Visual Basic codes. The fourth, fifth, and sixth sections deal with all the needed tools to control the "solver" function directly from codes in Visual Basic. And, finally the eighth and last section presents and describes advanced "solver" codes. The exercises are followed step by step and generally with figures to help. The exercise solution files and this manual in electronic version are available on the Internet in the path: http://nersp.nerdc.ufl.edu/VBE/ (Use Internet Explorer 4.01 or above). Victor E. Cabrera & Peter E. Hildebrand LINEAR PROGRAMMING WITH VISUAL BASIC TABLE OF CONTENTS 1. Macros Exercise 1 Copy and Paste Macro Exercise 2 Locate and run the macro in other ways 2. Visual Basic Objects and Buttons Exercise 3 Locate and familiarize yourself with VB objects Exercise 4 Set up a Combo Box 3. Filters (Inputting data) Exercise 5 Filter and Special Paste Exercise 6 Select Households from the Matrix Sheet 4. Visual Basic Solver Function 5. Visual Basic Reference to Solver Exercise 7 Including the Solver Reference 6. Direct Solver Call Exercise 8 Solver Button Exercise 9 Analyze your Solver file 7. Listing Results (Output Tables) Exercise 10 Output Tables 8. Solver with Commands Pg. ............... 4 ............... 6 ............... 8 ............... 11 ............... 11 ................ 12 ............... 13 8 11 11 12 13 ............... 15 Victor E. Cabrera & Peter E. Hildebrand LINEAR PROGRAMMING WITH VISUAL BASIC 1. Macros Macros are routines and subroutines of tasks in excel. Usually we do many of these routines and sub routines manually and frequently (e.g. copy and paste, cell and sheet selection, calculations, solver, etc.). Many times these routines are repetitive, time consuming, and with a chance of errors. The use of macros for handling large amounts of data, saving precious work time and reducing the chance of errors is very convenient. Macros can do any task that it is possible to do using the mouse and the keyboard in an Excel document. The principle of macros can be better understood if we think of it as analogous to a taperecording process; we can record any routine and recall it at any time by simply using a shortcut (usually Ctrl + "letter"). Exercise 1 Copy and Paste Macro 2. In an Excel spreadsheet. Go to Tools/Macro/Record New Macro... A Record Macro window will appear. SId Ma't IN 1 Macro name: ... I la f11 Shortcut ky: Store macro in: . . a& TThs Workbook Cancel oesciption: Macro recorded 10125199 by Victor E. Cabrera 3. In the Record Macro window. Leave as the Macro name; Macro assign "z" as the Shortcut key, Store the macro in: This Workbook, and click OK. A Stop Rec window will appear. 4. Now the recording process starts. Select cell C5, go to Edit/Copy. Select cell C10, go to Edit/Paste. Click in the blue square of the Stop Rec window. The macro is ready to use!. 5. Watch the macro work. Select cell C5 and write a number (e.g. 100), press Enter and press Ctrl + z. Victor E. Cabrera & Peter E. Hildebrand p I LINEAR PROGRAMMING WITH VISUAL BASIC The number in the cell C5 will be duplicated and assigned to cell C10. Play with this. You will realize that this simple principle could be applied to many useful routines Exercise 2 Locate and run the macro in other ways 1. You must be able to find where your macros are located. Let's continue with the previous excersise. Go to Tools/Macro/Macros... A Macro window will appear. Macro Name: ls  Bun I Cancel SepInto dt I I C~wel SWDf J oete J ] QBelete  Macros in: Ths Workbook ions.. Deescraipon .     Macro recorded 1025/99 by Victor E. Cabrerae 2. Make sure This Workbook is selected and Macro 1 is highlited. Then press the Run button. This is another way to call the macro. 3. Let's do it directly from the Visual Basic Editor. Go to Tools/Macro/Visual Basic Editor.VBE Microsoft Visual Basic will appear. You will see three frames in the Visual Basic Editor: in the upper left, the work directory; in the lower left the properties; and on the right the CODES. 4. You may need to locate your "Macrol", inside the Modulel in the Modules Folder. Once you have it, you will see, in the codes window, the codes of this simple macro. Any routine has a SubName( ) that is the starting point and an End Sub that is the end. Between these commands you will see comments (green and starting with an apostrophe) and the real codes. In this example the name of the routine is Macrol, the comments refer to the date and author of the macro and the keyboard short cut, and the codes are a set of selection, copy, and paste. Victor E. Cabrera & Peter E. Hildebrand lepb~ I~i~ir~sa~a~ns~e~~l~ LINEAR PROGRAMMING WITH VISUAL BASIC I4 B I IWnrta Famai W Jeu m ICdooe A ir i 1 Est'ldI .D I i m 0W2  ut :c. .. Sub hacrol () S VAProect (IBook2) I osMExcelO carol Macro I Sheetl (Shetl) j rn r nrdrl in/l2/fqq hy Vi ornr F. C, rarF. (MSheet2(Sheet2) shelat3(S 3) I*KybuLd 3SLutncu: C.L 1+ I ThisWorkmok Mo:' M es Range("CS") .Select S Moel Selection.Copy Range("C10O) .Select Modul____________ ______ActiveSheet.Poate IModulel Modu e End Sub hab I Categried Mi s Modulel s4aJ ::3 K k! 0 S _A1Jjg 1I R 'vSi., M ^ ! i ioA 5. Go for a moment to the spreadsheet: click on the Excel icon ..................... and delete whatever is in cell C10. Return to VBE: .............................. click on, now look for run icon in the middle top of the window:............. click on it and check to see if cell C5 is copied in C10 in the spreadsheet. Now you are able to create and run Macros in Excel. Save the workbook with the name of FirstMacro but keep in mind that at any time you ask to open this file, Excel may warn you of the existence of macros (which are similar to viruses) and you will need to enable them. 2. Visual Basic Objects and Buttons Microsoft Excel has a feature to insert objects and buttons in any spreadsheet. These are useful in the ethnographic linear programming work. Exercise 3 Locate and familiarize yourself with VB objects 1. Open the FirstMacro file, go to View/Toolbars and check Control Toolbox A Control Toolbox will appear lB E/ a l f IPEgJ 2 A J Victor E. Cabrera & Peter E. Hildebrand LINEAR PROGRAMMING WITH VISUAL BASIC 2. Let's concentrate for now on the six middle buttons of the ,pI8 g i window: Check Box, Text Box, Command Button, Option Button, List Box, and Combo Box. (You can find these names by passing and stopping the cursor on them). 3. Click on the Command Button. Go with the mouse to cell C5 in the spreadsheet (you will note that instead of an arrow for the mouse cursor, now you have a cross). Locate the cursor on the topleft corer of C5 and drag it to the rightbottom corer of D3. A button will appear a CommandButton1 U ...U  4. Double click in the middle of the new CommandButtonl. VBE will open and the cursor will be right in the middle of a program sentence; write g g "Macrol" and close VBE. Again in the spreadsheet you will realize that the Design Mode (Ruler Pencil icon) will be clicked or selected, then deselect this by cliking on it. 5. Go to cell C5. Write a number (e.g. 1000), press enter. Now click on the CommandButton1. You will see that the number you put in C5 will be duplicated in cell C10. You are calling the Macro 1 directly from the CommandButtonl !. 6. Play and insert other VB objects in the spreadsheet. Save your file as FirstButton. Exercise 4 Set up a Combo Box 1. Open the FirstButton file and repeat step 1 of Exercise 3. In the Control Toolbox click on the Combo Box option. Insert a Combo Box surrounding cell 12 and a second Command Button surrounding cells G2 and H3. 0 0 SoComnanclButton2 Q,  2. Write succesive numbers 1 to 10 in cells Jl to J10. Make sure that the design mode is selected in the Control Toolbox and then double click on CommandButton2. 3. In the VBE write this code: ComboBoxl.List = Sheetl.Range("J1:J10").Value between Sub and End Sub of the CommandButton2, then you will get this: Victor E. Cabrera & Peter E. Hildebrand LINEAR PROGRAMMING WITH VISUAL BASIC Private Sub CommandButton2_Click() ComboBoxl.List = Sheetl.Range("J1:J10").Value End Sub 4. Return to the spreadsheet, make sure the design mode is deselected. Click on the CommandButton2. You will see that the numbers of cells J1 to J10 are now selection options in the ComboBox...imagine that each number represents a specific household and the links to its input data in your matrix and you could select any of them easily. Save your file as FirstList. 3. FiltersInputting Data A critical function in individual household LP analysis is be able to insert each household's information in the matrix efficiently and rapidly without a chance of error. The Filter function of Excel, macros or routines, and Visual Basic objects will help in this task. Exercise 5 Filter and Special Paste 1. Open the file filter.xls.Open Internet Explorer (Netscape doesn't work), enter the URL: http://nersp.nerdc.ufl.edu/vecy/VBE/filter.xls Save the file in the hard drive. 2. Take a look at the file. You should be familiar with this, it is the solution to Exercise 5 of the Linear Programming manual. Note that there are two sheets: MATRIX and INPUT. Some RHS numbers in the MATRIX sheet (labor and maize consumption) are linked to the input sheet; they are highlighted with yellow. Whenever we change family composition in the INPUT table, the labor available and the consumption requirement change in the MATRIX. Let's see how Visual Basic can do that for us. 3. Understanding the FILTER function. Go to the INPUT sheet, select cell H9 and write in "Household" then select cell H10 and write the number 5. Click Enter to go outside Edit mode and go to Data/Filter/Advanced Filter... The Advanced Filter window will appear _. Cton  r Cgpy to another location List range: l Criteria range: N r Unique rLecords only w. Victor E. Cabrera & Peter E. Hildebrand LINEAR PROGRAMMING WITH VISUAL BASIC In the Advanced Filter window, select Copy to another location. In the List range, select the household table: INPUT!$A$9:$F$17 and in the Criteria range, select our criterion just written: INPUT!$H$9:$H$10. Finally, for the Copy to, select INPUT!$A$19:$F$19. . .e dI FIlter 1 l Action  C Filter the list, inplace 1 Copy to another location': List range: 1$A$9:$F$17 criteria range: $H$9:$H$10 Copy to: j$A$19:$F$19 I OK F Unique records only Click OK. You will realize that this Filter function is very useful. It looks inside the list range, finds a match according to the criteria range, takes the whole row (family composition) and copies them to the select cells. Change the number of household in cell H10 and do the filter again...play with this. 4. Now you are ready to make a macro called "filt"with a shortcut Ctrl+f. Save the macro in this workbook and make sure it works properly. 5. The next step will be to put the household composition (selected in cells B20 to F20) into the input table above (Cells B2 to B6). Manually, the easiest way is to just copy B20 to F20, and Transpose them in a Paste Special... ulI males I 1 uit females 1 descent males 1 olescent females 0 Paste C imlas " Values r vFmas .Formats C Cpnwnerts " Vadatoa C Al except borders Household AdMale. operation  1 1 c"Noe C dltply 2 1 ~AAd CDlyvde 3 1 Cubtrct 4 1 5 1 rSPhl 7 r'no I 6 1 7 1 l 8 I t K Can2el ster 8 1 Victor E. Cabrera & Peter E. Hildebrand LINEAR PROGRAMMING WITH VISUAL BASIC 6. Create a new macro called "hhcopy" with shortcut Ctrl+h that makes this Paste Special for you. Make sure it works correctly. This is neat. Change the H10 number, do Ctrl+f...the household selected changes, now do Ctrl+h...the labor available and consumption change not only in the above tables but also inside the matrix. Save this file as filter2.xls. Exercise 6 Select Households from the Matrix Sheet 1. Start by opening the filter2.xls file. (It is also available at: http://nersp.nerdc.ufl.edu/vecy/VBE/filter2.xls) 2. Select the MATRIX sheet. Insert a CommandButton between F26 and G27 and a ComboBox between H27 and 127. Write "Household" in H26 and center it between H26 and 126. 3Houghold 3 :: ! 3. Double click on the CommandButton and write: ComboBoxl.List = Sheets("INPUT").Range("al0:al7").Value This code is assigning the values of the household numbers (1 to8) to the Combobox. Exit Design Mode and click on the CommandButton...check that the household numbers in the ComboBox are assigned. 4. Insert another CommandButton in the MATRIX sheet, between cells K26 and K27. ' LI 7HHousehold _ Double click on this second button and write: Sheets("INPUT").Range("H10") = ComboBox1.Value Sheets("INPUT"). Select filt hhcopy Sheets("MATRIX"). Select These codes are a little more complex. In the first line we are assigning the current ComboBox value to cell H10 in the INPUT sheet. In the second line we are indicating that we will work for a while in the INPUT sheet. The third and fourth lines are calling the macros "filt" and "hhcopy" respectively. The last line is only to return to our worksheet, MATRIX. Victor E. Cabrera & Peter E. Hildebrand LINEAR PROGRAMMING WITH VISUAL BASIC Victor E. Cabrera & Peter E. Hildebrand 5. Deselect the Design Mode in the Control Toolbox, pick up some number in the ComboBox and click on the second CommandButton. Note that the numbers in the RHS of the matrix change automatically with each change in the household number, so you may solve the matrix for different households. Play changing the numbers and enjoy! Save your file as filter3.xls. 4. Visual Basic Solver Function It is possible to call the solver function directly from the Visual Basic through codes or macros. If we call the function from macros we need to previously setup the solver parameters manually (Max/Min/etc, Changing cells, Constraints, etc.). There are two important things to take into account before starting with the next exercises: 1. The solver can not be called if some cell in the sheet is being edited or if no specific cell is selected in a sheet. Taking this into account can avoid many frustrations. 2. The solver can only work from codes or macros in Visual Basic if a reference to them exists. Exercise 7 deals with this topic. 5. Visual Basic Reference to Solver Exercise 7 Including the Solver Reference 1. Open your previous file "filter3.xls" (also available at: http://nersp.nerdc.ufl.edu/vecv/VBE/filter3.xls) 2. Open the Visual Basic Editor (VBE). In the top left window, highlight the VBAProject (filter3.xls), go to Tools/References... References The VBAProject window will appear. In this window check solver.xla and click OK. The Project window must look like this:  t If Microsoft Excel Objects SSheet 1 (MATRIX) SSheet2 (INPUT) SThisWorkbook +: DC Modules : References & Reference to Solver.xla , LINEAR PROGRAMMING WITH VISUAL BASIC Note that there is a new folder (References) inside which is the reference to the solver. You can put the reference in another path and it will still work, but it is always a good idea to insert it in the file you are working on to make sure it will work in other computers. 3. Close the VBE and save your file as filter4.xls. 6. Direct Solver Call Exercise 8 Solver Button 1. Open your file filter4.xls (also available at: http://nersp.nerdc.ufl.edu/vecy/VBE/filter4.xls) 2. Record a new macro called "sol" with the "Ctrl+s" shortcut to call the solver. 3. Record another new macro called "del" with the "Ctrl+d" shortcut that deletes the varaible cells. 4. Insert a new button (between cells M26 and 027) in the MATRIX sheet to refer first to the delete macro and then to the solver macro. 5. Try it. You must first choose one household in the ComboBox, then click in the CommadButton2 to refer to those values in the matrix and finally solve the matrix by pushing the third_button. 6. Play with this and make sure all the solutions are correct. Which households obtain non feasible solutions? 7. Save your file as solvl.xls Exercise 9 Analyze your Solver file 1. Open your file solvl.xls (also available at: http://nersp.nerdc.ufl.edu/vecv/VBE/solv1.xls) 2. Go to VBE. Find your codes that call the solver (hint: they should be in module2). You will see a series of codes. Some of them set up the parameters and the last code "SolverSolve" takes the action of the solver. Victor E. Cabrera & Peter E. Hildebrand LINEAR PROGRAMMING WITH VISUAL BASIC Sub sol() sol Macro Macro recorded 11/9/99 by Victor E. Cabrera Keyboard Shortcut: Ctrl+s SolverOk SetCell:="$PS24", HaxMinVal:=l, ValueOf:="O", ByChange:="SC$7:$SI7" SolverSolve End Sub 3. The first parameters are exactly the same as they were in the solver window. The constraints are not inside these parameters. Rather it is using the current constraints in the solver window. We can delete or change them to comment mode (no active) and control the parameters directly from the spreadsheet. Put an apostrophe before the first codes line: Now, we are using the most important code only to call the solver function. Try it in the 'SolverOk SetCell:="$P$24", HaxHinVal:=1, Value0f:="0", ByChange:="$C$7:$M$7" SolverSolve spreadsheet. Once it is working, you can go to the Tools/Solver and for example delete the ">=" constraints. Now solve directly from the CommandButton. How do the solutions in the different households change? Are there more or fewer farmer nonfeasible solutions?. 4. Finally, we can delete the CommandButton2 (the filter button) and include its functions right before the delete in the CommandButton3. Select design mode. Double click in the CommandButton2, then copy all the commands in the VBE (between Private Sub() and End Sub). Return to the spreadsheet, double click in the CommandButton3, paste the codes before the del and sol commands. Return to the spreadsheet, click once on the CommadButton2, then delete it. Deselect design mode. 5. Try your new arrangement. Choose a household and just click in the solver button. You will realize that this single button now is doing all the work. First it filters the household, then puts the values in the matrix, then deletes the variable cells and finally solves. Is this not neat? Save your file as solv2.xls. 7. Listing Results Exercise 10 Output Tables 1. Open your file solv2.xls (also available at: http://nersp.nerdc.ufl.edu/vecy/VBE/s61v2.xls) Victor E. Cabrera & Peter E. Hildebrand LINEAR PROGRAMMING WITH VISUAL BASIC 2. Go to Insert/Worksheet. Call it OUTPUT. In cell B5, write "household" and in the next columns copy the same titles as in the variables of the matrix. After that copy all the names of the resources and constraints (hint: use Paste Special/Transpose). Then you will get column headings B5 to AC5. 3. In cells C4 to AC4 (yes, above not below) refer or link to the values of the matrix. Refer cell B4 to the INPUT sheet (H10). 4. Create a new macro that "insert" "copied cells" from the row 4 to row 6 using row 3 as a bridge. Call this macro "output" and use "Ctrl+o" as a shortcut. Right click above number "4" (of row 4) and select "Copy" Right click above number "3" (of row 3) and select "Paste Special..." and select "Values" Nee Paste r. am s .,,: : ...."Vala on ..*:. ,< .. ues r A ecept border SFor ai  ... :r ld alt : ': . r _:;  t c a n ( r 3 and Pse t Right click above number "3" (of row 3) and select "Copy" Right click above number "6" (of row 6) and select "Insert Copied Cells" 5. Go to the MATRIX sheet. Select design mode and double click on the Solver button. In the VBE, only write at the end of the commands "Sheets("OUTPUT"). Select output Sheets("MATRIX"). Select" Private Sub ConmmandButton3 Click() Sheets("INPUT") .Range ("H10") ComoBoxl.Value Sheets ("INPUT") .Select filt hhcopy Sheets ("ITRIX") .Select del sol Sheets ("OUTPUT") .Select output Sheets("HATRIX") .Select End Sub Victor E. Cabrera & Peter E. Hildebrand LINEAR PROGRAMMING WITH VISUAL BASIC 6. Try it to see if it works properly. Solve households 1 to 8 in order and see the differences at the end in the OUTPUT sheet. Save your file as solv3.xls 8. Solver with Commands The solver in Microsoft Office 97 can be called through commands in the VBE. For example, we could just write "SolverSolve" and refer it to some command button in the spreadsheet and it would work exactly the same as recording a macro. But the main advantage of call the solver by commands is that we can control all the parameters directly from buttons in the spreadsheet. For that purpose there is a list of all the commands that control the solver. These commands only work with Microsoft Excel 97 and the regular solver engine. For other versions, there are slight differences and you need to find the exact commands in the help files or in the manuals. Sometimes tutorial software handle these topics too. SolverAdd: Adds constraints to a Solver model. Takes three arguments: cellRefReference to a cell or a range of cells that forms the left side of a constraint. relation1 (<=), 2 (=), 3 (>=), or 4. (Cells referenced by cellRefmust contain integers.) formulaTextRight side of the constraint. SolverChange: Changes an existing constraint. Takes three arguments: cellRefReference to a cell or a range of cells that forms the left side of a constraint. relation1 (<=), 2 (=), 3 (>=), or 4. (Cells referenced by cellRef must contain integers.) formulaTextRight side of the constraint. SolverDelete: Deletes an existing constraint. Takes three arguments: relation1 (<=), 2 (=), 3 (>=), or 4. (Cells referenced by cellRefmust contain integers.) formulaTextRight side of the constraint. SolverFinish: After solving a problem, tells Excel to keep the results and to create a report. Takes two arguments: keepFinal1 keep final results; or 2 discard results and return to original values. reportArray1 create an Answer report; 2 create a Sensitivity report; or 3 create a Limit report. SolverFinishDialog: Equivalent to SolverFinish; however, also displays the Solver Results dialog box after solving a problem. Takes two arguments: Victor E. Cabrera & Peter E. Hildebrand LINEAR PROGRAMMING WITH VISUAL BASIC * keepFinal1 keep final results; or 2 discard results and return to original values. * reportArray1 create an Answer report; 2 create a Sensitivity report; or 3 create a Limit report. SolverGet: Returns information about the Solver model. Takes two arguments: * typeNumTakes values from 1 through 18 to return information about the Solver model. For details, use the Object Browser to view the SolverGet help topic in the XLM Function Reference section in Excel 5's online Help.  sheetNameName of the sheet containing the Solver model. SolverLoad: Loads parameters for an existing Solver model that have been saved to the worksheet. Takes one argument: * loadAreaRange on the active sheet that contains the Solver model. SolverOk: Defines a Solver model. Takes four arguments: * setCellTarget cell for the Solver model. * maxMinVal1 maximize target; 2 minimize target, 3 match target to a specific value. * valueOfIf the maxMinVal argument is set to 3, you must specify the value to which the target cell is to be matched. * byChangeCell or range of cells that will be changed in setting the target cell. SolverOkDialog: Same as SolverOk but also displays the Solver dialog box. Takes four arguments: * setCellTarget cell for the Solver model. * maxMinVal1 maximize target; 2 minimize target; 3 match target to a specific value. * valueOfIfthe maxMinVal argument is set to 3, you must specify the value to which the target cell is to be matched.  byChangeCell or range of cells that will be changed in setting the target cell. SolverOptions: Allows you to specify advanced options for your Solver model. Takes 10 arguments: * maxTimeMaximum time Excel will spend solving the problem. * iterationsMaximum iterations Excel will use in solving the problem. * precisionNumber between O and 1 that specifies the degree of precision to be used in solving the problem. * assumeLinearIf True, Solver assumes that the underlying model is linear. * stepThruIf True, Solver pauses at each trial solution. * estimates1 for tangent estimates; 2 for quadratic estimates. Victor E. Cabrera & Peter E. Hildebrand LINEAR PROGRAMMING WITH VISUAL BASIC derivatives1 for forward, 2 ffir central. search1 for QuasiNewton search; 2 for Conjugate Gradient search. intToleranceNumber between 0 and 1 that specifies tolerance. scalingIf Scaling is True and two or more constraints differ by several orders of magnitude, Solver scales the constraints to similar orders of magnitude during computation. SolverReset: Resets Solver options (cell selections and constraints in the Solver Parameters dialog box and all settings in the Solver Options dialog box) to their default values. SolverSave: Saves a Solver model definition to a range of cells on the worksheet. Takes one argument: saveAreaThe range of cells to which the Solver model is to be saved. SolverSolve: Starts a Solver solution run. Takes two arguments: userFinishIf True, Solver returns results without displaying anything. If False, Solver returns results and displays the Solver Results dialog box. showRefThis argument is used only if True is passed for the stepThru argument of the SolverOptions function. If so, you can pass the name of a subroutine (as a string) as the showRef argumentthis routine will be called whenever Solver returns an intermediate solution. You can try these exercises using your file solv3.xls (available also at: http://nersp. nerdc.ufl.edu/vecv/VBE/solv3.xls) 1. Make the last window of "solver results" dissapear at the end of the solver. After clicking on the solver button only results must appear. 2 Control the cash constraints with buttons. With a button take out these constraints, with another button include them before solving. 3. If you have some literacy in Visual Basic, you could try make a "loop" in which you could solve the eight households with only one button. 4. If you have some literacy in Visual Basic, you could try to insert "ifthen" statements that control constraints. 5. Your imagination is the limit. Victor E. Cabrera & Peter E. Hildebrand 