PDA

View Full Version : A function to monitor changes on the data



VB_Lover
05-03-2012, 07:16 AM
Hi everyone

On an excel sheet, assume we have a column contains real values..
At the end of this column, there is the average of these values

Of course, this average changes if any of its values changes

I tried to write a function that monitors any change on the average so that if the average exceeds a certain value for example 0.5, the function should print the values with their average on a file

This file should be open "for append" so that if the above condition is satisfied, the function should append the new values with their average on the same file

My function results in #VALUE!

Can we do that using a function?

The function is suitable for monitor any change compared to procedure, right?

We can replace the file media with the sheet itself, if possible

This is my the pseudo code


Function Monitor( x as double)
Done = false
If x > 0.5 then
Open the file for append
Write data to the file
Close the file
Done = true
End if
Monitor = done
End Function



Thanks for any help
VB_Lover

Kenneth Hobs
05-03-2012, 03:43 PM
Functions seldom do procedures. Their purpose is to return a value.

Most procedures are done by Subs. You can use the sheet's Calculate event and do Sub stuff or use the Change event for your =Average cell and use the Precendents of the Target range that was changed in the Intersect function to take action if a dependent cell value is changed which might change your average cell's value.