How to make my VBA function volatile?

[Kelvin Stott]

Hello,

I have written a custom function in VBA that includes a random component, with VBAs RND function, but unfortunately it doesn't behave as a volatile function when I use it in my worksheet. For example, it doesn't automatically recalculate when I hit the delete key on a blank cell.

The best I have come up with is to use the following formula in my spreadsheet:

"= MyFunction() + 0*RAND()"

Is there any way to change the VBA code so that the function is volatile when I use it in a worksheet?

Thanks for any help,
Kelvin

 

[Kelvin Stott]

Silly me, I just realized I can call the RAND() function from the worksheet into the VBA function, instead of using VBA's RND function within the code itself, so I now have:

"= MyFunction(RAND())"

This makes it perfectly volatile!

 

[shg]

Another way:

Function MyFunction() as Whatever
    Application.Volatile True
    ' ...

 

[Kelvin Stott]

Very nice, even better than I needed in the end, thanks!

 

[shg]

You're welcome.

 

[Kelvin Stott]

Just tested, and it works perfectly, even when the VBA function is used within other VBA functions, all the dependent VBA functions automatically become volatile also!