How to capture Max cell value (High Water Mark)

[Don]

I have a spreadsheet that uses DDE to repeatedly refresh data. Of course,
upon each refresh all formulas are calculated.

Is there a way to capture the Max value that a particular cell has contained
through all the refreshes?

I guess you could call this a high water mark function of some kind.

I am hoping a clever formula exists to do this.

Although I am not very savvy on VB I could probably muddle through it if
necessary. My big problem would be how to invoke the VB code automatically
with every refresh. I think the VB code has to be linked to something called
event processing. I have read about event linking and I have no clue what
they are talking about.

 

[OssieMac]

Hi Don,

The Calculate event should do it. You need to decide in what cell you want
to record the "High Water Mark" and use something like the code below. to
install the code follow these steps.

Right Click on your worksheet tab name
Select view code
Copy the below code and paste it into the VBA editor.
Close the VBA editor (Red cross top right corner)
Save the workbook


Private Sub Worksheet_Calculate()
If Range("A1") > Range("Z1") Then
Range("Z1") = Range("A1")
End If
End Sub


Now every time the worksheet recalculates, the value will be copied into the
High Water Mark cell if it is greater than what already exists. In the code,
Cell A1 is the one being monitored and Z1 is the High Water Mark.

Feel free to get back to me if you have any problems.

 

[Don]

OssieMac,

I did what you said, but it doesn't seem to automatically recalculate.

I was able to step through the code and the code works just fine (high water
mark is recorded).

When I inserted your code the VB editor inserted a line seemingly marking a
new entity of some kind (sorry for the lack of precise terminology). The
prior entity had two lines of code as follows:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub

Any other ideas?

BTW, thank you for such clear simple directions, I needed that.

 

[Dave Mills]

That happens when you right click and view code then click worksheet on the
dropdown menu before inserting the pasted text. Simply delete the two line so
the code reads exactly as OssieMac said..

 

[Don]

Dave, OssiwMac,

Okay, I deleted the lines and now only OssieMac's code is visible.

It still does not execute when a calculate is done. The two drop down
windows on top of the VB editor say "worksheet" and "calculate". I assume
that is normal due to similar parameters in the first line of OssieMac's
code.

Again, when I manually run the code using the VB editor "debug" functions it
works as it should (captures high water mark).

Apparently this code is not being invoked when a calculate is done.

Any other things I should try?

Thanks in advance.

 

[OssieMac]

Hi Don,

Yes the following is normal: "The two drop down windows on top of the VB
editor say "worksheet" and "calculate"."

Note that it only works with the worksheet under which it was installed.
Calculates on other worksheets will not run it. Needs some modification if
required to run when other worksheets calculate. If that is the case then
post the names of all the worksheets that you want to trigger it and the name
and cell of the worksheet where the changing cell is plus name of worksheet
and cell where the "High Water Mark" is to be saved.

Also, have you got your Excel Macro security set to allow macros?

To set in XL2007:
Click Microsoft (Large button top left of screen)
Click Excel Options (Towards bottom right of dialog box)
Click Trust Center (Left column of dialog box)
Click Trust Center settings (Middle Right of dialog box)
Click Macro Settings (Left column of dialog box)
Set Disable all macros with notification. (This really means “When the
workbook is opened notify user that it contains macros and ask if macro is
allowed to runâ€)

To set in XL2002/3
Click menu item Tools -> Macro ->Security
On Security level tab set to Medium. (This really means “When the workbook
is opened notify user that it contains macros and ask if macro is allowed to
runâ€)

I have tested the macro using RANDBETWEEN function and it certainly runs
everytime an alteration is made on the worksheet or I press F9 to force
calculation. (If you want to test using RANDBETWEEN then I think you need
Analysis ToolPack Add In. It is a standard Excel feature so check Help if you
do not know how to invoke it.)

 

[OssieMac]

Hi again Don,

I have just done some more testing and I the following is incorrect:-
"Note that it only works with the worksheet under which it was installed.
Calculates on other worksheets will not run it." The entire workbook
calculates and if there is a change in the data values on the worksheet to
which it is attached then the macro fires.

 

