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)

Function Trigger (theParameter As Variant)
      MsgBox "Success"
End Function

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?

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.

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?

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...

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?

Function Trigger(ByVal rCells2Check As Range) As Date

    Application.Volatile False
    
    If rCells2Check.Rows.Count > 1 Then
        MsgBox "This Function Will Only Work On 1 Row"
        Exit Function
    End If

    'create variable that will will hold values between triggers
    Static vArr As Variant

    Dim Index As Integer
    Dim YesNo As String
    Dim Price As Double
    Dim lCols As Long
    Dim lCol As Long

    'Tell VBA where to go if an error occurrs
    On Error GoTo EF

    'Turn off features that slow things down
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    'Ignore next error
    On Error Resume Next
    'check to see if the array exists
    lCols = UBound(vArr, 1)
    'reset On Error
    On Error GoTo EF

    'Assign row number as unique index number and YesNo and Price values from range
    Index = rCells2Check.Row
    YesNo = rCells2Check(1).Value
    Price = rCells2Check(2).Value

    If lCols <> rCells2Check.Columns.Count Then
        'either vArr has not been initialized
        'or the user has changed the # of columns to check

        ReDim vArr(1 To 1, 1 To 1)
        'just create a blank array to get things started
    End If
    
    If UBound(vArr, 2) < Index Then
        'add a new column to array if this data row has not been created yet
        ReDim Preserve vArr(1 To lCols, 1 To Index)
        'put the values from rCells2Check into the correct column in array
        For lCol = 1 To lCols
            vArr(lCol, Index) = rCells2Check.Columns(lCol).Value
        Next
        'Trigger = vArr
    Else
        'vArr was already initilized so check values
            If rCells2Check.Columns(lCol).Value <> vArr(lCol, Index) Then
                MsgBox "Value in one of the cells has changed"
            End If
    End If
    
EF:
    'Turn Features back on
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With

    'return the data & time to put into the cell with "=Trigger(.....)" in it.
    Trigger = Now
End Function

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?

    
Else
    'vArr was already initilized so check values
       If rCells2Check.Columns(lCol).Value <> vArr(lCol, Index) Then
               MsgBox "Value in " & changedCell & " has changed"
       End If
End If

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?

If IsEmpty(rCells2Check(1).Value) Or IsEmpty(rCells2Check(2).Value) Then
    Exit Function
End If

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?

If (r.value = vbError) or (IsEmpty(r.Value) Then
     GoTo EF

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