Worksheet Functions that Change Other Cells
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
|