nb-
12-13-2009, 07:32 AM
Hi,
I keep getting the following error from a solver based sub when ever I run it;
Set Cell must be a single cell on the active sheet
The attached workbook is a simplified example of the problem, which throws the above error at the line;
int_result = Application.Run("Solver.xla!SolverSolve", True) '// Solve! (Run Analysis)
Solver seems to throw Error 2015 here, which then obviously causes a type mismatch with an integer.
Code and sample workbook below.
Any ideas as to what is causing this? As far as I can see there is a single cell, $J$12?
Public Sub solve_test()
Dim int_result As Integer
Dim str_result As String
'//Adjusted for Application.Run() to avoid Reference problems with Solver
'// Is solver available, if not initialise it.
If CheckSolver() = False Then
On Error GoTo Solver_Error '// Error handler in case of incompatible Solver.xla or Excel.
Application.Run "Solver.xla!Solver.Solver2.Auto_open"
On Error GoTo 0
End If
'// Setup Solver & Use Solver
Application.Run "Solver.xla!SolverReset" '//Reset Solver
Application.Run "Solver.xla!SolverOk", "$J$12", 3, "0", "$H$4:$H$8" '// New Analysis
int_result = Application.Run("Solver.xla!SolverSolve", True) '// Solve! (Run Analysis)
Application.Run "Solver.xla!SolverFinish" '//Finish Analysis
'// End Solver Use
'// Output Analysis Results
Select Case int_result
Case 0
str_result = "0: Solution found, optimality and constraints satisfied."
Case 1
str_result = "1: Converged, constraints satisfied."
Case 2
str_result = "2: Cannot improve, constraints satisfied."
Case 3
str_result = "3: Stopped at maximum iterations."
Case 4
str_result = "4: Solver did not converge."
Case 5
str_result = "5: No feasible solution."
Case 6
str_result = "6: Solver stopped at user's request."
Case 7
str_result = "7: The conditions for Assume Linear Model are not satisfied."
Case 8
str_result = "8: The problem is too large for Solver to handle."
Case 9
str_result = "9: Solver encountered an error value in a target or constraint cell."
Case 10
str_result = "10: Stop chosen when maximum time limit was reached."
Case 11
str_result = "11: There is not enough memory available to solve the problem."
Case 12
str_result = "12: Another Excel instance is using SOLVER.DLL. Try again later."
Case 13
str_result = "13: Error in model. Please verify that all cells and constraints are valid."
Case Else
str_result = "??: Error: Unknown solver result!" '// Error!
MsgBox str_result, vbExclamation, "SOLVER ERROR"
Exit Sub
End Select
Exit Sub
Solver_Error:
MsgBox "Solver Error: Please Ensure Solver.xla is installed on this system, and that it is running and English Language Version", vbInformation, "SOLVER COMPATABILITY PROBLEM"
End Sub
And the solver test function from http://peltiertech.com/Excel/SolverVBA.html
Public Function CheckSolver() As Boolean
'' Adjusted for Application.Run() to avoid Reference problems with Solver
'' 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
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
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
I keep getting the following error from a solver based sub when ever I run it;
Set Cell must be a single cell on the active sheet
The attached workbook is a simplified example of the problem, which throws the above error at the line;
int_result = Application.Run("Solver.xla!SolverSolve", True) '// Solve! (Run Analysis)
Solver seems to throw Error 2015 here, which then obviously causes a type mismatch with an integer.
Code and sample workbook below.
Any ideas as to what is causing this? As far as I can see there is a single cell, $J$12?
Public Sub solve_test()
Dim int_result As Integer
Dim str_result As String
'//Adjusted for Application.Run() to avoid Reference problems with Solver
'// Is solver available, if not initialise it.
If CheckSolver() = False Then
On Error GoTo Solver_Error '// Error handler in case of incompatible Solver.xla or Excel.
Application.Run "Solver.xla!Solver.Solver2.Auto_open"
On Error GoTo 0
End If
'// Setup Solver & Use Solver
Application.Run "Solver.xla!SolverReset" '//Reset Solver
Application.Run "Solver.xla!SolverOk", "$J$12", 3, "0", "$H$4:$H$8" '// New Analysis
int_result = Application.Run("Solver.xla!SolverSolve", True) '// Solve! (Run Analysis)
Application.Run "Solver.xla!SolverFinish" '//Finish Analysis
'// End Solver Use
'// Output Analysis Results
Select Case int_result
Case 0
str_result = "0: Solution found, optimality and constraints satisfied."
Case 1
str_result = "1: Converged, constraints satisfied."
Case 2
str_result = "2: Cannot improve, constraints satisfied."
Case 3
str_result = "3: Stopped at maximum iterations."
Case 4
str_result = "4: Solver did not converge."
Case 5
str_result = "5: No feasible solution."
Case 6
str_result = "6: Solver stopped at user's request."
Case 7
str_result = "7: The conditions for Assume Linear Model are not satisfied."
Case 8
str_result = "8: The problem is too large for Solver to handle."
Case 9
str_result = "9: Solver encountered an error value in a target or constraint cell."
Case 10
str_result = "10: Stop chosen when maximum time limit was reached."
Case 11
str_result = "11: There is not enough memory available to solve the problem."
Case 12
str_result = "12: Another Excel instance is using SOLVER.DLL. Try again later."
Case 13
str_result = "13: Error in model. Please verify that all cells and constraints are valid."
Case Else
str_result = "??: Error: Unknown solver result!" '// Error!
MsgBox str_result, vbExclamation, "SOLVER ERROR"
Exit Sub
End Select
Exit Sub
Solver_Error:
MsgBox "Solver Error: Please Ensure Solver.xla is installed on this system, and that it is running and English Language Version", vbInformation, "SOLVER COMPATABILITY PROBLEM"
End Sub
And the solver test function from http://peltiertech.com/Excel/SolverVBA.html
Public Function CheckSolver() As Boolean
'' Adjusted for Application.Run() to avoid Reference problems with Solver
'' 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
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
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