Worksheet_Calculate - How Best To Filter Event For A Single Range 1

[mavest]

Anyone know the best method to filter the Worksheet_Calculate event such that code is executed only when the cell values of a certain range have changed, i.e. been re-calculated?

I noticed that no Target range is passed to this event function; so, I cannot find the range by the same method used for Worksheet_Change. Also I cannot use Worksheet_Change because my Trigger Range is not changed by the user or by VBA. (My Trigger Range is changed by Excel links.)

One way I know I can do it, is by saving a second range with static values of the last numerical value in my Trigger Range. I could then do a range compare on Worksheet_Calculate to see if the numbers have been updated. However, this seems awkward and prone to error; so, I thought someone might know a better method. Thanks for your help!!

-Mike

 

[SkipVought]

Hi,

You have Auto or Manual calculate.

Set to Manual.

In the Worksheet_Change event, when changes are made in the area of interest, perform a calculate, to whatever level is required.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[mavest]

Not sure what you mean, or if it applies to my situation.

Explanation:
The range of interest contains only Excel formulas. So, the values of the cells in the range of interest are changed by Excel logic in a large multiple worksheet workbook. Since the cells in the range of interest are not changed by a "user entry" or VBA, the worksheet_change event will not be triggered.

I could trace the value in the range of interest back to a user input, but with all the nested IF statements, it becomes quite a mess.

-Mike

 

[SkipVought]

Welll SOME CHANGE would cause the formulas to calculate new values.

What are they?

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[combo]

Build your own event, for instance:

Public Event FunctionChange(rRange As Range)
Private mRange As Range

Public Sub RaiseChange(rRange As Range)
Set mRange = rRange
RaiseEvent FunctionChange(rRange)
End Sub

Public Property Get RangeChange() As Range
Set RangeChange = mRange
End Property

Private WithEvents cChange As custChange

Public Sub InitEvent()
Set cChange = oChange
End Sub

Private Sub cChange_FunctionChange(rRange As Range)
MsgBox cChange.RangeChange.Address
End Sub

Public oChange As custChange

Public Sub InitEvent()
Set oChange = New custChange
ThisWorkbook.InitEvent
End Sub

Public Function Changer(rRange As Range)
If oChange Is Nothing Then InitEvent
oChange.RaiseChange Application.Caller
Changer = rRange
End Function

Call InitChange first, link via Changer.


combo

 

[mavest]

Very interesting! I tested it and it works! Thanks!

I might not have a full understanding of your intentions, but I took the range of interest and used it as the input parameter to the function Changer. I hide this function call somewhere on the worksheet. Since Excel will call the function anytime the parameters change value, Excel calls the function Changer anytime the values change.

I do not see the benefit to creating a class, as it seems sufficient to simply declare a function with the input parameter set to the range of interest, as follows:


Public Function newChanger(rRange As Range)
MsgBox rRange.Address
End Function
'no other VBA coding needed

If there is a benefit to your class design, please explain.

 

[combo]

Actually, this function will be sufficient to trap source changes.
The class could be useful in case of the need to have general information that the source has changed (event). This event can be used by any other object (WithEvents declaration) or objects, without specific coding in the funcion.
The drawback of both methods is lack of possibility to change workbook, as the code trigger is function in a worksheet.

combo

 

[mavest]

Thanks for your help! Your technique will be quite useful.

 

[Rofeu]

I've skimmed over your code, Combo, and I'm not following the logic of your code completely yet, but could that drawback not be overcome by sticking it in your PERSONAL.xls and creating the event in a class with the application object (See VBA Help 'Using Events with the Application Object')?

Cheers,

Roel

 

[combo]

Hi Roel,
the goal is to create an object, as only objects can raise events.
I'm afraid that it is not possible to change worksheet by function in worksheet, even by custom events (I've tried with the code in my initial answer). I was thinking about using timer and collection - the function feeds the collection with changed ranges, the timer tests the collection for ranges. If any exists, uses it and removes from the collection.

combo

 

[mavest]

Combo,

Not sure how our timer and collection system is to work. The problem I'm faced with is that I cannot change the worksheet while in a worksheet function.

If I could get out of the function and then raise the event, I'd be perfect.

