This is the UDF

Function ROll2D6()
ROll2D6 = Application.RoundUp(Rnd() * 6, 0) + Application.RoundUp(Rnd()
* 6, 0)
End Function

At current, when Excel recalculates, the random number generated by this
function, is replaced by another rnadom nuber.

Is there some sort of check that I can put into the UDF whereby IF the
current value of the cell (calling the function) is a number, it will return
that number instead of a new one.?

Thanks

You want the number to stay the same after it is entered, correct? One way
to do that would be to call it from a sub. In the same module you defined
the function, you could try something like this:

Sub RunRoll2D6()
Dim thecell As Range
Set thecell = ActiveCell

On Error Resume Next

If ActiveCell.Value = "" Then
thecell.Value = Roll2D6()
Range(ActiveCell).Copy thecell.PasteSpecial(xlPasteValues)
End If

End Sub


Then, call the sub instead of typing the function.

--
tj


"Adam Kroger @hotmail.com" wrote:

This is the UDF

Function ROll2D6()
ROll2D6 = Application.RoundUp(Rnd() * 6, 0) + Application.RoundUp(Rnd()
* 6, 0)
End Function

At current, when Excel recalculates, the random number generated by this
function, is replaced by another rnadom nuber.

Is there some sort of check that I can put into the UDF whereby IF the
current value of the cell (calling the function) is a number, it will return
that number instead of a new one.?

Thanks

Probbaly a stupid question but, how do I call the sub? the formula in the
worksheet is:
=IF(AA27="Y",Roll2D6(),"")

Adam


"tjtjjtjt" wrote in message
...
You want the number to stay the same after it is entered, correct? One
way
to do that would be to call it from a sub. In the same module you defined
the function, you could try something like this:

Sub RunRoll2D6()
Dim thecell As Range
Set thecell = ActiveCell

On Error Resume Next

If ActiveCell.Value = "" Then
thecell.Value = Roll2D6()
Range(ActiveCell).Copy thecell.PasteSpecial(xlPasteValues)
End If

End Sub


Then, call the sub instead of typing the function.

--
tj


"Adam Kroger @hotmail.com" wrote:

This is the UDF

Function ROll2D6()
ROll2D6 = Application.RoundUp(Rnd() * 6, 0) +
Application.RoundUp(Rnd()
* 6, 0)
End Function

At current, when Excel recalculates, the random number generated by this
function, is replaced by another rnadom nuber.

Is there some sort of check that I can put into the UDF whereby IF the
current value of the cell (calling the function) is a number, it will
return
that number instead of a new one.?

Thanks

You won't be able to do that with a function, and the sub won't work with the
formula you've written.
You could try a worksheet event. something like the code below in the
Worksheet_SelectionChange event might do what you want. (Right-click on the
sheet name and change the left drop-down to Worksheet.) It seems to me like
you are going to want something more flexible, though. Perhaps you could
provide more information about what you are trying to accomplish?

On Error Resume Next

If (Range("AA27").Value = "y" Or _
Range("AA27").Value = "Y") _
And Range("AB27") = "" Then
Range("AB27") = Val(Roll2D6)
End If

--
tj


"Adam Kroger @hotmail.com" wrote:

Probbaly a stupid question but, how do I call the sub? the formula in the
worksheet is:
=IF(AA27="Y",Roll2D6(),"")

Adam


"tjtjjtjt" wrote in message
...
You want the number to stay the same after it is entered, correct? One
way
to do that would be to call it from a sub. In the same module you defined
the function, you could try something like this:

Sub RunRoll2D6()
Dim thecell As Range
Set thecell = ActiveCell

On Error Resume Next

If ActiveCell.Value = "" Then
thecell.Value = Roll2D6()
Range(ActiveCell).Copy thecell.PasteSpecial(xlPasteValues)
End If

End Sub


Then, call the sub instead of typing the function.

--
tj


"Adam Kroger @hotmail.com" wrote:

This is the UDF

Function ROll2D6()
ROll2D6 = Application.RoundUp(Rnd() * 6, 0) +
Application.RoundUp(Rnd()
* 6, 0)
End Function

At current, when Excel recalculates, the random number generated by this
function, is replaced by another rnadom nuber.

Is there some sort of check that I can put into the UDF whereby IF the
current value of the cell (calling the function) is a number, it will
return
that number instead of a new one.?

Thanks

I think I have committed a newsgroup faux pas, in that I have 2 threads
going addressing this situation that I am having, and for that I apologize,
kinda a situation where I am exploring two different thought paths. The
other thread is subject lined "UDF help please." I guess where this one is
going has become more a question of , "What is a sub, and how do you use
it?" than "How can I make this function do what I want?" I was not really
aware that there was such a things as "sub" before you mentioned it and
would now like to know more about them. A sub may or may not be the best
way to deal with the situation I am currently exploring, but might have uses
further down the line. What I am specifically trying to accomplish now is:

Generate a random number in a given cell via a formula call from within that
cell. Once the random number is returned, I want it to remain THAT number
unless I specifically do something to make it recalculate (stop it form
changing every time Excel recalculates the sheet). The only way that I have
found to make that happen is if I reference a cell linked to a checkbox
(TRUE/FALSE), but that is not practicle for every application I want to do.
Additionally, I want to avoid circular references in the cell's formula, as
excel complains. I know I can turn off the circular refernce warnings, but
I would like to learn another way. One further wrinckle is that in one
instance of what I am trying to do, I do want it to generate a new random
number, IF a different cell has changed.

Basically, I am looking more for knowledge, than answers.


"tjtjjtjt" wrote in message
...
You won't be able to do that with a function, and the sub won't work with
the
formula you've written.
You could try a worksheet event. something like the code below in the
Worksheet_SelectionChange event might do what you want. (Right-click on
the
sheet name and change the left drop-down to Worksheet.) It seems to me
like
you are going to want something more flexible, though. Perhaps you could
provide more information about what you are trying to accomplish?

On Error Resume Next

If (Range("AA27").Value = "y" Or _
Range("AA27").Value = "Y") _
And Range("AB27") = "" Then
Range("AB27") = Val(Roll2D6)
End If

--
tj


"Adam Kroger @hotmail.com" wrote:

Probbaly a stupid question but, how do I call the sub? the formula in
the
worksheet is:
=IF(AA27="Y",Roll2D6(),"")

Adam


"tjtjjtjt" wrote in message
...
You want the number to stay the same after it is entered, correct? One
way
to do that would be to call it from a sub. In the same module you
defined
the function, you could try something like this:

Sub RunRoll2D6()
Dim thecell As Range
Set thecell = ActiveCell

On Error Resume Next

If ActiveCell.Value = "" Then
thecell.Value = Roll2D6()
Range(ActiveCell).Copy thecell.PasteSpecial(xlPasteValues)
End If

End Sub


Then, call the sub instead of typing the function.

--
tj


"Adam Kroger @hotmail.com" wrote:

This is the UDF

Function ROll2D6()
ROll2D6 = Application.RoundUp(Rnd() * 6, 0) +
Application.RoundUp(Rnd()
* 6, 0)
End Function

At current, when Excel recalculates, the random number generated by
this
function, is replaced by another rnadom nuber.

Is there some sort of check that I can put into the UDF whereby IF the
current value of the cell (calling the function) is a number, it will
return
that number instead of a new one.?

Thanks