How can I force a worsheet to recall functions.

[Steve]

Hi,

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.

Thanks.

Steve

 

[JE McGimpsey]

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

 

[Steve]

Thanks for response. I see what you're suggesting but I want to pass in an
integer only and have the function return the string value from a known
column of the integer row. I do not want the function caller to require
knowledge of where the string comes from.

Steve