Article ID: 843304  View products that this article applies to. On This PageSUMMARYThis article describes how to use Microsoft Excel Solver in Microsoft Excel 97 to create Microsoft Visual Basic macros. Microsoft Excel Solver is a Microsoft Excel addin. Additionally, this article contains information about how to create macros, how to design a macro, and how to work with constraints of a macro. This article also discusses the algorithm and methods that are used by Microsoft Excel Solver. The following list gives all the topics discussed in the article.
INTRODUCTIONThis article contains information about Microsoft Excel
Solver. MORE INFORMATIONDescription of the Microsoft Excel SolverMicrosoft Excel Solver is a Microsoft Excel addin. Microsoft Excel Solver helps you to determine the optimum value for a formula in a particular target cell on a Microsoft Excel worksheet. Microsoft Excel Solver adjusts the values of other cells that are related to the target cell by using an equation. After you construct an equation and define a set of parameters or constraints for the variables in the equation, Microsoft Excel Solver tries various solutions to arrive at an answer that satisfies all constraints. Microsoft Excel Solver uses the following elements to "solve" an equation:
http://download.microsoft.com/download/excel97win/solverex/1.0/WIN98Me/ENUS/SolverEx.exe Note You can also use the macros and the examples that are described in this
article in Microsoft Excel versions 5.0 and 7.0.
(http://download.microsoft.com/download/excel97win/solverex/1.0/win98me/enus/solverex.exe)
How to use the Microsoft Excel Solver functions in a VBA macroTo use the Microsoft Excel Solver addin functions in a VBA macro, you must reference the addin from the VBA project of the workbook that contains the macros. If you do not reference the Microsoft Excel Solver addin, you will receive the following compile error when you try to run the macro:Compile Error: Sub or Function not defined.
How to design a VBA macro that creates and solves a simple Microsoft Excel Solver modelAlthough Microsoft Excel Solver offers many functions, the following three functions are fundamental to creating and to solving a model:
The SolverOK functionThe SolverOK function defines a basic Microsoft Excel Solver model. The SolverOK function is generally the first function that you will use to build your Microsoft Excel Solver model. The SolverOK function is equivalent to clicking Solver on the Tools menu, and then specifying the options that are in the Solver Parameters dialog box. The following is the syntax for the SolverOK function:SolverOK(SetCell, MaxMinVal, ValueOf, ByChange) The following information describes the syntax for the SolverOK function:
Figure 1. Parameters that are associated with the SolverOK arguments Collapse this image The SolverSolve functionThe SolverSolve function solves the model using the parameters that you specified with the SolverOK function. Executing the SolverSolve function is equivalent to clicking Solve in the Solver Parameters dialog box. The following is the syntax for the SolverSolve function:SolverSolve(UserFinish, ShowRef)
The following information describes the syntax for the SolverSolve function:
The SolverFinish functionThe SolverFinish function indicates what to do with the results, and what kind of report to create after the solution process is completed. The following is the syntax for the SolverFinish function:SolverFinish (KeepFinal, ReportArray)
The following information describes the syntax for the SolverFinish function:
Collapse this image This article describes how to create a simple Microsoft Excel Solver model interactively. The first step is to create your worksheet for the model. The worksheet will contain some data cells and at least one cell that contains a formula. This formula depends on the other cells in the worksheet. After you set up your worksheet, click Solver on the Tools menu. In the Solver Parameters dialog box, specify the target cell, the value that you are solving for, the range of cells that will be changed, and the constraints. Click Solve to start the solution process. After Microsoft Excel Solver has found a solution, the results appear in your worksheet, and the Microsoft Excel Solver displays a message box that prompts you if you want to keep the final results or if you want to discard them. When you click one of these options, Microsoft Excel Solver finishes. Figure 3 illustrates a simple model that you can create by using these steps. Figure 3. A simple model: The Square Root model Collapse this image In this example, change cell A1, which contains the formula, =A1^2, to a value that will make cell A2 equal to a value of 50. In other words, find the square root of 50. There are no constraints in the Square Root model. The Find_Square_Root macro accomplishes the following tasks:
The following code describes the Find_Square_Root2 macro:
How to generate reports for solutionsMicrosoft Excel Solver offers several types of reports that describe how the results changed and how close the constraints came to their critical values. Each report is put on a separate worksheet in your workbook. These following are the types of reports that the Microsoft Excel Solver offers:
How to use the Microsoft ExcelSolver functions in a looping macroIn many situations, it is a good idea to have Microsoft Excel Solver solve the target cell for multiple values. You can generally accomplish this by using one of the looping structures that are available with VBA.The Create_Square_Root_Table macro demonstrates how Microsoft Excel Solver functions in a looping macro. The Create_Square_Root_Table macro creates a table in a new worksheet. It inserts the numbers one through ten and the corresponding square root of each number. The Create_Square_Root_Table macro creates the table using a For loop to iterate through the numbers 1 through 10 and to solve the target cell in the Square Root model for a value that matches the number of the iteration. The following code describes the Create_Square_Root_Table macro:
Figure 4. Output that is generated by the Create_Square_Root_Table macro Collapse this image How to work with constraintsA constraint is a restriction on the contents of one or more cells. A model can have one or multiple constraints. The constraint set is a set of inequalities or a set of equalities that remove certain combinations of values for the decision variables from the solution. For example, a constraint may require that one cell be greater than zero and that another cell contain only an integer value.The Square Root model that we have discussed up to this point is a simple model that does not contain any constraints. Figure 5 illustrates a model that uses constraints. The purpose of this model is to find the optimal combination of products for maximum profit. Figure 5. Product mix with diminishing profit margin Collapse this image For example, if a company manufactures TVs, stereos, and speakers, and it uses a common parts inventory of power supplies, speaker cones, and so on. The parts are in limited supply. Your goal is to determine the most profitable mix of products to build. Your profit per unit decreases with volume because additional price incentives are required to load the distribution channel. The diminishing returns exponent is 0.9. This exponent is used to calculate the profit by product in the range G11:I11. Your objective is to find the maximum profit (cell G14). The values that you will change to find the maximum profit are the number of units that you build. The range G9:G11 represents the changing cells in this model. Your only constraint is that the number of parts you use cannot exceed the number of parts you have on hand. With Microsoft Excel Solver, this constraint appears as E3:E7<=B3:B7. If you were to build this Microsoft Excel Solver model interactively, the Microsoft Excel Solver parameters would look similar to those that are in Figure 6. Figure 6. Microsoft Excel Solver parameters for the product mix with Diminishing Profit Margin model Collapse this image To create and solve the Product Mix with Diminishing Profit Margin model, you will use a new function, the SolverAdd function, in addition to the Microsoft Excel Solver VBA functions that were described earlier. The SolverAdd function adds the constraint to the model. Executing the SolverAdd function is equivalent to clicking the Add button in the Solver Parameters dialog box. The SolverAdd function has the following syntax: SolverAdd (CellRef, Relation, FormulaText)
The following information describes the syntax for the SolverAdd function:
Note In Microsoft Excel, versions 5.0 and 7.0, use the R1C1 notation when you specify a cell or a range of cells with the FormulaText argument. In contrast, in Microsoft Excel 97, use the A1style notation to specify the FormulaText argument. Figure 7. Fields that are associated with the SolverAdd arguments Collapse this image The Maximum_Profit macro that generates a model for the Product Mix with Diminishing Returns model. This macro executes the following functions or arguments:
When you run the Maximum_Profit macro, Microsoft Excel Solver will find a solution of building 160 TV sets, 200 stereos, and 80 speakers for a maximum profit of $14,917 dollars. How to change and delete constraintsConstraints in your model can be programmatically changed or deleted. Constraints are identified by their CellRef and Relation arguments.To programmatically change an existing constraint, use the SolverChange function. The following is the syntax for the SolverChange function: SolverChange (CellRef, Relation, FormulaText)
Note that the arguments for the SolverChange function are
the same to those that you use with the SolverAdd function. If you want to change the constraint in the Product Mix with Diminishing Returns model, you would use the SolverChange function. For example, currently the constraint that is specified is that the number of parts used is less than or equal to the number of parts on hand (E3:E7 <= B3:B7). If you want to change this constraint so that the number of parts used is less than or equal to the number of parts projected (number of parts on hand plus number of parts ordered). This new constraint would look like E3:E7 <= D3:D7. The following macro would change the existing constraint E3:E7<=B3:B7 to E3:E7 <= D3:D7 and solve for a solution. The following code describes the Change_Constraint_and_Solve macro:
SolverDelete (CellRef, Relation, FormulaText) Note that the arguments for the SolverDelete function are the same to those you use with the SolverAdd and the SolverChange functions. The following macro illustrates how to delete and add a constraint. In this example, the Change_Constraint_and_Solve2 macro removes the constraint E3:E7<=B3:B7 from the Product Mix with Diminishing Returns model and adds a new constraint. The new constraint is just a modification of the original constraint, where the left and right sides of the constraint are reversed. The following code describes the Change_Constraint_and_Solve2 macro:
How to load and save your modelsWhen you save your workbook, the last parameters that you specified in the Solver Parameters dialog box are saved with the workbook. Therefore, when you open the workbook, the parameters are the same as when you last saved the workbook.You can define more than one problem for a worksheet. Each problem is made up of cells and constraints that you enter in the Solver Parameter and the Solver Options dialog boxes. Because only the last problem is saved with the worksheet, you will lose all other problems unless you explicitly save them. To save them, click Save Model in the Solver Options dialog box. Similarly, when you want to restore the previously saved parameters, click Load Model in the Solver Options dialog box. Solver models are stored in a range of cells on a worksheet. The first cell in the range contains the formula for the target cell. The second cell in the range contains the formula that identifies the changing cells in the model. The last cell in the range contains an array that represents the options set in the Solver Options dialog box. The cells between the second cell and the last cell contain the formulas that represent the constraints in the model. Figure 8 illustrates a model for employee scheduling. Assume that you work for a small manufacturer. This table shows each employee’s hourly rate of pay, the number of hours they are scheduled, and a projected number of units each employee can produce in one hour. Your goal is to meet a specific quota for the number of units produced while minimizing the cost of labor. Figure 8. Employee Scheduling mode l Collapse this image Two additional factors (or constraints) that you must consider are the minimum/maximum number of hours any one employee can work and the number of units that you intend to produce. If for a specified week, you need to produce 3975 units and you want each employee to work between 30 and 45 hours, the Microsoft Excel Solver parameters would look similar to those outlined in the following table: Collapse this table
Your goals are to solve for optimal labor cost on a weekly basis, to save each model weekly, and to be able to load any weekly model when you need it. In a macro, the Microsoft Excel Solver parameters for a model can be saved and loaded by using the SolverSave and the SolverLoad functions respectively. The SolverSave and the SolverLoad functions have the following syntax: SolverSave (SaveArea) SolverLoad (LoadArea) The SolverSave and the SolverLoad functions each have only one argument, SaveArea and the LoadArea arguments respectively. These arguments specify a range on a worksheet where the model information is stored. The following New_Employee_Schedule macro, demonstrates how to create, to solve, and to save a model based on user input. The user is asked to supply the date of the model, the number of units to produce, and the minimum and maximum number of hours per employee. These data is then used to create the model. The model is solved and then saved with the user input. The following code describes the New_Employee_Schedule macro:
Figure 9 illustrates how the saved model information appears on the worksheet. Figure 9. Model information that is saved by the New_Employee_Schedule macro Collapse this image The New_Employee_Schedule macro saves each new model to the worksheet. The Load_Employee_Schedule macro can load one of these saved models. The macro prompts the user for the model to load and then searches column I for the model date. If the model date is found, the Load_Employee_Schedule macro loads the corresponding model, solves it, and then keeps the final results. The following code describes the New_Employee_Schedulemacro:
How to find more information about Microsoft Excel SolverThe following resources provide information about how to use the Microsoft Excel Solver addin.
How to learn more about the algorithm and methods that are used by Microsoft Excel SolverMicrosoft Excel Solver uses the Generalized Reduced Gradient (GRG2) nonlinear optimization code that was developed by Leon Lasdon, University of Texas at Austin, and Allan Waren, Cleveland State University.For additional information about the algorithm used by Microsoft Excel Solver, click the following article number to view the article in the Microsoft Knowledge Base: 82890
(http://support.microsoft.com/kb/82890/
)
Solver uses generalized reduced
Linear and integer problems use the simplex method with bounds on the variables, and the branchandbound method, implemented by John Watson and Dan Fylstra, Frontline Systems, Inc. For more information about the internal solution process used by Solver, contact: Frontline Systems, Inc. P.O. Box 4288 Incline Village, NV 894504288 (702) 8310300 Web site: http://www.frontsys.com Electronic mail: info@frontsys.com Selections of the Microsoft Excel Solver program code are copyright 1990, 1991, 1992, and 1995 by Frontline Systems, Inc. Portions are copyright 1989 by Optimal Methods, Inc. Note The Microsoft Excel Solver addin that is discussed in this article is provided "as is" and we do not guarantee that it can be used in all situations. Although Microsoft Support Professionals can help with the installation and existing functionality of this addin, they will not modify the addin to provide new functionality. NO WARRANTY. The software is provided "asis," without warranty of any kind, and any use of this software product is at your own risk. PropertiesArticle ID: 843304  Last Review: October 11, 2006  Revision: 1.2
