Excel Solver Programming Error

[Derek Chen]

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.

[Alf]

You could try this link http://support.microsoft.com/kb/843304

and see if you find any hints to your problem.

Did you set a reference to Solver in VBA? Instruction on how-to in this link.

Alf

[Tushar Mehta]

Thanks for the detailed description. You need to call the
SOLVER.Auto_open method before doing anything else.

*Hopefully,* it will solve your problem.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>, "=?
Utf-8?B?RGVyZWsgQ2hlbg==?=" <Derek [email protected]>
says...
> 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.
>
>
>
>
>

[monir]

Hi;

Here is a similar problem, but resulting from a slightly different situation.

1. Files one.xls and two.xls have several Solver vba macros each. Form.xls
is simple user form to open either of the two files. Each works fine
individually and in conjunction with the user form on computer A (IBM
desktop, Excel 2003, Win XP, 2 GHz)

2. On a new computer (Toshiba, Excel 2003, Win XP, 3 GHz), files one.xls
and two.xls work fine individually, with all Solver macros run with no
problem.
But, when either is opened via the userform Form.xls, and I try to run any
of the Solver macros, the following error message appears:
"Microsoft Excel Solver: an unexpected internal errored, or available
memory was exhausted. OK".

3. (Reference to Solver is set correctly in vba.) In the same session, if
I manually load any one of the Solver models and run Solver manually (just
once), the Solver error message DOES NOT re-appear, and I'm able to run any
of the Solver macros in the opened file. Once I end the session and re-open
the file via the Form.xls again and try to run any of the Solver macros, the
Solver error message re-appears!!

4. Is it still your suggestion to call "Solver.Auto_Open method" ?? despite
1. above. And if so, where would I use it ?? in the files one.xls and
two.xls or Form.xls ?

Thank you kindly.

"Tushar Mehta" wrote:

> Thanks for the detailed description. You need to call the
> SOLVER.Auto_open method before doing anything else.
>
> *Hopefully,* it will solve your problem.
>
> --
> Regards,
>
> Tushar Mehta
> www.tushar-mehta.com
> Excel, PowerPoint, and VBA add-ins, tutorials
> Custom MS Office productivity solutions
>
> In article <[email protected]>, "=?
> Utf-8?B?RGVyZWsgQ2hlbg==?=" <Derek [email protected]>
> says...
> > 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.
> >
> >
> >
> >
> >
>

[monir]

1. Here is a solution which works for me. For computer (B), include the
statement:
.......Application.Run "Solver.xla!Auto_Open
in files one.xls and two.xls in their respective Workbook_Open() events.
This will ensure that the above statement is invoked before the use of any
Solver vba macro

2. It is still a mystery to me, why such statement is required to run the
procedure successfully on computer (B), while the same files work fine and
error-free on computer (A). Both computer (A) and computer (B) have XL 2003,
Win XP, 512 MB.

3. It is possible that the difficulty encountered in my particular
situation (described in my above post) is the consequence of not setting
"identical" options/settings on both computers!

4. Another possibility, as Tushar mentioned in his reply (MrExcel
Discussion Forum), it is an intermittent or sporadic problem that is
associated with running XL 2003 in Win XP environment.



"monir" wrote:

> Hi;
>
> Here is a similar problem, but resulting from a slightly different situation.
>
> 1. Files one.xls and two.xls have several Solver vba macros each. Form.xls
> is simple user form to open either of the two files. Each works fine
> individually and in conjunction with the user form on computer A (IBM
> desktop, Excel 2003, Win XP, 2 GHz)
>
> 2. On a new computer (Toshiba, Excel 2003, Win XP, 3 GHz), files one.xls
> and two.xls work fine individually, with all Solver macros run with no
> problem.
> But, when either is opened via the userform Form.xls, and I try to run any
> of the Solver macros, the following error message appears:
> "Microsoft Excel Solver: an unexpected internal errored, or available
> memory was exhausted. OK".
>
> 3. (Reference to Solver is set correctly in vba.) In the same session, if
> I manually load any one of the Solver models and run Solver manually (just
> once), the Solver error message DOES NOT re-appear, and I'm able to run any
> of the Solver macros in the opened file. Once I end the session and re-open
> the file via the Form.xls again and try to run any of the Solver macros, the
> Solver error message re-appears!!
>
> 4. Is it still your suggestion to call "Solver.Auto_Open method" ?? despite
> 1. above. And if so, where would I use it ?? in the files one.xls and
> two.xls or Form.xls ?
>
> Thank you kindly.
>
> "Tushar Mehta" wrote:
>
> > Thanks for the detailed description. You need to call the
> > SOLVER.Auto_open method before doing anything else.
> >
> > *Hopefully,* it will solve your problem.
> >
> > --
> > Regards,
> >
> > Tushar Mehta
> > www.tushar-mehta.com
> > Excel, PowerPoint, and VBA add-ins, tutorials
> > Custom MS Office productivity solutions
> >
> > In article <[email protected]>, "=?
> > Utf-8?B?RGVyZWsgQ2hlbg==?=" <Derek [email protected]>
> > says...
> > > 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.
> > >
> > >
> > >
> > >
> > >
> >