Worksheet Functions that Change Other Cells

There seems to be some kind of security that prevents a function called from
a worksheet changing other cells (on that sheet or anyother). Is there anyway
to get around that? I'm hopeing to update a range of cells when the sheet
calculates without using the "onCalcluate" event.

In fact I know that it's possible because I use a thrid party product that
does just this. How it does it is somewhat of a mistery to all of us.

To hopefully make myself clearer if I call this function:

Public Function timesTheyAreAChanging() As String
timesTheyAreAChanging = "Time"
Range("A3") = Now()
End Function

From a worksheet using:

"=timesTheyAreAChanging()"

then it returns an error result.

Yet if I comment-out the line that changes another cell then it works fine.
This is obviously a simple example what I want to do is much more complex but
the problem remains the same.


- Rm

Functions can't change the values of cells other than those calling the
function...think about it , a function returns a value...

There is a workaround, and that is to use the worksheet's events to call a
procedure that can alter cells. You have a choice. A common one is the
_Changed event that is fired when and velue is eneterd into a cell. care with
this, because it gets fired whether the user enters a value or when code
eneters a value, so you need to either disable events or devise another way
to prevent endless loops if using code.
You could also use the _Calculate event to test various cell values and thus
fire off procedure calls. Again, be wary of endless loops.

Private Sub Worksheet_Calculate()
Dim calc_mode As Long
calc_mode = Application.Calculation
Application.Calculation = xlCalculationManual
Application.EnableEvents = False

' add your tests here
Select Case Range("TestCell").Value
Case 0
Call Proc_ZERO
Case Is < 0
Call Proc_LESSTHAN
Case Is > 0
Call Proc_POSITIVE
End Select


Application.EnableEvents = True
Application.Calculation = calc_mode
End Sub




"Robert Mulroney" wrote:

>
> There seems to be some kind of security that prevents a function called from
> a worksheet changing other cells (on that sheet or anyother). Is there anyway
> to get around that? I'm hopeing to update a range of cells when the sheet
> calculates without using the "onCalcluate" event.
>
> In fact I know that it's possible because I use a thrid party product that
> does just this. How it does it is somewhat of a mistery to all of us.
>
> To hopefully make myself clearer if I call this function:
>
> Public Function timesTheyAreAChanging() As String
> timesTheyAreAChanging = "Time"
> Range("A3") = Now()
> End Function
>
> From a worksheet using:
>
> "=timesTheyAreAChanging()"
>
> then it returns an error result.
>
> Yet if I comment-out the line that changes another cell then it works fine.
> This is obviously a simple example what I want to do is much more complex but
> the problem remains the same.
>
>
> - Rm