One way:

Include the Sheet1 column in your function's argument list. For
instance, instead of:

=MyFunc()

Public Function MyFunc() As String
Dim rCell As Range
For Each rCell In Worksheets(1).Range("A1:A10")
If Len(rCell.Text) 0 Then _
MyFunc = MyFunc & rCell.Text
Next rCell
End Function

use:

=MyFunc2(Sheet1!A1:A10)


Public Function MyFunc2(rng As Excel.Range) As String
Dim rCell As Range
For Each rCell In rng
If Len(rCell.Text) 0 Then _
MyFunc2 = MyFunc2 & rCell.Text
Next rCell
End Function

In article ,
"Steve" wrote:

I have a VBA function that is invoked multiple times in Sheet2. The function
scans a column in Sheet1 and returns a single string item. If I change the
Sheet1 column data, how do I request Sheet2 to "recall" the function to
reflect the new Sheet1 data.