I think what you are asking is, can the function return a calculated result to
the cell containing the formula and ALSO change that formula by modifying its
arguments.
The answer is NO. Functions can only calculate results. They can't change the
underlying cell content, which is the formula. A Sub procedure could do that,
but not a function called from a formula in a worksheet cell.
You can achieve something similar via circular references and iteration.
Let's say you write this function:
Function Test(N As Long)
Application.Volatile
Test = N + 1
End Function
and in C1 you put the formula =Test(C1).
This creates a circular reference, and the formula will work ONLY if you turn
on iterations and set the number of iterations to 1. After doing that, the
value in C1 will increment by 1 every time the worksheet is recalculated. But
no matter now many times you recalculate the worksheet, the *formula* remains
unchanged. It's still =Test(C1)
In general, if a procedure (sub or function) receives arguments passed ByRef,
the procedure can change any one of those arguments. If that procedure is used
ONLY within a VBA project, you will have no problems. But if it's called,
directly or indirectly, from a worksheet formula, it will return #VALUE!
On Thu, 28 Oct 2004 06:15:06 -0700, "Sunnous"
wrote:
Hi,
I would be grateful if someone could help me with the problem have got.
I have written a macro, with my user defined functions. One of my user
defined functions is called GetData and it has two parameters Reference and
Last_Value, eg GetData(Reference, Last_Value).
I call GetData by assigning this to a cell like so (which works fine):
Cell.Formula = GetData(Reference, Last_Value)
Now within the GetData function I use the Reference Parameter to get a New
Value, which will be returned to the cell (which works fine too). But what I
wanted to know is how can I change the Last_Value Parameter to the new Value
I have, which also change Last_Value which is assigned to cell while I'm
still in the GetData function.
Thanks
Sunnous
|