Hi Dana,
I managed to reproduce your example code and obtain results identical to
your own, so I now have a working test bed to review for understanding and
enhancement.
Your code examples have worked splendidly, and have given me much insight
into using the Solver. I am now able to perform numerical integration on the
function f(x), and to use Solver to minimise the integral by making changes
to its two parameter values. Wonderful work! This has enabled me to achieve
my initial goal in this thread. My next step will be to attempt to transplant
my iterative integrator code.
Now if only there was a way to define and use f(x) in a cell formula instead
of coding it in VBA... Oh well.
Thank you for the generousity of your time and knowledge, and patience to
assist a beginner. I salute you.
Graeme
PS I understand that Frontline's Premium Solver has the option to call VBA
code after making changes to the variable cells, then waiting on the
completion of the VBA call before it checks the target cell's value... I
think that would allow me to do this task via Sub calls, have f(x) defined in
a cell, and have values passed from VBA to cells for use by f(x), such as the
x value supplied by the integrator code. All outputs would be written to
specified cells. This would still need to have the cells hard coded in VBA,
as there's no way to specify them otherwise - I think. Now if a UDF could
write to cells, all input and output cells could be defined totally in the
UDF arguments. Life would be much easier.
PPS Further to the above. As you are a very experienced programmer in Excel
and VBA (and perhaps other areas), I'd appreciate your thoughts on this broad
issue: Is there some fundamental reason/rule for why UDFs are not allowed to,
say, write to cells? I don't mean because that is part of their
specification, but why such a specification exists in the first place? Why
does it HAVE to be this way? What/who would prevent that specification from
changing/evolving in the future, and why should it be prevented from doing
so? Taking it further, why shouldn't we have the option to use a third (yet
to be developed) structure, which has perhaps the cumulative options and
capabilities of Subs and UDFs? One such (new) structure could then do all the
things we now do with Subs and UDFs. It would simply be a matter of setting a
set of options to control or achieve the desired behaviour and outcomes.
-------------------------------
Dana DeLouis said:
Hi.
1. I did not know that Solver can (automatically) make repeated calls to the
integration code
I thought Solver would only work with cell formulas.
Yes, Solver can work this way by using custom functions.
(This is why Solver has to figure out the derivative via small samples
around the area in question)
2. Perhaps it can work this way where the code is written as a Function
instead of a Sub...?
A function returns a value, so it must be a Function.
3. Perhaps it can work this way where the Solver is called via its dialog
box, and not via the VBA code?
It can work either way.
Put 16 in A1, and 0.38 in A2.
In C1, put the function =Fx(A1,A2).
You should get the result of 1.95 E-6
With Solver, Minimize this Target value by changing the cells in A1:A2.
For this example, I didn't add any constraints, but it's always a good
idea.
Change some of the options as mentioned, and after a while, I got a
solution.
In VBA Code, see if this works for you.
You need to set a vba library reference to Solver.
Sub Demo()
[A1] = 16 'Changing Cells
[A2] = 0.38
[C1].Formula = "=Fx(A1,A2)" '<-Target Cell
'Minimize C1...
SolverOk SetCell:="C1", MaxMinVal:=2, ByChange:="A1:A2"
SolverOptions _
MaxTime:=500, _
Iterations:=100, _
Precision:=0.000000000001, _
AssumeLinear:=False, _
StepThru:=False, _
Estimates:=2, _
Derivatives:=1, _
SearchOption:=1, _
IntTolerance:=1, _
Scaling:=True, _
Convergence:=0.000000000001, _
AssumeNonNeg:=False
SolverSolve True
End Sub
'This is a very simple integration of the function from 60 to 145.
Function Fx(v1, v2)
Const k As Double = 0.01
Dim x, t
With WorksheetFunction
For x = 60 To 145 Step k
t = t + ((.Asin((x ^ 2 + 500 * v1 - v1 ^ 2) / (500 * x)) - v2) /
x) ^ 2
Next x
End With
Fx = t * k
End Function
Again, not the greatest, but something that could work for now.
--
HTH :>)
Dana DeLouis
Graeme said:
Hi Dana,
Thank you for your excellent work, and your preparedness to work through
this issue with me.
Unfortunately, I don't understand how Solver has been able to minimise the
integral, as your results show.
1. I did not know that Solver can (automatically) make repeated calls to the
integration code, and wait for it to complete its loop before checking the
new value in the target cell. I thought this was not possible. I thought
Solver would only work with cell formulas.
2. Perhaps it can work this way where the code is written as a Function
instead of a Sub...?
3. Perhaps it can work this way where the Solver is called via its dialog
box, and not via the VBA code?
Being well and truly far from an Excel expert, I'm obviously missing
something very basic, in that I'm not properly understanding the thrust of
your message. There's a small number of combinations of the points above
which I don't have a strong grasp on, as you can see.
I'd be most grateful for your clarifying advice on how you managed to obtain
the Solver results.
Graeme
<snip>