Solved

Solver in Excel 2010

Posted on 2010-11-27
28
6,552 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 37

Expert Comment

by:TommySzalapski
Comment Utility
Are you using the xla or xlam solver?
0
 

Author Comment

by:RWayneH
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
.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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:RWayneH
Comment Utility
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
Comment Utility
opps! forgot to include the screenshot... -R-
AutoBalanceError.png
0
 
LVL 37

Expert Comment

by:TommySzalapski
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

728 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now