Recalculating a custom function

[Fabian]

I have made a custom dice rolling function for my spreadsheet as below.
The problem is that it does not automatically update. Normally, having
randomize(1,6) or whatever will recalulate when i press F9, but to make
this recalculate, I have to go into the cell and press enter. Is there a
way to make this recalculate normally?


Function ROLL(count As Integer, die_size As Integer) As Integer

Dim i

For i = 1 To count Step 1

Randomize
ROLL = ROLL + Int((die_size * Rnd(1)) + 1)

Next i

End Function

 

[Frank Kabel]

Hi
add the line
application.volatile
to your function as first statement

 

[Jerry W. Lewis]

Frank has provided the solution. As for the reason, Excel only
recalculates a function when it needs to. Application.Volatile tells
Excel that it needs to whenever anything else is calculated in the
worksheet. Otherwise it needs to only when the arguments change. Thus
the function as originally posted would only recalculate if you changed
the count or the die_size.

Jerry

 

[Fabian]

Jerry W. Lewis hu kiteb:

Frank has provided the solution. As for the reason, Excel only
recalculates a function when it needs to. Application.Volatile tells
Excel that it needs to whenever anything else is calculated in the
worksheet. Otherwise it needs to only when the arguments change. Thus
the function as originally posted would only recalculate if you
changed the count or the die_size.

Thanks, both of you. I can always rely on this group to come through
with the goods.

 

[Jerry W. Lewis]

You're welcome.

Jerry

Jerry W. Lewis hu kiteb:



Thanks, both of you. I can always rely on this group to come through
with the goods.