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.
精彩评论