Automatic Functions?
 

I thought this would be easy but I can simply not find anything in the
help system.

I have a vb function that finds the largest value amoung a range and
essentially spits out the row who has the largest number.

When I type in the =MyFunctionName(), I get the correct value in the
field.
BUT if I change/update any numbers in the data, the function does not
get automatically recalled, so the older now incorrect value remains.

All I can find to correct this is to click back in the cell, provide to
edit it, and click out and the function will refresh with the new
correct value.

Is there anyway for this to happen automatically, like a normal formula
in Excel would?

Automatic Functions?
 

add the change event to your sheet's code (right click the tab, view
code)

so:
public sub Worksheet_Change(ByVal Target As Range)
call myfunctionname
end sub

::help file entry::

Change Event


Occurs when cells on the worksheet are changed by the user or by an
external link.

Syntax

Private Sub Worksheet_Change(ByVal Target As Range)

Target The changed range. Can be more than one cell.

Remarks

This event doesn't occur when cells change during a recalculation. Use
the Calculate event to trap a sheet recalculation.

Deleting cells doesn't trigger this event.

wrote:
I thought this would be easy but I can simply not find anything in the
help system.

I have a vb function that finds the largest value amoung a range and
essentially spits out the row who has the largest number.

When I type in the =MyFunctionName(), I get the correct value in the
field.
BUT if I change/update any numbers in the data, the function does not
get automatically recalled, so the older now incorrect value remains.

All I can find to correct this is to click back in the cell, provide to
edit it, and click out and the function will refresh with the new
correct value.

Is there anyway for this to happen automatically, like a normal formula
in Excel would?

Automatic Functions?
 

Try putting

Application.Volatile

in your function. This will cause the function to recalculate whenever
anything on the sheet calculates.

Col


--
colofnature
------------------------------------------------------------------------
colofnature's Profile: http://www.excelforum.com/member.php...o&userid=34356
View this thread: http://www.excelforum.com/showthread...hreadid=558476

Automatic Functions?
 

Have you tried doing it without VBA?
=MATCH(MAX(A:A),A:A,0)

Die_Another_Day

colofnature wrote:
Try putting

Application.Volatile

in your function. This will cause the function to recalculate whenever
anything on the sheet calculates.

Col


--
colofnature
------------------------------------------------------------------------
colofnature's Profile: http://www.excelforum.com/member.php...o&userid=34356
View this thread: http://www.excelforum.com/showthread...hreadid=558476

Automatic Functions?
 

Not sure why your UDF doesn't update unless of course you've have
Calculation set to Manual (Tools Options Calculation).

Do you really need a UDF

=ROW(A1)+MATCH(MAX(A1:A9),A1:A9,0)-1

Regards,
Peter T

wrote in message
ups.com...
I thought this would be easy but I can simply not find anything in the
help system.

I have a vb function that finds the largest value amoung a range and
essentially spits out the row who has the largest number.

When I type in the =MyFunctionName(), I get the correct value in the
field.
BUT if I change/update any numbers in the data, the function does not
get automatically recalled, so the older now incorrect value remains.

All I can find to correct this is to click back in the cell, provide to
edit it, and click out and the function will refresh with the new
correct value.

Is there anyway for this to happen automatically, like a normal formula
in Excel would?

Automatic Functions?
 

you need to provide the range you are searching as an argument to the
function: then excel will recalculate your function whenever anything in the
range is changed or recalculated.

=MyFunctionName(theRange as range)

Charles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com

wrote in message
ups.com...
I thought this would be easy but I can simply not find anything in the
help system.

I have a vb function that finds the largest value amoung a range and
essentially spits out the row who has the largest number.

When I type in the =MyFunctionName(), I get the correct value in the
field.
BUT if I change/update any numbers in the data, the function does not
get automatically recalled, so the older now incorrect value remains.

All I can find to correct this is to click back in the cell, provide to
edit it, and click out and the function will refresh with the new
correct value.

Is there anyway for this to happen automatically, like a normal formula
in Excel would?

Automatic Functions?
 

Thanks all, the Application.Volatile worked like a champ. To answer a
common question, there may have been a way for me to do what I wanted
without VB. But I am trying to teach myself VB, are there any other
good (free) sources of reference material on VB (particularly for
Excel)?

Automatic Functions?
 

you are using one of the best right now

wrote:
Thanks all, the Application.Volatile worked like a champ. To answer a
common question, there may have been a way for me to do what I wanted
without VB. But I am trying to teach myself VB, are there any other
good (free) sources of reference material on VB (particularly for
Excel)?