I want to write a VB function (not a sub) that does something similar
to Solver (but nothing nearly as sophisticated or generalized) insofar
as it finds the "best" result by trial and error. The algorithm is
tailored to the purpose of the worksheet.

Ideally, the function would modify some cells in the active worksheet
(identified by name or passed as arguments) and let Excel automatic
calculation derive the result in a "target" cell, which depends on the
modified the cells directly or indirectly, just as if I were manually
modifying the cells manually. (Ideally, the "target" cell is the same
cell that contains the function call.)

This works as a macro (sub). But it appears that a UDF (function) is
not permitted to modify cells of either the active worksheet or even
another worksheet.

I just want to confirm that this cannot be done in a UDF. Or is there
some way that I can do it?

I know that I could duplicate all of the formula calculations within
the UDF. But I do not want to hardcode the formulas in VB. Instead,
I want the flexibility of changing the formulas in one place, namely
the worksheet.

I believe I could use Evaluate to execute each formula within the VB
function. I will work with that idea. But I am concerned that it
will be a lot slower than Excel.

Are there good alternatives that I have overlooked?

UDFs cannot directly change cells, they can only return a value. A UDF can
change multiple cells with the help of an Event Macro.

Public triggger As Boolean
Public carryover As Variant
Function reallysimple(r As Range) As Variant
triggger = True
reallysimple = r.Value
carryover = r.Value / 99
End Function


In worksheet code:


Private Sub Worksheet_Calculate()
If Not triggger Then Exit Sub
triggger = False
Range("C1").Value = carryover
End Sub


Whenever reallysimple is called it returns a value. It also sets the global
flag triggger and the global variable carryover.


Now the event macro runs whenever the worksheet is calculated. As soon as
it sees that triggger has become true, it knows that reallysimple has been
executed and there is work to do. It clears triggger and moves carryover to
cell C1.


So even though a UDF can only directly change a single cell, it can
indirectly change many cells (with a little help from its friends).



--
Gary''s Student - gsnu2007d


"(E-Mail Removed)" wrote:

> I want to write a VB function (not a sub) that does something similar
> to Solver (but nothing nearly as sophisticated or generalized) insofar
> as it finds the "best" result by trial and error. The algorithm is
> tailored to the purpose of the worksheet.
>
> Ideally, the function would modify some cells in the active worksheet
> (identified by name or passed as arguments) and let Excel automatic
> calculation derive the result in a "target" cell, which depends on the
> modified the cells directly or indirectly, just as if I were manually
> modifying the cells manually. (Ideally, the "target" cell is the same
> cell that contains the function call.)
>
> This works as a macro (sub). But it appears that a UDF (function) is
> not permitted to modify cells of either the active worksheet or even
> another worksheet.
>
> I just want to confirm that this cannot be done in a UDF. Or is there
> some way that I can do it?
>
> I know that I could duplicate all of the formula calculations within
> the UDF. But I do not want to hardcode the formulas in VB. Instead,
> I want the flexibility of changing the formulas in one place, namely
> the worksheet.
>
> I believe I could use Evaluate to execute each formula within the VB
> function. I will work with that idea. But I am concerned that it
> will be a lot slower than Excel.
>
> Are there good alternatives that I have overlooked?
>

hi
confirmed. UDF and other built in functions return values, they cannot
perform actions like modify anything.
my suggestion is to stick with solver.

Regards
FSt1

"(E-Mail Removed)" wrote:

> I want to write a VB function (not a sub) that does something similar
> to Solver (but nothing nearly as sophisticated or generalized) insofar
> as it finds the "best" result by trial and error. The algorithm is
> tailored to the purpose of the worksheet.
>
> Ideally, the function would modify some cells in the active worksheet
> (identified by name or passed as arguments) and let Excel automatic
> calculation derive the result in a "target" cell, which depends on the
> modified the cells directly or indirectly, just as if I were manually
> modifying the cells manually. (Ideally, the "target" cell is the same
> cell that contains the function call.)
>
> This works as a macro (sub). But it appears that a UDF (function) is
> not permitted to modify cells of either the active worksheet or even
> another worksheet.
>
> I just want to confirm that this cannot be done in a UDF. Or is there
> some way that I can do it?
>
> I know that I could duplicate all of the formula calculations within
> the UDF. But I do not want to hardcode the formulas in VB. Instead,
> I want the flexibility of changing the formulas in one place, namely
> the worksheet.
>
> I believe I could use Evaluate to execute each formula within the VB
> function. I will work with that idea. But I am concerned that it
> will be a lot slower than Excel.
>
> Are there good alternatives that I have overlooked?
>

FSt1 wrote:
> hi
> confirmed. UDF and other built in functions return values, they cannot
> perform actions like modify anything.
> my suggestion is to stick with solver.
>
> Regards
> FSt1
>

More precisely, functions called directly from a worksheet can't modify
the Excel environment; if called by a Sub procedure, they can.

Alan Beban

not in the sense i was speaking. when used in code, they are(to me) no longer
true worksheet functions but as part of the code. but that is getting off the
subject.

regards
FSt1

"Alan Beban" wrote:

> FSt1 wrote:
> > hi
> > confirmed. UDF and other built in functions return values, they cannot
> > perform actions like modify anything.
> > my suggestion is to stick with solver.
> >
> > Regards
> > FSt1
> >
>
> More precisely, functions called directly from a worksheet can't modify
> the Excel environment; if called by a Sub procedure, they can.
>
> Alan Beban
>

FSt1 wrote:
> not in the sense i was speaking. when used in code, they are(to me) no longer
> true worksheet functions but as part of the code. but that is getting off the
> subject.
>
> regards
> FSt1
>
> "Alan Beban" wrote:
>
>
>>FSt1 wrote:
>>
>>>hi
>>>confirmed. UDF and other built in functions return values, they cannot
>>>perform actions like modify anything.
>>>my suggestion is to stick with solver.
>>>
>>>Regards
>>>FSt1
>>>
>>
>>More precisely, functions called directly from a worksheet can't modify
>>the Excel environment; if called by a Sub procedure, they can.
>>
>>Alan Beban
>>
UDF's are not worksheet functions. They are Function procedures that can
be called with VBA code or from a worksheet.

Alan Beban