Turn off On Error Resume next so you can see where it is actually failing, or replace
MsgBox ("Solver.xla or Solver.xlam not found. Exiting.")
with
MsgBox Err.Description
So at least it will tell you the actual error it is getting.
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
Title | # Comments | Views | Activity |
---|---|---|---|
Delete records using AutoFilter | 6 | 15 | |
Auto Operation of MS Excel 2013 | 2 | 29 | |
In Excel 2007, how should I change my formula so I don't get a #VALUE error? | 3 | 21 | |
VBA in SharePoint | 3 | 15 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
12 Experts available now in Live!