Function to get the maximum value of a list of dynamic cells?

Question

I have an excel sheet with a B column with more than 100 rows that are dynamic from a RTD link.

This RTD link updates every second and the values keeps increasing until at given moment it changes to zero. I just need to store at column C the last value before it changes to zero.

I tried by sub (code below), but the loop I created is crashing Excel.

I think the best way to do it is with VBA function, I tried but without success. Someone can think in a function?

Private Sub Worksheet_Calculate()
Dim rng As Range
Set rng = Me.Range("B3:B7")

For i = 3 To 7

    If Range("B" & i) > Range("C" & i) Then
        Range("C" & i) = Range("B" & i)
    End If

Next i

End Sub

Same question as yesterday? Looks familiar... Your Excel probably crashed because you are modifying your data within the calculate event, which triggers a recalculation and so on. See https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure — FunThomas, Jun 01 '21 at 15:35
Do they all drop to 0 at the same second or each value another time? And is the last value before dropping to 0 the maximum value? — Pᴇʜ, Jun 01 '21 at 15:44
@funthomas, is similar to the question I posted yesterday. I made by sub, but I think that by function will work better. I only could trigger with the calculation event, the change event isn't triggered by the RTD update — Tiago Delazari, Jun 01 '21 at 15:54
@peh, each value comes to zero at different moment. And yes, the last value before zero is the maximum value — Tiago Delazari, Jun 01 '21 at 15:54
I understand that you cannot use the Change event. But the problem is the same as in the given link: Deactivate Events (`Application.EnableEvents = False`) when manipulating sheet data while you are within a trigger — FunThomas, Jun 01 '21 at 15:59

score 0 · Answer 1 · answered Jun 01 '21 at 16:00

Imagine the following UDF (user defined function). It has a circular reference and probably Excel will complain about that it might calculate wrong (but it still works if you click OK at that complain).

Option Explicit

Public Function RTD_Max(ByVal Value As Variant)
    If Not Value = 0 Then
        RTD_Max = Value
    Else
        RTD_Max = Application.ThisCell.Value
    End If
End Function

The blue cells below have the formula =RTD_Max(B3) copied down.

As you can see when data comes in it keeps the last value of B in C before it drops to 0.

Off topic: I used the following code to animate incoming data as I didn't have any RTD available:

Public Sub Animate()
    Dim i As Long
    For i = 1 To 10
        Dim Cell As Range
        For Each Cell In Range("B3:B7").Cells
            Cell.Value = Cell.Value + Rnd
        Next Cell
        Application.Wait (Now + TimeValue("0:00:01"))
    Next i

    For Each Cell In Range("B3:B7").Cells
        Cell.Value = 0
    Next Cell
End Sub

@peh, nice! worked... but as you said, excel complain when the value becomes to zero because the circular reference. You know if I can avoid this msgbox with some code lines? — Tiago Delazari, Jun 01 '21 at 16:22
@TiagoDelazari Nope, sorry. No idea. Maybe someone else has. Or try what FunThomas suggested that could work too I guess. — Pᴇʜ, Jun 01 '21 at 16:24

Function to get the maximum value of a list of dynamic cells?

1 Answers1