Solved

Solver in Excel 2010

Posted on 2010-11-27
28
6,754 Views
Last Modified: 2012-05-10
I have a file that uses Solver in xl2003 and xl2007 just fine.  Now when we try and use this file in xl2010 it is failing.  It is failing on line 52.  I know something has changed in Solver 2010 that VBA calls to it from older versions does not like.

How would I edit the following code, to work in 2003/2007 and 2010?

Please advise and thanks.  -R-

Function CheckSolver() As Boolean
'Found solution to Solver issue online in Google search
' 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, should be gone
    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 was not found. This workbook will not work properly Solver addin is installed.", vbCritical
    CheckSolver = False
  End If
 
  If CheckSolver Then
  
  Dim solverProg As String
  
  On Error Resume Next ' turn on error handler
'Next line needed to deal/remove unexspected memory error.
    Application.Run "Solver.xla!Solver.Solver2.Auto_open"
    
    
'Application.Run "Solver.xla!Auto_Open"
    If Err.Number <> 0 Then
'solver.xla error occurred, use solver.xlam instead
        
        solverProg = "Solver.xlam"
        Application.Run solverProg + "!Solver.Solver2.Auto_open"
'MsgBox "File found and will used Solver.xlam "
        
        If Err.Number <> 0 Then
             MsgBox ("Solver.xla or Solver.xlam not found. Exiting.")
             ' or whatever message you want to use
             'Exit Sub
        End If
    Else   'solver.xla ran successfully
        solverProg = "Solver.xla"
        'MsgBox "File found and will use Solver.xla "
        
    End If
  End If
    On Error GoTo 0
 
End Function

Open in new window

0
Comment
Question by:RWayneH
  • 12
  • 12
  • +1
28 Comments
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34223991
It's failing on line 52? That's a MsgBox. So I guess it's failing somewhere earlier and Err.Number is not 0 so it displays the message.

Turn off On Error Resume next so you can see where it is actually failing, or replace
MsgBox ("Solver.xla or Solver.xlam not found. Exiting.")
with
MsgBox Err.Description
So at least it will tell you the actual error it is getting.
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34226041
Maybe just change

        Application.Run solverProg + "!Solver.Solver2.Auto_open"

to

        Application.Run solverProg & "!Solver.Solver2.Auto_open"

I take it you tried to make it run with .xlam?
It may help to show the original version pre attempts to make it run in 2010.
0
 

Author Comment

by:RWayneH
ID: 34226413
Here is the error message displayed when I chg the MsgBox to what you asked.  (see screenshot).  I know that it is not that all macros are disabled, because other macros are working fine.  Why would it now not be able to "run solver.xlam!Auto_Open" all of a sudden?  What is going on here?  Any ideas?  -R-
ErrorMsg11282010.png
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34226510
Are you using the xla or xlam solver?
0
 

Author Comment

by:RWayneH
ID: 34226528
I belive it is not defining what "solverProg" is correctly.  It should be the version of Solver that is going to be used and giving me the MsgBox.  xl2003 = Solver.xla, xl2007 = Solver.xlam, xl2010 =???

Chg'ing the line to replace the "+" with a "&" did nothing.  The code posted in the beginning of the thread is the pre 2010 code, which still works fine in xl2007 and xl2003.  I do understand why the same code does not work in 2010, or what needs to chg in order to execute the solver button in 2010?

I ran a recorder while running solver and the code output is in the code area.  It is different then any code that the other two had..  How would I write a new macro that tells what version of Excel is running to include xl2010?

I also included the code that determine what solver is being used...  do I need to add something else to it to see the version of solver in 2010?

Hope I am not confusing you too much.. this is not an easy fix.  -R-
SolverOk SetCell:="$M$17", MaxMinVal:=2, ValueOf:=0, ByChange:="$J$6:$J$16", _
        Engine:=2, EngineDesc:="Simplex LP"
    SolverOk SetCell:="$M$17", MaxMinVal:=2, ValueOf:=0, ByChange:="$J$6:$J$16", _
        Engine:=2, EngineDesc:="Simplex LP"
    SolverSolve


