Solver in a macro

  • Thread starter Thread starter Paul Mennen
  • Start date Start date
P

Paul Mennen

The last column in my spreadsheet is the solution to a quartic equation.
As you may know the closed form solution to a quartic is quite complex,
so I decided it would be easier to just use the solver function to compute
the root I needed numerically. Since there are 60 rows in my spreadsheet,
I had to manually click on Solver in the tools menu and manually select
the changeable and target cells 60 times. This works fine, but as you can
imagine is quite laborious. Once would be tolerable, but now I find I might
need to change some of the constants involved and run the whole thing again.
Clearly this is a case where some automation is needed.

Not being an excel expert (by a long stretch) I did the most obvious thing.
I turned on macro recording and proceeded to run the solver for the first
three rows of my spreadsheet. (I figured I could use an editor to add
the remaining 57 rows to the macro.)

The macro that is generated (which I guess is in visual basic) looks like:

***************************************************************************
Sub TASerr()
'
' TASerr Macro
' Minimize TASerr
'

'
SolverOk SetCell:="$S$7", MaxMinVal:=3, ValueOf:="0", ByChange:="$R$7"
SolverSolve
SolverOk SetCell:="$S$8", MaxMinVal:=3, ValueOf:="0", ByChange:="$R$8"
SolverSolve
SolverOk SetCell:="$S$9", MaxMinVal:=3, ValueOf:="0", ByChange:="$R$9"
SolverSolve
End Sub
***************************************************************************

Before even trying to expand the macro to include all 60 rows, I just tried
running it. When I do a Microsoft Visual Basic window pops up with the
following: Compile error- Sub or function not defined.

(Also the first instance of "SolverOk" is highlighted, so perhaps that
is the function it is complaining about).

Can anyone tell me why my macro won't run? Or if I am using a really
lame method of automatically running the solver, please feel free to
suggest an easier way. It would be nice if the whole 60 element column
updated automatically when I changed one of the dependent constants, but
having to manually run the macro each time is not a huge drawback.

Thanks in advance for any advice you can offer.

~Paul Mennen
 
Oh, and I forgot to say that I am using Excel 2000 (9.0.4402 SR1).
Don't know if that makes a difference, but I figure I should have
mentioned this.

Thanks.
~Paul
 
Hi Paul,

Here's one way to do it. To use macro, you must do the following:

1) Copy the macro into a code module in the workbook that you need to run
Solver on.

2) In the Visual Basic Editor choose Tools/References from the menu and put
a check mark beside the SOLVER entry.

3) The sheet where solver is being run must be active before you run the
macro.

Sub RunSolver()
Dim rngCell As Range
Dim rngTable As Range
Set rngTable = ActiveSheet.Range("S7").Resize(60)
For Each rngCell In rngTable
SolverOk SetCell:=rngCell.Address, MaxMinVal:=3, _
ValueOf:="0", ByChange:=rngCell.Offset(0, -1).Address
SolverSolve True
Next rngCell
End Sub

Note that I've set it up to not display the dialog after each run. If you
want to display the dialog change the line:

SolverSolve True

to

SolverSolve False

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *
 
2) In the Visual Basic Editor choose Tools/References from the menu and put
a check mark beside the SOLVER entry.

Thanks Rob. I didn't understand your visual basic code 100% and I
eventually went back to my primitive approach. However step 2 above
is essential as I wouldn't have made it to square one otherwise.
SolverSolve True

Knowing about the argument to suppress the dialog box would
have been my next stumbling block as I would not have wanted
to hit the OK button 60 times every time I ran the macro.
But you nipped that problem in the bud.

Thanks for the tips!

~Paul
 
Glad it helped you out. The only difference between my code and your
code was that I created a loop that would start at the top row and
automatically step down one row at a time, running solver once on each row,
for sixty rows. There's nothing inherently wrong with writing out a line of
code for each row.

Yes I could see the general idea, but I was a little scared
away since I didn't understand the syntax exactly. I eventually
tried your code anyway, and it worked perfectly well. And of course
in case I reorganize my spreadsheet, your code is going to be much
easier to modify in-line coded routine.

Thanks again.

~Paul
 
Back
Top