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