Sover.xla vs. Solver.xlam

rskshc

New Member
Joined
Sep 12, 2007
Messages
8
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
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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
 
Upvote 0
You need to make allowances for the filename.

Rich (BB 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.
 
Upvote 0
:) 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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,220
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top