UDF to capture change in cell values

I have created a UDF to display "Success" (to keep this simple) every time one of the cells in the range D1:D4 changes.

However, 3 out of 4 of the cells contain real-time data that is refreshed every 1500 milli-secs and so every time the cells refresh the function triggers the MsgBox to display even though the values haven't changed. I only want the MsgBox to display if the values in those cells change.... can anyone help, I have spent the last 3 days trolling the net for a resolution but to no avail?

=Trigger(D1:D4)

Please Login or Register  to view this content.

Appreciation in advance.

I can't reproduce the problem, at least in Excel 2007. Every time i enter the same data in one of the D1:D4 cells I get no response, but if I enter something different, the "success" msg appears. How do you do your 1.5 sec refresh?

Thanks for responding, I've tried Application.Volatile (false) but it didn't work and I can't cache or copy the data elsewhere to compare the changes because I need the function to work across many rows and data will be changing on different rows periodically so it will always be different.

Any other suggestions?

I'm using a third party add-in that provides the real-time data and the setting on the refresh is 1500 millisecs. It's definitiely the real time data because if I remove the input from the function cell for these 3 cells the function works only when I go in manually and change the value of the fourth cell.

I would use hundreds of these and I need to avoid resource strain on the workbook. Thanks for the input though. I am seeking a way to disregard the data refresh thats triggering the function and just trigger on change in values.

Maybe I didn't explain the scenario as clearly as I could have, hopefully this helps.

I will have the same function name on each row that will read in four fields of data on the corresponding row if any of the values change. Ie. =Trigger (D3:G3), =Trigger(D4:G4), =Trigger(D5:G5), etc.

http://www.designcymru.com/images/example.jpg

If a field on row 3 changes the function will pull in the values of those fields for me to do something specific with them. If I copy the values from row 3 to compare them next time I get a change and then a value in row 5 changes it will pull in a seperate set of data and of course it will be different. If I somehow store the data by each line to compare against the next time it will be very resource intensive on the workbook given I need this function to cover 1000+ lines.

I tried Application.Volatile (False) as one of the first solutions but this doesn't work with the real time data refreshing in these cells every 1500 millisecs. This is my real issue, initiating the function on a row when one of the correspinding cells it's watching on that row actually changes value.... instead of the functon triggering across 1000 rows every 1500 millisecs.

Hope this helps...

Hi foxguy,

You'll have to excuse me, my VBA skills leave alot to be desired. I've spent some time looking at this and I think the best way would be to create a 2d array with 3 columns that will store the data in memory that I can compare against:
1st col = index (can be derived from Application.ThisCell.Row)
2nd col = YesNo (1st item in array I read in when function triggers)
3rd col = Price (2nd item in array I read in when function triggers)

Could you help me write some code to do the following please?

Create 2d public array with 3 cols and 500+ rows (can this be dynamic?)

Function Trigger (theParameter As Variant)
Dim vArr As Variant
Dim Index As Integer
Dim YesNo As String
Dim Price As Double
vArr = theParameter

'Assign row number as unique index number
Index = Application.ThisCell.Row
'Assign Price as second item read in from variant arrray
Price = vArr (1, 3)

With 2d array
If Index exists in col1 of 2d array Then
If YesNo <> corresponding yesno in col2 of 2d array Then
MsgBox "YesNo different"
yesno in col2 of 2d array = YesNo
ElseIf Price <> corresponding price in col3 of 2d array Then
MsgBox "Price different"
price in col3 of 2d array = Price
End If
ElseIf Index does not exist in 2d array Then
Create Entry in 2d array with Index(1), YesNo(2), Price(3)
End If

End With

End Function

Much appreciation for any help on this in advance.

I don't think this will work completely the way you want.
Please go back and put code tags around your code, and indent between "If...End If", "For...Next", "With....End With", etc. like this:

Please Login or Register  to view this content.

This makes it easier to read and shows where each block of code starts and ends.
This will allow you to look more critically at your code to see where there may be errors in your logic.

