ExcelBanter - Solver Function - Making it Automatic

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Solver Function - Making it Automatic (https://www.excelbanter.com/excel-worksheet-functions/121584-solver-function-making-automatic.html)

JamesB

Solver Function - Making it Automatic

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

ExcelBanter AI

Answer: Solver Function - Making it Automatic

Hi James,

I understand your frustration with having to manually run Solver on all the different scenarios every time you adjust your rates. The good news is that there is a way to make Solver automated using VBA (Visual Basic for Applications).

Here are the steps to create a macro that will run Solver automatically:

Open the workbook that contains your Solver scenarios.
Press Alt + F11 to open the Visual Basic Editor.
In the Project Explorer window, double-click on the sheet that contains your Solver scenarios.
In the code window, paste the following code:

Formula:

Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1")) Is Nothing Then SolverOk SetCell:="$B$1", MaxMinVal:=3, ValueOf:=0, ByChange:="$A$1", _ Engine:=1, EngineDesc:="GRG Nonlinear" SolverSolve UserFinish:=True End If End Sub
In the code, replace "A1" with the cell reference of the cell that contains your rate.
Replace "B1" with the cell reference of the cell that contains your repayment figure.
Save the workbook and close the Visual Basic Editor.

Now, every time you change the rate in the designated cell, the macro will automatically run Solver and update the repayment figure in the designated cell.

Scott

Solver Function - Making it Automatic

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

JamesB wrote:
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

Dave F

Solver Function - Making it Automatic

You could also attach the macro to a button which you can place on your
worksheet directly. Then when you click the button the macro will run, which
triggers the Solver tool.

To get a button on the worksheet, show the Forms toolbar, draw the button,
right-click on the button icon, and assign the macro you recorded to the
button via the dialog box that appears when you right-click.

Save everything and see how it works.

As Scott suggests, it's a good idea to experiment with this on a backup copy
of your workbook until you're sure that XL is doing what you want it to do.

Dave
--
Brevity is the soul of wit.

"Scott" wrote:

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

JamesB wrote:
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

JamesB

Solver Function - Making it Automatic

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!

Cheers,

James.

"Dave F" wrote:

You could also attach the macro to a button which you can place on your
worksheet directly. Then when you click the button the macro will run, which
triggers the Solver tool.

To get a button on the worksheet, show the Forms toolbar, draw the button,
right-click on the button icon, and assign the macro you recorded to the
button via the dialog box that appears when you right-click.

Save everything and see how it works.

As Scott suggests, it's a good idea to experiment with this on a backup copy
of your workbook until you're sure that XL is doing what you want it to do.

Dave
--
Brevity is the soul of wit.

"Scott" wrote:

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

JamesB wrote:
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

Dave F

Solver Function - Making it Automatic

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

Dave
--
Brevity is the soul of wit.

"JamesB" wrote:

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!

Cheers,

James.

"Dave F" wrote:

You could also attach the macro to a button which you can place on your
worksheet directly. Then when you click the button the macro will run, which
triggers the Solver tool.

To get a button on the worksheet, show the Forms toolbar, draw the button,
right-click on the button icon, and assign the macro you recorded to the
button via the dialog box that appears when you right-click.

Save everything and see how it works.

As Scott suggests, it's a good idea to experiment with this on a backup copy
of your workbook until you're sure that XL is doing what you want it to do.

Dave
--
Brevity is the soul of wit.

"Scott" wrote:

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

JamesB wrote:
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

Dana DeLouis

Solver Function - Making it Automatic

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

"JamesB" wrote in message
...
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

Solver Function - Making it Automatic

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!!!

"Dana DeLouis" wrote:

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

"JamesB" wrote in message
...
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

Solver Function - Making it Automatic

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

"JamesB" wrote in message
...
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!!!

"Dana DeLouis" wrote:

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

"JamesB" wrote in message
...
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

Solver Function - Making it Automatic

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

"Dana DeLouis" wrote:

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

"JamesB" wrote in message
...
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!!!

"Dana DeLouis" wrote:

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

"JamesB" wrote in message
...
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

All times are GMT +1. The time now is 11:32 AM.