none
Excel Solver VBA Problem

    Question

  • I have a problem with programming Excel solver using vba. I have the following micro in a workbook named "Book2.xls":

    Sub Macro1()
    '
    ' Macro1 Macro
    ' Macro recorded 11/8/2005'
        SolverOk SetCell:="$H$10", MaxMinVal:=2, ValueOf:="0", ByChange:="$G$10"
        SolverAdd CellRef:="$G$10", Relation:=3, FormulaText:="$G$11"
        SolverOk SetCell:="$H$10", MaxMinVal:=2, ValueOf:="0", ByChange:="$G$10"
        SolverSolve userfinish:=True
    End Sub

    Macro1 runs fine if I open "Book2.xls" manually and either run it through "Tools\Macro\Macros...\Macro1 -> Run " or run it in the VB editor.

    I then created another workbook named "Test.xls" with the following code.

    Private Sub Workbook_Open()
        Workbooks.Open Filename:="C:\test\Book2.xls"
    End Sub

    With this code, I can open "Test.xls" manually, which then automatically opens "Book2.xls". But when I try to run Macro2 inside "Book2.xls" (either through "Tools\Macro\Macros...\Macro1 -> Run " or in the VB editor), the Solver gives me an error "Solver: An unexpected internal error occurred, or available memory was exhausted".

    Does someone have any idea of how to fix this problem?

    My original intent was to launch an Excel Workbook from within Microsoft Access and automatically run the Excel solver with the data exported from Access (since Access does not have the solver feature). I was given the same error by the solver when I tried to do so.


    Wednesday, November 09, 2005 5:03 PM

Answers

  • Hi,

    Since the problem is reported by a third-party component: Excel solver, it is out of our support team's boudaries and you would need to contact Excel Solver's support team [http://www.solver.com/suppstdvba.htm] for assistance with this issue.

    Perhaps someone else here has run across this issue?

    Sorry we can't help out with this one.
    -brenda (ISV Buddy Team)
    Friday, November 11, 2005 4:52 PM
  • Hi, not sure if it is too late, but I was having the same problem. I was calling the Solver from LabVIEW, and getting the same error. For my case, if I add the:

    Application.Run "Solver.xla!Auto_Open"

    on the first line of Macro, then I was able to boot the Solver from other app. Maybe it might work...

    tetsu-

    Monday, November 28, 2005 9:44 AM

All replies

  • Hi,

    Since the problem is reported by a third-party component: Excel solver, it is out of our support team's boudaries and you would need to contact Excel Solver's support team [http://www.solver.com/suppstdvba.htm] for assistance with this issue.

    Perhaps someone else here has run across this issue?

    Sorry we can't help out with this one.
    -brenda (ISV Buddy Team)
    Friday, November 11, 2005 4:52 PM
  • Hi, not sure if it is too late, but I was having the same problem. I was calling the Solver from LabVIEW, and getting the same error. For my case, if I add the:

    Application.Run "Solver.xla!Auto_Open"

    on the first line of Macro, then I was able to boot the Solver from other app. Maybe it might work...

    tetsu-

    Monday, November 28, 2005 9:44 AM
  • i had the same problem here when calling the solver from excel visual basis editor to optimize a portfolio, but when i applied the Application.Run line in my Sub() it was resolved.

    Thanks a million pal we are the best

    Thursday, August 24, 2006 6:17 AM
  • You guys are awesome.  I had the exact same problem.  I have a database that will open an excel file with solver and some data from the database.  The solver is hooked up to the workbook_open command and would always produce that error.  I used the application.run line before the solver sub and it worked perfectly.
    Friday, March 09, 2007 1:27 PM
  • I don't understand how you solve the problem in workbook_open command or Application.Run "Solver.xla!Auto_Open".

    When I use Application.Run "Solver.xla!Auto_Open" the excel gives error

    What should I do?
    Thanks
    Friday, March 30, 2007 5:54 AM
  • Fantastic.. A thousand thanks - I thought I was going mad!

    Wednesday, May 09, 2007 1:51 PM
  • Have you added the solver.xla to your addins? If not you can code it to add the solver.xla or you can manually add it to the workbook then save the workbook.
    Tuesday, July 10, 2007 1:30 PM
  • Has anyone come accross this problem in Excel 2007?  I have even tried running the solver functions explicitly (e.g.

    Application.Run "Solver.xlam!SolverReset" but it still fails...

     

    Any ideas...?

    Sunday, October 14, 2007 3:11 PM
  • Could you put more of your code online.

     

    Is the extension xlam new to Excel 2007?

     

    I have always used xla.

    Monday, October 15, 2007 11:21 AM
  • Yes, the xlam is the extension for addins in Excel 2007...  The code in Excel is:

     

    Sub RunSolver()

    With Application

        .Run "Solver.xlam!SolverReset"
        .Run "Solver.xlam!SolverOk", "$B$4", 1, "0", "$E$6EmbarrassedE$11"
        .Run "Solver.xlam!SolverAdd", "$E$6", 1, "10"
        .Run "Solver.xlam!SolverAdd", "$E$6", 3, "0"
        .Run "Solver.xlam!SolverAdd", "$E$7", 1, "100"
        .Run "Solver.xlam!SolverAdd", "$E$7", 3, "0"
        .Run "Solver.xlam!SolverAdd", "$E$8", 1, "1"
        .Run "Solver.xlam!SolverAdd", "$E$8", 3, "0"
        .Run "Solver.xlam!SolverAdd", "$E$9", 1, "1"
        .Run "Solver.xlam!SolverAdd", "$E$9", 3, "0"
        .Run "Solver.xlam!SolverAdd", "$E$10", 1, "1"
        .Run "Solver.xlam!SolverAdd", "$E$10", 3, "0"
        .Run "Solver.xlam!SolverAdd", "$E$11", 1, "1"
        .Run "Solver.xlam!SolverAdd", "$E$11", 3, "0"
        .Run "Solver.xlam!SolverOk", "$B$4", 1, "0", "$E$6EmbarrassedE$11"
        .Run "Solver.xlam!SolverOptions", 10000, 10000, 0.000001, False, False, 1, _
                1, 1, 0.00001, False, 0.0000001, True
        .Run "Solver.xlam!SolverOk", "$B$4", 1, "0", "$E$6EmbarrassedE$11"
        .Run "Solver.xlam!SolverSolve"
       
    End With

    End Sub

    and I run it in C# using:

     

    Excel.Application xl = null;

    Workbook wb = null;

     

    xl = new Excel.Application();

    xl.AutomationSecurity =

    Microsoft.Office.Core.MsoAutomationSecurity.msoAutomationSecurityLow;

    xl.Visible = true;

    wb = (xl.Workbooks.Open(fileName, Missing.Value,

    Missing.Value, Missing.Value, Missing.Value, Missing.Value,

    Missing.Value, Missing.Value, Missing.Value, Missing.Value,

    Missing.Value, Missing.Value, Missing.Value, Missing.Value,

    Missing.Value));

     

    xl.Run("RunSolver", Missing.Value, Missing.Value, Missing.Value, Missing.Value,

    Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,

    Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,

    Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,

    Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,

    Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,

    Missing.Value);

    Monday, October 15, 2007 8:44 PM
  • I am always getting a problem as solver cant find a solution?How to solve it?Please email me also if possible to grandhi [dot] srivardhan [add] gmail.com

    • Edited by Srivardhan G Wednesday, January 25, 2012 5:55 AM
    • Edited by danishaniModerator Thursday, January 26, 2012 4:23 AM edit e-mail to avoid spam
    Wednesday, January 25, 2012 5:54 AM