Calling VBA function from a worksheet
 

I have a VBA function that I am using in several worksheets (i.e. - each
cell in column K says the following: =getvintagerate(A41), where
getvintagerate expects a variant and returns a double). When I change
dependent cells in the worksheets, the functions do not automatically
recalculate. I tried writing code that fires on the workbook_sheetchange
event and calculates all sheets, but when I do that all my cells that call
the VBA function return #VALUE!. If I go into each cell and recalc (go to
each cell, hit F2, then hit Enter) the functions return the correct value.

I don't know if this matters, but I am using Excel 2007.

Any ideas??????

Calling VBA function from a worksheet
 

Hi Mike,

You didn't give us the code of your UDF, so we can just guess.

But the most common cause is that you don't include all input in the argument list. If you access cells from within the function
directly (not via the argument list) then Excel isn't aware of the dependencies and will not recalculate.
Best solution: include all the inputs in the argument list.

It is often advised to include Application.Volatile to the function. However, I am still not sure that the cells will be
recalculated in the correct sequence (why should they, if Excel is not aware of the dependencies).

So best advice is still to include all input in the argument list.


"Mike" wrote in message ...
|
| I have a VBA function that I am using in several worksheets (i.e. - each
| cell in column K says the following: =getvintagerate(A41), where
| getvintagerate expects a variant and returns a double). When I change
| dependent cells in the worksheets, the functions do not automatically
| recalculate. I tried writing code that fires on the workbook_sheetchange
| event and calculates all sheets, but when I do that all my cells that call
| the VBA function return #VALUE!. If I go into each cell and recalc (go to
| each cell, hit F2, then hit Enter) the functions return the correct value.
|
| I don't know if this matters, but I am using Excel 2007.
|
| Any ideas??????