Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 22
Default 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?

  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 11
Default 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?


  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 644
Default 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


  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,600
Default 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?





  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 968
Default 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?



  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 22
Default 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)?

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Automatic functions whispagirl Excel Worksheet Functions 2 December 27th 06 09:24 PM
Automatic Product Functions macabe Excel Worksheet Functions 1 June 8th 06 08:29 PM
Automatic recalculation of array functions Schizoid Man[_2_] Excel Programming 5 May 24th 06 12:58 AM
Automatic Sort with VLOOKUP/LARGE Functions G Excel Worksheet Functions 1 August 24th 05 08:59 PM
Automatic running Plug-in functions on load -- Urgent kumar_8675[_2_] Excel Programming 0 October 26th 04 02:22 PM


All times are GMT +1. The time now is 11:55 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"