RunningMax

[dukid]

This function is exactly what I need but I cant make it work in more than one cell, it shows same results in all cells where entered.
What should I do?

Function runningMax(inVal As Double) As Double
Static storedMax As Double
If inVal = 0 Then
storedMax = 0
Else
storedMax = Application.Max(inVal, storedMax)
End If
runningMax = storedMax
End Function

Function runningMin(inVal As Double) As Double
Static storedMin As Double
If inVal = 0 Then
storedMin = 9.9e+99
Else
storedMin = Application.Min(inVal, storedMin)
End If
runningMin = storedMin
End Function

[Kenneth Hobs]

Set inVal as Range rather than Double.

[dukid]

Thanks for suggestion but I just cant make it work, function is returning higest/lowest value of all cells.
Example.
A1 = 2
A2 = 5
A3 = 8

in B1 I have function =runningMax(A1), in B2 =runningMax(A2), B3=runningMax(A3)
Now I have result 8 in all cells whit function runningMax and what I need is to show result just for specific cell(in B1 for A1, B2 for A2...)

Cells in A column are auto updated every few seconds and I need to record higest/lowest value ever from specific cell.

Any suggestions?

[mikerickson]

Try this
Function RunningMax(aRange As Range) As Double
Static myColl As New Collection
Dim currentMax As Double
On Error GoTo AddToCollection
currentMax = myColl(aRange.Address(, , , True))
On Error Goto 0
If currentMax > Application.Max(aRange) Then
RunningMax = currentMax
Else
myColl.Remove aRange.Address(, , , True)
myColl.Add Item:=Application.Max(aRange), Key:=aRange.Address(, , , True)
RunningMax = myColl(aRange.Address(, , , True))
End If
Exit Function
AddToCollection:
Err.Clear
myColl.Add Item:=Application.Max(aRange), Key:=aRange.Address(, , , True)
Resume
End FunctionYou might want to add a "reset" argument.

[dukid]

Thats it, many thanks.
No I dont need reset because numbers can go below 0 so this is excellent.
Thank for this and for your first code also :thumb

[dukid]

ok here I am with another stupid question, how can I do all of this with runningMin function. I change all Max with Min on your code but I guess thats not good enough?

[mikerickson]

Did you change the inequalities? < for > ?

[dukid]

:doh:
I did now, works great with both functions.
thank you very much.

[edthehorse]

Hey,

Great bit of code, exactly what i have been looking for but i was just wondering if you could show me how to put in a reset argument.

I tried adding an if statement after "on Error GoTo 0"
with If I=0 Then CurrentMax = 0

it flips it to zero but it doesn't seem to reset the stored values to 0


any ideas?!

thanks in advance

[mikerickson]

myColl.Remove aRange.Address(, , , True)
myColl.Add Item:=0, Key:=aRange.Address(, , , True)
RunningMax = myColl(aRange.Address(, , , True))

[edthehorse]

That works perfectly thanks for the help!