Solver Function - Making it Automatic

[JamesB]

Hello,

I really appreciate any help I can get on this somewhat complex issue. I
will try to be as clear as possible.

Essentially, I am currently using Solver to evaluate the repayment amount
needed to give a designated Net Present Value over a designated time period.
Unfortunately, the PMT function cannot be used as there is more than one
"lending" of funds over the term. For example, whilst the initial Present
Value (amount lent) of a 24 month term may be $1000.00, 12 months into the
term we lend another $500.00. We refer to this is "MultiDraws". Hence, we use
Solver to find the constant repayment figures neccessary to give the required
IRR over the term taking into account the future drawdowns.

The problem I have is this - we do this for COUNTLESS scenarios, and every
month when we adjust our rates (required IRR), not only do we change the
"rate" cell, but we also then have to re-run the Solver function on all the
different scenarios.

Ideally, it would be excellent if we could just alter the "rate" cell and
have the repayment figure AUTOMATICALLY solved. I guess what I looking for is
the ability to make SOLVER AUTOMATED... The conditions/parameters of solver
will be the same every time, I'm just looking for a way to avoid going Tools

Solver > Solve > OK on EVERY affected cell (an extremely drawn-out process).

Surely there is a simple way to make the solving happen automatically, as a
normal function would?

I really, really really appreciate any assistance on this matter.

Regards,

James B

 

[Scott]

WIthout putting much thought into this, I'd recommend that the next
time you change the rate, you record a macro of you doing all the
alterations.

Tools -> Macro -> Record New Macro
Store the macro in the Workbook you're working on and rename it to
something suitable.

Once you've performed all the steps, stop recording.

Next time you need to use the macro, either go Tools -> Macro -> Macros
and choose the one you recorded. (Or hit Alt-F8 and choose it)

I'd recommend you save your file twice... and using the backup copy run
your macro with a new IRR to ensure that it does what you expect it to
do.

If you need more flexibility than that, we'll need more details I
think.

Scott

 

[Dave F]

Hi,
See this: http://support.microsoft.com/kb/843304

Dave

 

[Dana DeLouis]

Hi. You just need to set a reference to Solver in vba.
Here are the steps...

http://support.microsoft.com/kb/213689/en-us

Good luck.

[The error message is "Sub or Function not defined"]

--
Dana DeLouis
Windows XP & Office 2003

Hey guys,

Thanks for your assistance.

I'm having some problems still with the Macro thing (first shot at it). I
basically just "started recording a macro", performed the solver task
(i.e.
Tools > Solver > Set Target Cell etc. > Solve) then "stopped recording". I
saved the worksheet, then tried to run the macro and got the following:

<img src="http://img136.imageshack.us/img136/8928/excelscreenshotog7.png">
Does this make sense to you?
Apologies for my naivety.
Again, appreciate your help!

<snip>

 

[JamesB]

Thank you so much.

I'm ALOT closer to getting this thing nailed than I was a week ago, and I
really appreciate the assistance from both of you.

I've got it all "working", but there are two (hopefully simple) tweaks I'd
like to make before I implement the spreadsheet full-time:

1) As I explained before, the macro I recorded performs about 16 "solver"
functions. You know how when you use Solver, after the equation has been
"solved", you get that annoying pop-up window saying something along the
lines of "Do You Wish To Keep Solvers Solutions" etc.. The macro works
PERFECTLY, however, after it is run I have to click "OK" on 16 of these silly
pop-up windows to accept the changes! Is there any way I can avoid this
happening?

2) Alot of the data used in the solver equation is tucked away on numerous
spreadsheets that I don't particularly want to "see". I recorded the macro,
then hid any unneccessary spreadsheets - unfortunately, this includes
worksheets with some of the data required by solver. For some reason, if
these spreadsheets are hidden, solved adopts some kind of "bug" halfway
through calculation and the macro doesn't work. Again, is there any way to
hide these worksheets but still have the macro work?

Appreciate any further info you can share.

THANKS!!!

Hi. You just need to set a reference to Solver in vba.
Here are the steps...

http://support.microsoft.com/kb/213689/en-us

Good luck.

[The error message is "Sub or Function not defined"]

--
Dana DeLouis
Windows XP & Office 2003

Hey guys,

Thanks for your assistance.

I'm having some problems still with the Macro thing (first shot at it). I
basically just "started recording a macro", performed the solver task
(i.e.
Tools > Solver > Set Target Cell etc. > Solve) then "stopped recording". I
saved the worksheet, then tried to run the macro and got the following:

<img src="http://img136.imageshack.us/img136/8928/excelscreenshotog7.png">
Does this make sense to you?
Apologies for my naivety.
Again, appreciate your help!

<snip>

 

[Dana DeLouis]

