Solver error

[perco754]

Hi all experts,

I'm working with some updates of a Windows application (c++ Builder) where I at one place use the Solver Add-In in Excel (I'm calling it through OLE). This has been working since 2008 when I first developed it and now it is still working but I get some strange unwanted behaviour.

Everytime the Solver Add-In is awoken by a call through the Excel OLE object I get this dialog saying

"Macro error at cell: [SOLVER.XLA]Excel4Functions!A31"
followed by four buttons:

• Halt
• Step
• Continue
• GoTo (the last one is disabled).

If I press Continue the solver runs fine with expected result, if I press Step I run into a new dialog called "Single step" and here I got expect from the previous buttons also

• Step Into
• Step Over
• Help
• Pause

The text message has now changed from previous "Macro error at cell: [SOLVER.XLA]Excel4Functions!A31"
into
Formula:
=ELSE()

I now press button Evaluate several times until I get =RETURN() and here the solver fails to find a solution.

The whole Excel call is meant to be run in the background invisible for the end-user and I need get rid of this sudden behaviour.

Since last time I worked with this project I've had HD crash and a total reinstallation of Windows XP. We are running Office 2003 btw.

BR,
perco

 

[Andrew Poulsom]

Maybe this will help you:

http://www.pcreview.co.uk/forums/error-solver-reference-enabled-t972294.html

 

[perco754]

Maybe this will help you:

http://www.pcreview.co.uk/forums/error-solver-reference-enabled-t972294.html

Thanks, I actually googled into it prior to posting here. I'm not sure we have the same problem and I don't see how to get into the Autpen macro of Solver since it's password protected.
I'm not disabling or deleting any commandbars as described in that thread so ...

Anyway, maybe I'm too stupid to see the obvious solution but here's my code.

ThisWorkbook:

Sub Workbook_Open()
    
    If CheckSolver Then
        If Not Solver.AutoOpened Then Solver.Auto_open
        SetSeparator
    Else
        solverResult = -1
    End If
End Sub


Sub SetSeparator()
'
' SetSeparator Macro
' Macro recorded 2009-11-12 by Per Connman
'

'
    With Application
        .DecimalSeparator = "."
        .ThousandsSeparator = " "
        .UseSystemSeparators = False
    End With
End Sub


Function CheckSolverIntl() As Boolean
  '' Adjusted for Application.Run() to avoid Reference problems with Solver
  '' Adjusted for international versions of Excel
  '' Peltier Technical Services, Inc., Copyright © 2008. All rights reserved.
  '' Returns True if Solver can be used, False if not.

  Dim bSolverInstalled As Boolean
  Dim bAddInFound As Boolean
  Dim iAddIn As Long
  Const sAddIn As String = "solver.xla"

  '' Assume true unless otherwise
  CheckSolverIntl = True

  On Error Resume Next
  ' check whether Solver is installed
  bSolverInstalled = IsInstalled(sAddIn)
  Err.Clear

  If bSolverInstalled Then
    ' uninstall temporarily
    bAddInFound = AddInInstall(sAddIn, False)
    ' check whether Solver is installed (should be false)
    bSolverInstalled = IsInstalled(sAddIn)
  End If

  If Not bSolverInstalled Then
    ' (re)install Solver
    bAddInFound = AddInInstall(sAddIn, True)
    ' check whether Solver is installed (should be true)
    bSolverInstalled = IsInstalled(sAddIn)
  End If

  If Not bSolverInstalled Then
    MsgBox "Solver not found. This workbook will not work.", vbCritical
    CheckSolverIntl = False
  End If

  If CheckSolverIntl Then
    ' initialize Solver
    Application.Run "Solver.xla!Solver.Solver2.Auto_open"
  End If

  On Error GoTo 0

End Function

Function IsInstalled(sAddInFileName As String) As Boolean
  Dim iAddIn As Long
  
  IsInstalled = False
  
  For iAddIn = 1 To Application.AddIns.Count
    With Application.AddIns(iAddIn)
      If LCase$(.Name) = LCase$(sAddInFileName) Then
        If .Installed Then
          IsInstalled = True
        End If
        Exit For
      End If
    End With
  Next
  
End Function

Function AddInInstall(sAddInFileName As String, bInstall As Boolean) As Boolean
  Dim iAddIn As Long
  
  For iAddIn = 1 To Application.AddIns.Count
    With Application.AddIns(iAddIn)
      If LCase$(.Name) = LCase$(sAddInFileName) Then
        If .Installed <> bInstall Then
          .Installed = bInstall
        End If
        AddInInstall = True ' True = add-in is listed
        Exit For
      End If
    End With
  Next
  
End Function

Module1:

