Function CheckSolver() As Boolean
'Found solution to Solver issue online in Google search
' Peltier Technical Services, Inc., Copyright © 2007. All rights reserved.
' Returns True if Solver can be used, False if not.
Dim bSolverInstalled As Boolean
' 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, should be gone
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 was not found. This workbook will not work properly Solver addin is installed.", vbCritical
CheckSolver = False
End If
If CheckSolver Then
Dim solverProg As String
On Error Resume Next ' turn on error handler
'Next line needed to deal/remove unexspected memory error.
Application.Run "Solver.xla!Solver.Solver2.Auto_open"
'Application.Run "Solver.xla!Auto_Open"
If Err.Number <> 0 Then
'solver.xla error occurred, use solver.xlam instead
solverProg = "Solver.xlam"
Application.Run solverProg + "!Solver.Solver2.Auto_open"
'MsgBox "File found and will used Solver.xlam "
If Err.Number <> 0 Then
MsgBox ("Solver.xla or Solver.xlam not found. Exiting.")
' or whatever message you want to use
'Exit Sub
End If
Else 'solver.xla ran successfully
solverProg = "Solver.xla"
'MsgBox "File found and will use Solver.xla "
End If
End If
On Error GoTo 0
End Function
SolverOk SetCell:="$M$17", MaxMinVal:=2, ValueOf:=0, ByChange:="$J$6:$J$16", _
Engine:=2, EngineDesc:="Simplex LP"
SolverOk SetCell:="$M$17", MaxMinVal:=2, ValueOf:=0, ByChange:="$J$6:$J$16", _
Engine:=2, EngineDesc:="Simplex LP"
SolverSolve
Sub Axl2003_xl2007SolverMacro2()
' this will store the name of the version of solver you are using
Dim solverProg As String
Dim Result As Variant
Application.ScreenUpdating = False
On Error Resume Next ' turn on error handler
'Next line needed to deal/remove unexspected memory error.
Application.Run "Solver.xla!Auto_Open"
If Err.Number <> 0 Then
'solver.xla error occurred, use solver.xlam instead
Application.DisplayAlerts = False
solverProg = "Solver.xlam"
Application.Run solverProg + "!Auto_Open"
'MsgBox "Used Solver.xlam"
If Err.Number <> 0 Then
' MsgBox ("Solver.xla or Solver.xlam not found. Exiting.")
MsgBox Err.Description
' or whatever message you want to use
Exit Sub
End If
Else 'solver.xla ran successfully
solverProg = "Solver.xla"
'MsgBox "Used Solver.xla"
End If
On Error GoTo 0 ' turn off this error handler so it doesn't
'accidentally mask errors that you want to catch
Application.GoTo Reference:="AS_Price_DM_basis"
Application.Run solverProg + "!SolverOk", "AS_Price_DM_basis", 2, "Col_J"
Application.Run solverProg + "!SolverSolve", True 'this line stops the Solver Results dialog box from appearing
Application.Run solverProg + "!SolverFinish", 1, Array(2) 'This allows the rpt tabs to show.
' run the analysis
Result = Application.Run(solverProg + "!SolverSolve", True)
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 "AutoBalance/Solver found a solution. Click Ok, and for the Breaking-even Pricing Rpt.", vbInformation, "SOLUTION FOUND"
Beep
Else
Result = 4 ' Solver did not converge
Result = 5 ' No feasible solution
Beep
MsgBox "AutoBalance/Solver did not converge or was unable to find feasible solution, however the Break-even Pricing Rpt will still be generated.", vbExclamation, "SOLUTION NOT FOUND"
End If
Application.ScreenUpdating = True
End Sub
On Error Resume Next ' turn on error handler
'Next line needed to deal/remove unexspected memory error.
Application.Run "Solver.xla!Auto_Open"
'This line may fail
If Err.Number <> 0 Then
'solver.xla error occurred, use solver.xlam instead
Application.DisplayAlerts = False
solverProg = "Solver.xlam"
Application.Run solverProg + "!Auto_Open"
'MsgBox "Used Solver.xlam"
End If
On Error Resume Next ' turn on error handler
'Next line needed to deal/remove unexspected memory error.
Application.Run "Solver.xla!Auto_Open"
If Err.Number <> 0 Then
'solver.xla error occurred, use solver.xlam instead
Application.DisplayAlerts = False
solverProg = "Solver.xlam"
Application.Run solverProg + "!Auto_Open"
'MsgBox "Used Solver.xlam"
Err.Clear
If Err.Number <> 0 Then
MsgBox ("Solver.xla or Solver.xlam not found. Exiting.")
' or whatever message you want to use
Exit Sub
End If
Else 'solver.xla ran successfully
solverProg = "Solver.xla"
'MsgBox "Used Solver.xla"
End If
On Error GoTo 0 ' turn off this error handler so it doesn't
'accidentally mask errors that you want to catch
Application.GoTo Reference:="AS_Price_DM_basis"
Application.Run solverProg + "!SolverOk", "AS_Price_DM_basis", 2, "Col_J"
Application.Run solverProg + "!SolverSolve", True 'this line stops the Solver Results dialog box from appearing
Application.Run solverProg + "!SolverFinish", 1, Array(2) 'This allows the rpt tabs to show.
' run the analysis
Result = Application.Run(solverProg + "!SolverSolve", True)
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 "AutoBalance/Solver found a solution. Click Ok, and for the Breaking-even Pricing Rpt.", vbInformation, "SOLUTION FOUND"
Beep
Else
Result = 4 ' Solver did not converge
Result = 5 ' No feasible solution
Beep
MsgBox "AutoBalance/Solver did not converge or was unable to find feasible solution, however the Break-even Pricing Rpt will still be generated.", vbExclamation, "SOLUTION NOT FOUND"
End If
Application.ScreenUpdating = True
End Sub
If .xla Exists Then
Use .xla
Else
If .xlam Exists Then
Use .xlam
Else
Throw Error
End If
End If
On Error Resume Next ' turn on error handler
'Next line needed to deal/remove unexspected memory error.
Application.Run "Solver.xla!Auto_Open"
If Err.Number <> 0 Then
Err.Clear
'solver.xla error occurred, use solver.xlam instead
Application.DisplayAlerts = False
solverProg = "Solver.xlam"
Application.Run solverProg + "!Auto_Open"
'MsgBox "Used Solver.xlam"
If Err.Number <> 0 Then
MsgBox ("Solver.xla or Solver.xlam not found. Exiting.")
' or whatever message you want to use
Exit Sub
End If
Else 'solver.xla ran successfully
solverProg = "Solver.xla"
'MsgBox "Used Solver.xla"
End If
On Error GoTo 0 ' turn off this error handler so it doesn't
'accidentally mask errors that you want to catch
Application.GoTo Reference:="AS_Price_DM_basis"
Application.Run solverProg + "!SolverOk", "AS_Price_DM_basis", 2, "Col_J"
Application.Run solverProg + "!SolverSolve", True 'this line stops the Solver Results dialog box from appearing
Application.Run solverProg + "!SolverFinish", 1, Array(2) 'This allows the rpt tabs to show.
' run the analysis
Result = Application.Run(solverProg + "!SolverSolve", True)
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 "AutoBalance/Solver found a solution. Click Ok, and for the Breaking-even Pricing Rpt.", vbInformation, "SOLUTION FOUND"
Beep
Else
Result = 4 ' Solver did not converge
Result = 5 ' No feasible solution
Beep
MsgBox "AutoBalance/Solver did not converge or was unable to find feasible solution, however the Break-even Pricing Rpt will still be generated.", vbExclamation, "SOLUTION NOT FOUND"
End If
Application.ScreenUpdating = True
End Sub
If Application.Version < 12 Then 'if pre-2007
solverProg = "Solver.xla"
Else 'if xl2007 or xl2010
solverProg = "Solver.xlam"
End If
If you are experiencing a similar issue, please ask a related question
Join the community of 500,000 technology professionals and ask your questions.