-Mike

 

[combo]

I used API timer to get higher resolution (100 ms). If 1s is sufficient, no timer is needed and Application.OnTime can handle TimerProc (without arguments).
Without securing the code:

Private Declare Function SetTimer Lib "user32" ( _
    ByVal hWnd As Long, _
    ByVal nIDEvent As Long, _
    ByVal uElapse As Long, _
    ByVal lpTimerFunc As Long) As Long
Private Declare Function KillTimer Lib "user32" ( _
    ByVal hWnd As Long, _
    ByVal nIDEvent As Long) As Long

Private TimerId As Long
Public colRanges As Collection

Public Function Tracer(InputVal As Range) As Variant
If colRanges Is Nothing Then StartTimer
' pass changed range to collection
colRanges.Add Application.Caller
Tracer = InputVal
End Function

Private Sub StartTimer()
    On Error Resume Next
    ' start timer and collection
    Set colRanges = New Collection
    TimerId = SetTimer(0, 0, 100, AddressOf TimerProc)
End Sub

Private Sub StopTimer()
    On Error Resume Next
    KillTimer 0, TimerId
    Set colRanges = Nothing
End Sub

Private Sub TimerProc(ByVal hWnd As Long, _
    ByVal uMsg As Long, _
    ByVal wParam As Long, _
    ByVal lParam As Long)
    
    Dim rngRangeInCol As Range
    On Error Resume Next
    If colRanges.Count > 0 Then
        For Each rngRangeInCol In colRanges
            ' mark changed cells
            rngRangeInCol.Interior.ColorIndex = 56 * Rnd
        Next
    End If
    ' and reset collection
    Set colRanges = Nothing
    Set colRanges = New Collection
End Sub

combo

 

[mavest]

Thanks! I'll check out your method tomorrow. Today, I'm a bit tight on time.

For the record I did find another solution, although not as elegant.

The solution I found was to use a function on the worksheet to store the position of the cell whose value was changed. The position is stored in some global variables. Next, I relie on the fact that Worksheet_change event occurs at the end of the worksheet calculation. So, after the global variables have been updated Excel will automatically call Worksheet_Change. In change, I check if the global parameters have non zero length and if so, do what I want.

 

[combo]

The 'Change' event is not raised, I would rather use 'Calculate'. The collection is used, as 'Calculate' event occurs at the end of recalculation, more than one function calls can be before.

Public colRanges As Collection

Public Function Tracer(InputVal As Range) As Variant
' store changed range
If colRanges Is Nothing Then Set colRanges = New Collection
colRanges.Add Application.Caller
Tracer = InputVal
End Function

Public Sub CallerProc()
Dim rngRangeInCol As Range
On Error Resume Next
If Not colRanges Is Nothing Then
    If colRanges.Count > 0 Then
        ' disable events
        Application.EnableEvents = False
        ' mark changed cell
        For Each rngRangeInCol In colRanges
            ' proceed with workbook
            ' for instance mark changed cells
            rngRangeInCol.Interior.ColorIndex = 56 * Rnd
        Next
        Application.EnableEvents = True
    End If
End If
' reset collection
Set colRanges = Nothing
Set colRanges = New Collection
End Sub

Private Sub Worksheet_Calculate()
Call CallerProc
End Sub

combo

 

[combo]

A simple solution for limited and fixed number of cells with links (with hidden textbox on the sheet, its LinkedCell points to traced cell):

Private Sub TextBox1_Change()
Call ChangeFromTextBox(Me.TextBox1.LinkedCell)
End Sub

Private Sub ChangeFromTextBox(LinkedCell)
MsgBox "New value: " & Me.Range(LinkedCell), vbOKOnly, "Changed " & LinkedCell
End Sub

combo

 

[RajanVBA]

Hi mavest and combo,

I tried all methods.. but all of the returns me only the address of cell where I have placed my function in the sheet. Whereas I need the cell address that got changed. I cannot use the worksheet_change as my changes are made by a formula and no "Enter" key pressed to fire the event. I am sure that by this time you guys would have got the solution for it. I really appreciate if you help me out.

Thanks,
Rajan

 

[SkipVought]

RajanVBA,

Please post your question in a new thread.

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}