At first glance:
1) You Dim YesNo as string, but never assign a value.
2) theParameter will probably be a single cell passed to the Function. Assigning a single cell to a variable doesn't make an array.
3) the 1st time the Function is called the arrays will be empty so there is nothing to compare to. So you will want to create an array BEFORE the Function is called the first time and possibly use an "Undo" if it's empty when it's called in order to allow you to create the array.
4) Arrays can only be dynamic in the last dimension. I would initially advise setting up the array in the Workbook_Open() event like this vArray1 = Sheets1.Usedrange, except that would make the # of rows fixed but allow you to add columns to the array, so you would want to transpose it so that the columns are fixed and you can add rows to the array.


What you're doing is possible, and you're in the ballpark of a way to do it.

I'll wait for the refined code (with code tags and indents)

Apologies, here's my next shot. theParameter will pass in the 2 variables I need as an array from a range so the function will be: =Trigger(D1:E1)

I was thinking that given the array won't be populated the first time the function is triggered on each row, if an entry with the index (Row) doesn't exist then it will create it. As you can see from my requests in the code below, I need help on the global array front and as you suggested to initialize it before it can be used.

Please Login or Register  to view this content.

Thanks for this foxguy, I've been looking at this all morning but it seems it's not populating the array correctly.I had 2 cols of values for 50 rows and then the function in the 3rd col. It returned the time nicely and updated every time I manually changed a value but I couldn't get it further than initializing the array to test comparing the values.

I placed a break at Trigger = vArr to see how the array was populating in the Locals window and this is what it looked like:
Screenshot of Locals window
Also, is the code to compare rCells2Check values versus corresponding values in the array accurate?

Please Login or Register  to view this content.

Thanks foxguy. YesNo/Price are values I want to use when I get the msgbox displaying if values are different, getting a little ahead of myself there.

I created cellChanged = rCells2Check.Cells.Address to include in the msgbox.

I've tested the sheet and the array populates with the right # of cols perfectly, then when I change a value it takes the right route through the Else below but doesn't compare the values at all. I've spent some time looking at this but it seems this code should work. Am I missing something?

Please Login or Register  to view this content.

Also, it seems the function sometimes reads in NA/Empty values and then the actual values if a cell value changes. I've read this is common so I was planning on adding the following code to check each value in range separately and exit if this is the case right off the bat. Is this the most efficient way to do this?

Please Login or Register  to view this content.

Thank you so much foxguy for all your help on this. You're right I would need to update the one valid value if one of the values in the range was NA/Empty, what's the easiest method of doing this? I also thought if I checked these values right at the start of the function and they are both NA/Empty I could exit right away to save time given I need this function to work across 1,000 rows. Or wouldn't I notice a time difference? Minimal latency on updates is critical for me given the potential to expand rows further than 1,000...

Thanks for the ideas foxguy, this is the for the long term so it'll be worth investigating. If I compared the full 500 lines once versus each line 500 times how would I identify the row that changed, I've put some thought around what that code might look like but I'm just not getting it.

I still see the function pulling in NA values so this code doesn't seem to be working and this is a critical piece. It seems to continue through to the ElseIf statement and display the msgbox even with one of the cells in the range having an NA value, so I'm assuming what I have below is inaccurate?

Please Login or Register  to view this content.

I'm curious to see what romperstomper's suggestion yields. Could you elaborate on how I would iterate each cell and test each value against ID please?

Much appreciation in advance.

Apologies romperstomper I meant to direct this comment in my previous code to you, it didn't come across that way...

I'm curious to see what romperstomper's suggestion yields. Could you elaborate on how I would iterate each cell and test each value against ID please?

Hi foxguy, I don't see how this function is storing/comparing values in each cell. The rCell.ID (ie. $A$1) is always going to be different to the rCell.Value (ie. 256) so this would prompt me with a msgbox for every cell in the range when I make a single change. Can you please shed some light on this alternative because I'd really like to understand how this method would work.

RomperStomper, I have been using foxguys suggestion for the last month and reaching 1000+ lines using this UDF and it's doing the job but I would really like to explore your suggestion of using ID property of each cell to compare values. I'm thinking in order to make this solution scalable to 2000+ lines I need to find a more efficient method. Can you please elaborate on how I can acheive this?

Thanks