Concerned that the thread was bypassed after going 4 deep, I have reposted
it with full includes.

The way the macro works as currently written, it can return to multiple
cells; but in this instance, I just need it to return either the sum of 2
randomly generated numbers between 1 and 6 (simulating a dice roll), or a
single randomly generated number between 1 and 6, and then have the cell be
stable after that is done (not constantly recalculating with every chnage
made to the spreadsheet).


"Bob Phillips" wrote in message
...
That cannot be a UDF as you are trying to write to many cells, a UDF only
returns a result.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
. ..
what would I need to change to allow that UDF to be used in any cell?

This is what I want to run:

Sub ROll2D6()
Dim myCell As Range
Dim Sides As Integer
Dim Dies As Integer
Dim i As Integer
Dim myTemp As Integer

Sides = 6
Dies = 2
For Each myCell In Selection
Randomize
myTemp = 0
For i = 1 To Dies
myTemp = myTemp + Application.RoundUp(Rnd() * Sides, 0)
Next i
myCell.Value = myTemp
Next myCell
End Sub


"Paul B" wrote in message
...
Adam, not with a formula, but you can use a worksheet change event like
this, put in sheet code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If Target.Address = "$A$1" And UCase(Target.Value) = "Y" Then
'put your code here
End If
End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from
it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
. ..
Can you execute a macro from inside a formula?
IF(A1="Y",execute.macro,"")

Thanks

Adam,

In the previous threads you were talking about UDFing the sub. As we all
explained, the sub writes to however many cells that are in the selection,
and you cannot do that in a UDF. IF you now say that you just want to add
two randomly generated numbers, then try this

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


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
...
Concerned that the thread was bypassed after going 4 deep, I have reposted
it with full includes.

The way the macro works as currently written, it can return to multiple
cells; but in this instance, I just need it to return either the sum of 2
randomly generated numbers between 1 and 6 (simulating a dice roll), or a
single randomly generated number between 1 and 6, and then have the cell
be
stable after that is done (not constantly recalculating with every chnage
made to the spreadsheet).


"Bob Phillips" wrote in message
...
That cannot be a UDF as you are trying to write to many cells, a UDF
only
returns a result.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
. ..
what would I need to change to allow that UDF to be used in any cell?

This is what I want to run:

Sub ROll2D6()
Dim myCell As Range
Dim Sides As Integer
Dim Dies As Integer
Dim i As Integer
Dim myTemp As Integer

Sides = 6
Dies = 2
For Each myCell In Selection
Randomize
myTemp = 0
For i = 1 To Dies
myTemp = myTemp + Application.RoundUp(Rnd() * Sides, 0)
Next i
myCell.Value = myTemp
Next myCell
End Sub


"Paul B" wrote in message
...
Adam, not with a formula, but you can use a worksheet change event
like
this, put in sheet code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If Target.Address = "$A$1" And UCase(Target.Value) = "Y" Then
'put your code here
End If
End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from
it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
. ..
Can you execute a macro from inside a formula?
IF(A1="Y",execute.macro,"")

Thanks

I apologize for not being clearer from the outset., and thanks.

1 problem though, it is returning numbers outside of the 2 - 12 range.



"Bob Phillips" wrote in message
...
Adam,

In the previous threads you were talking about UDFing the sub. As we all
explained, the sub writes to however many cells that are in the selection,
and you cannot do that in a UDF. IF you now say that you just want to add
two randomly generated numbers, then try this

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


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
...
Concerned that the thread was bypassed after going 4 deep, I have
reposted
it with full includes.

The way the macro works as currently written, it can return to multiple
cells; but in this instance, I just need it to return either the sum of 2
randomly generated numbers between 1 and 6 (simulating a dice roll), or a
single randomly generated number between 1 and 6, and then have the cell
be
stable after that is done (not constantly recalculating with every chnage
made to the spreadsheet).


"Bob Phillips" wrote in message
...
That cannot be a UDF as you are trying to write to many cells, a UDF
only
returns a result.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
. ..
what would I need to change to allow that UDF to be used in any cell?

This is what I want to run:

Sub ROll2D6()
Dim myCell As Range
Dim Sides As Integer
Dim Dies As Integer
Dim i As Integer
Dim myTemp As Integer

Sides = 6
Dies = 2
For Each myCell In Selection
Randomize
myTemp = 0
For i = 1 To Dies
myTemp = myTemp + Application.RoundUp(Rnd() * Sides, 0)
Next i
myCell.Value = myTemp
Next myCell
End Sub