Sub Axl2003_xl2007SolverMacro2()


' this will store the name of the version of solver you are using
Dim solverProg As String
Dim Result As Variant

Application.ScreenUpdating = False

On Error Resume Next ' turn on error handler
'Next line needed to deal/remove unexspected memory error.
    Application.Run "Solver.xla!Auto_Open"
    If Err.Number <> 0 Then
        'solver.xla error occurred, use solver.xlam instead
        
        Application.DisplayAlerts = False
        
        solverProg = "Solver.xlam"
        Application.Run solverProg + "!Auto_Open"
        'MsgBox "Used Solver.xlam"
        
        If Err.Number <> 0 Then
'             MsgBox ("Solver.xla or Solver.xlam not found. Exiting.")
             MsgBox Err.Description

             ' or whatever message you want to use
             Exit Sub
        End If
    Else   'solver.xla ran successfully
        solverProg = "Solver.xla"
        'MsgBox "Used Solver.xla"
        
    End If
    
    On Error GoTo 0  ' turn off this error handler so it doesn't
                     'accidentally mask errors that you want to catch
    
 
    Application.GoTo Reference:="AS_Price_DM_basis"
    Application.Run solverProg + "!SolverOk", "AS_Price_DM_basis", 2, "Col_J"
    Application.Run solverProg + "!SolverSolve", True  'this line stops the Solver Results dialog box from appearing
    Application.Run solverProg + "!SolverFinish", 1, Array(2) 'This allows the rpt tabs to show.
 
    ' run the analysis
    Result = Application.Run(solverProg + "!SolverSolve", True)
    If Result <= 3 Then
     Result = 0  ' Solution found, optimality and constraints satisfied
     Result = 1  ' Converged, constraints satisfied
     Result = 2  ' Cannot improve, constraints satisfied
     Result = 3  ' Stopped at maximum iterations
    'MsgBox "AutoBalance/Solver found a solution.  Click Ok, and for the Breaking-even Pricing Rpt.", vbInformation, "SOLUTION FOUND"
     Beep
    Else
     Result = 4  ' Solver did not converge
     Result = 5  ' No feasible solution
    Beep
    MsgBox "AutoBalance/Solver did not converge or was unable to find feasible solution, however the Break-even Pricing Rpt will still be generated.", vbExclamation, "SOLUTION NOT FOUND"
    End If
    
    
Application.ScreenUpdating = True
    
End Sub

Open in new window

0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34226551
Quick question. Did you install the solver add-in to the new Excel? Is it listed in the add-ins? If not, that's the problem.
0
 

Author Comment

by:RWayneH
ID: 34226597
Yes Solver is listed in the addin's.  If have another procedure that checks for it on Workbook_Open.  I still believe for some reason it is not seeing solver.xlam correctly and casuing the Msgbox to display.  Is there another way to write the procedure that checks for the .xlam?  I need to get it away from the Exit Sub that it is hitting up against because .xlam is not being found.  Maybe I should force it to .xlam just to see if I can get it to fire correctly.  -R-
0
 

Author Comment

by:RWayneH
ID: 34226631
This is the code that it is skipping right over.  Run .xla should fail, because it is xl2010.  The If Err.Number <> 0 Then is setting the ver of Solver to use .xlam, but right after that code, that next If Err.Number <> is executing and causing the issue.  Is there a way to capture this check in a loop? so as soon as the ver of Solver being used is determine it continues... and does not goto the next Err.Number?  -R-
On Error Resume Next ' turn on error handler
'Next line needed to deal/remove unexspected memory error.
    
    Application.Run "Solver.xla!Auto_Open"
'This line may fail
    If Err.Number <> 0 Then
'solver.xla error occurred, use solver.xlam instead
        Application.DisplayAlerts = False
        
        solverProg = "Solver.xlam"
        Application.Run solverProg + "!Auto_Open"
'MsgBox "Used Solver.xlam"
    
    End If

Open in new window

