Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
question on user-defined formula calculation mode: can it be instantaneous?
Hello all, I'm a beginner in the realms of user-defined formulas. My problem is that the one that I just wrote doesn't update when its arguments are updated: it will only update when I edit the cell it is on, and validate the cell. My formula is called GetNthInRange(r as range, n as integer, size as integer). The formula is supposed to return the nth top non-blank cell in a 1-cell wide, size-cell long range. for instance, if I have a column that looks like this: A 1 2 3 1 4 2 5 3 6 well, GetNthInRange(A1,1,5) returns the first non-blank cell in the A1:A5 range, that is A3 or 1. GetNthInRange(A1,2,5) returns the second one, A4 or 2. etc. It works and provides the results that I expect when I first run it. The problem is that if, say, I enter in A6, =GetNthInRange(A1,2,5), (which evaluates to 2), if I then update A4, the value in A6 will not change. If I do edit A6 and press enter, then the value of A6 will be the new value of A4. The code of the function is as follows GetNthInRange = r.Offset(size - (Application.WorksheetFunction.CountA(Range(r, r.Offset(size - 1))) - (n - 1))) Is there anything I can do to make it update instantaneously? many thanks jerome -- jerjer ------------------------------------------------------------------------ jerjer's Profile: http://www.excelforum.com/member.php...o&userid=34571 View this thread: http://www.excelforum.com/showthread...hreadid=543394 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
question on user-defined formula calculation mode: can it be instantaneous?
You need to make the UDF volatile so that it is recalculated everytime ANY
cell on the worksheet is recalculated. Easy to do: Function phoobar(cat, dog) Application.Volatile .... rest of code best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "jerjer" wrote in message ... Hello all, I'm a beginner in the realms of user-defined formulas. My problem is that the one that I just wrote doesn't update when its arguments are updated: it will only update when I edit the cell it is on, and validate the cell. My formula is called GetNthInRange(r as range, n as integer, size as integer). The formula is supposed to return the nth top non-blank cell in a 1-cell wide, size-cell long range. for instance, if I have a column that looks like this: A 1 2 3 1 4 2 5 3 6 well, GetNthInRange(A1,1,5) returns the first non-blank cell in the A1:A5 range, that is A3 or 1. GetNthInRange(A1,2,5) returns the second one, A4 or 2. etc. It works and provides the results that I expect when I first run it. The problem is that if, say, I enter in A6, =GetNthInRange(A1,2,5), (which evaluates to 2), if I then update A4, the value in A6 will not change. If I do edit A6 and press enter, then the value of A6 will be the new value of A4. The code of the function is as follows GetNthInRange = r.Offset(size - (Application.WorksheetFunction.CountA(Range(r, r.Offset(size - 1))) - (n - 1))) Is there anything I can do to make it update instantaneously? many thanks jerome -- jerjer ------------------------------------------------------------------------ jerjer's Profile: http://www.excelforum.com/member.php...o&userid=34571 View this thread: http://www.excelforum.com/showthread...hreadid=543394 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
question on user-defined formula calculation mode: can it be instantaneous?
thanks a lot, Bernard! it worked fine! I'm learning a little everyday!! :) -- jerjer ------------------------------------------------------------------------ jerjer's Profile: http://www.excelforum.com/member.php...o&userid=34571 View this thread: http://www.excelforum.com/showthread...hreadid=543394 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
User controls on sheet question. - How to toggle between design and execute mode? | Excel Programming | |||
User-defined data type; Error: Only User-defined types... | Excel Programming | |||
Automatic calculation of user-defined worksheet function | Excel Programming | |||
A Question on User Defined Functions. | Excel Programming | |||
Inefficient Calculation of User Defined Functions in Excel 2000 | Excel Programming |