Public Sub SolveTheProblem()

    Dim row As Integer, rowTarget As Integer, columnTarget As Integer
    Dim cellTarget As String
    Dim vars As String, leftSide As String, rightSide As String
    Dim targetValue As String
    Dim resultInt As Integer
    
    solutionFound = False
    targetValue = CStr(Cells(1, 1).Value)
    targetValue = Replace(targetValue, ",", ".")
    rowTarget = Cells(2, 1).Value
    columnTarget = Cells(3, 1).Value
    
    If columnTarget = 2 Then
        cellTarget = "$B$" + CStr(rowTarget)
        'vars = "$B$" + CStr(rowTarget) + ","
    Else
        cellTarget = "$C$" + CStr(rowTarget)
    End If
    
    vars = "$B$" + CStr(rowTarget) + ","
    For row = 1 To 17
        If Cells(row, 4).Value > 0 Then
            vars = vars + "$B$" + CStr(row) + ","
        End If
    Next row
    
    
    vars = Left(vars, Len(vars) - 1)
    
    'SolverReset
    Application.Run "Solver.xla!SolverReset"
    
    'SolverOptions MaxTime:=100, Iterations:=400, Precision:=0.00001, AssumeLinear:= _
    '    False, StepThru:=False, Estimates:=1, Derivatives:=2, SearchOption:=1, _
    '    IntTolerance:=5, Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=True
    Application.Run "Solver.xla!Auto_Open"
    Application.Run "Solver.xla!SolverOptions", 1000, 400, 0.00001, False, False, _
                    1, 2, 1, 5, False, 0.0001, False
                    
        
    'SolverOk SetCell:=cellTarget, MaxMinVal:=3, ValueOf:=targetValue, ByChange:=vars
    Application.Run "Solver.xla!SolverOk", cellTarget, 3, targetValue, vars
    
    'SolverAdd CellRef:="$b$1:$b$16", Relation:=3, FormulaText:="0"
    'Application.Run "Solver.xla!SolverAdd", "$b$1:$b$16", 3, "0"
    'Application.Run "Solver.xla!SolverAdd", "$b$1:$b$15", 3, "0"
    Application.Run "Solver.xla!SolverAdd", "$b$1:$b$16", 3, "0"
    'SolverAdd CellRef:="$b$21", Relation:=1, FormulaText:="0.999"
    'Application.Run "Solver.xla!SolverAdd", "$b$21", 1, "0.999"
    Application.Run "Solver.xla!SolverAdd", "$b$22", 1, "0.999"
    
    For row = 1 To 17
        If Not row = rowTarget Then
            If Cells(row, 4).Value > 0 Then
                leftSide = "$C$" + CStr(row)
                rightSide = "$D$" + CStr(row)
                'SolverAdd CellRef:=leftSide, Relation:=2, FormulaText:=rightSide
                Application.Run "Solver.xla!SolverAdd", leftSide, 2, rightSide
            End If
        End If
    Next row
    
    'SolverAdd CellRef:="$c$1", Relation:=2, FormulaText:="$C$1"
    'SolverSolve
    'SolverSolve userFinish:=True
    
'The results of the SolverSolve function include:

'      0  Solver found a solution. All constraints and optimality conditions are satisfied.
'      1  Solver has converged to the current solution. All constraints are satisfied.
'      2  Solver cannot improve the current solution. All constraints are satisfied.
'      3  Stop chosen when the maximum iteration limit was reached.
'      4  The Set Cell values do not converge.
'      5  Solver could not find a feasible solution.
'      6  Solver stopped at user's request.
'      7  The conditions for Assume Linear Model are not satisfied.
'      8  The problem is too large for Solver to handle.
'      9  Solver encountered an error value in a target or constraint cell.
'     10  Stop chosen when maximum time limit was reached.
'     11  There is not enough memory available to solve the problem.
'     12  Another Excel instance is using SOLVER.DLL. Try again later.
'     13  Error in model. Please verify that all cells and constraints are valid.
    
    resultInt = Application.Run("Solver.xla!SolverSolve", True)
    
    Cells(31, 2) = resultInt
    
    'Equivalent to selecting options and clicking OK in the Solver Results dialog box that appears when the solution process is finished. The dialog box will not be displayed.
    'VBA Syntax
    'SolverFinish(KeepFinal:=, ReportArray:=)
    'Macro Language Syntax
    '=SOLVER.FINISH(keep_final, report_array)
    'KeepFinal is the number 1 or 2 and specifies whether to keep or discard the final solution.
    'If KeepFinal is 1 or omitted, the final solution values are kept in the changing cells.
    'If KeepFinal is 2, the final solution values are discarded and the former values of the changing cells are restored.
    
    If resultInt < 2 Then
        Application.Run "Solver.xla!SolverFinish", 1
    Else
        Application.Run "Solver.xla!SolverFinish", 2
    End If
    
End Sub

 

[Andrew Poulsom]

Why do you need to run Solver.Auto_open?

 

[perco754]

Why do you need to run Solver.Auto_open?

I'm not sure, that part of the code that deals with checking if Solver Add-in is installed or not etc. is copied from the net . I thought it was a way to make sure that the Solver is up running and ready?

Strangest thing is that I've never touched this code since 2008 and it's been working all the time with no problem. Could it be something with only my computer? I mean, we (in our company) get forced upon several CAT updates with all the different hotfixes you can imagine etc.

 

[Andrew Poulsom]

Do you still get the error if you comment out this line?

If Not Solver.AutoOpened Then Solver.Auto_open

 

[perco754]

Do you still get the error if you comment out this line?

If Not Solver.AutoOpened Then Solver.Auto_open

It worked, thanks!
I don't see why though

Somehow it did also manage to overwrite my Application.Visible = False setting??

Anyway thanks a bunch, you saved my afternoon! :D

BR,
perco