Let us create a simple application that solves the transportation problem. In this application, the user will specify references to the cell ranges containing the transportation costs, the volumes of products imported to consumption points, and exported from production points, as well as define the cell range reserved for the calculated transportation volumes. All other necessary actions will be performed by the application: entering auxiliary formulas into the worksheet cells that return the total transportation cost and the volumes of imported and exported products at each point; checking whether the model is balanced; setting all required parameters for the solution search; and starting the execution. The result of the solution will be displayed as a corresponding note on the worksheet.
Let us proceed to the construction of the application. First, place the necessary data on the worksheet and reserve space for the target cell. Also, place a button on the worksheet that will launch the appropriate form for your application. Next, create a form, place four labels, four RefEdit controls, and one button on it, and set their property values in the Properties window as shown in Table.
Table. Values of properties set in the Properties window
| Object | Property | Value |
| Form | Name | frmSolver |
| Caption | Transportation Problem | |
| Label | Caption | Transportation Costs |
| RefEdit | Name | refCosts |
| Label | Caption | Export Volumes |
| RefEdit | Name | refOut |
| Label | Caption | Import Volumes |
| RefEdit | Name | refIn |
| Label | Caption | Transportation Volumes |
| RefEdit | Name | refVar |
| Button | Name | cmdSolve |
| Caption | Execute |
Before writing the code, make sure that the reference to Solver.xlam is enabled in the References window, which is displayed on the screen by selecting the menu command Tools | References. If Solver.xlam is missing from the list of Available References, click Browse and open Solver.xla from the directory
C:\Program Files\Microsoft Office\Office14\Library\SOLVER.
Enter the required code accordingly into the modules of the form and the worksheet module Sheet1 .
NOTE
Notice how the Evaluate method is used in the program to convert an expression into a value. This approach saved us from the need to add two loops in the code to determine the total volumes of imported and exported products or to insert unnecessary additional formulas into the worksheet cells.
NOTE
The use of the SolverReset() function in the program before setting the solution search parameters is essential. By setting the solver parameters to their default values, the SolverReset() function clears the Solver window of all previous settings. If the function SolverReset() is not used, the constraints added by the SolverAdd() functions will accumulate with each click of the Execute button. It is easy to imagine what would happen to the Constraints list in the Solver window after, for example, a dozen clicks of the Execute button!