custom functions

  • Thread starter Thread starter Roel
  • Start date Start date
R

Roel

Hi,

In VBA, when I define a user function and use it in the spreadsheet, it
does not seem to update. You have to manually go into the cell and re-input
the function name to get it to update when data has changed. Can you let it
auto-update as if it was a built-in Excel function?

Thanks,

Roel
 
Hi Roel,

Probably you did not include all the arguments in the argument list of the
function definition.
If you access cells directly from within a function, not via the argument
list, Excel does not know, so it doesn't know when to recalculate.
Including "Application.Volatile" may seem to help, but there are no
guarantees that cells will be recalculated in the correct order. Even if
they seem to do so, that is no guarantee for future releases of Excel.
The only correct way is to include all input to the function in the argument
list.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
Roel said:
Hi,

In VBA, when I define a user function and use it in the spreadsheet, it
does not seem to update. You have to manually go into the cell and re-input
the function name to get it to update when data has changed. Can you let it
auto-update as if it was a built-in Excel function?
Click to expand...

Add "Application.Volatile = True" in the code for your UDF.

Regards,
 
Back
Top