Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Solver Runs from Code, but not from UI (XL2003)

    Hi,

    I have encountered some strange behaviour. I have a spreadsheet within which I have been manually running Solver, exploring solutions. I have also coded the whole process to run from a cmdbutton. Until yesterday, both were working fine. Now, the solver routine runs perfectly from the VBA code, but I am no longer able to access Solver from the Tools/Solver menu. If I create a new, blank file, Tools/Solver does bring up the Solver Parameters dialog, but it refuses to appear in my original spreadsheet. Solver is listed in the available (checked add-ins) and because I am calling it from code, it is also referenced in the VBA project.

    I have experienced the reverse of this with Excel 2000, but this was a bug documented in an MS KB article where solver wouldn't run from code, but it was available in the UI. If I recall correctly, it was related to O2k SP1a and I think SP3 fixed that problem.

    Any ideas gratefully accepted,

    Thanks,

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Solver Runs from Code, but not from UI (XL2003)

    Do you mean that
    a) The Tools | Solver menu item is absent from this particular workbook, or
    [img]/forums/images/smilies/cool.gif[/img] The Tools | Solver menu item is present, but it doesn't do anything?

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Solver Runs from Code, but not from UI (XL2003)

    Hi Hans,

    The Tools/Solver menu item is there and available, but when I select it, absolutely nothing happens.

    I have tried deselecting it and then I normally get a warning because it's referenced in the VBA project. I then close Excel and re-open it and Solver has been removed from the list of available add-ins. When I reload Solver, the same thing happens - from code it's OK, but from the UI, nothing happens,

    Thanks,

    Adrian

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Solver Runs from Code, but not from UI (XL2003)

    It would seem that there is something wrong with the installation of Solver. You can try Help | Detect and Repair from within Excel. If that doesn't help, try removing Solver completely, then reinstalling it. You will need to have the Office 2003 CD at hand for this.

    - Quit Excel (and other Office apps).
    - Select Start | Control Panel.
    - Open Add/Remove Programs.
    - Click on Microsoft Office 2003.
    - Click Change.
    - Select Add/Remove Components (or something similar), then click Next.
    - Expand Excel > Add-Ins > Solver.
    - Select Not Available in the dropdown list.
    - Click Update.
    - When Solver has been removed, repeat the process, but now set Run from this computer for Solver.

  5. #5
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Solver Runs from Code, but not from UI (XL2003)

    Thanks for the suggestion, I will try it.

    I think it is more likely that there is something wrong with the actual Excel file, since it displays this behaviour on my work PC and both my PC's at home.

    I managed to work around the problem by calling the relevant Solver procedure directly from some code :

    <font color=red>Sub ShowSolverDialog()
    'had to implement this work around because the "Solver" menu item stopped functioning !!

    ' Run the Solver macro to display the main Solver dialog box.
    Application.Run ("Solver.xla!Main")

    End Sub</font color=red>

    I can now run Solver manually by assigning the above to a cmdButton. Not ideal, but ultimately the entire Solver workings will run from code in my app, so the users of this particular file won't need to run it manually.

    Regards,

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Solver Runs from Code, but not from UI (XL2003)

    You could try saving the workbook as a HTML file, then opening the HTML file and saving it as a workbook again. Can you run the solver in the new workbook?

  7. #7
    Lounger
    Join Date
    Dec 2005
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Solver Runs from Code, but not from UI (XL2003)

    Is there a change in the code to make this code for Excel 2002? When I initially ran the code, it couldn't find the solver application (it was installed). Once I added the full path to "solver.xla", I get an application error when I run it.

    Thanks!

    Larry

  8. #8
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Solver Runs from Code, but not from UI (XL2003)

    I don't see why this code shouldn't work in Excel 2002.

    In your immediate window, type "? Application.LibraryPath". I get "C:Program FilesMicrosoft OfficeOFFICE11LIBRARY". WIthin the Library folder is the Solver folder which contains the Solver.xla and Solver32.dll files.

    I think your system will probably give you something like "C:Program FilesMicrosoft OfficeOFFICE10LIBRARY". If your solver files are present in the Solver folder in the library, then I don't see why the code shouldn't work for you.

    Regards,

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Solver Runs from Code, but not from UI (XL2003)

    It shouldn't be necessary to include the full path. Are you absolutely sure that the check box for "Solver add-in" in Tools | Add-ins... has been ticked? If so, SOLVER (SOLVER.XLA) should be listed in the Project Explorer in the Visual Basic Editor - see screenshot below.

  10. #10
    Lounger
    Join Date
    Dec 2005
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Solver Runs from Code, but not from UI (XL2003)

    Thanks to both if you - not sure why it works now and not before.....but it works....I lowered the security settings to Medium, but then it worked after I raised it again. I ran it from both the local workbook and personal macro workbook. Who knows......

    Thanks again!

    Larry

Posting Permissions

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