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