[Don]

OssieMac,

I am using Excel 2000 and my security is set to "medium" and I can record
and run macros.

I'm only using it on the same worksheet where the macro was created.

Still not working.

Thanks for all your help. If you have any other ideas please let me know.

BTW, would another method be to write my own function? I assume that user
written functions will be invoked upon calculation like any other formula.
If true then the only difficulty would be storing the high water mark
somewhere and I am wondering if there are variables available for that
storage

In other words, the result of the function would be to display the hi-water
mark in the cell containing the function. It also would be stored in a
variable for the next hi-water comparison. Hope this makes sense.

 

[OssieMac]

Hi there again Don,

I have never used xl2000 but if as you have said “The two drop down windows
on top of the VB editor say "worksheet" and "calculate" “ then I assume that
the calculate event is available in xl2000. Therefore something else is the
problem.

Do you have any other macros in the Workbook. Particularly a Workbook_ Open
event that runs when the workbook opens. It is possible that events have been
turned off with code.

A quick check can be done as follows:-
Open the VBA editor (Alt/F11)
Select menu item Insert -> Module
Copy the following code into the module in the VBA editor.

Sub Test_Enable_Events()
If Application.EnableEvents = True Then
MsgBox "Events are enabled"
Else
MsgBox "Events have been disabled"
End If
End Sub

Click anywhere between the sub and end sub to place the cursor within the sub.
Press F5 to run the macro from the VBA window.
If message says "Events are enabled" then I don’t have any idea what the
problem might be.
However if message is "Events have been disabled" then you need to find out
what is disabling them so follow the instructions below.

If not already open, Open up the VBA editor (either by right clicking the
sheet name tab and select View Code or simply Alt/F11).
Select menu item Edit->Find
In the dialog box enter EnableEvents (Note as one word – no spaces)
Select the radio button to search Current project.
Click ‘Find Next’.

You are looking for a line Application.EnableEvents = False. (Any that equal
True are not a problem.)

Now it is not unusual to have such a line at the beginning of a sub routine
so that undesired events are NOT fired as a result of the sub routine running
but at the end of the sub routine there should be another line to turn events
back on. Application.EnableEvents = True.

If you have any subs with the code then if you copy and post it to the forum
then perhaps we can have a look to see if it is really required or if it can
be turned back on.

As an added extra, if events are turned off, you can turn them back on
temporarily with the following code. Just copy it in below the test code in
the same module and place the cursor between the sub and end sub and press
F5. If you now run the other test code it and should return "Events are
enabled".

Sub Re_Enable_Events()
Application.EnableEvents = True
End Sub

I hope this brings some joy to you. If not, then I certainly don’t know the
answer.

 

[Don]

OssieMac,

First I want to thank you for all your help and your excellent instructions.

I don't have any other macros in the workbook. I have been using a new
spreadsheet to test this stuff.

Your code indeed says that "events are enabled".

Again, thanks for all your help. Although I was not successful I learned a
lot from you.

One other thing, does my alternative method to write a function have any
merit (details were in my prior note)?

 

[OssieMac]

Hi Don,

The principle of using a UDF (User Defined Function) sounded good until I
tried and I can't do it without creating a circular reference. The following
was my test:-

Function HiWater(Target As Range)
If Target.Value > ActiveCell.Value Then
ActiveCell.Value = Target.Value
End If
End Function

The function would be entered as =HiWater(A1)

where A1 is the cell that is changing value (and hence Target in the
function) and the cell where the function is would be the saved High Water
Mark.

I am hoping to get access to a computer with Office 2000 on Saturday. I will
test my previous solution and let you know how I go.

 

[Don]

OssieMac,

Thanks again for your help.

Relative to the function that you wrote, I thought I read that UDF's cannot
modify other cells. Perhaps that's why you had that circular reference
error.

Couldn't the UDF code be modified to use a global variable to remember the
last hi-water mark? That way you wouldn't have to save it in a cell thus
avoiding the circular reference error. The Cell containing the UDF could
display the Hi water mark.

