PDA

View Full Version : Solver Error: Set Cell must be a single cell on the active sheet



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

xld
12-13-2009, 08:19 AM
I just can't download that file, can you re-post it?

nb-
12-13-2009, 08:21 AM
Just reposted it ;).