UserDefinedFunction is not refreshed when recalcing w F9

[sbendbuckeye]

Hello all,

I have a UDF defined and used in my sheet, eg cell a1 contains =TestIt(). If I manually edit a1 and press <Enter> it runs, but if I recalculate with F9 the code is not rerun. I have a break point set to verify this. Any ideas on why this is the case? Thanks in advance for any ideas and/or suggestions!

 

[VoG]

Have you tried using

Application.Volatile

in your UDF?

 

[pgc01]

Hi

VoG's solution will work but will force the execution of the UDF each time the worksheet recalculates. I prefer to have the UDF recalculated only when necessary.

Excel knows that it must recalculate a formula by analysing its dependencies. The problem with your UDF is that it apparently doesn't depend on anything, since it has no parameters.

To tell excel the dependencies of the UDF you use the UDF's parameters.

Since I have no idea of what your UDF does I cannot tell you what parameters it should have but this is an example:

Let's suppose your UDF does some calculations using the values in C1:F4.
In this case
- You define the UDF with one parameter
- In the worksheet you use =TestIt(C1:F4)

This way excel knows that each time a value changes in C1:F4, the UDF must be recalculated.

Hope this helps
PGC

 

[xenou]

I was rather thinking on the lines of pgc but without so much understanding behind it all Your UDF probably doesn't run unless the input values on which it depends have been changed. If you have your calculation set to manual (as you probably do since you are hitting F9) I suspect that you still wouldn't actually get the UDF running code unless Excel had flagged the function for recalculation. Application.Volatile forces a recalculation every time Excel recalculates, but normally this is something to be avoided as it slows calculation down. An interesting case is a UDF that doesn't refer to any cells - it would not be dependent on alterations in cell values so it would need to be volatile in order to be recalculated.

There is a lot on the web on this. Just google some of your keywords.
http://www.dicks-blog.com/archives/2004/06/22/volatile-functions/

There is an idea here for using Ctrl + Alt + F9 to force a recalculation of everything whether Excel has flagged it or not.
http://www.mvps.org/dmcritchie/excel/slowresp.htm#volatile

HTH

 

[sbendbuckeye]

Thanks to all of you for helping me to figure this out! I posted the above as a general question and so did not include the entire UDF signature. Sorry, I wasn't trying to be unclear. Here is my UDF signature: Public Function MVLookUp(LookUpValue As Variant, DataRange As Variant, SearchColumn As Integer, Optional FirstInSeries As Boolean = False) As Variant

But the discussion above helped me to see the problem. One of my arguments was a range and I was allowing it to be passed in as an actual range (eg A1:B4) or as a range string (eg "A1:B4"). It was the latter option which was causing the problem because Excel treated it like a string and so didn't recognize changes within the actual range.

As soon as I changed it to accept only a range argument it worked.

What I am really trying to do is to return multiple values from a UDF that internally creates and returns an array. It is an adaption of this thread which I have not been able to get to work. VLookUp - Is it possible to have VLookup return more than one value at a time? http://www.mrexcel.com/board2/viewtopic.php?t=19517&postdays=0&postorder=asc&&start=0. In the thread Juan Pablo Gonzales posts the code which is the starting point for my current UDF.

 

[xenou]

Hi:
Very interesting. Probably beyond my abilities I am curious how you pass the array variable to the UDF - if from a vba statement I get it - but if from a worksheet user then ??. I attempted to use the InputBox Method but this returns a string value (and Juan Pablo Gonzalez' UDF requires the argument to pass the IsObject() test - if I understand it right). Would the UDF itself need to be entered as an array formula?

Regards

 

[sbendbuckeye]

Hello,

He creates the array internally in the UDF and then returns the array as the UDF result. However I can only ever get it to return the first element of the array, presumably because you cannot modify cells outside of the current cell from within a UDF. Good Luck!