How can I force a worsheet to recall functions.
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 |
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. |
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 "JE McGimpsey" wrote in message ... 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. |
All times are GMT +1. The time now is 03:03 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com