0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34226954
.xla won't fail if the .xla exists. Excel 2010 can read 2003 files no problem.
If you want to avoid all this, you can just set a reference in your project to the solver, then you can refer to the macros directly without using the Application.Run workaround. Click on tools->references and select your add-in, then you can do something like SOLVER.Auto_Open (full discussion here http://peltiertech.com/Excel/SolverVBA.html)
The Application.Run method is kind of a loose workaround, not the way MS intended things to work. It may not function the way you want it to. I assume the peltier folks will eventually make it work for 2010 as well, but until then setting explicit references should always work.
0
 

Author Comment

by:RWayneH
ID: 34226995
It is not about the use of Application.Run.  It is about: How do I write a solution that would loop, if .xla exists use it, if .xlam exists use it.  If neither exists use the alert Msgbox to alert user that neither exist and Exit Sub.

I actually got this to work by excluding the second: If Err.Number statement and it used .xlam just fine, but now I am worried about how that will impact the .xlm users.

It is about how can I loop through the checking of which version of solver will be used.  I think this is a two case if, with a else at the end.  I am terrible at writing loops in VBA, that is why I need assistance re-writting this.  Is it possible to get a loop written that will force the use of the proper solver existance?

Please advise and thanks. -R-
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34227146
Oh. I get it. It's only missing one little thing. Here's what's happening. When it finds that xla is missing, it sets Err.Number to something. Now when it hits the second If Err.Number <> 0, the Er.Number is still set from before.
Put this line of code
Err.Clear
right after the first if Err.Number <> 0 block. This way it will 'unset' the error so it will test the real error the next time.
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34227155
So now, if it doesn't find the first one it will look for the second, otherwise it will use the first.
If it doesn't find the second, it will throw the error, otherwise it will use the second.
The only problem you had was that it thought it had an error both times since the error was never cleared.
0
 

Author Comment

by:RWayneH
ID: 34227200
I will try this and get back with you tomorrow.  I have a commentment the rest of the day.  Thanks for the help...  I think you nailed it and will let you know after I try it.  Thanks. -R-
0
 

Author Comment

by:RWayneH
ID: 34229788
I tested in xl2003 first.  It should set the version to solver.xla and I am getting the screenshot error below, which appears to be looking for the .xlam version of solver.  It almost appears that the first if is true when using xl2003, when it should be false and moving to the Else... statement.  It appears that it is not looping through the sequence correct.

Could you provide a rewrite that will select the proper verison and if neither is selected alert user with a Msgbox saying so, and exiting sub?

The adding of the Err.Clear, only worked if xl2007 and xl2010.  Need this to work in xl2003 also.

Please advise and thanks. -R-
On Error Resume Next ' turn on error handler
'Next line needed to deal/remove unexspected memory error.
    Application.Run "Solver.xla!Auto_Open"
    If Err.Number <> 0 Then
        'solver.xla error occurred, use solver.xlam instead
        
        Application.DisplayAlerts = False
        
        solverProg = "Solver.xlam"
        Application.Run solverProg + "!Auto_Open"
        'MsgBox "Used Solver.xlam"
        Err.Clear
        
        If Err.Number <> 0 Then
             MsgBox ("Solver.xla or Solver.xlam not found. Exiting.")
             ' or whatever message you want to use
             Exit Sub
        End If

    Else   'solver.xla ran successfully
        solverProg = "Solver.xla"
        'MsgBox "Used Solver.xla"
        
    End If
    
    On Error GoTo 0  ' turn off this error handler so it doesn't
                     'accidentally mask errors that you want to catch
    
 
    Application.GoTo Reference:="AS_Price_DM_basis"
    Application.Run solverProg + "!SolverOk", "AS_Price_DM_basis", 2, "Col_J"
    Application.Run solverProg + "!SolverSolve", True  'this line stops the Solver Results dialog box from appearing
    Application.Run solverProg + "!SolverFinish", 1, Array(2) 'This allows the rpt tabs to show.
 
    ' run the analysis
    Result = Application.Run(solverProg + "!SolverSolve", True)
    If Result <= 3 Then
     Result = 0  ' Solution found, optimality and constraints satisfied
     Result = 1  ' Converged, constraints satisfied
     Result = 2  ' Cannot improve, constraints satisfied
     Result = 3  ' Stopped at maximum iterations
    'MsgBox "AutoBalance/Solver found a solution.  Click Ok, and for the Breaking-even Pricing Rpt.", vbInformation, "SOLUTION FOUND"
     Beep
    Else
     Result = 4  ' Solver did not converge
     Result = 5  ' No feasible solution
    Beep
    MsgBox "AutoBalance/Solver did not converge or was unable to find feasible solution, however the Break-even Pricing Rpt will still be generated.", vbExclamation, "SOLUTION NOT FOUND"
    End If
    
    
Application.ScreenUpdating = True
    
End Sub

Open in new window

0
 

Author Comment

by:RWayneH
ID: 34229802
opps! forgot to include the screenshot... -R-
AutoBalanceError.png
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34230383
Why are you doing "Solver.xla!Auto_Open" instead of "Solver.xla!Solver.Solver2.Auto_Open" ?
I think that's all that's left. I do that all the time where I try to fix something and then by the time it's actually fixed, I've messed up something else trying to fix the first thing.
0
 

Author Comment

by:RWayneH
ID: 34230562
I have no clue... why.  I am just trying to tag what version of solver needs to be pegged to  use. (put in the solverProg = ).  It does appear to me that it needs to loop instead of using the If.Err.Number...  or if the If.Err.Number.  If that needs to stay? or not needs to be taken out... no problem here.  Just need it to set the solverProg correctly when the file is used in xl2003, (xla) and again the correct one when the file is used in xl2007 and xl2010 (xlam).  If neither one exists, else it to a msgbox alert user of it and exit sub.  I am so bad at writing loops it is not funny.  That is why I request help doing them.  -R-
0
 
LVL 37

Accepted Solution

by:
TommySzalapski earned 500 total points
ID: 34231585
There is no reason to use a loop. You just want code that does this:
If .xla Exists Then
   Use .xla
Else
  If .xlam Exists Then
    Use .xlam
  Else
    Throw Error
  End If
End If

Open in new window


Which is what that function is trying to do.

I just noticed something. You cleared the error in the wrong spot. It will never find the xlam file the way you have it. The Err.Clear line needs to move up to right after the first If Err.Number <> 0 check.
Doing Err.Clear sets Err.Number to 0 so doing this right before testing means it will always think it's fine. So the current code will think it found .xlam when in fact it found neither.

Fix the Err.Clear and put it back to using "!Solver.Solver2.Auto_Open" like in Peltier's code.

In the meantime. I can try to download the solver on my Excel 2007 machine in my lab (hooray for Remote Desktop) and see if I can get the function to run on both that one and this. The code you have is structured just fine and should work just fine once you get Err.Clear where it needs to be.
0
 

Author Comment

by:RWayneH
ID: 34233993
Ok now I am really messing this up, (and getting myself confused) I feel that you know the issue and can fix it, however I am not so sure I can.  It now works in xl2003 and not in xl2007 or 2010...  :-(

I am not getting how the statements need to produce the correct results.  Does the middle "If" now need to be an Else? and moved to the bottom?  Or is the code you suggested now need to be inserted... or is that just a high level structure thing.

I added the code that it is currently being using... that works in xl2003, but is returning the xla and xlam neither existing... Exiting.  The opposite of what was working before, only working in xlam to not and only working in xla.

Still need a rewrite of this and a possible restructure of the if else else... instead of If if else.

Please help.. I am destroying this more than fixing.  -R-

PS: If I need to move a line around please comment using line numbers so I put them in the right place.  Thanks.

On Error Resume Next ' turn on error handler
'Next line needed to deal/remove unexspected memory error.
    Application.Run "Solver.xla!Auto_Open"
    If Err.Number <> 0 Then
    Err.Clear
        'solver.xla error occurred, use solver.xlam instead
        
        Application.DisplayAlerts = False
        
        solverProg = "Solver.xlam"
        Application.Run solverProg + "!Auto_Open"
        'MsgBox "Used Solver.xlam"
        
        
        If Err.Number <> 0 Then
             MsgBox ("Solver.xla or Solver.xlam not found. Exiting.")
             ' or whatever message you want to use
             Exit Sub
        End If

    Else   'solver.xla ran successfully
        solverProg = "Solver.xla"
        'MsgBox "Used Solver.xla"
        
    End If
    
    On Error GoTo 0  ' turn off this error handler so it doesn't
                     'accidentally mask errors that you want to catch
    
 
    Application.GoTo Reference:="AS_Price_DM_basis"
    Application.Run solverProg + "!SolverOk", "AS_Price_DM_basis", 2, "Col_J"
    Application.Run solverProg + "!SolverSolve", True  'this line stops the Solver Results dialog box from appearing
    Application.Run solverProg + "!SolverFinish", 1, Array(2) 'This allows the rpt tabs to show.
 
    ' run the analysis
    Result = Application.Run(solverProg + "!SolverSolve", True)
    If Result <= 3 Then
     Result = 0  ' Solution found, optimality and constraints satisfied
     Result = 1  ' Converged, constraints satisfied
     Result = 2  ' Cannot improve, constraints satisfied
     Result = 3  ' Stopped at maximum iterations
    'MsgBox "AutoBalance/Solver found a solution.  Click Ok, and for the Breaking-even Pricing Rpt.", vbInformation, "SOLUTION FOUND"
     Beep
    Else
     Result = 4  ' Solver did not converge
     Result = 5  ' No feasible solution
    Beep
    MsgBox "AutoBalance/Solver did not converge or was unable to find feasible solution, however the Break-even Pricing Rpt will still be generated.", vbExclamation, "SOLUTION NOT FOUND"
    End If
    
    
Application.ScreenUpdating = True
    
End Sub

Open in new window

0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34234292
That looks fine. The thing I posted was just an example of the structure and not actual code. (I wish there was an Exists keyword that always worked).
It appears that the solver thing is something you have to buy? If not, where'd you get it.
0
 

Author Comment

by:RWayneH
ID: 34234648
It looks fine but does not work.. only for xl2003 and declares solver.xla  It should redefine for xl2007 and xl2010 users to do a solverProg = "Solver.xlam" but it does not.  solverProg's value should change based on the version of excel used.

I am almost tempted to try something else like, because what I have is only working in xl2003.

-R-
If Application.Version < 12 Then 'if pre-2007

solverProg = "Solver.xla"

Else 'if xl2007  or xl2010

solverProg = "Solver.xlam"

End If

Open in new window

0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34234839
If it's running this line
Application.Run "Solver.xla!Auto_Open"
without getting any errors then it has to be finding the .xla file. Excel 2007 has no trouble reading and using .xla files. Are you absolutely positive that the .xla file isn't on the Excel 2007 computer? If it's there, it will use it (which might not be a problem).

Did you buy those .xla and .xlam files or download them? If you downloaded them, post the link so I can play with them.
0
 

Author Comment

by:RWayneH
ID: 34235327
Sorry I forgot to answer the question on were I got Solver.  Solver is a third party add in for Excel.  In Excel if you goto add ins, in Tools>Options... you need to specifically tell Excel that you are going to use it.  It comes with Excel and is part of the program, it is just not used a lot by average folks.

I think, you touched on something... I am running xl2000, xl2003, xl2007 and xl2010 all on the same box... it is going to find .xla and xlam everytime...  I am actual going to try the version < 12 approach to see if I can set the solverProg = value.

Testing it now... I will let you know how it goes. -R-
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34235371
The actual files should be in
C:\Program Files\Microsoft Office\OFFICE11\Library
Or whatever number. I'd be interested to know which ones you had where.
If you want to use the xlam just check Application.Version > 11 (like you mentioned, I guess)
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34412520
I assume he got it working. I added in the solver myself and tested it on 2003, 2007, and 2010 and it all worked.
0
 
LVL 24

Expert Comment

by:broomee9
ID: 34459708
This question has been classified as abandoned and is being closed as part of the Cleanup Program.  See my comment at the end of the question for more details.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question