<%BANNER%>
PRIVATE ITEM
Digitization of this item is currently in progress.
Ethnographic linear programming with visual basic, a training manual for AEB 5167
CITATION THUMBNAILS PAGE IMAGE ZOOMABLE
Full Citation
STANDARD VIEW MARC VIEW
Permanent Link: http://ufdc.ufl.edu/AA00008190/00001
 Material Information
Title: Ethnographic linear programming with visual basic, a training manual for AEB 5167 economic analysis in small farm livelihood systems
Course title : Economic analysis in small farm livelihood systems
Physical Description: 17 p. : ill., ; 28 cm.
Language: English
Creator: Cabrera, Victor E
Hildebrand, Peter E
University of Florida -- Food and Resource Economics Dept
Publisher: University of Florida, Food and Resource Economics Department
Place of Publication: Gainesville, FL
Publication Date: c1999
 Subjects
Subjects / Keywords: Farms, Small -- Economic aspects -- Study and teaching   ( lcsh )
Agricultural systems -- Computer simulation   ( lcsh )
Agricultural mathematics -- Linear programming -- Study and teaching   ( lcsh )
Genre: non-fiction   ( marcgt )
 Notes
Scope and Content: 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 course AEB 5167, Economic analysis in small farm livelihood systems, in the Food and Resource Economics Department at the University of Florida.
Statement of Responsibility: Victor E. Cabrera, Peter E. Hildebrand.
General Note: Cover title.
General Note: "Fall Semester, 1999 ; Visual basic & Excel 97."
 Record Information
Source Institution: University of Florida
Rights Management: All rights reserved by the source institution and holding location.
Resource Identifier: oclc - 755797617
ocn755797617
Classification: lcc - S566.7 .C3 1999
System ID: AA00008190:00001

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 32611-0240
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 sub-routines 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
tape-recording 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


I|4 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*K-ybuLd 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 _A1-Jjg 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 ,p-I8 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 top-left corer of C5 and drag it to the right-bottom 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. Filters-Inputting 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, in-place
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.


-' L-I 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 non-feasible 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 ("I-TRIX") .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:
cellRef-Reference to a cell or a range of cells that forms the left side of a constraint.
relation-1 (<=), 2 (=), 3 (>=), or 4. (Cells referenced by cellRefmust contain integers.)
formulaText-Right side of the constraint.

SolverChange:
Changes an existing constraint. Takes three arguments:
cellRef-Reference to a cell or a range of cells that forms the left side of a constraint.
relation-1 (<=), 2 (=), 3 (>=), or 4. (Cells referenced by cellRef must contain integers.)
formulaText-Right side of the constraint.

SolverDelete:
Deletes an existing constraint. Takes three arguments:
relation-1 (<=), 2 (=), 3 (>=), or 4. (Cells referenced by cellRefmust contain integers.)
formulaText-Right side of the constraint.

SolverFinish:
After solving a problem, tells Excel to keep the results and to create a report. Takes two
arguments:
keepFinal-1 keep final results; or 2 discard results and return to original values.
reportArray-1 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


* keepFinal-1 keep final results; or 2 discard results and return to original values.
* reportArray-1 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:
* typeNum-Takes 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.
- sheetName-Name 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:
* loadArea-Range on the active sheet that contains the Solver model.
SolverOk:
Defines a Solver model. Takes four arguments:
* setCell-Target cell for the Solver model.
* maxMinVal-1 maximize target; 2 minimize target, 3 match target to a specific value.
* valueOf-If the maxMinVal argument is set to 3, you must specify the value to which the target
cell is to be
matched.
* byChange-Cell 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:
* setCell-Target cell for the Solver model.
* maxMinVal-1 maximize target; 2 minimize target; 3 match target to a specific value.
* valueOf-Ifthe maxMinVal argument is set to 3, you must specify the value to which the target
cell is to be
matched.
- byChange-Cell 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:
* maxTime-Maximum time Excel will spend solving the problem.
* iterations-Maximum iterations Excel will use in solving the problem.
* precision-Number between O and 1 that specifies the degree of precision to be used in solving
the problem.
* assumeLinear-If True, Solver assumes that the underlying model is linear.
* stepThru-If True, Solver pauses at each trial solution.
* estimates-1 for tangent estimates; 2 for quadratic estimates.


Victor E. Cabrera & Peter E. Hildebrand










LINEAR PROGRAMMING WITH VISUAL BASIC


derivatives-1 for forward, 2 ffir central.
search-1 for Quasi-Newton search; 2 for Conjugate Gradient search.
intTolerance-Number between 0 and 1 that specifies tolerance.
scaling-If 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:
saveArea-The range of cells to which the Solver model is to be saved.

SolverSolve:
Starts a Solver solution run. Takes two arguments:
userFinish-If True, Solver returns results without displaying anything. If False, Solver returns
results and displays the Solver Results dialog box.
showRef-This 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 argument-this 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 "if-then" statements that
control constraints.

5. Your imagination is the limit.


Victor E. Cabrera & Peter E. Hildebrand