I attempted to write such a UDF below using common English where I don't
know the proper commands:

Function HiWater(Target As Range)
If Target.Value > global variable Value Then
Target.Value = global variable
End If
Display global variable
End Function

There would need to be a one-time initialization of the global variable to
zero which I don't know how to do. Unless that is not necessary if the
default for newly created variables is zero.

Don

 

[OssieMac]

Hi yet again Don,

Try the following. Insert it in a standard module. Note that tempHiWater is
declared in the declarations area at the top of the VBA editor before any
subs or functions.

I only tested it with random numbers and pressing F9 to force recalculation
and it appears to do what you want. It does not appear to need initializing
because it starts at zero and on the first recalc it gets its value. However,
it is not going to retain the value when the worksheet is closed and then
reopened. If you need it to retain the value then let me know and I will have
a look saving the value and initializing it on worksheet open.

I will still look at xl2000 when I get access to it and see why the events
do not appear to work. On your question on my previous attempt at the UDF, I
used ActiveCell that would not normally be used otherwise it would be
addressing whatever cell was active on the worksheet. During the test it was
the active cell. I just did that for convenience of the test.

Option Explicit

Dim tempHiWater As Long

Function HiWater(Target As Range)

If Range("A1") > tempHiWater Then
tempHiWater = Range("A1")
End If
HiWater = tempHiWater

End Function

 

[OssieMac]

Don,

My apologies; the function should be like the following example; not
directly addressing the changing cell. That is why Target is in the function
in the first place.

Option Explicit
Dim tempHiWater As Long

Function HiWater(Target As Range)

If Target > tempHiWater Then
tempHiWater = Target
End If
HiWater = tempHiWater

End Function

Enter the function like this:- =HiWater(A1)

Where A1 is the cell that is changing and the HiWater function can be in any
cell.

 

[Don]

OssieMac,

It Worked!

Thank you, thank you, thank you.

I don't need to retain the value between open/close of worksheets.

Don

 

[OssieMac]

Hi Don,

I hope you saw my last post with the correction. But anyway, pleased that it
is working.

 

[Bernd P]

Sub trace_cell(rresult As Range, routput As Range)
'Keep track of extreme values of a cell calculation.
'Call this sub from a worksheet's calculation
'event like
'Private Sub Worksheet_Calculate()
' Call trace_cell(Range("A1"), Range("C2:IV3"))
'End Sub
'In this example the extreme values for cell A1
'calculation will be shown in C2:IV3:
' Result DateTime Formula Inputparams
'Max 19.00 05/02/2007 06:52 =A2+A3 4 15
'Min 7.00 05/02/2007 06:52 =A2+A3 4 3
Dim i As Long, j As Long, l As Long, vc As Variant
l = Application.Calculation
'Set calculation to manual or we would recursively
'calculate for ever.
Application.Calculation = xlCalculationManual
If rresult.FormulaLocal <> routput(1, 3) Then
'If formula changed reset whole statistics
i = 1
routput.ClearContents
ElseIf rresult > routput(1, 1) Then
i = 1 'New max result
ElseIf rresult < routput(1, 1) Then
i = 2 'New min result
Else
Exit Sub 'Nothing new
End If
all:
j = 1
routput(i, j) = rresult 'First store result
routput(i, j + 1) = Now 'Then store DateTime
'Then store formula
routput(i, j + 2) = "'" & rresult.FormulaLocal
'Now store all input parameters
'Please notice that in case of input arrays we
'store only upper left cell for each array
For Each vc In rresult.DirectPrecedents
routput(i, j + 3) = vc
j = j + 1
Next vc
If rresult.FormulaLocal <> routput(2, 3) Then
'If formula changed calculate min stats anew, too
i = 2
GoTo all
End If
Application.Calculation = l
End Sub

Regards,
Bernd

 

[Don]

OssieMac,

I did encounter the "target" problem you mentioned, but (amazingly) I
figured it out and made the necessary correction.

Thanks again.

Don