Thread: 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?

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:

Function Trigger (theParameter As Variant
    Dim vArr As Variant
    '  code
    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"
           ElseIf Price <> corresponding price in col3 of 2d array Then
                MsgBox "Price different"
           End If
    End With
End Function

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.

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 YesNo & Price as two fields read in from variant arrray
YesNo = vArr (1, 1)
Price = vArr (1, 2)

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

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