Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.
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
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
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.