Re: How to capture Max cell value (High Water Mark)

Tech-Archive recommends: Fix windows errors by optimizing your registry

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 work*** 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 work*** 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 work***. 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

--
Regards,

OssieMac


"Don" wrote:

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" <OssieMac@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7D3392D3-DC67-4933-91C6-19D68C9A7770@xxxxxxxxxxxxxxxx
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.

--
Regards,

OssieMac


"Don" wrote:

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
spread*** 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" <OssieMac@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:5E417EBB-3A91-4ABF-8500-C01E3261FEBF@xxxxxxxxxxxxxxxx
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 "work***" 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
*** 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.


--
Regards,

OssieMac


"Don" wrote:

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 work*** 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" <OssieMac@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F2F69621-0D64-4AFD-B5A8-57729246C4B7@xxxxxxxxxxxxxxxx
Hi again Don,

I have just done some more testing and I the following is
incorrect:-
"Note that it only works with the work*** 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
work***
to
which it is attached then the macro fires.

--
Regards,

OssieMac


"OssieMac" wrote:

Hi Don,

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

Note that it only works with the work*** 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 work*** where the changing cell is plus name of
work***
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 work*** 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.)

--
Regards,

OssieMac










.

Quantcast