UDF doesn't refresh result

**nougain** · 01-10-2007, 12:46 AM

First sheet has some data. Second sheet uses UDF written in VBA and this UDF uses data from first sheet to return result displayed in second sheet. It works fine. But when I change data in first sheet, result does not get refreshed in the second sheet. I have to manually click on the cell which is calling UDF followed with ENTER press to get the result refreshed.

How to solve this problem to get the result automatically refreshed?

**MrShorty** · 01-10-2007, 02:01 AM

How are you accessing the information in "firstsheet?" If you are getting it within the UDF code (a statement like myvar=worksheets("firstsheet").cells(1,1).value), then your UDF won't automatically update. In this case, be sure to pass values to the UDF through the argument list (Function myfunc(arg1 as double)). When you pass variables through the argument list, Excel can put the function in its proper place in the dependency tree for each calculate event.

**MSP77079** · 01-10-2007, 09:50 AM

I haven't tried Mr Shorty's method, but I like it and will try is soon. Here are some other thoughts on the subject.

Option 1: add "Volatile" to the UDF. The downside of this is it will recalculate the UDF more often than you really need and slow down the workbook.

Option 2: instead of F9, you need to use Ctrl+Shift+F9 (according to Excel documentation). I find this does not work for me and I need to use Ctrl+Shift+Alt+F9. This takes all of my fingers on both hands!

To force UDF to recalculate via code, you would use:
Application.CalculateFull

Hope this helps.

**dominicb** · 01-10-2007, 11:56 AM

Hi MSP77079

instead of F9, you need to use Ctrl+Shift+F9 (according to Excel documentation)

This is a funny subject and I don't fully understand the ins and outs of it in detail but using Application.Volatile doesn't always work, for example use a UDF to detect a cell colour and it won't update if the cell changes (whether you make it volatile or not). You can press just F9 to refresh it.

However, some UDF's need a full recalc (Alt + Shift + Ctrl +F9). I wrote a UDF recently that dumps the range behind a named range into a cell and that needs a full recalc.

The moral, I guess, is that if F9 doesn't work, go for a full recalc. And if your UDF doesn't respond automatically to the Volatile command, take it out of the code, 'cos it uses up valuable system resources.

Just my thoughts...

DominicB

**nougain** · 01-10-2007, 01:47 PM

Please see the attached .xls file (after unzip). There is only one sheet. Select ID from drop down (cell A10), and you will see the value changing correctly in cell B10. Cell B10 uses a VBA UDF 'doit(integer)'.

Now lets assume I have selected ID as 2, so the result in B10 will show 20. So far so good. Now go to cell B3 and change value from 20 to 22. At this point what I want is to get it reflected *immediately* in cell B10 which is using doit(2) function. How to do it? Thanks

**dominicb** · 01-10-2007, 02:02 PM

Good evening nougain

You need to set the UDF as volatile, thus :

Please Login or Register  to view this content.

HTH

DominicB

Edit : BTW, why not just use a VLOOKUP function? Excel's native functions are far faster than a UDF ... ?

**MrShorty** · 01-10-2007, 03:33 PM

Some people say using "Application.volatile" is a crutch rather than a good fix. In this case, I think I would agree. If this were my function, I would pass datatablerange to the function.

function doit(partid as integer, datarangetable as range) as integer.
code
end function

Excel builds the dependency tree based on a functions argument list. Anything that you want to effect the auto-calculation needs to be passed to the function through the argument list.

Application.volatile will work, but I am of the opinion that one shouldn't get into the habit of using application.volatile unless necessary. One should get into the habit of passing necessary values to a UDF via the argument list.