Function does not recalculate
Posted by idoia on August 03, 2001 1:31 PM
Hi,
I have created a function in VB for Excel. It works well, but it does not recalculate automatically.
The function uses values that are offset to the cells in the input range (argument), like this:
Function Weighted(Rg)
For Each cell In Rg
If (cell.Value * cell.Offset(0, 1).Value) <> 0 Then
TotalDen = TotalDen + cell.Offset(0, 1).Value
...
The problem is, when numbers in the offset column change, the formula does not recalculate. it does not recalculate even with F9. I need to edit the cell (F2) and hit "enter" for it to work.
Is there any way to solve this, without having to set the offset range as an additional argument?
Thanks
Posted by Cory on August 03, 2001 2:08 PM
Have you tried putting the trigger for the funtion in the WorkSheet_Change event handler? Then every time you change the value of the cell, the function is run...
Cory
Posted by Ivan F Moala on August 03, 2001 2:38 PM
Try putting [Application.Volatile]
after your Function statement, eg
Function whatever(abc as String)as String
Application.Volatile
'your code
End Function
