Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Solver Reference-programmatically
For the umpteenth time, can I please push my request for the syntax t *programmatically *set SOLVER reference. If it can't be done, I'll b content to settle for that as an answer. Thanks Davi -- david ----------------------------------------------------------------------- davidm's Profile: http://www.excelforum.com/member.php...fo&userid=2064 View this thread: http://www.excelforum.com/showthread.php?threadid=53765 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Solver Reference-programmatically
It can be done. I won't tell you how, because it will bring you no joy. This
advice is the result of hours of trial and error, mostly error, and fruitless searching of Microsoft and Frontline Systems help. Later versions of Excel not only have the macro protection but also protect the VBA project from changes, and this means the ability to set references. "Enable macros" is one thing, but it's never a good idea to rely on the user to muck around with VBA protection. What you need to do is make sure Solver is installed on the computer, then run Solver's Auto_Open procedure to initialize it, then use Solver. All calls to Solver should be made using Application.Run, to avoid needing the reference. When opening the workbook check that the following function CheckSolver is true, which checks that the Solver add-in is installed, then initializes the add-in: ''================================================ ========================== Function CheckSolver() As Boolean '' Adjusted for Application.Run() to avoid Reference problems with Solver '' © 2004 J. Peltier, Peltier Technical Services. Dim bSolverInstalled As Boolean If gbDebug Then Debug.Print Now, "NewCheckSolver" '' Assume true unless otherwise CheckSolver = True On Error Resume Next ' check whether Solver is installed bSolverInstalled = Application.AddIns("Solver Add-In").Installed Err.Clear If bSolverInstalled Then ' uninstall temporarily Application.AddIns("Solver Add-In").Installed = False ' check whether Solver is installed (should be false) bSolverInstalled = Application.AddIns("Solver Add-In").Installed End If If Not bSolverInstalled Then ' (re)install Solver Application.AddIns("Solver Add-In").Installed = True ' check whether Solver is installed (should be true) bSolverInstalled = Application.AddIns("Solver Add-In").Installed End If If Not bSolverInstalled Then MsgBox "Solver not found. This workbook will not WORK.", vbCritical, sTITLE CheckSolver = False End If If CheckSolver Then ' initialize Solver Application.Run "solver.xla!SOLVER.Solver2.Auto_open" End If On Error GoTo 0 End Function ''================================================ ========================== The following shows the sequence you'd use to run Solver with Application.Run: ''================================================ ========================== ' reset Application.Run "solver.xla!SolverReset" ' set up new analysis Application.Run "solver.xla!SolverOk", "Blah1", 1, , "BlahBlah1" ' add constraints Application.Run "solver.xla!SolverAdd", "Blah2", 3, 0 Application.Run "solver.xla!SolverAdd", "Blah3", 2, "BlahBlah3" ' run the analysis Result = Application.Run("solver.xla!SolvSolve", True) ' report on success of analysis If Result <= 3 Then ' Result = 0, Solution found, optimality and constraints satisfied ' Result = 1, Converged, constraints satisfied ' Result = 2, Cannot improve, constraints satisfied ' Result = 3, Stopped at maximum iterations MsgBox "Solution Found", vbInformation, sTITLE Else ' Result = 4, Solver did not converge ' Result = 5, No feasible solution Beep MsgBox "No solution was found.", vbExclamation, sTITLE End If ''================================================ ========================== - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "davidm" wrote in message ... For the umpteenth time, can I please push my request for the syntax to *programmatically *set SOLVER reference. If it can't be done, I'll be content to settle for that as an answer. Thanks David -- davidm ------------------------------------------------------------------------ davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645 View this thread: http://www.excelforum.com/showthread...hreadid=537657 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting Solver Reference-programmatically
Sub SolverInstall()
'// Dana DeLouis Dim wb As Workbook On Error Resume Next ' Set a Reference to the workbook that will hold Solver Set wb = ActiveWorkbook With wb.VBProject.References .Remove .Item("SOLVER") End With With AddIns("Solver Add-In") .Installed = False .Installed = True wb.VBProject.References.AddFromFile .FullName End With End Sub -- Regards, Tom Ogilvy "davidm" wrote: For the umpteenth time, can I please push my request for the syntax to *programmatically *set SOLVER reference. If it can't be done, I'll be content to settle for that as an answer. Thanks David -- davidm ------------------------------------------------------------------------ davidm's Profile: http://www.excelforum.com/member.php...o&userid=20645 View this thread: http://www.excelforum.com/showthread...hreadid=537657 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Setting HotKey Programmatically | Excel Programming | |||
WhatsThisHelp in VBA - setting the help file path programmatically | Excel Programming | |||
Setting Printer Options Programmatically | Excel Programming | |||
Programmatically Add Reference` | Excel Programming | |||
Setting a Solver constraint using vba? | Excel Programming |