Thread: UDF to capture change in cell values

"Trigger = vArr" was commented out. I left your code in the macro so you could compare your code with code that works (hopefully).
"Trigger = vArr" is trying to put an array into a Date variable for the Function to return. That will always crash with a "invalid type" (or something similar) error.

Didn't catch a bug in my macro

    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
 
should be

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

        lCols = rCells2Check.Columns.Count        
        ReDim vArr(1 To lCols, 1 To 1)
        'just create a blank array to get things started
    End If

The old code was always reinitializing the entire array every time (which erases all the data in it).

I don't see any point to these lines:

    YesNo = rCells2Check(1).Value
    Price = rCells2Check(2).Value

You never use them anywhere.


You comparison looks fine. I would put into the message the address of the cell that triggered it, so the user could easily find the change.

Originally Posted by chris1983

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

What if only 1 of the cells in Cess2Check will be N/A. Do you want the values in the rest of cells to be available for the next comparison.

I didn't look at the entire macro. I just looked at the comparison code. I didn't even notice that you weren't cycling through the cells.
I also failed to notice that you're not putting the new value into the array for the next change to check against.

I'm writing this on the website. I'm not testing this, so if there is a typo or oversight, I hope you catch it.

    
Else
    'vArr was already initilized so check values
    For lCol = 1 to lCols
       set r = rCells2Check.Columns(lCol)
       If (r.value = vbError) or (IsEmpty(r.Value) Then
           'skip this one
       ElseIf r.Value <> vArr(lCol, Index) Then
           cellChanged = r.Address
           vArr(lCol, Index) = r.Value
           MsgBox "Value in " & changedCell & " has changed"
       End If
    Nex lCol
End If

The UDF() is currently checking every cell in the range passed to it. Checking all the values first in order to exit the UDF() immediately is pointless IMHO. It would then lengthen the time it takes to look at them again if they aren't all N/A. The amount of time it takes to put a value into an array can't be measured by your computer because it's so fast. I would bet that having to put 500 values into an array takes less than 1 millisecond for them all.

The UDF() will be triggered in any cell where the parameters to the UDF() change.
If the UDF() is called with =Trigger(A3:B3), it will be triggered any time Excel thinks that A3 or B3 changes.

If you have 500 rows with that formula (the bottom one being =Trigger(A502:B502) and Excel interprets it as a change when a cell is changed to the same value, then the UDF() is going to be triggered 500 times every time.
If in fact that is the case, it would probably be faster to just have 1 formula =Trigger(A3:B502) and you would obviously have to change the UDF() to check 500 rows. I feel confident that it would take less time to call the UDF() 1 time and check 500 rows, than to call the UDF() 500 times and check 1 row each time.

I believe it is possible to set up formulas to determine if cells change values and then only call the UDF() for the rows that actually change value. It would trigger one of Excel's internal functions (which are always faster than a UDF()) 500 times, but it would require the values to be on the worksheet instead of in an array in the UDF().

I also just occurred to me. If you put a Msgbox into the UDF(), it will stop until the user hits a key. If 500 rows do change value, the user would have to hit a key 500 times before the data gets refreshed. You might consider just putting the address of the changed cells back into the cell with =Trigger(...) in it, and use conditional formatting to highlight the cells that have changed.

A consideration: Excel can do the comparisons faster than VBA. It could be offset by the time it takes to put the values back on the worksheet for Excel to compare, but if it's going to end up checking 500 rows all the time, it might be faster to put the values back on the worksheet and let Excel compare and just highlight the cells that are changed.

I know this probably complicates things, but they are all things that you should consider.

If I was in your position I would time the UDF() and print the results for all the different ways to accomplish what you want.
The "TIMER" staement counts how many ticks since MidNight (1 tick is approximately 1 millisecond)

Function Trigger(ByVal rCells2Check as Range) as Date
    dim l as Long
    l = Timer
    '    UDF code
    Debug.Print rCells2Check.Address,Timer - l
End Function

This would tell you how many milliseconds it takes to run the UDF(). Try it by checking what the time using arrays vs time putting it on the worksheet, checking 500 rows in 1 call to the UDF() vs checking 1 row in 500 calls to the UDF(), etc. It's a lot of work, but if this is something you're going to use for a long time, it's worth figureing out the fastest method.

Have you tried using the ID property of the cells? In the function, iterate each cell and test value against ID; if different, update the ID and process; if not, do nothing.

I never new there was an Cell.ID property. You learn something new every day.

Chris;
This sounds promising. Use the ID property instead of an array in the UDF(). I can't imagine that it's enough faster to make a difference, but it looks like it would be much easier to understand and program.

In fact I think I'm going to look into using it in all my workbooks. It would make it easy to Undo changes I don't want to allow.

It would be:

If IsError(r.value)

quick answer. This will get you started.

function Trigger(ByVal rEntireRange as Range) as date
    For Each rCell in rEntireRange
        If (not isempty(rcell))  and (not iserror(rcell)) and (rcell.id <> rcell.value) Then
            msgbox rcell.address & " is changed"
            rCell.id = rcell.value
        End If
    Next rCell
    Trigger = Now
End Function

You seem to be saying that you don't want the value N/A to remain in the cell. Do you want to replace the N/A with the old value? If so, then figure out what to check in order know to replace it with .id.