开发者

Having Excel Fields Auto Update when New Data Added

开发者 https://www.devze.com 2023-03-02 19:31 出处:网络
I have an Excel doc that starts with some fields that come from calculations done on the rows below it.To do the calculations I currently have a module with about 4 functions that loop through rows 20

I have an Excel doc that starts with some fields that come from calculations done on the rows below it. To do the calculations I currently have a module with about 4 functions that loop through rows 20 through N(first blank cell). These functions are called straight from the cells at the 开发者_如何学运维top of the sheet. The problem is that the calculations at the top are not updating whenever someone adds/removes data from the rows below. How can this be accomplished?


If your functions are Excel VBA user-defined functions called from worksheet cells, then you will get this not-recalculating behaviour if the UDF refers to cells that are not in the input parameters of the UDF.

If this is the case a good solution would be to define some Dynamic Named Ranges that expand/contract as data is added/deleted and use them as input to the function.

Another solution would be to add Application.Volatile to your UDF, but this has the undesirable side-effect that your UDFs will be recalculated at every calculation which can be painfully slow.


If I understand your question correctly, you can use Worksheet_Change event to accomplish such tasks.


In your sheet module, add a Worksheet_Change event:

Private Sub Worksheet_Change(ByVal rngChanged As Range)

    ' Call your subs and functions here

    MsgBox "You just changed something!"

End Sub

Note that the Worksheet_Change sub must have one and only one argument of type Range. Excel will make it a reference to the range that was changed by the sheet user. If you want to observe its behaviour, try placing this line in the sub:

rngChanged.Interior.ColorIndex = 4

Read more e.g. here.

0

精彩评论

暂无评论...
验证码 换一张
取 消