Solver in Macros | © 2014 Leong Thin Yin. All rights reserved. | |||||||
Solver is developed by a software company (not Microsoft) called Frontline Systems. | ||||||||
As such, it does not work seamlessly with Excel as the other native features. | ||||||||
Notes | ||||||||
To make Solver available to the Excel spreadsheets, you must activate the Solver Add-in: | ||||||||
1. Click Office/Excel Options. | ||||||||
2. In Manage input box, select Excel Add-ins and click Go. Tick Solver Add-in and click OK. | ||||||||
To make Solver available to VBA macros, you must make reference to Solver in the VBE: | ||||||||
1. In the Visual Basic Editor (VBE), select the required VBAProject (i.e. Excel Workbook). | ||||||||
2. Select (from the main menu in VBE) Tools/References and tick Solver. | ||||||||
a. If you cannot see Solver, click on Browse and try to find Solver.xlam. | ||||||||
b. It is usually found in C:\Program Files\Microsoft Office\OFFICE12\Library. | ||||||||
c. Select and open Solver.xlam. Find Solver in the dialog box and tick it. | ||||||||
Additional Notes | ||||||||
Any VBA macro with Solver will always have a dialog box pop up each time it is run. | ||||||||
To make the Solver dialog box not pop up (i.e. to automatically close itself), do the following: | ||||||||
1. Revise SolverSolve in your Sub to SolverSolve Userfinish:=True. | ||||||||
Solver in macros will not solve problems with integer decision variables. To overcome this | ||||||||
bug: | ||||||||
1. Add an extra row of SolverOk ... in the Sub. | ||||||||
Get Free Quote!
440 Experts Online