Call GoalSeek from a user-defined-function

[Guest]

Hello,
I am trying to call GoalSeek within a function, but GoalSeek appears to
only work if called from a sub. For example, calling the function below from
a cell will not update any cells, but having a button call the function will.
Is there some contraint that GoalSeek must be triggered by a sub instead of
being called from a user-defined function?

Function TestGoalSeek(rTarget As Range, rChange As Range, dblGoal As Double)
As Double
Call DoGoalSeek(rTarget, rChange, dblGoal)
TestGoalSeek = rChange(1, 1)
End Function

Private Sub DoGoalSeek(rTarget As Range, rChange As Range, dblGoal As Double)
'Range("F11").GoalSeek Goal:=10, ChangingCell:=Range("E11")
Dim b As Boolean
b = rTarget.GoalSeek(dblGoal, rChange)
End Sub

Thanks,
Mark

 

[Tom Ogilvy]

User defined functions called from a worksheet can only return a value to
the cell in which they are entered. So it would be no surprise that
goalseek would not work from a UDF.