"Paul B" wrote in message
...
Adam, not with a formula, but you can use a worksheet change event
like
this, put in sheet code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If Target.Address = "$A$1" And UCase(Target.Value) = "Y" Then
'put your code here
End If
End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit
from
it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
. ..
Can you execute a macro from inside a formula?
IF(A1="Y",execute.macro,"")

Thanks

I figured it out. I changed the &_ to "+" and it seems to work, at least it
isn't returning illegal values anymore.. It looks like it was multiplying
before. Am I correct?


"Bob Phillips" wrote in message
...
Adam,

In the previous threads you were talking about UDFing the sub. As we all
explained, the sub writes to however many cells that are in the selection,
and you cannot do that in a UDF. IF you now say that you just want to add
two randomly generated numbers, then try this

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


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
...
Concerned that the thread was bypassed after going 4 deep, I have
reposted
it with full includes.

The way the macro works as currently written, it can return to multiple
cells; but in this instance, I just need it to return either the sum of 2
randomly generated numbers between 1 and 6 (simulating a dice roll), or a
single randomly generated number between 1 and 6, and then have the cell
be
stable after that is done (not constantly recalculating with every chnage
made to the spreadsheet).


"Bob Phillips" wrote in message
...
That cannot be a UDF as you are trying to write to many cells, a UDF
only
returns a result.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
. ..
what would I need to change to allow that UDF to be used in any cell?

This is what I want to run:

Sub ROll2D6()
Dim myCell As Range
Dim Sides As Integer
Dim Dies As Integer
Dim i As Integer
Dim myTemp As Integer

Sides = 6
Dies = 2
For Each myCell In Selection
Randomize
myTemp = 0
For i = 1 To Dies
myTemp = myTemp + Application.RoundUp(Rnd() * Sides, 0)
Next i
myCell.Value = myTemp
Next myCell
End Sub


"Paul B" wrote in message
...
Adam, not with a formula, but you can use a worksheet change event
like
this, put in sheet code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If Target.Address = "$A$1" And UCase(Target.Value) = "Y" Then
'put your code here
End If
End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit
from
it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
. ..
Can you execute a macro from inside a formula?
IF(A1="Y",execute.macro,"")

Thanks

& means to concatenate--usually used when you want to join strings:

msgbox "Your bill is due: " & format(date,"mm/dd/yyyy")

But you had two dice (say 5 and 4) and just jammed them together to get: 54.



"Adam Kroger

I figured it out. I changed the &_ to "+" and it seems to work, at least it
isn't returning illegal values anymore.. It looks like it was multiplying
before. Am I correct?

"Bob Phillips" wrote in message
...
Adam,

In the previous threads you were talking about UDFing the sub. As we all
explained, the sub writes to however many cells that are in the selection,
and you cannot do that in a UDF. IF you now say that you just want to add
two randomly generated numbers, then try this

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


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
...
Concerned that the thread was bypassed after going 4 deep, I have
reposted
it with full includes.

The way the macro works as currently written, it can return to multiple
cells; but in this instance, I just need it to return either the sum of 2
randomly generated numbers between 1 and 6 (simulating a dice roll), or a
single randomly generated number between 1 and 6, and then have the cell
be
stable after that is done (not constantly recalculating with every chnage
made to the spreadsheet).


"Bob Phillips" wrote in message
...
That cannot be a UDF as you are trying to write to many cells, a UDF
only
returns a result.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
. ..
what would I need to change to allow that UDF to be used in any cell?

This is what I want to run:

Sub ROll2D6()
Dim myCell As Range
Dim Sides As Integer
Dim Dies As Integer
Dim i As Integer
Dim myTemp As Integer

Sides = 6
Dies = 2
For Each myCell In Selection
Randomize
myTemp = 0
For i = 1 To Dies
myTemp = myTemp + Application.RoundUp(Rnd() * Sides, 0)
Next i
myCell.Value = myTemp
Next myCell
End Sub


"Paul B" wrote in message
...
Adam, not with a formula, but you can use a worksheet change event
like
this, put in sheet code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
If Target.Address = "$A$1" And UCase(Target.Value) = "Y" Then
'put your code here
End If
End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit
from
it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Adam Kroger @hotmail.com" <adam_kroger<nospam wrote in message
. ..
Can you execute a macro from inside a formula?
IF(A1="Y",execute.macro,"")

Thanks














--

Dave Peterson