Consulting

Results 1 to 3 of 3

Thread: Solver Error: Set Cell must be a single cell on the active sheet

  1. #1

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

    Hi,

    I keep getting the following error from a solver based sub when ever I run it;
    [vba]Set Cell must be a single cell on the active sheet[/vba]
    The attached workbook is a simplified example of the problem, which throws the above error at the line;
    [vba]
    int_result = Application.Run("Solver.xla!SolverSolve", True) '// Solve! (Run Analysis)
    [/vba]

    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?


    [vba]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[/vba]
    And the solver test function from http://peltiertech.com/Excel/SolverVBA.html

    [vba]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[/vba]
    Last edited by nb-; 12-13-2009 at 07:52 AM.

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,446
    Location
    I just can't download that file, can you re-post it?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    Just reposted it .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •