Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Sover.xla vs. Solver.xlam

This is a discussion on Sover.xla vs. Solver.xlam within the Excel Questions forums, part of the Question Forums category; hi, i have some 2003 excel workbooks which used solver.xla. now when i open them in 2007 it says "missing:solver.xla". ...

  1. #1
    New Member
    Join Date
    Sep 2007
    Posts
    8

    Default Sover.xla vs. Solver.xlam

    hi,

    i have some 2003 excel workbooks which used solver.xla. now when i open them in 2007 it says "missing:solver.xla". when i try to reference solver.xlam for this, it still gives me the error. what is the best way to resolve this?

    thanks

  2. #2
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    3,781

    Default Re: Sover.xla vs. Solver.xlam

    There are problems even between Excel versions that all use the same xla extension. I learned long ago not to use references to Solver, but instead use Application.Run:

    http://peltiertech.com/Excel/SolverVBA.html
    Jon Peltier
    Peltier Technical Services, Inc.
    Try the Peltier Tech Chart Utility

  3. #3
    New Member
    Join Date
    Sep 2007
    Posts
    8

    Default Re: Sover.xla vs. Solver.xlam

    thanks for your help!

  4. #4
    New Member
    Join Date
    Jan 2011
    Posts
    9

    Default Re: Sover.xla vs. Solver.xlam

    Tried Jon's CheckSolver. It failed using post-2003 .xlam type environment. I used Application.Run but no soap. I assume that is what he means by "late binding". Like a few of you, I noticed his Solver.xla call at the end of CheckSolver.

    What up? What am i missing. In my main routine I do this:

    Public Sub TestSolverReference()
    Dim wsSolver As Worksheet: Set wsSolver = Worksheets("SolverSheet")
    Dim solverExists As Boolean

    'Verify Solver exists.
    solverExists = CheckSolver
    If solverExists = False Then Exit Sub

    'Set initial parameter estimates.
    wsSolver.Range("B4").Value = 1
    wsSolver.Range("B5").Value = 1

    'Run Solver.
    wsSolver.Activate
    Application.Run "Solver.xla!SolverReset"
    Application.Run "solver.xla!SolverAdd", "$B$4:$B$5", 1, "4"
    Application.Run "Solver.xla!SolverOk", "$B$1", 1, "0", "$B$4:$B$5"
    Application.Run "Solver.xla!SolverSolve", True
    End Sub



    And let the bombs begin. As soon as the first .xla is encountered, dots it!

    Help prease.. thx

  5. #5
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    3,781

    Default Re: Sover.xla vs. Solver.xlam

    You need to make allowances for the filename.

    Code:
    Public Sub TestSolverReference()
        Dim wsSolver As Worksheet
        Dim solverExists As Boolean
    
        Dim sSolver As String
        sSolver = "Solver.xla" & Iff(Val(Application.Version) > 11, "m", "")
    
        Set wsSolver = Worksheets("SolverSheet")
        'Verify Solver exists.
        solverExists = CheckSolver
        If solverExists = False Then Exit Sub
        'Set initial parameter estimates.
        wsSolver.Range("B4").Value = 1
        wsSolver.Range("B5").Value = 1
        'Run Solver.
        wsSolver.Activate
        Application.Run sSolver & "!SolverReset"
        Application.Run sSolver & "!SolverAdd", "$B$4:$B$5", 1, "4"
        Application.Run sSolver & "!SolverOk", "$B$1", 1, "0", "$B$4:$B$5"
        Application.Run sSolver & "a!SolverSolve", True
    End Sub
    Presumably this also needs to be done within the CheckSolver routine.
    Jon Peltier
    Peltier Technical Services, Inc.
    Try the Peltier Tech Chart Utility

  6. #6
    New Member
    Join Date
    Jan 2011
    Posts
    9

    Default Re: Sover.xla vs. Solver.xlam

    Now ur talking. well done, my friend. thought that was the key, detecting the version. will try, if it barfs will get back. if not, verrrrrry g.

  7. #7
    New Member
    Join Date
    Nov 2011
    Location
    Thailand
    Posts
    38

    Default Re: Sover.xla vs. Solver.xlam

    Hello everyone. I have a macro (with a solver in it) which has been working very well on MAC Excel 2004 (and actually also perfectly works on PC Windows Excel 2007). But now that I upgraded to MAC Excel 2011 (I actually had no choice since my previous MAC just died and Excel 2004 does not work on new MACs), things have turned bitter. Here is the problem: when the macro starts running and the solver is called the small solver.app starts bouncing on the dock but it refused to solve anything until it eventually stops and nothing is solved. I found out that if while the solver.app is bouncing I manually select the sheet where the solver is supposed to do its job it eventually will solve what it is supposed to solve. Now that obviously is not a solution. Below you will find the code that I use:

    Code:
    Application.ScreenUpdating = False
            Dim fmForm As FrmDERatio 'Object variable for form
            Set fmForm = New FrmDERatio 'create new instance of the form
        With fmForm
              .Show
                If Not .Cancel Then
        Application.ScreenUpdating = False
                Sheets("DATA").Range("F24").Value = val(.InputReturn)
                Call A1_Reset_Prodction_Cost_Line24
                Call B1_Prod_Cost_Goal_Seek_Phase1_Copy_Paste_Value
                Call C1_Prod_Cost_Goal_Seek_Phase2_Copy_Paste_Value
                Call D1_Prod_Cost_Goal_Seek_310Days_Copy_Paste_Value
                Call E1_Delete_Marginal_Prod_Cost_Cell_P24
            Sheets("DATA").Range("F46").Value = val(.InputReturn3) & "% "
            Sheets("CAPITAL COST SUM").Range("AZ100").Value = val(.InputReturn2) / 100
            Sheets("CAPITAL COST SUM").Range("C31:C32,E37").ClearContents
            Sheets("CAPITAL COST SUM").Select
        Application.Run "SolverReset"
        Application.Run "SolverOk", "$C$42", 3, val(.InputReturn2) / 100, "$C$31,$C$32,$E$37"
        Application.Run "SolverAdd", "$C$31", 3, "0"
        Application.Run "SolverAdd", "$C$32", 3, "0"
        Application.Run "SolverAdd", "$D$31", 3, "1000000"
        Application.Run "SolverAdd", "$D$32", 3, "1000000"
        Application.Run "SolverAdd", "$G$37", 2, "0"
        Application.Run "SolverOptions", 100, 1000, 1e-20, False, False, 1, 1, 1, 5, False, 1e-05, False
        Application.Run "SolverSolve", True
        Application.Run "SolverFinish", 1
        Application.ScreenUpdating = True
        Sheets("PRICES").Select
        MsgBox "- The Marginal Production Costs have been calculated." & vbCr & "" & vbCr & "- The LPG Cost has been set to: " & .InputReturn & " USD/Ton " & vbCr & "" & vbCr & "- The Debt / Equity Ratio has been set to:  " & 100 - .InputReturn2 & "% " & "/ " & .InputReturn2 & "% " & vbCr & "" & vbCr & "- The Corporate Tax Rate has been set to: " & .InputReturn3 & "% " & vbCr & "" & vbCr & "Click OK to continue with the Simulation."
        End If
    End With
    End Sub
    I tried taking out the ScreenUpdating function but that didnt help. Is it perhaps that the code is incompatible with the new solver for Excel 2011 for MAC. Can anyone propose a solution?

    Thanks in advance.

  8. #8
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    3,781

    Default Re: Sover.xla vs. Solver.xlam

    Do you know whether Solver ran any of its commands? For example, if you put

    Code:
    Debug.Print "Solver Reset"
    after the SolverReset command, the Immediate Window will display "SolverReset", so you know you've gotten this far. Insert one of these after each Solver step.

    I'm a big dummy about Macs, but what works in Windows sometimes is to insert

    Code:
    DoEvents
    after a line that might take longer for Excel to change than VBA needs to get to the next command. Essentially this tells VBA to wait while Excel does its thing.

    So stick DoEvents before the first Solver command.
    Jon Peltier
    Peltier Technical Services, Inc.
    Try the Peltier Tech Chart Utility

  9. #9
    New Member
    Join Date
    Nov 2011
    Location
    Thailand
    Posts
    38

    Default Re: Sover.xla vs. Solver.xlam

    Quote Originally Posted by Jon Peltier View Post
    Do you know whether Solver ran any of its commands? For example, if you put

    Code:
    Debug.Print "Solver Reset"
    after the SolverReset command, the Immediate Window will display "SolverReset", so you know you've gotten this far. Insert one of these after each Solver step.

    I'm a big dummy about Macs, but what works in Windows sometimes is to insert

    Code:
    DoEvents
    after a line that might take longer for Excel to change than VBA needs to get to the next command. Essentially this tells VBA to wait while Excel does its thing.

    So stick DoEvents before the first Solver command.

    Jon, thank you for your answer.

    I am not sure whether the solver ran any of the commands although I highly suspect it didn't since the MsgBox at the end of the code does pop up while the solver.app is still bouncing on the dock and no apparent calculation is done. After a minute or so it just stops bouncing and when I go to see the results on the specific sheet nothing has been solved by the solver.

    I will try to insert your code just to make sure none of the commands were ran and try the DoEvents see if that helps. The strange thing thought as I said before is, if while the solver.app is bouncing on the dock I manually select the sheet where the solver is supposed to do its job it eventually will solve what it is supposed to solve. It is like the solver refuses to do anything unless the sheet is active/selected.

  10. #10
    MrExcel MVP
    Join Date
    May 2003
    Location
    USA
    Posts
    3,781

    Default Re: Sover.xla vs. Solver.xlam

    Well, Solver only works on the active sheet. That's why I suspect due to sequencing issues the proper sheet is activated but isn't quite active yet, when the Solver Reset command wakes up Solver, and it starts bouncing on the dock. That must be annoying as hell, those icons jumping up and down.

    Maybe you could also move the sheet.activate command up higher in your routine.
    Jon Peltier
    Peltier Technical Services, Inc.
    Try the Peltier Tech Chart Utility

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com