Hi.
(1)
' Solve w/ no prompts
SolverSolve True
' Keep Final values
SolverFinish 1

(2)
Solver doesn't work well with data spread out on other sheets.
Before making big changes, try removing any spaces in any sheet names.
Be sure to remove any spaces in the workbook name also.
Not guaranteed here, but it may help.
--
HTH :>)
Dana DeLouis
Windows XP & Office 2003

Thank you so much.

I'm ALOT closer to getting this thing nailed than I was a week ago, and I
really appreciate the assistance from both of you.

I've got it all "working", but there are two (hopefully simple) tweaks I'd
like to make before I implement the spreadsheet full-time:

1) As I explained before, the macro I recorded performs about 16 "solver"
functions. You know how when you use Solver, after the equation has been
"solved", you get that annoying pop-up window saying something along the
lines of "Do You Wish To Keep Solvers Solutions" etc.. The macro works
PERFECTLY, however, after it is run I have to click "OK" on 16 of these
silly
pop-up windows to accept the changes! Is there any way I can avoid this
happening?

2) Alot of the data used in the solver equation is tucked away on numerous
spreadsheets that I don't particularly want to "see". I recorded the
macro,
then hid any unneccessary spreadsheets - unfortunately, this includes
worksheets with some of the data required by solver. For some reason, if
these spreadsheets are hidden, solved adopts some kind of "bug" halfway
through calculation and the macro doesn't work. Again, is there any way to
hide these worksheets but still have the macro work?

Appreciate any further info you can share.

THANKS!!!

Hi. You just need to set a reference to Solver in vba.
Here are the steps...

http://support.microsoft.com/kb/213689/en-us

Good luck.

[The error message is "Sub or Function not defined"]

--
Dana DeLouis
Windows XP & Office 2003

Hey guys,

Thanks for your assistance.

I'm having some problems still with the Macro thing (first shot at it).
I
basically just "started recording a macro", performed the solver task
(i.e.
Tools > Solver > Set Target Cell etc. > Solve) then "stopped
recording". I
saved the worksheet, then tried to run the macro and got the following:

<img
src="http://img136.imageshack.us/img136/8928/excelscreenshotog7.png">
Does this make sense to you?
Apologies for my naivety.
Again, appreciate your help!

<snip>

 

[Barkleyh]

This may not be elegant, but can you save each of the 16 cases as a
"Scenario" for Scenario Manager, and then put the Scenario Manager button on
the toolbar, and then you can just choose the Scenario you want to run from
the toolbar button (it's a drop-down list).

--HB

Hi.
(1)
' Solve w/ no prompts
SolverSolve True
' Keep Final values
SolverFinish 1

(2)
Solver doesn't work well with data spread out on other sheets.
Before making big changes, try removing any spaces in any sheet names.
Be sure to remove any spaces in the workbook name also.
Not guaranteed here, but it may help.
--
HTH :>)
Dana DeLouis
Windows XP & Office 2003

Thank you so much.

I'm ALOT closer to getting this thing nailed than I was a week ago, and I
really appreciate the assistance from both of you.

I've got it all "working", but there are two (hopefully simple) tweaks I'd
like to make before I implement the spreadsheet full-time:

1) As I explained before, the macro I recorded performs about 16 "solver"
functions. You know how when you use Solver, after the equation has been
"solved", you get that annoying pop-up window saying something along the
lines of "Do You Wish To Keep Solvers Solutions" etc.. The macro works
PERFECTLY, however, after it is run I have to click "OK" on 16 of these
silly
pop-up windows to accept the changes! Is there any way I can avoid this
happening?

2) Alot of the data used in the solver equation is tucked away on numerous
spreadsheets that I don't particularly want to "see". I recorded the
macro,
then hid any unneccessary spreadsheets - unfortunately, this includes
worksheets with some of the data required by solver. For some reason, if
these spreadsheets are hidden, solved adopts some kind of "bug" halfway
through calculation and the macro doesn't work. Again, is there any way to
hide these worksheets but still have the macro work?

Appreciate any further info you can share.

THANKS!!!

Hi. You just need to set a reference to Solver in vba.
Here are the steps...

http://support.microsoft.com/kb/213689/en-us

Good luck.

[The error message is "Sub or Function not defined"]

--
Dana DeLouis
Windows XP & Office 2003


Hey guys,

Thanks for your assistance.

I'm having some problems still with the Macro thing (first shot at it).
I
basically just "started recording a macro", performed the solver task
(i.e.
Tools > Solver > Set Target Cell etc. > Solve) then "stopped
recording". I
saved the worksheet, then tried to run the macro and got the following:

<img
src="http://img136.imageshack.us/img136/8928/excelscreenshotog7.png">
Does this make sense to you?
Apologies for my naivety.
Again, appreciate your help!

<snip>